Cursor as output of a PLSQL procedure

From: Vigi98 <vigi98_at_my-deja.com>
Date: Wed, 29 Aug 2001 18:51:34 GMT
Message-ID: <WQaj7.196$yS2.392271_at_nnrp3.proxad.net>



[Quoted] Hi all,

Some time ago, I sent a question on how fill in a cursor with dynamic query. [Quoted] I had my response, thanks a lot. But now, I have an other problem : I cannot [Quoted] 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 Received on Wed Aug 29 2001 - 20:51:34 CEST

Original text of this message