Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

creating table and appending current date in table name from unix script

 
nishant vaishnaw
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
using my below code i can able to create the table and appending current date in table name from unix script.
But the script not working properly fronm unix cron job.
sqlplus <>/<><> <<EOF
COLUMN today_col NEW_VALUE today;
SELECT TO_CHAR ( SYSDATE, 'DDMMYYYY') AS today_col FROM dual;
CREATE TABLE VALID_NUMBE_&today AS (SELECT * FROM VALID_NUMBER);
EXIT


can you please suggest any good script for creating table and appanding todays date with table name.

thanks
 
Anayonkar Shivalkar
Bartender
Posts: 1557
5
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi nishant,

I doubt how much I can help you here, because I'm not well versed in cron jobs.

However, I can suggest following approach:
1) Create a simple query which does this (creating table with date appended to it)
2) Try to incorporate this in simple shell script and make sure that it is doing the job - this means that sql query and connection details are correct
3) Push this script to cron job

This way, it will be easy to identify the actual issue.

Also, please let us know what do you mean by
But the script not working properly fronm unix cron job.

Are there any error messages? Please provide more details so that it will be help others to help you.
 
nishant vaishnaw
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Have created one script , which is working fine,but when iam running from Cron it is not giving any resut,Please guide :
sqlplus <>/<>@<> <<EOF > logcr.txt
Declare
select_s VARCHAR2(2556);
v_sysdate VARCHAR2(10) := to_char(SYSDATE ,'DDMMYYYY');
BEGIN
select_s := 'CREATE TABLE TABLE1_'||v_sysdate||' AS SELECT * FROM VALID_NUMBER_BACKUP';
EXECUTE IMMEDIATE select_s;
COMMIT;
END;
/
EXIT
EOF
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic