Oracle PLSQL refcursor%ROWTYPE the declaration of the type of this expression is incomplete or malformed

user_5499118 0
Viewed 1453

I want to assign cursor variable in PL/SQL code and then fetch a row from that. When I declared cursor explicitly, it works.

DECLARE
cursor c1 is SELECT * from acme;
row1 c1%ROWTYPE;

but when I use code to assign cursor per e.g. Oracle PLSQL setting a cursor from a variable like that:

  c1 sys_refcursor;
  r1 c1%ROWTYPE;

I get error

the declaration of the type of this expression is incomplete or malformed

writting just r1 %ROWTYPE produces error too.

As per general use of variables explained e.g. here How to declare variable and use it in the same Oracle SQL script? I've tried:

SET SERVEROUTPUT ON
DECLARE
  c1 sys_refcursor;
BEGIN
    open c1 for 'SELECT * FROM foo';
    declare r1 c1%ROWTYPE;
    begin
        fetch c1 into r1; 
    end;
    DBMS_OUTPUT.PUT_LINE('FOO');    
    close c1;
END;

and again got the declaration of the type of this expression is incomplete or malformed

How can I declare variable of row type it my case? Oracle 11g.
P.S. workaround would be to fetch c1 into v_empno, v_ename, ect declaring several variables, still, is there a way to use rowtype one?

oracle11g oracle
2 Answers

Something like this?

SQL> set serveroutput on
SQL>
SQL> declare
  2    c1 sys_refcursor;
  3    r1 dept%rowtype;                      --> this
  4  begin
  5    open c1 for 'select * from dept';
  6    fetch c1 into r1;
  7    dbms_output.put_line(r1.dname);
  8  end;
  9  /
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>
user_90979060

If your intention is to display result from any given query, you may use this standard procedure similar to the one described in this AskTOM article

create or replace procedure return_result( l_query varchar2 )
   is
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(4000);
       l_status        integer;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );

       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );


           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_output.put( l_separator || l_descTbl(i).col_name );
               l_separator := ',';
           end loop;
           dbms_output.put_line('');

        for i in 1 .. l_colCnt loop
           dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
       end loop;
       l_status := dbms_sql.execute(l_theCursor);

       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               dbms_output.put( l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           dbms_output.new_line;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   end;
 /

Pass any query to this procedure to get result.

set serveroutput on
set feedback off
set sqlformat ansiconsole
set pages 0
BEGIN
return_result('select e.employee_id,e.first_name,e.salary,
          d.department_name from 
          employees e join 
  departments d on e.department_id= d.department_id');
END;
/

Result

EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME
200,Jennifer,4400,Administration
201,Michael,13000,Marketing
202,Pat,6000,Marketing
114,Den,11000,Purchasing

Note: Oracle 12c provides DBMS_SQL.RETURN_RESULT using which this procedure can be easily implemented.

user_79985910
Related