Hi All,
I have a procedure proc_emp and script emp_details.sql . I need to run the procedure and the script one after another ( procedure first and script second ) automatically based on dates in sql plus .
My procedure:
create or replace procedure proc_emp as
cursor c1 is
select * from employee ;
begin
for i in c1 loop
dbms_output.put_line ('emp_id is '|| i.emp_id);
dbms_output.put_line ('emp name is '|| i.emp_name);
end loop;
end ;
My sql script emp_details.sql :
SET echo OFF ver OFF pages 0 trimspool ON feed OFF und OFF;
ALTER session SET current_schema=&1;
SET term OFF;
SET linesize 30000;
SET sqlblanklines ON;
SET WRAP OFF;
spool &3
SELECT ' '
|| EMP_NAME
||'|'
|| EMP_DOB
||'|'
|| EMP_SALARY
||'|'
|| EMP_ID
||'|'
||'' AS output
FROM
( SELECT EMP_NAME,EMP_DOB,EMP_SALARY,EMP_ID FROM EMPLOYEE);
SPOOL OFF ;
EXIT ;
Suppose i run procedure first and the script automatically in sql plus . Suppose i run today procedure and script , next day also it should run auomatically till a cut off date says 10 days from today's date . we have oracle scheduler but is it possible to run in sql plus with procedure and script combined. Is there any other way . Please advise
Regards,
Uday