loop thru db_links via cursor - issue, PL/SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Mon Jun 10, 2013 10:10 pm

Hi all,

I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.

The links are retrieved via a cursor.

However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'

Any ideas how to handle db_links using a cursor in a pl/sql block?

Thanks in advance.

jd

The code is as follows:

Code: Select all
DECLARE
db_link_rec VARCHAR2(30);

CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;

BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;

INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME)
(SELECT *
FROM
(
SELECT
d.SESSION_KEY,
d.STATUS,
INPUT_TYPE||' '||i.INCREMENTAL_LEVEL,
to_char(d.START_TIME,'mm/dd/yy hh24:mi'),
to_char(END_TIME,'mm/dd/yy hh24:mi'),
round(d.elapsed_seconds/60,0),
round(d.OUTPUT_BYTES/1024/1024/1024,2),
ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
sysdate,
n.name "DB_NAME"
FROM v$database@db_link_rec n,v$RMAN_BACKUP_JOB_DETAILS@db_link_rec d, v$backup_set_details@db_link_rec i
WHERE d.session_recid = i.session_recid
AND d.start_time >= (sysdate -1)
AND INPUT_TYPE like '%INC%'
)
WHERE R =1);
END LOOP;
CLOSE db_link_cur;
END;
/
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Mon Jun 10, 2013 10:38 pm

Hi.

It looks like you are using a variable called "db_link_rec" as your database. I assume you are hoping the value in this variable will be used as the name for the database link. Sorry. That's not going to work. You can't use variables as structural items in a query, such as table or column names etc. You can't even do this in dynamic SQL using bind variables. The database link has to be part of the statement, or if you are using dynamic sql, the variable value has to concatenated into the dynamic SQL string, not used as a bind variable.

So if you build up a string correctly ans execute that, this could work. All single quotes would have to be made into two single quotes and the database link variable would have to be concatenated into the string. Something like this:

Code: Select all
l_sql := 'INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME)
(SELECT *
FROM
(
SELECT
d.SESSION_KEY,
d.STATUS,
INPUT_TYPE||'' ''||i.INCREMENTAL_LEVEL,
to_char(d.START_TIME,''mm/dd/yy hh24:mi''),
to_char(END_TIME,''mm/dd/yy hh24:mi''),
round(d.elapsed_seconds/60,0),
round(d.OUTPUT_BYTES/1024/1024/1024,2),
ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
sysdate,
n.name "DB_NAME"
FROM v$database@' || db_link_rec || ' n,v$RMAN_BACKUP_JOB_DETAILS@' || db_link_rec || ' d, v$backup_set_details@' || db_link_rec || ' i
WHERE d.session_recid = i.session_recid
AND d.start_time >= (sysdate -1)
AND INPUT_TYPE like '%INC%'
)
WHERE R =1)';

EXECUTE IMMEDIATE l_sql;


You would have to make sure the user connected to by the database link has select on the v_$... views, not the v$... synonym.

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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Tue Jun 11, 2013 5:13 pm

Hi Tim,

That worked a treat! Thanks.

Do you have any idea how I can handle exceptions? Im thinking how to update the central table with a trapped error message.

I paste the latest code.

My current attempt at trapping a NO_DATA_FOUND error is not working as expected. I wanted to update the BACKUP_INFO table with the DB_NAME and an error message if there is no current backup info retrieved over the db_link.

The manual exception complains that the IF is not within the original SQL.

Im not sure how to handle exceptions within a declared SQL statement..

Any ideas?



Code: Select all
DECLARE

        no_backup_data EXCEPTION;

        l_sql varchar(2000);

        CURSOR db_link_cur IS
        SELECT DB_NAME,DB_LINK
        from MESSAGING_PROD_LIST
        WHERE STATUS = 'ACTIVE'
        ORDER BY 1;

        db_link_rec db_link_cur%ROWTYPE;

BEGIN
        OPEN db_link_cur;
        LOOP
        FETCH db_link_cur INTO db_link_rec;
        EXIT when db_link_cur%NOTFOUND;

        l_sql := 'INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME,DEVICE_TYPE)
        (SELECT *
        FROM
        (
        SELECT
        d.SESSION_KEY,
        d.STATUS,
        INPUT_TYPE||'' ''||i.INCREMENTAL_LEVEL,
        to_char(d.START_TIME,''mm/dd/yy hh24:mi''),
        to_char(END_TIME,''mm/dd/yy hh24:mi''),
        round(d.elapsed_seconds/60,0),
        round(d.OUTPUT_BYTES/1024/1024/1024,2),
        ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
        sysdate,
        n.name "DB_NAME",
        i.device_type
        FROM v$database@' || db_link_rec.db_link || ' n,v$RMAN_BACKUP_JOB_DETAILS@' || db_link_rec.db_link || ' d, v$backup_set_details@' || db_link_rec.db_link || ' i
        WHERE d.session_recid = i.session_recid
        AND d.start_time >= (sysdate -1)
        )
        WHERE R =1)';

        EXECUTE IMMEDIATE l_sql;

