Running a select script through UNIX and sending output to file

Hi,

(Oracle, AIX)

I have googled this and searched this forum, however I haven't had much luck with an answer and have tried several different things.

Basically I have a SQL select statement which generates a whole load of UPDATE statements, I want to run the select statement via sqlplus in UNIX and to output the results (i.e. the update statements) to a file. I would then like the script to do some other stuff, but not worried about that atm.

What I have so far...

sqlplus user/password <<selectupdate
@<scriptname.sql>;
selectupdate

Any ideas?

Thanks in advance

You can use spool.

sqlplus user/password <<selectupdate
spool /your/filename
@<scriptname.sql>;
spool off
selectupdate

Thanks for the suggestion. Here's an update of my code:

sqlplus user/password@SID <<selectupdate >  selectupdate.txt
  set heading  off
  spool  selectupdateX.txt
   @<select_script>.sql;
spool  off
 selectupdate

With this, I don't get the update statements from the select script, just some useless information (well, useless to me). I was hoping that either selectupdate.txt or selectupdateX.txt would produce a file like...
update table set column = something
udpate table2 set column = something
The select statement I created does this when running via sqlDeveloper. Hope that makes sense?
Any help is appreciated! In the meantime I'll keep playing with the script.
Cheers,
Gary

---------- Post updated at 06:05 AM ---------- Previous update was at 06:03 AM ----------

Ach, sorry for the bad formatting there.

---------- Post updated at 10:02 AM ---------- Previous update was at 06:05 AM ----------

Update -

So my latest version...

X= 'sqlplus -r u/p@sid  <<selectupdate
   spool  selectupdateX.txt
    @<script>.sql;
  selectupdate'
  
  echo $X >  selectupdateY.txt

And this produces the following error...

-bash: sqlplus -r u/p@sid  <<selectupdate
   spool  selectupdateX.txt
    @scrambler_SELECT_UPDATE_V0.4.sql;
 selectupdate: No such file or  directory

Any idea why this error is caused?

Cheers

I suspect it means what it says; no such file or directory.

Good point, I didn't think of that.................

I don't understand why it's gerating that error; I don't believe that I'm ever setting "selectupdate" as a file or directory.

Try the following :

sqlplus /nolog <<EOF
conn user/pass@$ORACLE_SID
spool result.txt
@sqltorun.sql
disconnect
EOF

Cannot confirm since i'm @ home, but that's how i do it.

Something you're doing but didn't post is trying to run that variable literally.

There's no point putting it in a variable, to put it in a file, to run the file. Just put the code into the script as it was given to you and it should work.

sqlplus -r u/p@sid  <<selectupdate
spool selectupdateX.txt
@<script>.sql;
# Note selectupdate must be at the BEGINNING of the line
selectupdate

@ Peasant - Tried that, although it gives the same result as some of my previous attempts. A file is produced and the content is:

SQL>   @scrambler_SELECT_UPDATE_V0.4.sql
 93    EOF
  94   

In fairness I haven't posted the rest of the original script, so here it is...

#!/bin/sh
 #
 # some text

  
  cd <some dir> 

  
  export  ORACLE_HOME
  
  X= 'sqlplus -r u/p@sid <<selectupdate
   spool <some dir>/selectupdateX.txt
    @<script>.sql;
  selectupdate'
  
  #echo $X >  selectupdateX.txt

@Corona688 - I'll make a new script and test that to see what happens, hopefully it'll give the result I'm after and I can build on that.

Thanks for the help, appreciated.

Gary

*Thinking about it, guess the CD and Oracle_Home bits at the start are probably cocking it up. Please excuse the newb

I don't think the cd will stop it from working.

The export won't hurt it, but I don't think it's doing what you wanted either. If ORACLE_HOME wasn't set to anything, exporting it won't figure out the right value for you. Exporting a blank ORACLE_HOME variable won't actually help Oracle find its home.

Instead do:

