Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

My procedure and a script should run automatically in sql plus

Uday_NJul 14 2022

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

Comments

Frank Kulash

Hi, Uday_N
we have oracle scheduler but is it possible to run in sql plus with procedure and script combined.
You can use server's job scheduler to run a SQL*Plus script. It might look like this

EXEC  proc_emp
@emp_details

Is the procedure you posted really what you're running? It doesn't look like the output is being saved. Wouldn't it be helpful to have an ORDER BY clause? You could get the same results in pure SQL:

SELECT 'emp_id is '|| emp_id || '
emp name is '|| emp_name
FROM    employee;

Notice that the string literal containing 'emp name is' starts with a newline.
SELECT ' '
 || EMP_NAME
 ||'|'
 || EMP_DOB ...
There's a simpler way to generate CSV output in SQL*Plus:

SET MARKUP CSV ON  DELIMITER |

See the SQL*Plus manual: SET System Variable Summary (oracle.com)

1 person found this helpful
Arun Kumar Gupta

Add this line in script itself to make sure that both procedure and scripts gets executed one after another.
exec proc_emp;

And for some reason if you done want to modify the script, create another wrapper script like this
wrapper_emp_details.sql :

SET SERVEROUT ON 
SET FEEDBACK OFF TIMING OFF
exec proc_emp; 
@"full_path_of_emp_details_script/emp_details.sql"

Regards
Arun

1 person found this helpful
Uday_N

Hi Frank /Gupta ,
Really Thanks for your solution . I will implement it . Thanks once again for the solutions .

regards ,
uday

1 - 3

Post Details

Added on Jul 14 2022
#sqlplus
3 comments
200 views
2 mentions