Oracle-L: Re: Ora-1001

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 13 Feb 2003 06:14:04 -0800
Message-ID: <F001.0054BBB5.20030213061404@fatcity.com>

Ora-1001You are opening your cursor (l_cursor) outside of the loop, and you are closing it inside the loop. So, on the second iteration your l_cursor is invalid. Move closing cursor statement outside the loop:

end loop;
dbms_sql.close_cursor( l_cursor );
end;
/

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message ----- From: Hatzistavrou John To: Multiple recipients of list ORACLE-L Sent: Thursday, February 13, 2003 7:58 AM Subject: Ora-1001

Dear all,

I am trying to construct the following PL/SQL block. When I run it I always get an 0RA-1001 error. May you please help me

Environment

Oracle 8.1.7.0

O/S W2K Sp3

declare

l_cursor integer := dbms_sql.open_cursor;

output utl_file.file_type;

cursor lnklst_cur is

select db_link lnk from dba_db_links;

lala varchar2(300);

begin

for lnklst_rec in lnklst_cur loop

output := utl_file.fopen( 'c:\', lnklst_rec.lnk||'_tablespace_healthcheck_20030213.slk', 'w',

lala := ' select tablespace_name from dba_tablespaces@'||lnklst_rec.lnk;

utl_file.put_line(output , lala);

dbms_sql.parse( l_cursor,lala ,dbms_sql.native );

/*owa_sylk.show(

p_file => output ,

p_cursor => l_cursor,

p_sum_column =>

                 owa_sylk.owaSylkArray( 'N' ),

             p_show_grid => 'YES' );*/

         dbms_sql.close_cursor( l_cursor );

             utl_file.fclose( output );

      end loop;

end;

(DC07DB-SYSADM)>/ declare

ÓÖÁËÌÁ óôç ãñáììÞ 1: ORA-01001: ìç áðïäåêôüò cursor

ORA-06512: óå "SYS.DBMS_SYS_SQL", ãñáììÞ 824 ORA-06512: óå "SYS.DBMS_SQL", ãñáììÞ 32 ORA-06512: óå ãñáììÞ 12 It seems that dbms_sql.parse cannot understand the statement.

Kind Regards,

Hatzistavrou Yannis

Database Administrator

SchlumbergerSema

Phone ext. 478

Email: John.Hatzistavrou.sema_at_mail.tellas.gr

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Igor Neyman
INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 13 2003 - 08:14:04 CST