Oracle-L: Re: PL/SQL problem - beginner question

From: <Mary.M.Spence_at_GPC.COM>
Date: Fri, 19 Jan 1996 20:20:31 -0500
Message-Id: <9601200135.AA28984@alice.jcc.com>

I had some problems with PL/SQL blocks that reference db links until I ran the script CATRPC.SQL. That seemed to fix my problem.

--
| X-To: "ORACLE database mailing list. (oracle)"
| <ORACLE-L_at_CCVM.SUNYSB.EDU>
|
| I am working with a small PL/SQL program to automate some database updates
| for a monitoring system I have and am running into what I know is a very
| basic problem, but for which I am not finding the answer (still looking,
btw).
|
| So, if this is an easy one and someone could at least point me in the
right
| direction, I would appreciate it very much.
|
| The following code opens a cursor for loop that reads the host names from
| the dba_db_links table. Within the loop, I am attempting to update
several
| local tables with data from the remote hosts. In my SQL statement that
| performs the update, I am referencing the host field to populate a field
in
| the local table and in the database link reference. The program fails and
| tells me that the link, DB_REC.HOST doesn't exist indicating that PL/SQL
| is not attempting to resolve the variable name as I have placed it in
| the SQL statement. I tried prefixing the variable name with ':', but get
| the message that the bind variable isn't declared.
|
| It may be that I need to call the update SQL statement as a procedure.
| Or is there another way.
|
| Thanks for any help,
|
| Glenn Stauffer
|
|
| ------------------ CODE FOLLOWS ------------------------------
|
| DECLARE
| CURSOR db_cur IS
| select host from dba_db_links;
| BEGIN
| FOR db_rec IN db_cur
| LOOP
| insert into files
| (db_nm,
| ts,
| check_date,
| file_nm,
| blocks)
| select
| upper(db_rec.host),
| tablespace_name,
| trunc(sysdate),
| file_name,
| blocks
| from sys.dba_data_files_at_db_rec.host;
| END LOOP;
| commit;
| END;
|
|
Received on Fri Jan 19 1996 - 20:35:33 CST