Hello All,
just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table.
Can you please suggest why? or any better idea?
export FILE="/abc/autom/file/geo_JOB.csv"
Export= `sqlplus -s dev01/password@dEV3 <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL $FILE
SELECT * FROM geo_JOB ;
export FILE="/abc/autom/file/geo_JOB2.csv"
SELECT * FROM geo_JOB2;
SPOOL OFF
EXIT
EOF`
More details, please. Like script behaviour, execution logs, undesired results, error messages, what have you. It's difficult to base an analysis on thin air... and we have nothing.
Might be worthwhile to clarify some wording in your request: You're using "export" in various contexts which may become quite confusing if not sharply discriminated. "[Ee]xport" is
a keyword in bash (and other shells) for the "export" attribute to variables and functions "marking it for export to child processes" ( man bash ).
a term in ORACLE (and other DB) context to create a backup / copy of a database and/or its tables and meta data. Fortunately, the respective SQL command is "exp".
a variable in your code snippet above.
used in your description for an action that doesn't necessarily comply with what you're showing in your snippet.
Now to your script. You don't show it, but I guess you should have received an SQL error message telling you it doesn't understand the "export" command. Well, it doesn't as that is a shell command that sql can't interpret and thus ignores or, worse, crashes on. The latter may be the reason why there is only one table in the spool file. The second table should be in the file if the wrong command were simply ignored.
You'd need to define the various file variables upfront, and then switch the SPOOL ing from one to the next in sql.
BTW, the Export variable will be empty in above script as the space after the = will assign it such, and then bash will try to interpret / run the result of the sqlplus "command substitution", and error out on that (presumably).
Sorry for choosing a confusing variable "Export". I have changed it to Download_job.
The first table is exported to the first file as needed and unfortunately, there is no error message to show you.
here is my updated code:
export FILE="/abc/autom/file/geo_JOB.csv"
Download_job = `sqlplus -s dev01/password@dEV3 <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL $FILE
SELECT * FROM geo_JOB ;
export FILE="/abc/autom/file/geo_JOB2.csv"
SELECT * FROM geo_JOB2;
SPOOL OFF
EXIT
EOF`
Thank you Rudic,
What do you mean step by step?
geo_JOB1.csv
geo_JOB2.csv at the beginning of the script? Can you elaborate if possible?
Regards,
Hope
export FILE="/abc/autom/file/geo_JOB.csv"
Download_job = `sqlplus -s dev01/password@dEV3 <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL $FILE
SELECT * FROM geo_JOB ;
export FILE="/abc/autom/file/geo_JOB2.csv"
SELECT * FROM geo_JOB2;
SPOOL OFF
EXIT
EOF`
Here is another try, Any body who can make this more efficient? I don't want to use the setting and the sql connection more than once.
Thanks ,
Hope
FIle = "geo_JOB.csv"
sqlplus -s dev01/password@dEV3 <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL $FILE
SELECT * FROM geo_JOB ;
SPOOL OFF
EXIT
EOF
FIle = "geo_JOB2.csv"
sqlplus -s dev01/password@dEV3 <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL $FILE
SELECT * FROM geo_JOB2 ;
SPOOL OFF
EXIT
EOF
#!/usr/bin/env bash
tables=(geo_JOB geo_JOB1)
(
cat <<EOF
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
EOF
for i in "${tables[@]}"
do
cat<<EOF
SPOOL $i.csv
SELECT * FROM $i ;
SPOOL OFF
EOF
done
echo "EXIT"
) | sqlplus -s dev01/password@dEV3 > somelog.txt
It is a very good code, It is putting all the files in one file, how can I spool each query to different CSV files. (Eg. Table1 to table 1.csv, table 2 to table2.csv ).
Is it possible to spool it to different excel files? It is now dumping all the tables in one excel, which is not useful for my purpose.
Thanks in advance.
Difficult to believe. Unfortunately I can't test against an SQL DB, but this is what is piped into sqlplus -s dev01/password@dEV3 in Ashkay Hedge's fine proposal:
SET HEADING ON
SET WRAP OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET NEWPAGE NONE
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SPOOL geo_JOB.csv
SELECT * FROM geo_JOB ;
SPOOL OFF
SPOOL geo_JOB1.csv
SELECT * FROM geo_JOB1 ;
SPOOL OFF
EXIT
There should be two .csv files created containing the data from the respective tables.
Hello Rudic,
Thank you for your time,
Are you still using the loop, I have so many tables to spool from and wanted to make sure my code is efficient.
In your code, where are you keeping the connection string? Can you please update the existing code with your changes?
I didn't make it too clear: What i posted is what Akshay Hedge's would produce BEFORE it is piped into the sqlplus command. Try it literally, and post your results. Then run his code and post.
By the way, is it possible to suppress(just don't show ) the script log report, I need to see the data in the excel sheets only. If yes, how do you do that?