Select all from table using procedure oracle

Question

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

Answer 1

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

• Nirmal Rao Dhanwada: Ah! Such a stupid mistake! now that solves the error that I was getting. and btw, why does that semicolon seem odd to you? and now that my error is cleared but I am still stuck at not getting my desired o/p!

Answer 2

You have three cursors here, but one is implicit in the for loop. Your code indicates that for each row in the testnirmal table you want a column name from the table and then a row from the testnirmal table that has a literal value of that column name. It is unclear what you are trying to accomplish, but here is one interpretation:

CREATE OR REPLACE PROCEDURE show_all1 AS
BEGIN
 DBMS_Output.Put_Line('Def_Id ID1');
 For vRow in (SELECT Def_Id, ID1 FROM testnirmal) Loop
 DBMS_Output.Put_Line(vRow.Def_Id) || ' ' || vRow.ID1);
 End Loop;
END show_all1;
/

If you want each field on a separate line, that is a simple change. If you really do want to have the gathering of the field names be dynamic, then you will need some dynamic sql inside the loop. This would certainly be doable, but not a normal requirement.