Skip to Main Content

Having issue while passing PL/SQL collections as a parameter.

User_BU3NGMar 26 2021

Hello Oracle Experts,

I am facing while calling PKG.PROC_AUTO Procedure with collection as a parameter which is ISSUECOll, where as i am able to print these values properly. when i pass i am getting following error :
Error(142,13): PLS-00306: wrong number or types of arguments in call to 'PR_REDL_SREQ_AUTOMATE'
below is the sample code.
create or replace procedure "PROC1" (out_message OUT VARCHAR2) IS
lt_sr_hdr TP_TAB_BODS_UC_HDR;-- TP_TAB_UC_HDR;
ls_sr_hdr TP_BODS_UC_HDR;
ISSUECOll TP_BODS_TAB_UC_SCOPE;
lt_sr_user TP_BODS_UC_SCOPE;
lv_timestamp TIMESTAMP;
lv_exist_flag VARCHAR2(1);
lo_error_code NUMBER;
lo_error_msg VARCHAR2(32000);
lt_svtable TP_TAB_UC_SERV;
lo_write_msg VARCHAR2(200);
lo_jobname VARCHAR2(200);
lo_sreq_prev_stat VARCHAR2(30);
lo_sreq_prev_stag VARCHAR2(30);
counter integer :=0;
BEGIN

lt\_sr\_hdr := parseheader;  
lt\_svtable := TP\_TAB\_UC\_SERV ();  

FOR I IN lt_sr_hdr.FIRST .. lt_sr_hdr.LAST LOOP
lt_sr_hdr.EXTEND;
IF UPPER(lt_sr_hdr(I).UC_DBID) = 'ORACLE' AND lt_sr_hdr(i).uc_sreq = '788769' THEN
ISSUECOll := PARSE2(lt_sr_hdr(I).uc_sreq);

dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(i).UC\_SSYS);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(i).UC\_TABL);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(i).UC\_EXCL);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(i).UC\_FILT);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(2).UC\_SSYS);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(2).UC\_TABL);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(2).UC\_EXCL);  
dbms\_output.put\_line( 'scope('||i||'):'||ISSUECOll(2).UC\_FILT);  


       PKG.PROC\_AUTO  
       (  
       to\_char(lt\_sr\_hdr(i).uc\_sreq) ,  
       '' ,  
       lt\_sr\_hdr(i).uc\_name ,  
       lt\_sr\_hdr(i).uc\_abbr ,  
       lt\_sr\_hdr(i).uc\_mart ,  
       lt\_sr\_hdr(i).uc\_aown ,  
       lt\_sr\_hdr(i).uc\_domn , --Mandate  
       lt\_sr\_hdr(i).uc\_accl ,  
       lt\_sr\_hdr(i).uc\_prjc ,  
       lt\_sr\_hdr(i).uc\_comm ,  
       lt\_sr\_hdr(i).uc\_dbid , --Mandate  
      ISSUECOll,  
       NULL, --lt\_sr\_user,  
       lt\_svtable,  
    --  'RDA',  
       'UI',  
       out\_message  
       );  
       lo\_write\_msg := 'Script generation scheduled in background - ' || out\_message;  
       INSERT INTO "T\_REDL\_SSLOG\_ACTIVITY" ("UC\_NUMB","UC\_SREQ", "SS\_CRTS", "SS\_ORIGIN", "SS\_COMN", "SS\_VIEW")  
       VALUES ('',lt\_sr\_hdr(i).uc\_sreq,lv\_timestamp,'PROCESS\_FILE',lo\_write\_msg, 'N');  
       COMMIT;  
END IF;  
  END LOOP;  

END;

This post has been answered by James Su on Mar 26 2021
Jump to Answer
Comments
Post Details
Added on Mar 26 2021
3 comments
22 views