Dynamic SQL - Real time scenario (Execute Immediate)

 Execute Immediate - Real time scenario.

I want to delete AR Invoice. I have the AR Invoice Number.

Here if we want to delete the AR Invoice, we have to delete first from the below tables.

Sequence to be followed

1. Distributions table.

delete from ra_cust_trx_line_gl_dist_all where customer_trx_id = '2127173';

2. Invoice Lines table

delete from ra_customer_trx_lines_all where customer_trx_id = '2127173';

3. Invoice table

delete from ra_customer_trx_all where trx_number = 'PREBILL-4579' ;

When I run this command, it is getting error due to some trigger issue on distributions table.

I tried to Alter the trigger to Disable, execute the commands and then Enable the trigger.

Anonymous Block:

begin

ALTER TRIGGER RA_CUST_TRX_LINE_GL_DIST_BRI DISABLE;

delete from ra_cust_trx_line_gl_dist_all where customer_trx_id = '2127173';

delete from ra_customer_trx_lines_all where customer_trx_id = '2127173';

delete from ra_customer_trx_all where trx_number = 'PREBILL-4579' ;

ALTER TRIGGER RA_CUST_TRX_LINE_GL_DIST_BRI ENABLE;

COMMIT;

end;

Error Message

Encountered the symbol "ALTER" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe.

Solution

You cannot issue DDL as static Sql in a PL/SQl Block. If you want to put these commands in a

Pl/sql block, you need Dynamic SQL.

begin 

 EXECUTE IMMEDIATE 'ALTER TRIGGER RA_CUST_TRX_LINE_GL_DIST_BRI DISABLE';

delete from ra_cust_trx_line_gl_dist_all where customer_trx_id = '2127173'; 

delete from ra_customer_trx_lines_all where customer_trx_id = '2127173'; 

 delete from ra_customer_trx_all where trx_number = 'PREBILL-4579' ; 

EXECUTE IMMEDIATE 'ALTER TRIGGER RA_CUST_TRX_LINE_GL_DIST_BRI ENABLE'; 

COMMIT; 

 end;