This discussion is archived
13 Replies Latest reply: Jan 2, 2012 2:02 AM by APC RSS

Pass DB Link in procedure as parameter.

VIRU Newbie
Currently Being Moderated
Hi,

I am using ORACLE Database 11g .

I have a procedure in which i am already passing some parameters. The procedure contains few statements in which it has to refer to specific user for getting the data. So we use DB Link. As which user to connect is not sure so the DB LINK is also not constant. That is why i want to pass the DB link as a parameter to procedure. But if i don't give the db link name in procedure the procedure will not compile.

A sample of my code is as follows :-
create or replace procedure P_GET_TABLES(V_DBLINK in varchar2)
as

FOR I in (select s.TABLE_NAME
                from user_tables@V_DBLINK s, dba_tables d          --- Obviously it gives me error that table does not exists.Due to variable V_DBLINK
               where d.table_name = s.TABLE_NAME
                 and s.TABLE_NAME != 'ERROR_LOG'
                 and d.owner = V_SOURCE_SCHEMA_NAME
              union (select s.TABLE_NAME
                      from user_tables@V_DBLINK s
                    minus
                    select TABLE_NAME
                      from dba_tables d
                     where owner = V_SOURCE_SCHEMA_NAME)) Loop


-- other code for the process.....

END LOOP;
END;
/
Is their any method that i can pass a compiled procedure DB LINK as parameter or at run-time. ??

