Calling a Package

[ramorac]

hi all

i am a new member to this forum.i am tryin to call a package from an anonymous pl/sql block(say a wrapper).the package is like(IS SPECIFIC TO OUR APPLICATION).
[code=oracle]

create or replace package E1QSFR05 as

procedure E1QSFPOP(
P_UlcIfc in out SRYULCIFC_1
, P_Hdr in out nocopy E1Y_SF_HDR_1
, P_Dtls in out nocopy E1C_SF_DTLS_1
);

function F_Revision return char;

end E1QSFR05;
/


create or replace package body E1QSFR05 as

LOG_DECLARE_ID( G_LogId,'E1QSFR 05')

G_UB number;
G_SP number;
G_SU number;

procedure E1QSFPOP(
P_UlcIfc in out SRYULCIFC_1
,P_Hdr in out nocopy E1Y_SF_HDR_1
,P_Dtls in out nocopy E1C_SF_DTLS_1
) is

detrec1 E1Y_SF_DTLS_1;
anly_type_1 varchar2(1);


select_string varchar2(1000);
where_string varchar2(100);
cond_string varchar2(100);
sql_string varchar2(4000);

type curs_type is ref cursor;
curs1 curs_type;

begin
P_Dtls := E1C_SF_DTLS_1() ;

--select_string:= 'select C.E1_POS_ID "POS_NO", decode(row_numb er() over (partition by C.BEZ_LANG,A.E1 _GRP_NO order by C.BEZ_LANG),1,C .BEZ_LANG,NULL) ||','||B.E1_QUE S_NO "Group/Question" from E1_GRP_MSTR A, E1_GRP_QUES_MST R B, E11POS_MSTR C,E1_SF_HDR';
select_string:= 'select C.E1_POS_ID "POS_NO",A.E1_G RP_NO,B.E1_QUES _NO,C.BEZ_LANG from E1_GRP_MSTR A, E1_GRP_QUES_MST R B, E11POS_MSTR C,E1_SF_HDR';
where_string:= 'where E1_ANLY_TYPE =:a';
cond_string:='a nd A.E1_GRP_NO=B.E 1_GRP_NO';

begin
if(P_Hdr.E1_ANL Y_TYPE = '1') then
anly_type_1:='1 ';
sql_string := select_string|| ' '||where_string ||' '||cond_string;

elsif(P_Hdr.E1_ ANLY_TYPE = '2') then
anly_type_1:='2 ';
sql_string := select_string|| ' '||where_string ||' '||cond_string;
end if;

open curs1 for sql_string using anly_type_1;
loop
detrec1:= E1Y_SF_DTLS_1.F _GetNewRecord() ;
fetch curs1 into detrec1.E1_POS_ ID,detrec1.E1_G RP_NO,detrec1.E 1_QUES_NO,
detrec1.BEZ_LAN G;

exit when curs1%notfound;
P_Dtls.EXTEND;
P_Dtls(P_Dtls.L AST):= detrec1;
end loop;
close curs1;
end;

exception
when others then
RAISE_EXCEPTION

end E1QSFPOP;

procedure InitPackage is
begin
G_UB := SRQETIF0.F_UB;
G_SP := SRQETIF0.F_SP;
G_SU := SRQETIF0.F_SU;
end;

function F_Revision return char is
begin
return('$Revisi on: 0.0 $');
end F_Revision;

begin
InitPackage;
end E1QSFR05;



i WANT TO CALL THE PACKAGED PROCEDURE NOW.I USED THIS TO CALL.. I AM NOT GETTING THE REQUIRED O/P.

set serveroutput on size 100000

Declare

P_UlcIfc SRYULCIFC_1;
P_Dtls E1C_SF_DTLS_1;
P_Hdr E1Y_SF_HDR_1;
detrec E1Y_SF_HDR_1;
detrec1 E1Y_SF_DTLS_1;
Begin

P_Dtls := E1C_SF_DTLS_1() ;
detrec1 := E1Y_SF_DTLS_1.F _GetNewRecord() ;
detrec := E1Y_SF_HDR_1.F_ GetNewRecord();

detrec.E1_ANLY_ TYPE:=1;

detrec1.E1_POS_ ID:= '101';
detrec1.E1_GRP_ NO:= '5';
detrec1.E1_QUES _NO:='1';
detrec1.BEZ_LAN G:='Hi';

P_Dtls.EXTEND;
P_Dtls(P_Dtls.L AST):= detrec1;
DBMS_OUTPUT.PUT _LINE('Procedur e Call'||P_Dtls.L AST);

P_Dtls := E1C_SF_DTLS_1() ;

detrec1 := E1Y_SF_DTLS_1.F _GetNewRecord() ;
detrec := E1Y_SF_HDR_1.F_ GetNewRecord();

detrec.E1_ANLY_ TYPE:=1;

detrec1.E1_POS_ ID:= '102';
detrec1.E1_GRP_ NO:= '4';
detrec1.E1_QUES _NO:='2';
detrec1.BEZ_LAN G:='Bye';

P_Dtls.EXTEND;
P_Dtls(P_Dtls.L AST):= detrec1;
DBMS_OUTPUT.PUT _LINE('Procedur e Call'||P_Dtls.L AST);

P_Dtls := E1C_SF_DTLS_1() ;


e1qsfr05.E1QSFP OP(P_UlcIfc,det rec,P_Dtls);
end;
O/P:

Procedure Call : 1
Procedure Call: 1

[/code]

pleas help me with this..

[amitpatel66]

Could you please provide more explanation about your procedure and the output that you are expecting?

[ramorac]

Could you please provide more explanation about your procedure and the output that you are expecting?

sure amit..
actually i m trying to populate the fields in the front end..
just fr testing purpose..when i call the package from a pl/sql block as u see..i gt an o/p like
procedure call :1..

bt i require the o/p to be: 101 5 1 hi..(something like this).. for THE BELOW DECLARATION..am i clear? pleas any suggestions

detrec1.E1_POS_ ID:= '101';
detrec1.E1_GRP_ NO:= '5';
detrec1.E1_QUES _NO:='1';
detrec1.BEZ_LAN G:='Hi';

[amitpatel66]

sure amit..
actually i m trying to populate the fields in the front end..
just fr testing purpose..when i call the package from a pl/sql block as u see..i gt an o/p like
procedure call :1..

bt i require the o/p to be: 101 5 1 hi..(something like this).. for THE BELOW DECLARATION..am i clear? pleas any suggestions

detrec1.E1_POS_ ID:= '101';
detrec1.E1_GRP_ NO:= '5';
detrec1.E1_QUES _NO:='1';
detrec1.BEZ_LAN G:='Hi';

Since you are printing array's LAST position using array.LAST, it is printing as 1. If you need to print other values then include them in DBMS_OUTPUT as well.