v_ENV := f_get_schema ('SC1');
v_LINK := f_get_dblink ('DB1');
'DELETE FROM mytable1
WHERE mem_type = ''PH''
AND ID IN ( select id from '
|| ' )';
This was working fine until I had to change a cursor in the procedure. Is there a way to pass a schema and link to a procedure? I have done this with variables but never with these components, so I am a bit stumped.Any ideas so I can get rid of the hardcoded schema and link name? Otherwise the original effort above is diminished.
WHERE id LIKE 'XXX%';
1) You can do this using a REF CURSOR. In that way you can open a cursor with a string.
https://oracle-base.com/articles/8i/ ... hp#cursors
2) You may prefer to accomplish this with conditional compilation, which would allow you to use static SQL, but maintain a single code base.
https://oracle-base.com/articles/10g ... -10gr2.php
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 2 guests