pl/sql variable replace issue
declare
-- should pass in the diff. schemas. How to do that???
v1 varchar2(100) := "'SYSTEM','SCOTT'";
CURSOR tbl_cur IS
select table_name from dba_tables where owner in (v1);
Begin
for i in tbl_cur
loop
dbms_output.put_line(i);
-- expecting to print all table names in scott and system
end loop;
end;
/
Get a lot of errors. For example,
...
PLS-00201: identifier ''SYSTEM','SCOTT'' must be declared
...
Questions:
1) how to pass in the variables with ','?
2) how to replace the variables in the sql statement?
-- should pass in the diff. schemas. How to do that???
v1 varchar2(100) := "'SYSTEM','SCOTT'";
CURSOR tbl_cur IS
select table_name from dba_tables where owner in (v1);
Begin
for i in tbl_cur
loop
dbms_output.put_line(i);
-- expecting to print all table names in scott and system
end loop;
end;
/
Get a lot of errors. For example,
...
PLS-00201: identifier ''SYSTEM','SCOTT'' must be declared
...
Questions:
1) how to pass in the variables with ','?
2) how to replace the variables in the sql statement?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
dynamic sql carries its own overhead. Like instr it can also work, but your dba will hate you for using string concatenation of literals instead of using bind variables
ASKER
Excellent!!