Thanks in advance.
  • 1. Re: Pass DB Link in procedure as parameter.
    sb92075 Guru
    Currently Being Moderated
    VIRU wrote:
    Hi,

    I am using ORACLE Database 11g .

    I have a procedure in which i am already passing some parameters. The procedure contains few statements in which it has to refer to specific user for getting the data. So we use DB Link. As which user to connect is not sure so the DB LINK is also not constant. That is why i want to pass the DB link as a parameter to procedure. But if i don't give the db link name in procedure the procedure will not compile.

    A sample of my code is as follows :-
    create or replace procedure P_GET_TABLES(V_DBLINK in varchar2)
    as
    
    FOR I in (select s.TABLE_NAME
    from user_tables@V_DBLINK s, dba_tables d          --- Obviously it gives me error that table does not exists.Due to variable V_DBLINK
    where d.table_name = s.TABLE_NAME
    and s.TABLE_NAME != 'ERROR_LOG'
    and d.owner = V_SOURCE_SCHEMA_NAME
    union (select s.TABLE_NAME
    from user_tables@V_DBLINK s
    minus
    select TABLE_NAME
    from dba_tables d
    where owner = V_SOURCE_SCHEMA_NAME)) Loop
    
    
    -- other code for the process.....
    
    END LOOP;
    END;
    /
    Is their any method that i can pass a compiled procedure DB LINK as parameter or at run-time. ??

    Thanks in advance.
    SQL code must be static; otherwise abuse EXECUTE IMMEDIATE
  • 2. Re: Pass DB Link in procedure as parameter.
    VIRU Newbie
    Currently Being Moderated
    Thanks for your reply.

    I know about the EXECUTE IMMEDIATE option. As the procedure is very big around 1000 lines and have lot many calls to functions with single quotes,so its very difficult to adjust them in the execute immediate as in future also the code an grow.

    If you have anymore ideas please let me know.
  • 3. Re: Pass DB Link in procedure as parameter.
    LokanathGiri Expert
    Currently Being Moderated
    Some sample code.
    CREATE OR REPLACE PROCEDURE pro_test (pv_dblink VARCHAR2)
    IS
      emp_refcur      SYS_REFCURSOR;
      empno VARCHAR2(20);
      ename VARCHAR2(20);
    BEGIN
       OPEN emp_refcur FOR 'SELECT empno, ename FROM emp@'||pv_dblink;
       LOOP
       FETCH emp_refcur into empno,ename;
       EXIT WHEN  emp_refcur%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Employee no: '||empno||'/ Employee name: '||ename);
       END LOOP;
    END;
    Created a DB link DL_Test
    Execute.
    DECLARE
    BEGIN
      pro_test ('DL_Test');
    END;
    /
    Employee no: 7369/ Employee name: SMITH
    Employee no: 7499/ Employee name: ALLEN
    Employee no: 7521/ Employee name: WARD
    Employee no: 7566/ Employee name: JONES
    Employee no: 7654/ Employee name: MARTIN
    Employee no: 7698/ Employee name: BLAKE
    Employee no: 7782/ Employee name: CLARK
    Employee no: 7788/ Employee name: SCOTT
    Employee no: 7839/ Employee name: KING
    Employee no: 7844/ Employee name: TURNER
    Employee no: 7876/ Employee name: ADAMS
    Employee no: 7900/ Employee name: JAMES
    Employee no: 7902/ Employee name: FORD
    Employee no: 7934/ Employee name: MILLER
    ~Lokanath

    Edited by: Lokanath Giri on २ जनवरी, २०१२ ११:५० पूर्वाह्न
  • 4. Re: Pass DB Link in procedure as parameter.
    VIRU Newbie
    Currently Being Moderated
    Thanks Lokanath. Your reply was helpful.

    Its a request can you just do the same thing with the FOR LOOP given by me in the first post.

    Is it compulsory to use a SYS_REFCURSOR?
  • 5. Re: Pass DB Link in procedure as parameter.
    sybrand_b Guru
    Currently Being Moderated
    Please do not try to build synchronisation mechanisms between databases in PL/SQL. Oracle already has several options for that.
    Dump this 'code' and start using them!

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 6. Re: Pass DB Link in procedure as parameter.
    newbie Explorer
    Currently Being Moderated
    VIRU wrote:
    Thanks Lokanath. Your reply was helpful.

    Its a request can you just do the same thing with the FOR LOOP given by me in the first post.
    CREATE OR REPLACE PROCEDURE pro_test (pv_dblink VARCHAR2)
    IS
      --emp_refcur      SYS_REFCURSOR;
      empno VARCHAR2(20);
      ename VARCHAR2(20);
    BEGIN
       FOR i in(SELECT empno, ename FROM emp@||pv_dblink)
       LOOP
      -- FETCH emp_refcur into empno,ename;
     --  EXIT WHEN  emp_refcur%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Employee no: '||i.empno||'/ Employee name: '||i.ename);
      END LOOP;
    END;
  • 7. Re: Pass DB Link in procedure as parameter.
    LokanathGiri Expert
    Currently Being Moderated
    As your resultset is dynamic so a dynamic resultset to be attached to a reference cursor.
    For homogeneous data sources & few tables I don't find any issue in this kind of approach.
  • 8. Re: Pass DB Link in procedure as parameter.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    VIRU wrote:

    Is it compulsory to use a SYS_REFCURSOR?
    Either a ref cursor or a DBMS_SQL cursor needs to be used.

    Execute Immediate is a single call and if used, will have to perform a single fetch of all the rows - and this can be problematic as all those rows need to be copied into local private process memory. This is not scalable and can cause a serious dent in server resources and performance.

    Also keep in mind that 99% of the time, dynamic SQL is not only needed - it is likely the WRONG approach too.

    Which asks the question as why do you think you need dynamic SQL to address the problem? What is The Problem ?
  • 9. Re: Pass DB Link in procedure as parameter.
    VIRU Newbie
    Currently Being Moderated
    Following this approach got me this error :-

    Error: PL/SQL: ORA-01729: database link name expected
    Line: 37
    Text: from user_tables@||V_DBLINK s, dba_tables d


    Thanks.
  • 10. Re: Pass DB Link in procedure as parameter.
    newbie Explorer
    Currently Being Moderated
    test the query first which is in for loop




    SELECT empno, ename FROM emp@pv_dblink

    do you able to get the data for this?
  • 11. Re: Pass DB Link in procedure as parameter.
    APC Oracle ACE
    Currently Being Moderated
    @Newbie

    I know your name gives it away, but you have completely failed to understand the problem here. Your "solution" just recapitulates the original posted code and so recreates the original error.

    In future you should run your code before posting it.

    Cheers, APC
  • 12. Re: Pass DB Link in procedure as parameter.
    908002 Expert
    Currently Being Moderated
    It seems, some times you will not send any value to DB link parameter. If that is true make following changes.

    and send parameter as '@dblinkname'

    so if dblinkname is null your query will remain as "select empno, ename from emp";


    CREATE OR REPLACE PROCEDURE pro_test (pv_dblink VARCHAR2 default null)
    IS
    emp_refcur SYS_REFCURSOR;
    empno VARCHAR2(20);
    ename VARCHAR2(20);
    BEGIN
    OPEN emp_refcur FOR 'SELECT empno, ename FROM emp'||pv_dblink;
    LOOP
    FETCH emp_refcur into empno,ename;
    EXIT WHEN emp_refcur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee no: '||empno||'/ Employee name: '||ename);
    END LOOP;
    END;
  • 13. Re: Pass DB Link in procedure as parameter.
    APC Oracle ACE
    Currently Being Moderated
    VIRU wrote:
    Its a request can you just do the same thing with the FOR LOOP given by me in the first post.
    It will look something like this:
    create or replace procedure P_GET_TABLES
        (V_DBLINK in varchar2
          , V_SOURCE_SCHEMA_NAME  in varchar2)
    as
        rc sys_refcursor;
        l_table_name all_tables.table_name%type;
    begin
        open rc for 'select s.table_name
                    from user_tables@'||V_DBLINK||' s, dba_tables d
                   where d.table_name = s.table_name
                     and s.table_name != ''ERROR_LOG''
                     and d.owner = :1
                  union (select s.table_name
                          from user_tables@'||V_DBLINK||' s
                        minus
                        select table_name
                          from dba_tables d
                         where owner = :1)'
        using V_SOURCE_SCHEMA_NAME, V_SOURCE_SCHEMA_NAME;
       loop
            fetch rc into l_table_name;
            exit when rc%notfound;
            --  do your processing here....
       end loop;
       close rc;
    end P_GET_TABLES;
    /
    The Oracle online documentation covers dynamic SQL quite comprehensively. [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm#i14500]Find out more.

    ------

    I think you have some confusion with the data dictionary views you're using, USER_TABLES@remote_db will look for tables in a schema with the same name as the current user in the local database. The current user in the local database has the privileges to read the DBA_ level views in te local data dictionary but apparently isn't the account owning the tables in the remote database.

    Is that right? It sounds like a mess. I have to [url https://forums.oracle.com/forums/thread.jspa?messageID=10062119#10062119]agree with Billy that you might be trying to solve the wrong problem. Perhaps you need better configuration/release management processes?

    Cheers, APC

    Edited by: APC on Jan 2, 2012 9:52 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points