Data Target - Database SQL Examples
SQL Actions is Insert
This will go inside the SQL Statment of Database Target
"""Insert into table_name
(column1,column2,column3,column4) values
(?,?,?,?)""","column1","column2","column3","column4"
SQL Actions is Update
This will go inside the SQL Statment of Database Target
"""UPDATE table_name SET PICKED_STATUS = 'Y',
column_name = systimestamp where column1= ? and column2= ? and column3= ?
and column4= ?""",
"column1", "column2", "column3", "column4"
SQL Actions is PLSQL Procedure with tuple
This will go inside the SQL Statment of Database Target
"""DECLARE
column_name VARCHAR2 (4000);
BEGIN
apps.xxxxxxx.insert_prc(
column_1 => ?
,column_2 => ?
,column_3 => ?
);
EXCEPTION WHEN OTHERS THEN
OUTPUT.PUT_LINE ('xxxxxx ' || xxxxx);
END;""","column_1","column_2","column_3"
SQL Actions are not Insert, Update like Delete or Running a PLSQL Procedure
This will go inside the SQL Statment of Database Target
The field Order Set of Values (Tuple Key) should be empty in the Database Target
"""DECLARE
column_name varchar2(1000); BEGIN
END;"""
SQL Action is Upsert or Merge
Below is the example of 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 is also 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 work 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 = 'YOUR_TABLE_NAME' 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;