Re: PL/SQL cursor trouble

In article <1omodc$85v_at_nz12.rz.uni-karlsruhe.de>, rz34_at_hp850.rz.uni-karlsruhe.de (Schillinger) writes:
|> Background: Oracle v.6.0.34.2.1
|> PL/SQL v. 1.0.34.0.1
|> HP/UX 9000 Series 800
|>
|> I'm working on my first PL/SQL script which should merely produce a

Yes, you need to use an output table, but don't assume you are the only user, and don't assume the data will return in the order you put it in.

Define a table using a session_id field to identify the output, include a user name field and a create_date field so you can clean up garbage that collects, and identify who put it there.

I also use a name field to tell what process wrote the output     

Use a sequence number to guarentee the order of the output. After you leave your procedure, spool the output to a file and clean up data where sessionid= your session id.

SQL> desc plsql_output

 Name                            Null?    Type
 ------------------------------- -------- ----
 CREATE_DATE                              DATE
 USER_NAME                                CHAR(30)
 SESSIONID                                NUMBER
 OUTPUT_NAME                              CHAR(10)
 SEQ                                      NUMBER
 LINE                                     CHAR(132)

SQL> l
  1 insert into plsql_output values (sysdate,user,USERENV('SESSIONID'),'DUMMY',   2 PLSQL_MESSAGE_SEQ.nextval,
  3* 'This is one line of output.' )
SQL> / 1 row created.

SQL> select line from plsql_output where SESSIONID = USERENV('SESSIONID')   2 order by seq
  3 ;

LINE

delete from plsql_output where SESSIONID = USERENV('SESSIONID'); Received on Fri Mar 26 1993 - 20:49:31 CET