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;
