| To call SQL script based on date range [message #646665] |
Wed, 06 January 2016 12:32  |
 |
rohit_shinez
Messages: 139
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 #646685 is a reply to message #646683] |
Wed, 06 January 2016 15:17  |
 |
Barbara Boehmer
Messages: 9104
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:
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 |
|
rohit_shinez
Messages: 139
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 |
|
Barbara Boehmer
Messages: 9104
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 #646740 is a reply to message #646705] |
Thu, 07 January 2016 11:00 |
|
Barbara Boehmer
Messages: 9104
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');
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
