Dynamically controlling schema and dblink in cursor

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Dynamically controlling schema and dblink in cursor

Postby mode09 » Tue Oct 15, 2013 7:20 pm

Hello I have written some stored procedures that use logic to lookup schema and dblinks based on whatever environment they are compiled in, because these are not consistent across all environments I am working in. This way I only have to have the functions maintenanced to set to return the correct data.

Example:
v_ENV := f_get_schema ('SC1');
v_LINK := f_get_dblink ('DB1');

EXECUTE IMMEDIATE
'DELETE FROM mytable1
WHERE mem_type = ''PH''
AND ID IN ( select id from '
|| v_ENV
|| '.mytable2'
|| '@'
|| v_LINK
|| ' )';

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.

CURSOR cx
IS
SELECT f1,
f2,
f3,
f4
FROM DASCHEMA.mst_ib_attr_val@DALINK
WHERE id LIKE 'XXX%';
mode09
Member
 
Posts: 17
Joined: Wed Jul 16, 2008 3:19 pm

Re: Dynamically controlling schema and dblink in cursor

Postby Tim... » Tue Oct 15, 2013 7:48 pm

Hi.

1) You can do this using a REF CURSOR. In that way you can open a cursor with a string.

http://www.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.

http://www.oracle-base.com/articles/10g ... -10gr2.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests

cron