13 Replies Latest reply: Jan 2, 2012 4:02 AM by APC RSS

    Pass DB Link in procedure as parameter.

    VIRU
      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
          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
            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.
              Lokanath Giri
              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
                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
                  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
                    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.
                      Lokanath Giri
                      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.
                        Billy~Verreynne
                        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
                          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
                            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
                              @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
                                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
                                  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