CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Tikkaty
Tikkaty

Reputation: 802

How do I convert SQL code to PL/SQL for an APEX process

I've got the below code which works perfectly in SQL Developer, but I need to input the code within a process block in APEX and it is only giving me a PL/SQL option. Below is the code which I've written:

BEGIN
truncate table TEMP_UPLOAD;
Merge into INVOICE b
USING (
 SELECT CUSTOMER_CLASS,RULE_AGGREGATOR,BA
 FROM CUSTOMER_TEMP_UPLOAD
 WHERE CUSTOMER_CLASS = 'CUSTOMER88') u
ON (b.BA = u.BA)
WHEN MATCHED THEN UPDATE SET b.CUSTOMER88_DATE_UPDATED = sysdate
WHEN NOT MATCHED THEN
  INSERT (b.CUSTOMER_CLASS,b.RULE_AGGREGATOR,b.BA,b.CUSTOMER88_DATE_ADDED)
  VALUES (u.CUSTOMER_CLASS,u.RULE_AGGREGATOR,u.BA,sysdate);

UPDATE INVOICE a 
    SET a.CUSTOMER88_DATE_REMOVED = sysdate
    WHERE BA IN 
        (select b.BA 
 from INVOICE b 
 left join CUSTOMER_temp_upload u 
 on b.BA = u.BA 
 where u.BA is null and b.CUSTOMER_CLASS = 'CUSTOMER88');
 END;

Getting following error

1 error has occurred
•ORA-06550: line 3, column 14: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ;

enter image description here

Upvotes: 0

Views: 309

Answers (2)

Tikkaty
Tikkaty

Reputation: 802

Seems that the issue was it did not like my BEGIN and END; in uppercase. When I changed it to Begin and end; as well as changed the TRUNCATE table command to a delete, it then accepted it and the PL/SQL command worked as intended.

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

The error message is pointing you to your TRUNCATE TABLE command.

TRUNCATE is a DDL command - don't call it from PL/SQL. Instead, use a DELETE so that your process will be transaction-safe.

(P.S. it is technically possible to run DDL from PL/SQL using EXECUTE IMMEDIATE - but I don't advise it)

Upvotes: 3

Related Questions