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 Moreselect 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 MoreIn 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 MoreTo 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 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 MoreIntroduction/ Issue: CSV blob file to raw file move from database table blob column to database directory. Why we need to do / Cause of the issue: We can move from blob to raw CSV file from database table blob column to database directory. How do we solve: Step 1: Create one database procedure PROCEDURE P_FILE_TO_DBSER (p_blob IN BLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) AS l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER; BEGIN l_blob_len := DBMS_LOB.getlength(p_blob); — Open the destination file. l_file := UTL_FILE.fopen(p_dir, p_filename,’wb’, 32767); — Read chunks of the BLOB and write them to the file until complete. WHILE l_pos <= l_blob_len LOOP DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; — Close the file. UTL_FILE.fclose(l_file); …
Read MoreIntroduction/ Issue: CSV file move from client local machine to database table blob column. Why we need to do / Cause of the issue: We can store the CSV file from client local machine to database table blob column. How do we solve: Step 1: Create new table in database CREATE TABLE TB_FILE_UPLOAD (ID NUMBER, FILE_DATA BLOB); Step 2: Create new sequence in database CREATE SEQUENCE FILE_UPLOAD_SEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER NOKEEP GLOBAL; Step 3: In oracle form builder create on button and write the below code in WHEN-BUTTON-PRESSED trigger. DECLARE v_seq number; v_file_path varchar2(500) := ‘C:\test_file.csv’; v_bool boolean; BEGIN SELECT FILE_UPLOAD_SEQ .NEXTVAL INTO v_seq FROM DUAL; INSERT INTO TB_FILE_UPLOAD (ID, file_data) VALUES (v_seq, NULL); IF v_file_path IS NOT NULL THEN…
Read MoreSQL Server execution plans are valuable tools for developers and database administrators aiming to optimize query performance.
Read MoreWe all have noticed how some SQL Server queries feel like a luxury express train, zooming to their destination, while others resemble a sluggish car through traffic jams?
Read MoreTransactional replication in SQL Server is a robust feature that allows you to replicate data from a primary database (publisher) to one or more secondary databases (subscribers). While this is…
Read More