Home » Infrastructure » Unix » Unix and SQLPlus shell scripts
Unix and SQLPlus shell scripts [message #249267] Tue, 03 July 2007 19:57 Go to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
I know that there are many threads on this subject to begin with, however I still can't seem to figure this out even after scouring the internet for answers. I would appreciate it if someone could assist me with my specific script, thanks ahead of time for your patience... Smile

Okay, I have a Unix (Sun Solaris 10) script that needs to query one of my Oracle tables for a value and then use those results in other commands elsewhere in the same script. Most of the examples that I have seen using SQLPlus use "HERE" files to extract data, however I continue to get an error while trying to embed a "HERE" file within my parent script. I will post it here, hopefully it will help.

<snipped>
#Oracle variables
oracleUID='DW'
oraclePass='password'

                #Call SqlPlus to get vaulues from Oracle using a here file then populate the variables 
                #(finaldestinationfolder reportperfdest passphrase, ...)
                sqlplus -s oracleUID/oraclePass@noah << !EOSQL | \
                  awk -F: '{printf("%s ", $1); printf("%s ", $2 ); printf("%s ", $3 )}' | \
                  read finaldestinationfolder reportperfdest passphrase
              
                WHENEVER SQLERROR  exit 5
                set define on
                set heading off
                set pagesize 0
                set feedback off
                set echo off
                column passphrase format a90
                set linesize 100
                SELECT CASE RDP.REPORT_DESTINATION 
                       WHEN 'MC' THEN RDP.MEMBERCONNECT_FOLDER 
                       WHEN 'SF' THEN RDP.SAFELINK_FOLDER 
                       ELSE 'UNKNOWN' END ||
                       ':'||RDP.REPORT_DESTINATION||':'||CP.PASSPHRASE 
                FROM SACORP.MCI, DW.CLIENT_PASSPHRASE CP, DW_REF.EI_REPORT_DEST_PREF RDP 
                WHERE MCI.PSC_ID = RDP.PSC_ID
                  AND RDP.PSC_ID(+) = CP.PSC_ID
                  AND CP.EFFECTIVE_DATE = TRUNC(SYSDATE, 'MM')
                 AND MCI.CLIENT_ID = $clientid;
              
                exit 0
              
                !EOSQL
                #End of here file and call into Oracle
<snipped>


Any advice would be great!

Thanks in advance,

Danielle

[Updated on: Wed, 04 July 2007 01:20] by Moderator

Report message to a moderator

Re: Unix and SQLPlus shell scripts [message #249273 is a reply to message #249267] Tue, 03 July 2007 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Most of the examples that I have seen using SQLPlus use "HERE" files to extract data,
>however I continue to get an error while trying to embed a "HERE" file within my parent script.

error? What error? I don't see any error.
Can you debug code without knowing the error that needs to be fixed?
I know I can't, therefore You're On Your Own (YOYO)!

IMO, you are using the wrong tool for this job & paying the price for using a hammer to divide a board into two pieces.

A much better tool to solve this problem is PERL.

In the future, please limit your posted lines to 80 characters.

Re: Unix and SQLPlus shell scripts [message #249274 is a reply to message #249267] Tue, 03 July 2007 21:10 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
Sorry, didn't realize I should limit the size of my post. Razz Should I edit the thread to shorten it?

Here is the error I get when trying to execute the script:
$ ./script2.sh
destfilespath /export/home/boeprod/processfiles/destfiles/TestFiles
memberconnectpath /export/home/boeprod/processfiles/destfiles/TestFiles/MemberConnect
safelinkpath /export/home/boeprod/processfiles/destfiles/TestFiles/SafeLink
./script2.sh[61]: syntax error at line 121 : `<<' unmatched


How would I use Perl to accomplish this task?

Thanks again,

Danielle
Re: Unix and SQLPlus shell scripts [message #249279 is a reply to message #249267] Tue, 03 July 2007 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How would I use Perl to accomplish this task?
By not having to resort to HERE script, by being able to directly invoking SQL statement via DBI/DBD.
The errors you post contain lines NUMBERS, but posted code does not show any.

What you don't realize is the the termination string "!EOSQL" MUST exist without intervening white space. It MUST the 1st characters on the line; leftmost.

   sqlplus << EOF
   / as sysdba
   set term on echo on
   select 'This works' from dual;
   exit
EOF


+ sqlplus

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 3 19:56:54 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter user-name: 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SQL> 
'THISWORKS
----------
This works

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

[Updated on: Tue, 03 July 2007 21:59] by Moderator

Report message to a moderator

Re: Unix and SQLPlus shell scripts [message #249282 is a reply to message #249267] Tue, 03 July 2007 22:54 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
Thanks... Razz That worked. Bad newbie mistake.
Re: Unix and SQLPlus shell scripts [message #249286 is a reply to message #249267] Tue, 03 July 2007 23:29 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
I am now getting the following error, though if I try to login to SQLPlus without using a script it works just fine... Any thoughts?

invalid username/password; logon denied        
ERROR   ORA-12162  TNS net service name is incorrectly specified       
SP2-0306  Invalid option.  
Usage  CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]  
where <logon> = <username>[/<password>][@<connect_identifier>] | / 
SP2-0157  unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[Updated on: Wed, 04 July 2007 01:19] by Moderator

Report message to a moderator

Re: Unix and SQLPlus shell scripts [message #249295 is a reply to message #249267] Wed, 04 July 2007 00:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How sad..............
It appears no know neither Unix nor Oracle
>sqlplus -s oracleUID/oraclePass@noah
I am just guessing here, because you offer no useful clues but
sqlplus -s ${oracleUID}/${oraclePass}@noah
might work better, but can't work any worse if these are defined higher/above/before in the code you have hidden from us (TINU)

When are you going to fix the *&^%$#$^&& line length problem?
Re: Unix and SQLPlus shell scripts [message #249314 is a reply to message #249267] Wed, 04 July 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Script:
res=`sqlplus -s / <<EOF
set pagesize 0
set feedback off
select 1 from dual
union all
select 2 from dual
union all
select 3 from dual
/
EOF`
echo "res=$res"
echo $res | read v1 V2 v3
echo "v1=$v1 v2=$v2 v3=$v3 "

Interactive execution:
$ res=`sqlplus -s / <<EOF
set pagesize 0
set feedback off
select 1 from dual
union all
select 2 from dual
union all
select 3 from dual
/
EOF`
> set pagesize 0
> set feedback off
> select 1 from dual
> union all
> select 2 from dual
> union all
> select 3 from dual
> /
> EOF`
$ echo "res=$res"
res=         1
         2
         3
$ echo $res | read v1 V2 v3
$ echo "v1=$v1 v2=$v2 v3=$v3 "
v1=1 v2= v3=3

Regards
Michel
Re: Unix and SQLPlus shell scripts [message #250004 is a reply to message #249314] Fri, 06 July 2007 14:45 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you can ident your here-document with TABs (including the closing tag) by using <<- rather than <<

Previous Topic: env variable
Next Topic: How to Create Oracle Directory for remote location
Goto Forum:
  


Current Time: Thu Mar 28 05:48:52 CDT 2024