Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using reference cursors

Re: using reference cursors

From: Quantarc <info_at_quantarc.co.uk>
Date: Mon, 19 Jul 2004 12:17:12 +0100
Message-ID: <40fbada5$0$229$ed2619ec@ptn-nntp-reader03.plus.net>


Todd Barry wrote:

> 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.

OMG! yeah, i just chacked the tables and they all have a field called "DATE". ack, i would never have thought it could have been that, thanx a lot!! i think this might be the end of this problem now, i've been stuck on it for long enough.

a very grateful rod. Received on Mon Jul 19 2004 - 06:17:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US