Re: using reference cursors

Is it possible that these tables have a PL/SQL reserved word as a column name - such as TIMESTAMP?

sql>create table test (id int primary key, name varchar2(10), timestamp timestamp);

Table created.

sql>create or replace procedure test_rc(p_rc out sys_refcursor)   2 is
  3 begin
  4 open p_rc for
  5 select * from test;
  6 end;
  7 /

Warning: Procedure created with compilation errors.

sql>show errors
Errors for PROCEDURE TEST_RC:

LINE/COL ERROR

5/5      PL/SQL: SQL Statement ignored
5/19     PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis
terminated
         ORA-06553: PLS-320: the declaration of the type of this
         expression is incomplete or malformed

-Todd

On Fri, 16 Jul 2004 16:56:41 +0100, Quantarc <info_at_quantarc.co.uk> wrote:

>hi, i'm using reference cursors with oracle 9.2 and have a question.
>when i use the corsor i just do this...
>
>opem myCursor for
> select * from wherever;
>
>and that usually works fine, but for some tables they just won't let me
>use them with a reference cursor. when i try and use them in any way i
>get the error message...
>
>Line # = 26 Column # = 1 Error Text = PL/SQL: SQL Statement ignored
>Line # = 28 Column # = 12 Error Text = PL/SQL: ORA-06552: PL/SQL:
>Compilation unit analysis terminated ORA-06553: PLS-320: the declaration
>of the type of this expression is incomplete or malformed
>
>now i was told before that the open cursor thing should take a string as
>it's argument, and when i do that the problem goes away and the
>procedure/function compiles fine. so my question is why do i have to
>use a string sometimes, when most of the time i can just write the sql
>without putting it in a string? some of the sql i have to go in my
>functions is pretty long so putting in all in a string isn't reallt what
>i want to do... has anyone else had this problem? know any work arounds?
>
>the names of some of the tables i can't seem to use are "OPS_HD_MAIL",
>"COND_COMPONENT" and "DDA". there are others, i'm finding more all the
>time. if i try and compile these functions in enterprise manager
>console then it's throws an error and refuses to display the procedure
>any more, which is odd.
>
>any help appriciated!
>thanx.
>rod.
Received on Fri Jul 16 2004 - 18:58:47 CDT