Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Beginners question : dbms_output whole line of a cursor (all columns)

danielwetzlerJun 6 2006 — edited Jun 6 2006
Dear Pracle experts,

I have another beginners question :

I wrote just for training purposes the Procedure below, which only displays the data of a selected table on the screen.
I try to print the whole contents of the Cursor-line instead of only one column (ID in the case below).
I've read something about the Rowtype argument for a cursor so I tried to define a variable which is filled by a whole curso-line and to feed dbms_output with it.
all tries to perform that ended with an ORA-6502 error.

Could someone give me an advice how to solve my problem ?


Here's my Procedure

CREATE OR REPLACE PROCEDURE SA.SELECTFROMTABLE3
(mymode BOOLEAN, mytablename VARCHAR2) as

v_SQLString VARCHAR2(200);
-- v_TableName VARCHAR2(200);
v_PLSQLBlock VARCHAR2(200);
BEGIN
-- v_TableName := 'Variables';

if mymode = true then
v_PLSQLBLock :=
'BEGIN
FOR v_Rec IN (SELECT * FROM ' || mytablename || ' ) LOOP
DBMS_OUTPUT.PUT_LINE(v_Rec.ID);
END LOOP;
END;';
else
v_PLSQLBLock :=
'BEGIN
FOR v_Rec IN (SELECT * FROM ' || mytablename || ' where rownum = 1) LOOP
DBMS_OUTPUT.PUT_LINE(v_Rec.ID);
END LOOP;
END;';
end if;
EXECUTE IMMEDIATE v_PLSQLBlock
END;


Here's what I tried to print out the whole line of the cursor output (buildt instead of the block above) :

v_PLSQLBLock :=
'Cursor v_Rec is SELECT * FROM ' || mytablename || ' where rownum <= 5
output v_Rec%ROWTYPE;
OPEN v_Rec;
LOOP
FETCH v_Rec INTO output;
DBMS_OUTPUT.put_line(output);
EXIT WHEN v_Rec%NOTFOUND;
END LOOP;
CLOSE v_Rec;
';

I got an ORA-6502 Error using that..
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2006
Added on Jun 6 2006
4 comments
3,154 views