ORACLE_HOME=/correct/path/to/oracle/stuff
export ORACLE_HOME

Also: You're still putting that in a variable for no benefit. How about using the code directly, instead of cramming it into a variable?

You also haven't moved the last 'selectupdate' to the beginning of the line. It absolutely HAS to be at the beginning of the line, no tabs, no spaces, no nothing.

cd <wherever>
ORACLE_HOME=/path/to/oracle/stuff/
export ORACLE_HOME

sqlplus -r u/p@sid <<selectupdate
spool <some dir>/selectupdateX.txt
@<script>.sql;
selectupdate

Okay thanks for the feedback. I'm off now but will be picking this up first thing tomorrow morning.

Looking forward to nailing this... !!

Try this :

(
sqlplus /nolog <<EOF
conn u/p@$SID
@sql
EOF ) |  tee /path/to/output.txt

Feel free either to use set <options> to remove stuff you don't need, or awk/grep/sed pipe before tee.

@Corona688 - I created a new script, the content is:

#!/bin/sh

ORACLE_HOME=/apps/oracle/10.2.0.4
export ORACLE_HOME
 
sqlplus u/p@sid <<selectupdate
spool selectupdateX.txt
@<script>.sql;
selectupdate

It created a file called selectupdateX.txt, and the contents are:

SQL> @<script>.sql;
 93  

I did try adding a -r after sqlplus, however when executing the script the sqlplus help popped up and nothing else happened (no file).

I'll keep playing with this, but do you have any more ideas?

@Peasant - I tried your script too:

#!/bin/sh

ORACLE_HOME=/apps/oracle/10.2.0.4
export ORACLE_HOME
 
(
sqlplus /nolog <<EOF
conn TMH7ESD0_TM_ONLINE/TMH7ESD0_TM_ONLINE@TMH3ESD0
@scrambler_SELECT_UPDATE_V0.4.sql;
EOF
) tee selectupdateX.txt

This generated the following error:

-bash: <scriptname>.sh: line 11: syntax error near unexpected token `tee'
-bash: <scriptname>.sh: line 11: `) tee selectupdateX.txt'

I haven't attempted to figure out the errors yet.

I'm going to keep trying with both versions.

Not sure I'll be able to put bash scripting as a skill on my CV just yet

Cheers,
g

---------- Post updated at 09:14 AM ---------- Previous update was at 03:34 AM ----------

There's something "wrong" with the SQL that I'm running within <script.sql>. Basically the SQL generates a whole load of UPDATE statements through CASE statements.

When I do something like this...

sqlplus u/p@sid <<selectupdate
spool selectupdateX.txt
select id from <table>;
selectupdate

...then the selectupdateX.txt file is generated with a list of ids!!

When I do something like this...

sqlplus u/p@sid <<selectupdate
 spool selectupdateX.txt
 select case when ... blah blah end from <table> where <something>;
 selectupdate

...then the selectupdateX.txt file is generated with nothing other than some sqlplus stuff, no UPDATE statements at all.

The issue I have looks like it's because of something dodgy happening with the SQL, and not the UNIX script itself... but maybe I'm wrong...

---------- Post updated at 09:31 AM ---------- Previous update was at 09:14 AM ----------

Got it, and I feel pretty stupid. I failed to add a semi colon to the end of my massive select statement, so... that's the reason it wasn't doing what I hoped.

Thanks again for everyone's help, appreciated.

You are not pasting it right ...

This works fine.

(
sqlplus /nolog <<EOF
conn user/pass@$ORACLE_SID
@test.sql
EOF
) | grep '^update' | tee out.sql

test.sql i out.sql content ..

test.sql :
select 'update table set value=''1'' where value2=''2'';' from dual;
select 'update table set value=''5'' where value2=''7'';' from dual;
out.sql :
update table set value='1' where value2='2';
update table set value='5' where value2='7';

Ofcourse you can use various SET options to avoid pipe grep.

Resolved, cheers everyone