--        IF count(*) = 0 THEN
--           RAISE no_backup_data;
--        END IF;

        commit;
        END LOOP;
        CLOSE db_link_cur;

EXCEPTION
        WHEN NO_DATA_FOUND THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values
        (db_link_rec.db_name,'NO BACKUP');
END;
/
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Tue Jun 11, 2013 5:44 pm

Hi.

No exception is raised for an insert of 0 rows. You have to check the number of rows touched with SQL%ROWCOUNT.

Replace:

Code: Select all
--        IF count(*) = 0 THEN
--           RAISE no_backup_data;
--        END IF;


With:

Code: Select all
        IF SQL%ROWCOUNT = 0 THEN
           RAISE no_backup_data;
        END IF;


Note. Your current code will stop the whole process if just one insert results on 0 rows inserted. Is this what you wanted?

If not, then consider something like this.

Code: Select all
DECLARE
  l_sql varchar(2000);

  CURSOR db_link_cur IS
  SELECT DB_NAME,DB_LINK
  from MESSAGING_PROD_LIST
  WHERE STATUS = 'ACTIVE'
  ORDER BY 1;

  db_link_rec db_link_cur%ROWTYPE;
BEGIN
  OPEN db_link_cur;
  LOOP
    FETCH db_link_cur INTO db_link_rec;
    EXIT when db_link_cur%NOTFOUND;

    l_sql := 'INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME,DEVICE_TYPE)
    (SELECT *
    FROM
    (
    SELECT
    d.SESSION_KEY,
    d.STATUS,
    INPUT_TYPE||'' ''||i.INCREMENTAL_LEVEL,
    to_char(d.START_TIME,''mm/dd/yy hh24:mi''),
    to_char(END_TIME,''mm/dd/yy hh24:mi''),
    round(d.elapsed_seconds/60,0),
    round(d.OUTPUT_BYTES/1024/1024/1024,2),
    ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
    sysdate,
    n.name "DB_NAME",
    i.device_type
    FROM v$database@' || db_link_rec.db_link || ' n,v$RMAN_BACKUP_JOB_DETAILS@' || db_link_rec.db_link || ' d, v$backup_set_details@' || db_link_rec.db_link || ' i
    WHERE d.session_recid = i.session_recid
    AND d.start_time >= (sysdate -1)
    )
    WHERE R =1)';

    EXECUTE IMMEDIATE l_sql;

    IF count(*) = 0 THEN
       INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
       values (db_link_rec.db_name,'NO BACKUP');
    END IF;
    COMMIT;
  END LOOP;
  CLOSE db_link_cur;       
END;
/


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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Tue Jun 11, 2013 7:26 pm

Hmm, Thanks Tim,

I run into the following error when using the last suggestion:

Code: Select all
       IF count(*) = 0 THEN
           *
ERROR at line 45:
ORA-06550: line 45, column 12:
PLS-00204: function or pseudo-column 'COUNT' may be used inside a SQL statement
only
ORA-06550: line 45, column 9:
PL/SQL: Statement ignored


I believe its the dynamic part of the code which is causing the issue and was why I tried to use an exception. But as you say the exception will exit the script.

Can I put the IF THEN as part of the declared sql statement?
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Tue Jun 11, 2013 8:07 pm

Got it!

It was a combination of your 2 solutions:

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
values
(db_link_rec.db_name,'NO BACKUP');
END IF;

Now I just need to work out how to use an exception and input the exception error into the table without the script exiting.

I will try with SQLERRM
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Tue Jun 11, 2013 8:42 pm

Hi.

There will be no exception, except the one you artificiality raise yourself, because an INSERT ... SELECT of no rows is still a valid piece of DML. Just the same way an UPDATE of zero rows, or a delete of zero rows doesn't raise an exception. The only thing you will trap is the exception you manually raised yourself, so capturing SQLERRM adds no value to your code.

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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Tue Jun 11, 2013 8:48 pm

What I need to do is trap other codes, ie ORA-01017: invalid username/password; logon denied
but not have the script exit.

for example.
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Tue Jun 11, 2013 9:05 pm

Hi.

LOL. I told you what to correct, then didn't put the change into the code I posted. That's rushing because I was late for the cinema. :)

OK. In that case, put a BEGIN EXCEPTION ... END around the INSERT ... EXECUTE IMMEDIATE and you will trap exceptions for that statement only, then the loop will continue to process the other tables.

