Dynamic SQL

[gpacheco]

Hey guys. Good afternoon everyone.
I'm new with PL / SQL programming and I'm in need of help.

I need to create a procedure to delete data and run Commit to each x line numbers. Good so far it was quiet, but my idea is to create a variable of type (vtbl) where I can pass the name of the table dynamically. I did this and I performed the command. It worked partly. I will put the first procedure I did and the second with the change.

- p1

Select all

1 SET SERVEROUTPUT ON  
2 DECLARE 
3 TYPE RI IS TABLE OF ROWID; 
4  ROWIDS RI; 
5  linhas INTEGER :=0; 
6  linhascommmit INTEGER:=20000; 
7  vTBL VARCHAR2(20):='C_L_PRSN_DTL'; 
8 BEGIN 
9  SELECT ROWID BULK COLLECT INTO ROWIDS FROM [color=#BF0000]C_L_PRSN_DTL[/color] WHERE SRC_SYS_BU = (SELECT * FROM   OW_MDMDEV.TBL_DQ_CONTROL_BU); 
10  FOR I in ROWIDS.FIRST .. ROWIDS.LAST  
11   LOOP  
12      EXECUTE IMMEDIATE 'DELETE ' || vTBL ||' WHERE ROWID = '''||ROWIDS(I)||''''; 
13       IF MOD(linhas,linhascommmit)=0 then  
14          COMMIT; 
15       END IF; 
16      linhas := linhas + 1; 
17    END LOOP; 
18 END;

- P2

Select all

1 SET SERVEROUTPUT ON  
2 DECLARE 
3  TYPE RI IS TABLE OF ROWID; 
4  ROWIDS RI; 
5  linhas INTEGER :=0; 
6  linhascommmit INTEGER:=20000; 
7  vTBL VARCHAR2(20):='C_L_PRSN_DTL'; 
8 BEGIN 
9  SELECT ROWID BULK COLLECT INTO ROWIDS FROM vTBL WHERE SRC_SYS_BU = (SELECT * FROM OW_MDMDEV.TBL_DQ_CONTROL_BU); 
10  FOR I in ROWIDS.FIRST .. ROWIDS.LAST  
11    LOOP  
12      EXECUTE IMMEDIATE 'DELETE ' || vTBL ||' WHERE ROWID = '''||ROWIDS(I)||''''; 
13        IF MOD(linhas,linhascommmit)=0 then  
14          COMMIT; 
15       END IF; 
16      linhas := linhas + 1; 
17    END LOOP; 
18 END;

99]] The difference from @ p1 to @ p2 is that in line 9 where it had the name of the table I put the variable. When I do it returns the message

Select all

PL/SQL: ORA-00942: table or view does not exist. 

I believe it is because, the variable value is not a bank object in the case (table). So I tried to do another test, see the following example:

- P3

Select all

1 SET SERVEROUTPUT ON  
2 DECLARE 
3  TYPE RI IS TABLE OF ROWID; 
4  ROWIDS RI; 
5  linhas INTEGER :=0; 
6  linhascommmit INTEGER:=20000; 
7  vTBL VARCHAR2(20):='C_L_PRSN_DTL'; 
8 BEGIN 
9  EXECUTE IMMEDIATE 'SELECT ROWID BULK COLLECT INTO ROWIDS FROM '|| vTBL ||' WHERE SRC_SYS_BU = (SELECT * FROM OW_MDMDEV.TBL_DQ_CONTROL_BU)'; 
10  FOR I in ROWIDS.FIRST .. ROWIDS.LAST  
11    LOOP  
12      EXECUTE IMMEDIATE 'DELETE ' || vTBL ||' WHERE ROWID = '''||ROWIDS(I)||''''; 
13        IF MOD(linhas,linhascommmit)=0 then  
14          COMMIT; 
15       END IF; 
16      linhas := linhas + 1; 
17    END LOOP; 
18 END;

believing that I had a change in the line 9, where I put a dynamic SQL, but it did not work. I imagined that logic would be the same as I used in line 12.

Thanks for everyone's attention.
Strong hug.

Note: Attached the file contains the command.

Giovani Winter: D: D

[stcoutinho]

Hello Giovanni,

All right?

I believe that the most correct would be to use the dynamic SQL concept by opening a cursor based on a dynamic queries.
See if you can take some ideas from these articles:
[[0] https://oracle-base.com/articles/8i/native-dynamic-sql
Hugs,

99]] Sergio Coutinho