Skip to Main Content
  • Questions
  • How to schedule a script in sql developer

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nalaka.

Asked: June 30, 2016 - 10:11 am UTC

Last updated: July 02, 2016 - 1:42 am UTC

Version: 4.1.1

Viewed 1000+ times

You Asked

Hi;
I have tried many ways to schdule an a script using sql developer;But for these scripts those are failed.But those can nicely run using built in oracle sql-plus.i can use the script by using @ sign and with the file name (eg : @filename)

Here is a example code;

set serveroutput on size 1000000
set verify off
set linesize 200
set feedback off
set head off
set echo off
set trimspool on
set numformat 999,999,999,999,999,999.99
set timing off

alter session set NLS_DATE_FORMAT='dd-mm-yyyy

set termout on

col date_stp new_value date_stp noprint
select to_char(SYSDATE,'YYYYMMDD-DayHH24-MI-SS') date_stp from dual;

spool 'sum_calculation-&date_stp'

select 'Start Time: ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
exec sum_calculation;
select 'End Time : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;

select EOD_DATE , sum(total_BALANCE) from sum_calculation where to_date(EOD_DATE,'dd-mm-yyyy')=to_date(sysdate-1,'dd-mm-yyyy') group by EOD_DATE order by EOD_DATE;

spool off

and Connor said...

Besides the missing quote at the end of the alter session, it looked fine to me.

I did the following in SQL Plus first

SQL> create or replace
  2  procedure sum_calculation is
  3  begin
  4    dbms_output.put_line('Some proc output');
  5  end;
  6  /

Procedure created.



and then ran it from SQL Dev with no dramas

https://youtu.be/XWLz1Cwou3A


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Re

Nalaka, July 01, 2016 - 10:02 am UTC

Actually i need to run a set of scripts to a single procedure and then schedule using oracle sql developer.In my queries it contains couple of truncate tables and create tables and indexes.

Thank you
Connor McDonald
July 02, 2016 - 1:42 am UTC

That doesn't tell us much ... we dont have a magic mirror to see your code :-)

- Show us your code
- Show us the error
- Describe the problem

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library