Data Target - Database SQL Examples
Example : Running a Merge Query in Oracle Database.
Insert and Update at same time in ORDERS_TEST table
t is a alias for target table
s is a alias for source table
This will go inside the SQL Statment of Database Target
"""MERGE INTO ORDERS_TEST t
USING (
SELECT
? AS AMAZON_ORDER_ID,
TO_DATE(?, 'YYYY-MM-DD') AS LAST_SHIP_DATE,
? AS ORDER_TYPE,
TO_DATE(?, 'YYYY-MM-DD') AS PURCHASE_DATE,
? AS BUYER_EMAIL,
TO_DATE(?, 'YYYY-MM-DD') AS AMZ_LAST_UPDATE_DATE,
? AS IS_REPLACEMENT_ORDER,
? AS NUM_ITEMS_SHIPPED,
? AS SHIPMENT_SERVICE_LEVEL,
? AS ORDER_STATUS,
? AS SALES_CHANNEL,
? AS IS_BUSINESS_ORDER,
? AS NUM_ITEMS_UNSHIPPED,
? AS GLOBAL_EXPRESS_ENABLED,
? AS IS_SOLDBY_AB,
? AS IS_PREMIUM_ORDER,
? AS ORDER_TOTAL_AMOUNT,
? AS ORDER_TOTAL_CURRENCY,
TO_DATE(?, 'YYYY-MM-DD') AS EARLIEST_SHIP_DATE,
? AS MARKETPLACE_ID,
? AS FULFILLMENT_CHANNEL,
? AS PAYMENT_METHOD,
? AS SHIPPING_CITY,
? AS SHIPPING_POSTAL,
? AS SHIPPING_STATE,
? AS SHIPPING_COUNTRY,
? AS IS_ISPU,
? AS IS_PRIME,
? AS SELLER_ORDER_ID,
? AS SHIPMENT_SERVICE_CATEGORY,
? AS NEXTTOKEN
FROM dual
) s
ON (t.AMAZON_ORDER_ID = s.AMAZON_ORDER_ID)
WHEN MATCHED THEN
UPDATE SET
t.LAST_SHIP_DATE = s.LAST_SHIP_DATE,
t.ORDER_TYPE = s.ORDER_TYPE,
t.PURCHASE_DATE = s.PURCHASE_DATE,
t.BUYER_EMAIL = s.BUYER_EMAIL,
t.AMZ_LAST_UPDATE_DATE = s.AMZ_LAST_UPDATE_DATE,
t.IS_REPLACEMENT_ORDER = s.IS_REPLACEMENT_ORDER,
t.NUM_ITEMS_SHIPPED = s.NUM_ITEMS_SHIPPED,
t.SHIPMENT_SERVICE_LEVEL = s.SHIPMENT_SERVICE_LEVEL,
t.ORDER_STATUS = s.ORDER_STATUS,
t.SALES_CHANNEL = s.SALES_CHANNEL,
t.IS_BUSINESS_ORDER = s.IS_BUSINESS_ORDER,
t.NUM_ITEMS_UNSHIPPED = s.NUM_ITEMS_UNSHIPPED,
t.GLOBAL_EXPRESS_ENABLED = s.GLOBAL_EXPRESS_ENABLED,
t.IS_SOLDBY_AB = s.IS_SOLDBY_AB,
t.IS_PREMIUM_ORDER = s.IS_PREMIUM_ORDER,
t.ORDER_TOTAL_AMOUNT = s.ORDER_TOTAL_AMOUNT,
t.ORDER_TOTAL_CURRENCY = s.ORDER_TOTAL_CURRENCY,
t.EARLIEST_SHIP_DATE = s.EARLIEST_SHIP_DATE,
t.MARKETPLACE_ID = s.MARKETPLACE_ID,
t.FULFILLMENT_CHANNEL = s.FULFILLMENT_CHANNEL,
t.PAYMENT_METHOD = s.PAYMENT_METHOD,
t.SHIPPING_CITY = s.SHIPPING_CITY,
t.SHIPPING_POSTAL = s.SHIPPING_POSTAL,
t.SHIPPING_STATE = s.SHIPPING_STATE,
t.SHIPPING_COUNTRY = s.SHIPPING_COUNTRY,
t.IS_ISPU = s.IS_ISPU,
t.IS_PRIME = s.IS_PRIME,
t.SELLER_ORDER_ID = s.SELLER_ORDER_ID,
t.SHIPMENT_SERVICE_CATEGORY = s.SHIPMENT_SERVICE_CATEGORY,
t.NEXTTOKEN = s.NEXTTOKEN
WHEN NOT MATCHED THEN
INSERT (
AMAZON_ORDER_ID,
LAST_SHIP_DATE,
ORDER_TYPE,
PURCHASE_DATE,
BUYER_EMAIL,
AMZ_LAST_UPDATE_DATE,
IS_REPLACEMENT_ORDER,
NUM_ITEMS_SHIPPED,
SHIPMENT_SERVICE_LEVEL,
ORDER_STATUS,
SALES_CHANNEL,
IS_BUSINESS_ORDER,
NUM_ITEMS_UNSHIPPED,
GLOBAL_EXPRESS_ENABLED,
IS_SOLDBY_AB,
IS_PREMIUM_ORDER,
ORDER_TOTAL_AMOUNT,
ORDER_TOTAL_CURRENCY,
EARLIEST_SHIP_DATE,
MARKETPLACE_ID,
FULFILLMENT_CHANNEL,
PAYMENT_METHOD,
SHIPPING_CITY,
SHIPPING_POSTAL,
SHIPPING_STATE,
SHIPPING_COUNTRY,
IS_ISPU,
IS_PRIME,
SELLER_ORDER_ID,
SHIPMENT_SERVICE_CATEGORY,
NEXTTOKEN
) VALUES (
s.AMAZON_ORDER_ID,
s.LAST_SHIP_DATE,
s.ORDER_TYPE,
s.PURCHASE_DATE,
s.BUYER_EMAIL,
s.AMZ_LAST_UPDATE_DATE,
s.IS_REPLACEMENT_ORDER,
s.NUM_ITEMS_SHIPPED,
s.SHIPMENT_SERVICE_LEVEL,
s.ORDER_STATUS,
s.SALES_CHANNEL,
s.IS_BUSINESS_ORDER,
s.NUM_ITEMS_UNSHIPPED,
s.GLOBAL_EXPRESS_ENABLED,
s.IS_SOLDBY_AB,
s.IS_PREMIUM_ORDER,
s.ORDER_TOTAL_AMOUNT,
s.ORDER_TOTAL_CURRENCY,
s.EARLIEST_SHIP_DATE,
s.MARKETPLACE_ID,
s.FULFILLMENT_CHANNEL,
s.PAYMENT_METHOD,
s.SHIPPING_CITY,
s.SHIPPING_POSTAL,
s.SHIPPING_STATE,
s.SHIPPING_COUNTRY,
s.IS_ISPU,
s.IS_PRIME,
s.SELLER_ORDER_ID,
s.SHIPMENT_SERVICE_CATEGORY,
s.NEXTTOKEN
)
"""
Database Target Troubleshoot
Not able to update or truncate the records in RDBMS tables like Oracle, MSSQL etc.
Below is the example as how you can overcome the update issue in Oracle Database.
Assume a user X is updating a record in an application with a username appuser and at the same time user Y also is updating the same record by using the same username as appuser . In such case there will be two session for making update. At this stage the Oracle database will go into locking mode and you will get error like below and the update will not happen.
ORA-00060: deadlock detected while waiting for resource
If one of the user commits, then the lock will get open and then update will happen. In many cases the sessions get locks when user uses application like Oracle sql developer , TOAD or DBeaver.
To check is the session is open and locked, use the below sql. At any given time the below query response should be of 0 records. If there are 0 records then the update statement and truncate statement will perfectly
SELECT s.sid, s.serial#, s.username, s.program, s.machine
FROM v$session s
WHERE s.sid IN (
SELECT DISTINCT l.sid
FROM v$lock l
JOIN dba_objects o ON l.id1 = o.OBJECT_ID
WHERE o.OBJECT_NAME = 'XXMRD_AFN_LIST_ORDERS_TEST' AND o.OBJECT_TYPE = 'TABLE'
);
To kill the user session, use the blow sql
--1551 is sid and 4184 is serial#
ALTER SYSTEM KILL SESSION '1551,4184';
commit;