loop thru db_links via cursor - issue, PL/SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

Postby jonnyd » Wed Jun 19, 2013 12:53 pm

Thanks Tim,

Any idea why I still get the following error:

Code: Select all
DECLARE
*
ERROR at line 1:
ORA-00904: "D"."NAME": invalid identifier
ORA-06512: at line 24
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 1:35 pm

Hi.

You gave me this:

Code: Select all
l_sql := l_sql||'  WHERE d.name = cur_rec.db_name';


I rewrote it to this:

Code: Select all
l_sql := l_sql||'  WHERE d.name = :db_name';


I kinda assumed that you have checked that your table "oradba.MESSAGING_PROD_LIST" contained a "NAME" column. :)

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 19, 2013 1:44 pm

Im a bit confused...

The oradba.MESSAGING_PROD_LIST has a DB_NAME column.

My idea was to use 'd.name' and reference the v$database table in the sql statement.

I was thinking that I can use the "NAME" column in v$database and match that with DB_NAME column in oradba.MESSAGING_PROD_LIST to ensure that the right version is matched with the right DB.
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 » Wed Jun 19, 2013 1:56 pm

ok got it.

WHERE upper(db_name) = upper(:db_name)

Thanks again!
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 2:56 pm

:)
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 » Sun Jul 28, 2013 11:55 am

Hi Tim,

A quick follow up on this pl/sql question.

I have the following procedure that is working fine and collecting hourly info on current backups for all our DBs in our environment.

Code: Select all
CREATE OR REPLACE PROCEDURE msbkinfo

IS

--DECLARE
  l_sql varchar(2000);
  v_sqlerrm varchar2(500);
BEGIN

  delete from oradba.backup_info;

  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 SQL%ROWCOUNT = 0 THEN
        INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
        values (cur_rec.db_name,'NO BACKUP');
      END IF;

      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);
    END;
    COMMIT;
  END LOOP;

END



It is trapping the situation where no rows are returned with the EXCEPTION SQL%ROWCOUNT and OTHERS exceptions etc.

The condition I am trying to trap is where there has been an archivelog backup but no recent full backup. The original query checks the the most recent type of backup (using the R = 1 ).

I have attempted to modify the code with the following:

Code: Select all
...
...
    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)||chr(59)
     IF i.INCREMENTAL_LEVEL NOT LIKE ''||chr(37)||INCR||chr(37)||'' THEN
      INSERT INTO oradba.BACKUP_INFO (DB_NAME,ERROR_MESSAGE)
      values (cur_rec.db_name,''CHECK LATEST FULL BACKUP'')||chr(59)
      END IF';


Is it possible to have this condition in the sql?

Or do I need to reconstruct with more PL/SQL cursors?

Hope this is clear. Thanks for any help in advance.

jd
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... » Sun Jul 28, 2013 1:49 pm

Hi.

If your current solution works, why bother changing it? It's not like this is a high performance function or anything...

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 » Sun Jul 28, 2013 3:57 pm

Hi Tim,
Thanks for feedback. I need to update to catch a certain condition that is not being found at present.
Sorry! I thourght I made that clear.
Best regards,
Jd
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 Jul 29, 2013 8:42 am

Hi.

I'm not totally sure what you are trying to achieve here...

You are pulling back the latest backups, but who is to say the latest full backup is not much older than 1 day old. You might have a full backup once a week, then use incremental backups on a daily basis for a week, which means you will never know what the full backup status is from looking at the last days backups.

What would I suggest?

1) You use a recovery catalog with all your databases.
2) You schedule and monitor all your backups using Cloud Control.
3) You will then know exactly what backups have, or have not, been done.

Trying to write this sort of thing for yourself is a very big waste of time when there are free tools out there that do all this for you...

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 » Mon Jul 29, 2013 8:59 am

Thanks for your suggestions but I really need help with this piece of sql.
Will keep looking.

Thanks again for your great site!
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 Jul 29, 2013 9:26 am

OK
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

Previous

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 8 guests