Tuesday, 30 June 2020

ORACLE PROCEDURE WITH VARIABLE ARGUMENTS

set serveroutput on ;
CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
\
CREATE OR REPLACE PROCEDURE testing(t_in MyType, v_type in varchar) IS
  ret varchar2(100):='';
 BEGIN
   FOR i IN 1..t_in.count LOOP
    dbms_output.put_line(t_in(i));
    IF i < t_in.count THEN
     ret:=ret||' '''||t_in(i)||''',  ';
    ELSE
     ret:=ret||' '''||t_in(i)||'''  ';
    END IF ; 
   END LOOP;
  dbms_output.put_line(ret);
  dbms_output.put_line(v_type);
 END;
\
 DECLARE
      v_t MyType;
    BEGIN
      v_t := MyType();
      v_t.EXTEND(3);
      v_t(1) := 'WAPPR';
      v_t(2) := 'APPR';
      v_t(3) := 'INPRG';
      testing(v_t,'Test');
    END;