Home » SQL & PL/SQL » Client Tools » To call SQL script based on date range
To call SQL script based on date range [message #646665] Wed, 06 January 2016 12:32 Go to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Hi Guys,

I am having below Test.sql where i am accepting values from user as start date and end date

COLUMN  sub_script  NEW_VALUE  sub_call

accept start_date char prompt 'Enter start date ( dd.mm.yyyy ) : '  
accept   	end_date char prompt 'Enter end date ( dd.mm.yyyy ) : '  

ACCEPT  user_yn  PROMPT  "Would you like to Process records for 01-JAN-2015? (Y, N): "
SELECT  CASE
            WHEN  UPPER (LTRIM ('&user_yn')) LIKE 'Y%'
            THEN  'call_sql'
            ELSE  'do_nothing'
        END     AS sub_script 
FROM    dual;
 
@@&sub_script  &start_date 


I need to loop the values for eg 01-JAN-2015 and 05-JAN-2015 is my start and end dates, i need to pass the dates 01-JAN-2015 to another sql script
call_sql 01-JAN-2015, like this once done then 02-JAN-2015,03-JAN-2015...
Re: To call SQL script based on date range [message #646668 is a reply to message #646665] Wed, 06 January 2016 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3882913

>i need to pass the dates 01-JAN-2015
please make up your/my mind on how month is specified; as MON or MM
> prompt 'Enter start date ( dd.mm.yyyy )

Realize that MON is language dependent & client may have different NLS_LANG than what the DB has
Re: To call SQL script based on date range [message #646669 is a reply to message #646665] Wed, 06 January 2016 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generate and spool what you want in a file and then call the file.
Something like:
SQL> select '@myscript '''||to_char(to_date('&start_date','DD/MM/YYYY')+level-1,'DD/MM/YYYY')||'''' calls
  2  from dual
  3  connect by level <=to_date('&end_date','DD/MM/YYYY')-to_date('&&start_date','DD/MM/YYYY')+1
  4  /
Enter value for start_date: 01/01/2016
Enter value for end_date: 05/01/2016
Enter value for start_date: 01/01/2016
CALLS
----------------------
@myscript '01/01/2016'
@myscript '02/01/2016'
@myscript '03/01/2016'
@myscript '04/01/2016'
@myscript '05/01/2016'

Re: To call SQL script based on date range [message #646679 is a reply to message #646669] Wed, 06 January 2016 13:57 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
should i need to use spool on
Re: To call SQL script based on date range [message #646680 is a reply to message #646679] Wed, 06 January 2016 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is what I meant by "spool ... in a file".

Re: To call SQL script based on date range [message #646681 is a reply to message #646680] Wed, 06 January 2016 14:08 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
actually i didn't get how i can call in my script
ACCEPT  user_yn  PROMPT  "Would you like to Process records for 01-JAN-2015? (Y, N): "
SELECT  CASE
            WHEN  UPPER (LTRIM ('&user_yn')) LIKE 'Y%'
            THEN  'call_sql'
            ELSE  'do_nothing'
        END     AS sub_script 
FROM    dual;
 
[b]@@&sub_script  &start_date [/b]
Re: To call SQL script based on date range [message #646682 is a reply to message #646681] Wed, 06 January 2016 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You call the file you spool.

Re: To call SQL script based on date range [message #646683 is a reply to message #646682] Wed, 06 January 2016 14:22 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
i didnt get how to call and spool like user accepting
Re: To call SQL script based on date range [message #646684 is a reply to message #646683] Wed, 06 January 2016 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

spool something
select ... above
spool off
@something

Re: To call SQL script based on date range [message #646685 is a reply to message #646683] Wed, 06 January 2016 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
The following just expands Michel's example.

If you have a test.sql script like this:
COLUMN sub_script  NEW_VALUE  sub_call
ACCEPT start_date CHAR PROMPT 'Enter start date ( dd.mm.yyyy ) : '  
ACCEPT end_date   CHAR PROMPT 'Enter end date ( dd.mm.yyyy ) : '  
ACCEPT user_yn    CHAR PROMPT 'Would you like to Process records for 01-JAN-2015? (Y, N): '
STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGESIZE 0 VERIFY OFF
SPOOL something.sql
SELECT  CASE
          WHEN UPPER (LTRIM ('&user_yn')) LIKE 'Y%'
          THEN '@myscript ''' || TO_CHAR (TO_DATE ('&start_date', 'DD/MM/YYYY') + LEVEL - 1, 'DD/MM/YYYY') || '''' 
          ELSE NULL
        END calls
FROM    DUAL
CONNECT BY LEVEL <= TO_DATE ('&end_date', 'DD/MM/YYYY') - TO_DATE ('&&start_date', 'DD/MM/YYYY') + 1
/
SPOOL OFF
START saved_settings
@ something.sql


and you run it by typing:
@ test.sql


entering the following at the prompts:
SCOTT@orcl> ACCEPT start_date CHAR PROMPT 'Enter start date ( dd.mm.yyyy ) : '
Enter start date ( dd.mm.yyyy ) : 01.01.2015
SCOTT@orcl> ACCEPT end_date   CHAR PROMPT 'Enter end date ( dd.mm.yyyy ) : '
Enter end date ( dd.mm.yyyy ) : 05.01.2015
SCOTT@orcl> ACCEPT user_yn        CHAR PROMPT 'Would you like to Process records for 01-JAN-2015? (Y, N): '
Would you like to Process records for 01-JAN-2015? (Y, N): Y


then that will create and run a file called something.sql, with the following contents:
@myscript '01/01/2015'
@myscript '02/01/2015'
@myscript '03/01/2015'
@myscript '04/01/2015'
@myscript '05/01/2015'


This assumes that you have a script named myscript.sql that accepts individual dates in this manner. It will cause it to run once for each date shown above.
Re: To call SQL script based on date range [message #646686 is a reply to message #646665] Wed, 06 January 2016 15:47 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Yes this what i am having apart from that i would like to pass another parameter something like this, so basically two parameters to be passed where tab_ is constant value only thing is it will get append tab_1,tab_2


@myscript '01/01/2015' TAB_1
@myscript '02/01/2015' TAB_2
@myscript '03/01/2015' TAB_3
@myscript '04/01/2015' TAB_4
@myscript '05/01/2015' TAB_5



And i don't think so this will prompt user by asking for next subsequent range since there is no loop

Would you like to Process records for 01-JAN-2015? (Y, N): Y
@myscript '01/01/2015' TAB_1
Would you like to Process records for 02-JAN-2015? (Y, N): Y
@myscript '02/01/2015' TAB_1
Would you like to Process records for 03-JAN-2015? (Y, N): Y
.
.

[Updated on: Wed, 06 January 2016 16:09]

Report message to a moderator

Re: To call SQL script based on date range [message #646697 is a reply to message #646686] Thu, 07 January 2016 00:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
I think I understand now what you were trying to do in the beginning. The following is closer to your original concept without any spooling. There are four scripts. Just run main.sql and it will call the others and prompt you. You can copy these four files and test them as is, then modify the process.sql file to include or call whatever else you want. It should prompt you for the start_date and end_date once, then prompt you for the user_yn once for each date, processing those for which you enter y and skipping others, stopping after the last date.

-- main.sql:
ACCEPT start_date CHAR PROMPT 'Enter start date ( dd.mm.yyyy ) : '  
ACCEPT end_date   CHAR PROMPT 'Enter end date ( dd.mm.yyyy ) : '
@test


-- test.sql:

ACCEPT user_yn  PROMPT  'Would you like to Process records for ''&start_date''? (Y, N): '
SET TERM OFF VERIFY OFF
COLUMN sub_script NEW_VALUE sub_call
SELECT CASE 
         WHEN  UPPER (LTRIM ('&user_yn')) LIKE 'Y%' 
         AND   TO_DATE ('&end_date', 'DD.MM.YYYY') >= TO_DATE ('&start_date', 'DD.MM.YYYY')
         THEN '@process &start_date.'
         WHEN  TO_DATE ('&end_date', 'DD.MM.YYYY') >= TO_DATE ('&start_date', 'DD.MM.YYYY')
         THEN '@myscript'
         ELSE ''
       END as sub_script
FROM   DUAL
/
SET TERM ON
@&sub_call.


-- process.sql:
-- whatever processing you want to do, such as:
EXECUTE DBMS_OUTPUT.PUT_LINE ('&start_date.')
@myscript


-- myscript.sql:
COLUMN next_date NEW_VALUE start_date
SELECT TO_CHAR (TO_DATE ('&start_date', 'DD.MM.YYYY') + 1, 'DD.MM.YYYY') next_date
FROM   DUAL
/
@test



[Updated on: Thu, 07 January 2016 00:34]

Report message to a moderator

Re: To call SQL script based on date range [message #646705 is a reply to message #646697] Thu, 07 January 2016 02:03 Go to previous messageGo to next message
rohit_shinez
Messages: 138
Registered: January 2015
Senior Member
Basically I need to pass two parameters in myscript.sql one
is date and T1 before professing the script ask the user to call the script or not

Myscript 01-jan-2015 t1
After this script execution
Prompt user to call the script with next date
Myscript 02-Jan-2015 t2

Like this

 myscript.sql
Alter table m1 exchange with &1 with table &2 using index

Re: To call SQL script based on date range [message #646706 is a reply to message #646705] Thu, 07 January 2016 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can't you try to do it now with all what we have posted?

Re: To call SQL script based on date range [message #646740 is a reply to message #646705] Thu, 07 January 2016 11:00 Go to previous message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
The following includes everything but your myscript.sql. I have made some changes below and used different file names so that they will not conflict with your myscript.sql. You should be able to just run main.sql and be prompted for dates once, then whether to process each date. The increments to dates and table numbers will be automatic and it will pass both to your myscript.sql. I have included a substitute myscript.sql that I used for testing, since I don't have your tables and partitions and data and indexes and such. You might want to test with that first, then substitute your own myscript.sql with your alter table statement.

-- main.sql:
COLUMN next_tab NEW_VALUE start_tab
SELECT 't1' next_tab 
FROM   DUAL
/
ACCEPT start_date CHAR PROMPT 'Enter start date ( dd.mm.yyyy ) : '  
ACCEPT end_date   CHAR PROMPT 'Enter end date ( dd.mm.yyyy ) : '
@test


-- test.sql:
ACCEPT user_yn  PROMPT  'Would you like to Process records for ''&start_date''? (Y, N): '
SET TERM OFF VERIFY OFF
COLUMN sub_script NEW_VALUE sub_call
SELECT CASE 
         WHEN  UPPER (LTRIM ('&user_yn')) LIKE 'Y%' 
         AND   TO_DATE ('&end_date', 'DD.MM.YYYY') >= TO_DATE ('&start_date', 'DD.MM.YYYY')
         THEN '@process'
         WHEN  TO_DATE ('&end_date', 'DD.MM.YYYY') >= TO_DATE ('&start_date', 'DD.MM.YYYY')
         THEN '@continue'
         ELSE ''
       END as sub_script
FROM   DUAL
/
SET TERM ON
@&sub_call.


-- process.sql:
@myscript '&start_date' '&start_tab'
@continue


-- continue.sql:
COLUMN next_date NEW_VALUE start_date
SELECT TO_CHAR (TO_DATE ('&start_date', 'DD.MM.YYYY') + 1, 'DD.MM.YYYY') next_date
FROM   DUAL
/
COLUMN next_tab NEW_VALUE start_tab
SELECT 't' || (SUBSTR ('&start_tab', 2) + 1) next_tab 
FROM   DUAL
/
@test


-- myscript:
EXEC DBMS_OUTPUT.PUT_LINE ('&1');
EXEC DBMS_OUTPUT.PUT_LINE ('&2');


Previous Topic: Is there a difference between procedure successfully completed and anonymous block competed? (merged 3)
Next Topic: Does Oracle SQL Developer Support Unicode ?
Goto Forum:
  


Current Time: Thu Oct 18 17:05:52 CDT 2018