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 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 MoreIntroduction; The Data Migration Assistant (DMA) tool is a Microsoft utility designed to simplify the migration of databases from older SQL Server versions or on-premises environments to newer versions or…
Read MoreMove concurrent program from One Instance to Another Instance having same name as old instance. Working as same as under old instance same responsibility and request group Step 1) we…
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 MoreIntroduction: This SQL query is used to fetch the Holidays list for Employees like Employee number, Holiday name, Start date, End date etc… Cause of the issue: Business wants a…
Read More