oracle procedure example, Oracle

oracle procedure example

--Oracle 11g Express Edition
--please drop objects you've created at the end of the script 
--or check for their existance before creating
--'\\' is a delimiter

create or replace procedure EMPTY_CHECKLIST (v_bu_code_type varchar, v_from_date date,v_to_date date)
AS
  v_bu_code varchar(50);
  v_bu_type varchar(50);
  len number;
  len1 number;
 
  l_value   varchar2(2000);
BEGIN
  dbms_output.put_line(v_bu_code_type);
  dbms_output.put_line(v_from_date);
  dbms_output.put_line(v_to_date);
  len := REGEXP_COUNT( v_bu_code_type, ',' );
  
  
  for i in 1 .. len
  loop
     l_value := regexp_substr(v_bu_code_type,'[^,]+',1,i);
     dbms_output.put_line(l_value);
     len1 := length(l_value);
     v_bu_code := REGEXP_SUBSTR(l_value,'(\d)([^,]+)');
     v_bu_type := SUBSTR(l_value,1,len1-3);
     dbms_output.put_line(v_bu_code);
     dbms_output.put_line(v_bu_type);
     
     
  end loop; 
--  EXCEPTION
--WHEN OTHERS THEN
 --  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;\\

begin
EMPTY_CHECKLIST('STO001,STO002,SO004,',TO_DATE('10102015','DDMMYYYY'),TO_DATE('30102015','DDMMYYYY'));
end;


run \| edit \| history \| help	0

edited to show how to call procedures in oracle at rextester

by renz, 5 years ago

Please log in to post a comment.