Select all from table using procedure oracle

oracleselectstored-procedures

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the elements of the coloumn. It is printing the names of the columns again without printing the elements of the columns and also with an error!

set serveroutput on;
CREATE OR REPLACE PROCEDURE show_all1
AS
cursor gen_row is
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='TESTNIRMAL';
cursor gen1_row(i varchar) is
select i  from testnirmal;
a1 varchar2(22);
a2 varchar2(10);
BEGIN
open gen_row;
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;
close gen1_row;
END show_all1;
/

DEF_ID and ID1 are the two columns in my table and the table name is testnirmal.
The procedure is created without any compilation errors.
But while executing the procedure I am getting this.

DEF_ID
ID1 BEGIN show_all1; END;

  • ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at "USER.SHOW_ALL1", line 20 ORA-06512: at line 1

Best Answer

You open two cursors but close the outer one before the inner one

open gen_row; -- open the outer cursor...this semicolon seems odd
for data in(SELECT * from testnirmal)
loop
fetch gen_row into a1;
open gen1_row(a1);
fetch gen1_row into a2;
dbms_output.put_line(a2);
close gen1_row;
end loop;
close gen_row;--here you close the outer cursor
close gen1_row; --here you close the inner cursor when it is out of scope
Related Question