Re: Cursor as output of a PLSQL procedure

[Quoted] "Vigi98" <vigi98_at_my-deja.com> wrote in message news:WQaj7.196$yS2.392271_at_nnrp3.proxad.net...
> Hi all,
>
> Some time ago, I sent a question on how fill in a cursor with dynamic
query.
> I had my response, thanks a lot. But now, I have an other problem : I
cannot
> get this cursor as output of my procedure.
> My program is something like that :
>
> CREATE OR REPLACE PACKAGE AAApkgLocation AS
>
> -- Package constant
>
> -- types declaration
> TYPE t_Building IS RECORD (
> BuildingCode location.name%TYPE,
> CityAbbreviation location.name%TYPE,
> StreetCode location.address1%TYPE,
> StreetName location.address2%TYPE,
> HouseNumber location.address3%TYPE,
> ZipCode location.zip%TYPE,
> Comments location.notes%TYPE );
> TYPE c_Building IS REF CURSOR RETURN t_Building;
>
> -- Package PROCEDURE
> PROCEDURE SearchLocationBuilding ( o_ErrorCode OUT NUMBER,
> o_ErrorText OUT VARCHAR2,
> i_CityAbbreviation IN VARCHAR2,
> i_BuildingCode IN VARCHAR2,
> i_StreetCode IN VARCHAR2,
> i_StreetName IN VARCHAR2,
> i_HouseNumber IN VARCHAR2,
> i_ZipCode IN VARCHAR2,
> i_Comments IN VARCHAR2,
> o_ListBuilding OUT c_Building );
> END;
> /
>
> CREATE OR REPLACE PACKAGE BODY AAApkgLocation AS
> -- ******************************************************
> -- SearchLocationBuilding Procedure
> -- ******************************************************
> PROCEDURE SearchLocationBuilding ( o_ErrorCode OUT NUMBER,
> o_ErrorText OUT VARCHAR2,
> i_CityAbbreviation IN VARCHAR2,
> i_BuildingCode IN VARCHAR2,
> i_StreetCode IN VARCHAR2,
> i_StreetName IN VARCHAR2,
> i_HouseNumber IN VARCHAR2,
> i_ZipCode IN VARCHAR2,
> i_Comments IN VARCHAR2,
> o_ListBuilding OUT c_Building ) AS
>
> TYPE BuildingCurTyp IS REF CURSOR;
> v_ListBuilding BuildingCurTyp ;
>
> v_query VARCHAR2(400) := '';
>
> BEGIN
> -- v_query is buid here
> OPEN v_ListBuilding FOR v_query;
> -- and here I do not know how fill in o_ListBuilding with what is in
> v_ListBuilding
> END;
> -- ******************************************************
> -- SearchLocationBuilding END
> -- ******************************************************
>
> Thanks for your help
>
>

Move the type definition to package level in the package space or use weakly typed cursors.

Hth,

Sybrand Bakker,

Senior Oracle DBA Received on Thu Aug 30 2001 - 00:49:02 CEST