By jumping to an exception handler outside the loop, you effectively end the loop. What if there are 1000 tables to process, but the first one fails. Do you want to ignore the next 999 tables?

Code: Select all
DECLARE
  l_sql varchar(2000);

  CURSOR db_link_cur IS
  SELECT DB_NAME,DB_LINK
  from MESSAGING_PROD_LIST
  WHERE STATUS = 'ACTIVE'
  ORDER BY 1;

  db_link_rec db_link_cur%ROWTYPE;
BEGIN
  OPEN db_link_cur;
  LOOP
    FETCH db_link_cur INTO db_link_rec;
    EXIT when db_link_cur%NOTFOUND;

    BEGIN
      l_sql := 'INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME,DEVICE_TYPE)
      (SELECT *
      FROM
      (
      SELECT
      d.SESSION_KEY,
      d.STATUS,
      INPUT_TYPE||'' ''||i.INCREMENTAL_LEVEL,
      to_char(d.START_TIME,''mm/dd/yy hh24:mi''),
      to_char(END_TIME,''mm/dd/yy hh24:mi''),
      round(d.elapsed_seconds/60,0),
      round(d.OUTPUT_BYTES/1024/1024/1024,2),
      ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
      sysdate,
      n.name "DB_NAME",
      i.device_type
      FROM v$database@' || db_link_rec.db_link || ' n,v$RMAN_BACKUP_JOB_DETAILS@' || db_link_rec.db_link || ' d, v$backup_set_details@' || db_link_rec.db_link || ' i
      WHERE d.session_recid = i.session_recid
      AND d.start_time >= (sysdate -1)
      )
      WHERE R =1)';
 
      EXECUTE IMMEDIATE l_sql;

      IF IF SQL%ROWCOUNT = 0 THEN THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (db_link_rec.db_name,'NO BACKUP');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (db_link_rec.db_name, SQLERRM);
    END;
    COMMIT;
  END LOOP;
  CLOSE db_link_cur;       
END;
/


Did you know that is more efficient to use a cursor foor loop like this?

Code: Select all
DECLARE
  l_sql varchar(2000);
BEGIN
  FOR cur_rec IN (SELECT DB_NAME,DB_LINK
                  from MESSAGING_PROD_LIST
                  WHERE STATUS = 'ACTIVE'
                  ORDER BY 1)
  LOOP
    BEGIN
      l_sql := 'INSERT INTO oradba.BACKUP_INFO (SESSION_KEY,STATUS,INPUT_TYPE,START_TIME,END_TIME,MINUTES,GB,R,INSERT_TIME,DB_NAME,DEVICE_TYPE)
      (SELECT *
      FROM
      (
      SELECT
      d.SESSION_KEY,
      d.STATUS,
      INPUT_TYPE||'' ''||i.INCREMENTAL_LEVEL,
      to_char(d.START_TIME,''mm/dd/yy hh24:mi''),
      to_char(END_TIME,''mm/dd/yy hh24:mi''),
      round(d.elapsed_seconds/60,0),
      round(d.OUTPUT_BYTES/1024/1024/1024,2),
      ROW_NUMBER() OVER (PARTITION BY INPUT_TYPE ORDER BY end_time desc nulls last ) R,
      sysdate,
      n.name "DB_NAME",
      i.device_type
      FROM v$database@' || cur_rec.db_link || ' n,v$RMAN_BACKUP_JOB_DETAILS@' || cur_rec.db_link || ' d, v$backup_set_details@' || cur_rec.db_link || ' i
      WHERE d.session_recid = i.session_recid
      AND d.start_time >= (sysdate -1)
      )
      WHERE R =1)';
 
      EXECUTE IMMEDIATE l_sql;

      IF IF SQL%ROWCOUNT = 0 THEN THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (cur_rec.db_name,'NO BACKUP');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (cur_rec.db_name, SQLERRM);
    END;
    COMMIT;
  END LOOP;

END;
/


No Oracle does the opening and closing of the cursor for you. It's faster and looks nice in my opinion.

The only time you need to use an explicit OPEN, FETCH, CLOSE is when you are using REF CURSORS, or when you are doing bulk binds.

http://www.oracle-base.com/articles/mis ... rdsets.php
http://www.oracle-base.com/articles/9i/ ... ing-9i.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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Tue Jun 11, 2013 11:01 pm

Thanks Tim!

Hope was cinema was good!

i think we have that now.

Btw, do you think it might be a good idea to close the session after using the db_link?

i read somewhere that it might affect session parameter or some other.

I think one might have to close the session with 'ALTER SESSION CLOSE DATABASE LINK' syntax.

What do you think?
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Tue Jun 11, 2013 11:04 pm

Hi.

You shouldn't have to worry about this.

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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Wed Jun 12, 2013 9:39 am

Thank you so much Tim.

