Re: PL/SQL problem - beginner question

From: Ken Friday <ken.friday_at_TELDTA.COM>
Date: Thu, 18 Jan 1996 15:13:45 -0600
Message-Id: <9601182122.AA25930@alice.jcc.com>

     Glenn,

     I think this applies: Take a look at the DBMS_SQL package. This
     facilitates "dynamic" SQL within PL/SQL. The latest issue of "Oracle
     Integrator" has a Steve Feuestein article on this.

     Ken F.


______________________________ Reply Separator _________________________________

Subject: PL/SQL problem - beginner question Author: "ORACLE database mailing list." <ORACLE-L%ccvm.sunysb.edu_at_internet.teldta.com> at TDSMAIL Date: 1/18/96 3:37 PM

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 Thu Jan 18 1996 - 16:22:39 CST