Re: Cursor as output of a PLSQL procedure

"Vigi98" <vigi98_at_my-deja.com> wrote in message news:zZlj7.955$QI3.368758_at_nnrp6.proxad.net...
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:toqt2p45otks54_at_news.demon.nl...
> >
> > "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
> >
> Sorry for such a question, but what do you mean by "Move the type
definition
> to package level in the package space " ? Is package space an other word
> for package body ?
>
>

Typo.
Each package has a package spec and a package body. Definitions in the package spec will be visible globally. You can also have
create or replace package body foo
as
type ...

procedure bar
begin
end;

end ; -- package body

which will make the type visible to all procedures in that package. You can not define such an OUT type *locally* in the *procedure* (This is a generic procedural languages issue, and has nothing to do with PL/SQL)

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Aug 30 2001 - 10:47:38 CEST