Beginners question : dbms_output whole line of a cursor (all columns)
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..