Pre-requisite for the Sales Orders purging
Introduction In the oracle EBS the concept of Purging and before purging the order management related tables, pre-requisite to be done for the smooth purging process. The below script will…
Read MoreItem Where-Used Analysis Report (Excel – PL/SQL)
Introduction: This blog has the SQL query that can be used for Item where used Report in Oracle Apps R12. Cause of the issue: Business wants a PLSQL excel based report…
Read MoreCentralized Content Management with APEX Shortcuts
Introduction: In Oracle APEX 21.2, managing label content across multiple pages can become inefficient and error-prone when updates are required. To centralize control and ensure consistency, we propose using APEX…
Read MoreThe Oracle Migration Challenge – Datatype Conversion Nightmares
When Database Migrations Go Wrong: Snowflake to Oracle Every database migration project starts with optimism: “We’ve mapped the tables, the data looks similar – how hard can it be to…
Read MoreFrom Slow to Lightning Fast: Optimizing SQL Bulk Operations with Python’s executemany()
The Problem: When Good Code Goes Slow Picture this: You have a Python application that needs to insert thousands of records into a database. Your code works perfectly for small…
Read MoreOracle PLSQL to Generate XML Tag Using Standard Functionality
declare l_ctx dbms_xmlquery.ctxHandle; l_clob clob; begin l_ctx := dbms_xmlquery.newContext(‘select * from <TABLE_NAME>’); dbms_lob.createtemporary(:g_clob,true,dbms_lob.session); :g_clob := dbms_xmlquery.getXml(l_ctx); end;
Read MoreOracle R12 SQL Query – Price List or Fee Schedule for Customer
select HP.PARTY_NAME “Customer Name”,HCAA.ACCOUNT_NUMBER,HCAA.ACCOUNT_NAME,qph.name “Price List Name”,HCSUA.SITE_USE_CODE,HCSUA.LOCATION from qp_list_headers qph,APPS.HZ_CUST_ACCOUNTS_ALL HCAA,APPS.HZ_CUST_ACCT_SITES_ALL HCASA,APPS.HZ_CUST_SITE_USES_ALL HCSUA,APPS.HZ_PARTIES HPWhere hcaa.account_number = –Customer Account Numberand HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_IDand HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_IDAND HCSUA.PRICE_LIST_ID = QPH.LIST_HEADER_IDAND HCAA.PARTY_ID=HP.PARTY_ID
Read MoreStreams in Snowflake
In snowflake, Streams are a powerful feature designed for tracking changes (like inserts, updates, and deletes) to a table. They allow you to capture data changes in realtime and use this data for various purposes, such as incremental loading, change data capture (CDC), or auditing. Here’s an overview of Streams in Snowflake and how to use them: What is a Stream in Snowflake? A Stream in Snowflake is an object that tracks changes (DML operations like INSERT, UPDATE, and DELETE) made to a table. It captures these changes in a special table that is internally managed by Snowflake, and these changes can then be queried. A Stream has two important components: Change tracking:It captures DML changes, including the type of change (insert, update, delete). Metadata:Information such as the row’s previous state for updates, and how many rows have changed since the last time the stream was queried. How to Create a Stream You can create a stream on a table using the CREATE STREAM command. Basic Syntax: CREATE OR REPLACE STREAM <stream_name> ON TABLE <table_name> [ SHOW_INITIAL_ROWS = TRUE | FALSE ]; SHOW_INITIAL_ROWS: If set to TRUE, it will include existing rows in the stream at…
Read MoreSnowpipe (a continuous data ingestion pipeline) in Snowflake
To create a new Snowpipe (a continuous data ingestion pipeline) in Snowflake, follow these steps: Prerequisites Storage Integration: Set up a cloud storage integration (AWS S3, Azure Blob, GCP) to securely connect Snowflake to your cloud storage. Stage: Create an external stage pointing to your cloud storage (if not already created). Target Table: Ensure the destination table exists in Snowflake. Create a Stage (if needed) Define a stage pointing to your cloud storage location. Example for AWS S3: CREATE OR REPLACE STAGE my_s3_stage URL = ‘s3://your-bucket/path/’ STORAGE_INTEGRATION = my_storage_integration FILE_FORMAT = my_file_format; — (e.g., CSV, JSON) Create a Pipe A pipe uses the…
Read MoreDeterministic Functions in Oracle
Deterministic Functions in Oracle Overview A deterministic function in Oracle is a function that always returns the same result for the same input values and has no side effects.…
Read More