Test Window and cursor variables

[ScottMattes]

I thought I knew how to define a cursor variable in the test window so that I could look at the result set after the run, something like

select *
into :my_result_set
from all_tables;

And then scan for variables and change my_result_set to a cursor type, but when I tried the exact stuff above I got the following

ora-06550
pl/sql ora-00947 not enough vars

If I change the * to a single field I get another msg that says vars are of wrong type.

I am on latest version.

 

[TenGor]

Hy,

when i try it with * i get a msg that says vars are of wrong type.
(ora-06550 PLS-00597)

 

[Marco Kalter]

You will need to open a cursor in PL/SQL. For example:

begin
  open :my_result_set for select * from all_tables;
end;

 

[TenGor]

Thanks for the clue ;-)

 

[ScottMattes]

Stewpid me!

Can this be added to the help file?

Thank you.

 

[Marco Kalter]

It is our policy to not duplicate the Oracle documentation .

 

[ScottMattes]

I must have missed Test Window functionality in the Oracle docs. I am guessing that I need to go back and read the PL/SQL Developers Guide, right?

 

[William Robertson]

select *
into :my_result_set
from all_tables;

What datatype is :my_result_set?

If it's a collection type, you are missing BULK COLLECT.

If it's a cursor variable, it should be

OPEN :my_result_set
FOR
SELECT * FROM all_tables;

PS
Oops, just read Marco's post that already mentioned this.

 

[Brujo_ADR]

hi Scott Mattes:

If you are test a program that return a cursor, you can get the data and verify if work correctly. Copy this code and test in a "test window".

-- Created on 01-07-2005 by Brujo_ADR
DECLARE
-- Local variables here
TYPE t_cursor IS REF CURSOR;

TYPE t_data IS RECORD(
fielda INTEGER,
fieldb INTEGER,
fieldc INTEGER);

c_cursor t_cursor;
reg_data t_data;
BEGIN
-- Test statements here
OPEN c_cursor FOR
SELECT 1 a,
2 b,
3 c
FROM dual;

FETCH c_cursor
INTO reg_data;
dbms_output.put_line('fielda: ' || to_char(reg_data.fielda));
dbms_output.put_line('fieldb: ' || to_char(reg_data.fieldb));
dbms_output.put_line('fieldc: ' || to_char(reg_data.fieldc));

CLOSE c_cursor;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(substr(SQLERRM, 1, 100));
IF c_cursor%ISOPEN THEN
CLOSE c_cursor;
END IF;
END;

Results:
fielda: 1
fieldb: 2
fieldc: 3

PD: apply pl/sql beautifier, to paste this lost the format.

Brujo_ADR

 

[ScottMattes]

Brujo_ADR,
Thank you. I avoid DBMS_OUTPUT because as a debug tool it is severely braindead (255 chars per msg and 1,000,000 bytes for all msgs)!!

 

[ScottMattes]

For anyone that finds this thread, DBMS_OUTPUT is much more useful in version 10 of PLSD, even to the point where you can view the output while stepping through the execution!