How to display counter on runtime?

[BenjaminLim]

Hi,

I need help to

1. Put a counter(need synthax for this) in the loop (witin a Procedure), I am using cursor to extract records.

2. Display the final counter value upon completion of extraction.

3. As I am generating an ASCII file and the program somehow just generate the ASCII file half way, I need some form of check to see at which point the program faces problem.

Regards
Benjamin

 

[rcurva]

if you are running via sqlplus, you can do these:

1. Include this line inside your procedure


dbms_output.put_line('Hey! Im here!'||TO_CHAR(rec_counter));


2. run your procedure via sqlplus, but set serveroutput on first.

SQL>SET SERVEROUTPUT ON
SQL>exec your_procedure

Expect something like this...

SQL>Hey! Im here! 4000

 

[carp]

Your cursor has an attribute that will serve as a counter.
If your cursor is called my_curser, then
my_cursor%ROWCOUNT
will tell you how many records have been fetched in the loop so far. Upon exiting the loop (but before closing the cursor!), my_cursor%ROWCOUNT will tell you the total number of times through the loop.

 

[BenjaminLim]

Thanks rcurva and carp.

rcurva,

I put in the synthax in my Oracle form procedure & run the form but there the message you suggested did not appear.

carp,

I tried your recommendation using the following synthax & there were compilation error.

dbms_output.put_line('Hey! Im here!'||my_cursor%ROWCOUNT
);

Please advice. Thanks.

Regards
Benjamin

 

[rcurva]

Try,
dbms_output.put_line('Hey! Im here!'||to_char(my_cursor%ROWCOUNT));

serveroutput only works with sqlplus, am not sure with Oracle Forms. Maybe you could try this instead of dbams_output.put_line:

message('Hey! Im here!'||to_char(my_cursor%ROWCOUNT));

 

[BenjaminLim]

How do I run a procedure file in sqlplus?

Could I run with cursor in sqlplus?

Any sample synthax for the above.

Please advice. Thanks.

 

[rcurva]

If you want to run a stored procedure in sqlplus, you must...

1. Create the procedure first
create or replace procedure MYPROC1
v_today VARCHAR2;
begin
select to_char(sysdate,'DD-MON-YYYY')
into V_TODAY
from sys.dual;

dbms_output.putline(v_today);
end;
/
2. Call the procedure using sqlplus
SQL> set serveroutput on
SQL> execute MYPROC1