Hello,
I am running a PL/SQL code using a variable for table name that changes with each loop iteration and I bulk collect results into another table.
Here is the statement that I have:
The statement below works fine
EXECUTE IMMEDIATE 'select * from ' || CompTblName || ' ' BULK COLLECT INTO SEGTBL;
however, I would like to refine the results by using a where filter as shown:
EXECUTE IMMEDIATE 'select * from ' || CompTblName || ' where line = '2' ' BULK COLLECT INTO SEGTBL;
note that line is a column name in the table CompTblName.
The error that I am getting is shown below:
Error report:
ORA-06550: line 20, column 74:
PLS-00103: Encountered the symbol "2" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
The symbol "* was inserted before "2" to continue.
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Here is complete PL/SQL Code:
DECLARE
ROWNUMBER PLS_INTEGER;
CompTblName varchar2(20);
COMPTBLNAMEHARDCODE varchar2(20);
CURSOR c1 IS
SELECT owner, table_name FROM ALL_TABLES WHERE OWNER LIKE 'MTO_W%' AND TABLE_NAME = 'PDTABLE_12';
CURSOR c2 IS SELECT * FROM mto_wgc.pdtable_12;
TYPE SEGMENTTBLTYPE IS TABLE OF c2%ROWTYPE INDEX BY PLS_INTEGER;
SEGTBL SEGMENTTBLTYPE;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE
('Owner = ' || item.owner || ', Table = ' || item.table_name);
CompTblName := concat(item.owner, concat('.', item.table_name));
DBMS_OUTPUT.PUT_LINE
(comptblname);
EXECUTE IMMEDIATE 'select * from ' || CompTblName || ' ' BULK COLLECT INTO SEGTBL;
END LOOP;
END;