I had to modify the last solution to declare the SQLERRM and then it worked fine.

This is going to help so much in this oracle env to centralise DB info.. I cant believe they havent had it for so long!!

Thanks again and good luck

ORACLE_BASE rocks! :-)

Code: Select all
DECLARE
  l_sql varchar(2000);
  v_sqlerrm varchar2(500);     

....

 EXCEPTION
      WHEN OTHERS THEN
        v_sqlerrm := substr(SQLERRM,1,90);
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (cur_rec.db_name, v_sqlerrm);
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Wed Jun 12, 2013 10:09 am

:)
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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: loop thru db_links via cursor - issue, PL/SQL

Postby jonnyd » Wed Jun 19, 2013 11:38 am

Hi Tim,

Im having some trouble updating the database list table with version valuses retrieved via the cursor looping thru db links.

Code: Select all
DECLARE

  l_sql varchar2(2000);
  v_sqlerrm varchar2(500);

BEGIN

  FOR cur_rec IN (SELECT DB_NAME,DB_LINK
                  from MESSAGING_PROD_LIST
                  WHERE STATUS = 'ACTIVE'
                  ORDER BY 1)
  LOOP
    BEGIN

      l_sql := 'UPDATE oradba.MESSAGING_PROD_LIST
      SET DBVERSION =
      (SELECT substr(banner, 48,10)
      FROM v$version@' || cur_rec.db_link || ' v, v$database@' || cur_rec.db_link || ' d
      WHERE v.banner like ''Oracle%'')';

--      l_sql := l_sql||'  WHERE upper('||chr(39)||d.name||chr(39)||') = upper('||chr(39)||cur_rec.db_name||chr(39)||')';
      l_sql := l_sql||'  WHERE d.name = cur_rec.db_name';

      dbms_output.put_line(l_sql);

      EXECUTE IMMEDIATE l_sql;

--      dbms_output.put_line(l_sql);

    END;

    COMMIT;

  END LOOP;

END;
/


The error I keep getting is the following:

Code: Select all
DECLARE
*
ERROR at line 1:
ORA-00904: "CUR_REC"."DB_NAME": invalid identifier
ORA-06512: at line 26


The problem seems to be when the code trys to ensure that the right DB NAME is updated.

I think I may have to revert back to an explicit cursor approach.

Any ideas what going on and how to fix?

Ive given it a try with the following:

Code: Select all
DECLARE

  l_sql varchar2(2000);

  c1_rec MESSAGING_PROD_LIST%ROWTYPE;

  CURSOR c1 IS
  SELECT *
  from MESSAGING_PROD_LIST
  ORDER BY 1;


BEGIN

    OPEN c1;
    LOOP
    FETCH c1 INTO c1_rec;
    EXIT when c1%NOTFOUND;

      l_sql := 'UPDATE oradba.MESSAGING_PROD_LIST
      SET DBVERSION =
      (SELECT substr(banner, 48,10)
      FROM v$version@' || c1_rec.db_link || ' v
      WHERE v.banner like ''Oracle%''
      AND upper(DB_NAME) = upper(c1_rec.DB_NAME)';

      dbms_output.put_line(l_sql);

      EXECUTE IMMEDIATE l_sql;

      COMMIT;

  END LOOP;

  CLOSE c1;

END;
/
jonnyd
Senior Member
 
Posts: 66
Joined: Fri Mar 05, 2010 9:59 am

Re: loop thru db_links via cursor - issue, PL/SQL

Postby Tim... » Wed Jun 19, 2013 12:48 pm

Hi.

The problem is you are trying to reverence the cursor from within the dynamic SQL. You either have to concatenate the value in, or better still use a bind variable.

Code: Select all
DECLARE
  l_sql varchar2(2000);
  v_sqlerrm varchar2(500);
BEGIN
  FOR cur_rec IN (SELECT DB_NAME,DB_LINK
                  from MESSAGING_PROD_LIST
                  WHERE STATUS = 'ACTIVE'
                  ORDER BY 1)
  LOOP
    BEGIN

      l_sql := 'UPDATE oradba.MESSAGING_PROD_LIST
                SET DBVERSION = (SELECT substr(banner, 48,10)
                                 FROM v$version@' || cur_rec.db_link || ' v, v$database@' || cur_rec.db_link || ' d
                                 WHERE v.banner like ''Oracle%'')';

      l_sql := l_sql||'  WHERE d.name = :db_name';

      dbms_output.put_line(l_sql);

      EXECUTE IMMEDIATE l_sql USING cur_rec.db_name;
--      dbms_output.put_line(l_sql);
    END;
    COMMIT;
  END LOOP;
END;
/


You would have had the same problem with any PL/SQL variable. This was not a problem with cursor for loops. It was your misunderstanding.

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: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 8 guests