Re: Re Cursor Help

Hello Dan,

FRo Oracle documentation:

PLS-00372: in a procedure, RETURN statement cannot contain an expression

Cause: In a procedure, a RETURN statement contains an expression, which is not allowed. In functions, a RETURN statement must contain an expression because its value is assigned to the function identifier. However, in procedures, a RETURN statement simply lets you exit before the normal end of the procedure is reached.

Action: Remove the expression from the RETURN statement, or redefine the procedure as a function.

Erika

Dan White wrote:
>
> I have a stored procedure that uses a ref cursor i'm get the following error
>
> SQL> select * from user_errors
> 2 /
>
> NAME TYPE SEQUENCE LINE POSITION
> ------------------------------ ------------ --------- --------- ---------
> TEXT
> ---------------------------------------------------------------------------
> PKGMARKETEXTRACT3 PACKAGE BODY 1 15 27
> PLS-00382: expression is of wrong type
>
> PKGMARKETEXTRACT3 PACKAGE BODY 2 15 4
> PL/SQL: SQL Statement ignored
>
> PKGMARKETEXTRACT3 PACKAGE BODY 3 24 4
> PLS-00372: In a procedure, RETURN statement cannot contain an expression
>
> PKGMARKETEXTRACT3 PACKAGE BODY 4 24 4
> PL/SQL: Statement ignored
>
> SQL>
>
> PROCEDURE procNoIcon
> IS
> TYPE rcur_NoIcon IS REF CURSOR RETURN m_tempextractmktshr%ROWTYPE;
> recNoIcon_rc rcur_NoIcon;
> BEGIN
> OPEN recNoIcon_rc FOR SELECT hin,
> outlet,
> name,
> street,
> city,
> state,
> zip
> FROM m_tempextractmktshr
> WHERE iconmemberid1 IS NULL;
> RETURN recNoIcon_rc;
>
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('ERROR IN NO ICON :'||SQLERRM);
> END;
Received on Mon Nov 06 2000 - 02:48:19 CST