as93is
asked on
Shell Script
SHELL SCRIPT
We have to create a shell script with an Oracle INSERT command to insert rows into Table B from Table A every night with the following two conditions:
1. where users NOT IN XX and
2. based on SYSDATE
This shell script will be scheduled to run every night through the cron.
This is an urgent task. Please respond at your earliest convenience.
Thanks!
We have to create a shell script with an Oracle INSERT command to insert rows into Table B from Table A every night with the following two conditions:
1. where users NOT IN XX and
2. based on SYSDATE
This shell script will be scheduled to run every night through the cron.
This is an urgent task. Please respond at your earliest convenience.
Thanks!
ASKER
The suggestions are useful.
Please give me a sample of a complete shell script. I am new to AIX.
I want to use this shell script in the cron job.
I want to try this script and finalize it today. This is an high priority task.
Thanks!!!
Please give me a sample of a complete shell script. I am new to AIX.
I want to use this shell script in the cron job.
I want to try this script and finalize it today. This is an high priority task.
Thanks!!!
This is on way..
#! /bin/ksh
sqlplus username/password >> /var/tmp/sqllogg.log 2>&1 <<!
@/oracle/sqlscripts/somefi le.sql
exit
!
#! /bin/ksh
sqlplus username/password >> /var/tmp/sqllogg.log 2>&1 <<!
@/oracle/sqlscripts/somefi
exit
!
ASKER
I am new to AIX
Please explain what the following code will do. This is code from your example:
>> /var/tmp/sqllogg.log 2>&1 <<!
Also, could you please suggest another way in case the above doesn't work in my environment.
Thanks!!!
Please explain what the following code will do. This is code from your example:
>> /var/tmp/sqllogg.log 2>&1 <<!
Also, could you please suggest another way in case the above doesn't work in my environment.
Thanks!!!
/var/tmp/sqllogg.log is the logfile from the sqlscript you are running.
In other words.. You can check how the sql-job went.
It's good to create a log if you are running the job through the crontab.
In other words.. You can check how the sql-job went.
It's good to create a log if you are running the job through the crontab.
ASKER
Thank you very much for your quick reponse with useful sample code and explanation.
I will try this and get back to you.
Thanks!!!
I will try this and get back to you.
Thanks!!!
why is the schell script really a requirement?
you can create a procedure and schedule a dbms_job.
then, it's in the database
you can create a procedure and schedule a dbms_job.
then, it's in the database
CREATE OR REPLACE PROCEDURE why_use_shell_script
IS
BEGIN
INSERT INTO TABLE_B (SELECT * FROM TABLE_A WHERE users NOT IN XX);
commit;
END;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'why_use_shell_script;'
,next_date => to_date('20081009','yyyymmdd')
,interval => 'TRUNC(SYSDATE) + 1 + 8/24 + 30/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
ASKER
There is TABLE_A in the ORACLE database. There is a DATABASE VIEW based on TABLE_A.
The platform is AIX.
Here are the REQUIREMENTS:
1. EXPORT TABLE_A data EVERY NIGHT.
2. EVERY NIGHT AFTER THE EXPORT
INSERT into TABLE_B (SELECT a, b, c
FROM VIEW
WHERE user NOT IN XXX);
3. EVERY NIGHT AFTER THE INSERT
TRUNCATE TABLE_A.
STEP 1 - CREATE THREE SHELL SCRIPTS
I thought that I could create a shell script for each of the above requirements as follows:
1. One SHELL SCRIPT for EXPORT
2. One SHELL SCRIPT for INSERT with the INSERT statement in a SQL script
3. One SHELL SCRIPT for TRUNCATING TABLE_A.
STEP 2 - SET UP THREE CRON JOBS
SET UP THREE CRON JOBS TO RUN THE ABOVE THREE SHELL SCRIPTS EVERY NIGHT WITH A GAP OF HALF AN HOUR.
Please let me know if I've to SET VARIABLES in each of the above shell scripts.
Please let me know how best to meet the above requirements with examples/sample code ASAP.
This is a high priority task with a strict deadline - 10-13-2008.
Thanks.
The platform is AIX.
Here are the REQUIREMENTS:
1. EXPORT TABLE_A data EVERY NIGHT.
2. EVERY NIGHT AFTER THE EXPORT
INSERT into TABLE_B (SELECT a, b, c
FROM VIEW
WHERE user NOT IN XXX);
3. EVERY NIGHT AFTER THE INSERT
TRUNCATE TABLE_A.
STEP 1 - CREATE THREE SHELL SCRIPTS
I thought that I could create a shell script for each of the above requirements as follows:
1. One SHELL SCRIPT for EXPORT
2. One SHELL SCRIPT for INSERT with the INSERT statement in a SQL script
3. One SHELL SCRIPT for TRUNCATING TABLE_A.
STEP 2 - SET UP THREE CRON JOBS
SET UP THREE CRON JOBS TO RUN THE ABOVE THREE SHELL SCRIPTS EVERY NIGHT WITH A GAP OF HALF AN HOUR.
Please let me know if I've to SET VARIABLES in each of the above shell scripts.
Please let me know how best to meet the above requirements with examples/sample code ASAP.
This is a high priority task with a strict deadline - 10-13-2008.
Thanks.
Hello!
"Please let me know if I've to SET VARIABLES in each of the above shell scripts."
Yes!
Regards.
"Please let me know if I've to SET VARIABLES in each of the above shell scripts."
Yes!
Regards.
ASKER
Hi peter991,
This is NOT THE SOLUTION that I expected.
The solution that I expected was help to create the SHELL SCRIPTS for STEP 1 and STEP 2.
My question WAS NOT "Please let me know if I've to SET VARIABLES in each of the above shell scripts."
There is TABLE_A in the ORACLE database. There is a DATABASE VIEW based on TABLE_A.
The platform is AIX.
Here are the REQUIREMENTS:
1. EXPORT TABLE_A data EVERY NIGHT.
2. EVERY NIGHT AFTER THE EXPORT
INSERT into TABLE_B (SELECT a, b, c
FROM VIEW
WHERE user NOT IN XXX);
3. EVERY NIGHT AFTER THE INSERT
TRUNCATE TABLE_A.
STEP 1 - CREATE THREE SHELL SCRIPTS
1. One SHELL SCRIPT for EXPORT
2. One SHELL SCRIPT for INSERT with the INSERT statement in a SQL script
3. One SHELL SCRIPT for TRUNCATING TABLE_A.
STEP 2 - SET UP THREE CRON JOBS
SET UP THREE CRON JOBS TO RUN THE ABOVE THREE SHELL SCRIPTS EVERY NIGHT WITH A GAP OF HALF AN HOUR.
I need help to CREATE THE SHELL SCRIPTS FOR STEP 1 and STEP 2.
Please let me know how best to meet the above requirements with examples/sample code ASAP.
This is a high priority task with a strict deadline - 10-13-2008.
CAN SOMEONE HELP ME WITH THE ABOVE ASAP TO MEET MY DEADLINE?
Thanks.
This is NOT THE SOLUTION that I expected.
The solution that I expected was help to create the SHELL SCRIPTS for STEP 1 and STEP 2.
My question WAS NOT "Please let me know if I've to SET VARIABLES in each of the above shell scripts."
There is TABLE_A in the ORACLE database. There is a DATABASE VIEW based on TABLE_A.
The platform is AIX.
Here are the REQUIREMENTS:
1. EXPORT TABLE_A data EVERY NIGHT.
2. EVERY NIGHT AFTER THE EXPORT
INSERT into TABLE_B (SELECT a, b, c
FROM VIEW
WHERE user NOT IN XXX);
3. EVERY NIGHT AFTER THE INSERT
TRUNCATE TABLE_A.
STEP 1 - CREATE THREE SHELL SCRIPTS
1. One SHELL SCRIPT for EXPORT
2. One SHELL SCRIPT for INSERT with the INSERT statement in a SQL script
3. One SHELL SCRIPT for TRUNCATING TABLE_A.
STEP 2 - SET UP THREE CRON JOBS
SET UP THREE CRON JOBS TO RUN THE ABOVE THREE SHELL SCRIPTS EVERY NIGHT WITH A GAP OF HALF AN HOUR.
I need help to CREATE THE SHELL SCRIPTS FOR STEP 1 and STEP 2.
Please let me know how best to meet the above requirements with examples/sample code ASAP.
This is a high priority task with a strict deadline - 10-13-2008.
CAN SOMEONE HELP ME WITH THE ABOVE ASAP TO MEET MY DEADLINE?
Thanks.
Why 3 scripts? As time goes on and things get bigger, these individual tasks may take more than 30 minutes to complete.
I just threw this together and it is not tested. Some of the syntax may be incorrect as well, but it should be a good start.
You are going to have to fill in the ? with all the correct values for your system.
I just threw this together and it is not tested. Some of the syntax may be incorrect as well, but it should be a good start.
You are going to have to fill in the ? with all the correct values for your system.
#!/bin/ksh
# Set Oracle environment
ORACLE_HOME=?
ORACLE_SID=?
PATH=${PATH}:${ORACLE_HOME}/bin
export ORACLE_HOME ORACLE_SID PATH
logfile="nightly_script.log.`date`"
(
#
# Export TABLE_A
# NOTE: This should log in as the owner of the table
#
exp userid=user/pass file=? tables=table_a compress=n
#
# Copy data to TABLE_B and truncate TABLE_A
# NOTE: This should log in as the owner of the table and the owner of both tables
# is assumed to be the same
sqlplus << EOF
user/pass
WHENEVER SQLERROR EXIT;
INSERT into TABLE_B
(SELECT a, b, c
FROM VIEW
WHERE user NOT IN XXX
);
truncate table table_a;
exit
EOF
) > "$logfile" 2>&1
ASKER
Hi JOHNSONE,
This is an excellent solution. This is the kind of solution that I am looking for. I will include all the correct values for my system and test it thoroughly.
I was thinking of three shell scripts for my three requirements. You have combined all the three requirements into one shell script. This is good.
WHAT WILL HAPPEN EXPORT or the INSERT FAILS in the shell script?
The EXPORT of TABLE_A AND INSERT INTO TABLE_B SHOULD to be successfully completed before I TRUNCATE TABLE_A.
I will be in trouble if the EXPORT and INSERT fail and TABLE_A is truncated. This is why I was thinking of three shell scripts instead of one.
There is a STEP 2 in my requirement. I NEED HELP with the following:
SCHEDULE CRON JOB TO RUN the nightly_script.sh EVERY NIGHT AT 1 A.M.
I have to use the nightly_script.sh and schedule a cron job to run this shell script every night at 1 a.m.
Please help me with the CRON JOB code. I have complete this by 10-13-200 (Monday).
I am thankful to you for your timely help!!!
This is an excellent solution. This is the kind of solution that I am looking for. I will include all the correct values for my system and test it thoroughly.
I was thinking of three shell scripts for my three requirements. You have combined all the three requirements into one shell script. This is good.
WHAT WILL HAPPEN EXPORT or the INSERT FAILS in the shell script?
The EXPORT of TABLE_A AND INSERT INTO TABLE_B SHOULD to be successfully completed before I TRUNCATE TABLE_A.
I will be in trouble if the EXPORT and INSERT fail and TABLE_A is truncated. This is why I was thinking of three shell scripts instead of one.
There is a STEP 2 in my requirement. I NEED HELP with the following:
SCHEDULE CRON JOB TO RUN the nightly_script.sh EVERY NIGHT AT 1 A.M.
I have to use the nightly_script.sh and schedule a cron job to run this shell script every night at 1 a.m.
Please help me with the CRON JOB code. I have complete this by 10-13-200 (Monday).
I am thankful to you for your timely help!!!
ASKER
WHAT WILL HAPPEN EXPORT or the INSERT FAILS in the shell script?
The EXPORT of TABLE_A AND INSERT INTO TABLE_B SHOULD to be successfully completed before I TRUNCATE TABLE_A.
I will be in trouble if the EXPORT and INSERT fail and TABLE_A is truncated. This is why I was thinking of three shell scripts instead of one.
There is a STEP 2 in my requirement. I NEED HELP with the following:
SCHEDULE CRON JOB TO RUN the nightly_script.sh EVERY NIGHT AT 1 A.M.
I have to use the nightly_script.sh and schedule a cron job to run this shell script every night at 1 a.m.
Please help me with the CRON JOB code. I have complete this by 10-13-200 (Monday).
The EXPORT of TABLE_A AND INSERT INTO TABLE_B SHOULD to be successfully completed before I TRUNCATE TABLE_A.
I will be in trouble if the EXPORT and INSERT fail and TABLE_A is truncated. This is why I was thinking of three shell scripts instead of one.
There is a STEP 2 in my requirement. I NEED HELP with the following:
SCHEDULE CRON JOB TO RUN the nightly_script.sh EVERY NIGHT AT 1 A.M.
I have to use the nightly_script.sh and schedule a cron job to run this shell script every night at 1 a.m.
Please help me with the CRON JOB code. I have complete this by 10-13-200 (Monday).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
INSERT INTO TABLE_B (SELECT * FROM TABLE_A WHERE users NOT IN XX);
After updating that line save it to a somefile.sql (name it something meaningful so you know whats its for later).
Next create your shell script (I know this works on windows, the syntax my differ on linux) with the following line:
sqlplus username/password @"somefile.sql"