PASSING FOR C LOOP parameter

[lecorr]

Hi,

(Note that I did not find an answer to this question in this forum.)

I have this code in a package:

PROCEDURE TEST(C CURSOR!!!, others param..) IS
aa varchar2(100);
BEGIN
aa := C.value;
END;


PROCEDURE MAIN(...) IS

BEGIN
FOR C IN (SELECT value FROM table WHERE...) LOOP
TEST(C,....); << Is this possible ?????
END LOOP;
END;

As you saw, the question is: is there a way to pass an implicit cursor to a proc??? Knowing that my cursor has a lot of columns, it is more comfortable to use this writing than to pass as much parameters than there are columns in the cursor.

Thanks.

 

[sem]

Yes, you may pass REF CURSOR, but in fact cursor doesn't hold any value (and even doesn't point to it) untill fetch. As you probably know, cursor can not be fetched back, so this may be an issue.
My bet is that %rowtype is all you really need. If the number of columns is really huge, you may use NOCOPY modifier.

Regards, Dima

 

[lecorr]

Thank Dima,

Could you please explicit the good syntax since my test (which looks like my sample code) did not work (PL compiler does seem to even understand it and finish with an ORA-01007 error)?

The fact is I don't know what to put there:

'PROCEDURE TEST(C CURSOR!!!, others param..) IS'

in place of CURSOR.

Or should I use another syntax?

This would be helpfull.


Thanks.

Christian

 

[sem]

An example:

create PROCEDURE PRINT_TEST(C emp%rowtype)
IS
begin
dbms_output.put_line(c.ename||':'||c.hiredate);
end;

create PROCEDURE GET_TEST
IS
begin
for f in (select * from emp) loop
print_test(f);
end loop;
end;

exec get_test


Regards, Dima

 

[lecorr]

Thanks, I'll try that.

Christian