Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Unanswered: DB Link Variable problem

    I have tried different ways, however, it is not working. Here is what I have:


    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;

    The "vrec_newconnection.db_link" is a "%rowtype" variable that contains the DB Link name. This value changes every time a new database has to be accessed, that is why it is a variable.

    The error that I get is: "06/12/2003 10:33 AM Error: ORA-04054: database link VREC_NEWCONNECTION.DB_LINK does not exist."

    does anybody has any Idea why this variable is not working? If I type the db link it works (eg:.....sys.view_stats@link_name. However, if I replace the db link name for the variable it does not work.

    Help!

    Thank you,

    Paul

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: DB Link Variable problem

    You can not use a variable in the FROM clause of a sql-statement. Oracle does not know how to evaluate it and thinks that vrec_newconnection.db_link is the actual name of the db_link.

    The only way to dynamically run sql-statements is to use Dynamic Sql, Execute Immedate, or DBMS_SQL. Since you want to use the query in a cursor I would suggest the DBMS_SQL since you can loop through the parsing of the data.

  3. #3
    Join Date
    May 2003
    Posts
    87

    Re: DB Link Variable problem

    You will have to use dynamic sql for achieving this. Something like this :

    execute immediate 'insert into emp select * from emp@' || db_link_name || ' where deptno=70';


    Originally posted by pvelit
    I have tried different ways, however, it is not working. Here is what I have:


    cursor new_stats_cursor is select * from sys.view_stats@vrec_newconnection.db_link;

    The "vrec_newconnection.db_link" is a "%rowtype" variable that contains the DB Link name. This value changes every time a new database has to be accessed, that is why it is a variable.

    The error that I get is: "06/12/2003 10:33 AM Error: ORA-04054: database link VREC_NEWCONNECTION.DB_LINK does not exist."

    does anybody has any Idea why this variable is not working? If I type the db link it works (eg:.....sys.view_stats@link_name. However, if I replace the db link name for the variable it does not work.

    Help!

    Thank you,

    Paul

  4. #4
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10

    Re: DB Link Variable problem

    I know it may seem as a dum question, but, I am kind of new at this. How would I run the executed immediate to the an SQL statement that is part of a CURSOR assignment line. What is the syntax to acomplish this:

    cursor my_cursor is select * from sys.view_stats@vrec_newconnection.db_link;

    Would it be:
    execute immeditate 'cursor my_cursor_t is select * from sys.view_stats@'||vrec_newconnection.db_link;

    Can you execute immediate a cursor?

    Thank you for your help.

    Paul








    Originally posted by dbmadcap
    You will have to use dynamic sql for achieving this. Something like this :

    execute immediate 'insert into emp select * from emp@' || db_link_name || ' where deptno=70';

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: DB Link Variable problem

    Execute Immediate can only run a single DML or DDL. So that would be an SQL statement not containing any PL/SQL code such as the Cursor definition. This is why I suggest the DBMS_SQL package due to the ability to loop through the parsing of the data much like you would do with looping through a cursor.

  6. #6
    Join Date
    May 2003
    Posts
    87

    Re: DB Link Variable problem

    As Carloa mentioned, execute immediate can be used only to execute single stmts -- kind of quick & fast dynamic sql. Since you want to loop thru the cursor, here is an example which uses dbms_sql. The quoted example reads the source table over a dblink and inserts row by row into the destination table.

    Hope this helps !!

    Code:
    DECLARE
       L_empno            NUMBER; 
       L_ename            VARCHAR2(30); 
       L_dob              DATE; 
       L_src_cursor       INTEGER; 
       L_dst_cursor       INTEGER; 
       L_ignore           INTEGER; 
    BEGIN 
     
       -- Prepare a cursor to select from the source table: 
       L_src_cursor := dbms_sql.open_cursor; 
       DBMS_SQL.PARSE(L_src_cursor, 'SELECT empno, ename, dob FROM emp@' || L_dblink_var, DBMS_SQL.native); 
       DBMS_SQL.DEFINE_COLUMN(L_src_cursor, 1, L_empno); 
       DBMS_SQL.DEFINE_COLUMN(L_src_cursor, 2, L_ename, 30); 
       DBMS_SQL.DEFINE_COLUMN(L_src_cursor, 3, L_dob); 
       ignore := DBMS_SQL.EXECUTE(L_src_cursor); 
     
       -- Prepare a cursor to insert into the destination table: 
       L_dst_cursor := DBMS_SQL.OPEN_CURSOR; 
       DBMS_SQL.PARSE(L_dst_cursor, 'INSERT INTO emp2 VALUES (:empno_bind, :ename_bind, :dob_bind)', DBMS_SQL.native); 
     
       -- Fetch a row from the source table and insert it into the destination table: 
       LOOP 
          IF DBMS_SQL.FETCH_ROWS(L_src_cursor)>0 THEN 
             -- get column values of the row 
             DBMS_SQL.COLUMN_VALUE(L_src_cursor, 1, L_empno); 
             DBMS_SQL.COLUMN_VALUE(L_src_cursor, 2, L_ename); 
             DBMS_SQL.COLUMN_VALUE(L_src_cursor, 3, L_dob); 
     
            -- Bind the row into the cursor that inserts into the destination table. You 
            -- could alter this example to require the use of dynamic SQL by inserting an 
            -- if condition before the bind. 
            DBMS_SQL.BIND_VARIABLE(L_dst_cursor, ':empno_bind', L_empno); 
            DBMS_SQL.BIND_VARIABLE(L_dst_cursor, ':ename_bind', L_ename); 
            DBMS_SQL.BIND_VARIABLE(L_dst_cursor, ':dob_bind', L_dob); 
            ignore := DBMS_SQL.EXECUTE(L_dst_cursor); 
          ELSE 
            -- No more rows to copy: 
            EXIT; 
          END IF; 
       END LOOP; 
     
       -- Commit and close all cursors: 
       COMMIT; 
       DBMS_SQL.CLOSE_CURSOR(L_src_cursor); 
       DBMS_SQL.CLOSE_CURSOR(L_dst_cursor); 
    EXCEPTION 
       WHEN OTHERS THEN 
          IF DBMS_SQL.IS_OPEN(L_src_cursor) THEN 
             DBMS_SQL.CLOSE_CURSOR(L_src_cursor); 
          END IF; 
          IF DBMS_SQL.IS_OPEN(L_dst_cursor) THEN 
             DBMS_SQL.CLOSE_CURSOR(L_dst_cursor); 
          END IF; 
          RAISE; 
    END; 
    /
    Originally posted by pvelit
    I know it may seem as a dum question, but, I am kind of new at this. How would I run the executed immediate to the an SQL statement that is part of a CURSOR assignment line. What is the syntax to acomplish this:

    cursor my_cursor is select * from sys.view_stats@vrec_newconnection.db_link;

    Would it be:
    execute immeditate 'cursor my_cursor_t is select * from sys.view_stats@'||vrec_newconnection.db_link;

    Can you execute immediate a cursor?

    Thank you for your help.

    Paul

  7. #7
    Join Date
    Jun 2003
    Location
    McLean, VA -- USA
    Posts
    10
    Thank you guys,

    I think this is what I was looking for. I will try to apply this code to my program.

    Have a good weekend.

    Paul Velit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •