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).

 
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,'E1QSFR05')
 
   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_number() over (partition by C.BEZ_LANG,A.E1_GRP_NO order by C.BEZ_LANG),1,C.BEZ_LANG,NULL)||','||B.E1_QUES_NO "Group/Question" from E1_GRP_MSTR A,  E1_GRP_QUES_MSTR B, E11POS_MSTR C,E1_SF_HDR';
select_string:='select C.E1_POS_ID "POS_NO",A.E1_GRP_NO,B.E1_QUES_NO,C.BEZ_LANG from E1_GRP_MSTR A,  E1_GRP_QUES_MSTR B, E11POS_MSTR C,E1_SF_HDR';
where_string:= 'where E1_ANLY_TYPE =:a';
cond_string:='and A.E1_GRP_NO=B.E1_GRP_NO';
 
begin
  if(P_Hdr.E1_ANLY_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_GRP_NO,detrec1.E1_QUES_NO,
    detrec1.BEZ_LANG;
 
        exit when curs1%notfound;
        P_Dtls.EXTEND;
        P_Dtls(P_Dtls.LAST):= 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('$Revision: 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_LANG:='Hi';
 
P_Dtls.EXTEND;
     P_Dtls(P_Dtls.LAST):= detrec1;
     DBMS_OUTPUT.PUT_LINE('Procedure Call'||P_Dtls.LAST);
 
    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_LANG:='Bye';
 
P_Dtls.EXTEND;
     P_Dtls(P_Dtls.LAST):= detrec1;
     DBMS_OUTPUT.PUT_LINE('Procedure Call'||P_Dtls.LAST);    
 
    P_Dtls  := E1C_SF_DTLS_1();     
 
 
e1qsfr05.E1QSFPOP(P_UlcIfc,detrec,P_Dtls);
    end;
O/P:
 
Procedure Call : 1
Procedure Call: 1
 
 

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.