Visit Our UNIX and Linux User Community


Extract Oracle DB Connect and SQL execution log


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Extract Oracle DB Connect and SQL execution log
# 1  
Old 08-19-2010
Extract Oracle DB Connect and SQL execution log

Hi,

I am trying to write a generic script as a part of a framework which will establish Oracle DB connection once and loops in to check for some files which gives the SQL statements to execute.

The script is running but I am stuck with capturing errors ( ORA and SP) and outputs. Example:
Code:
${ORACLE_HOME}/bin/sqlplus -s ${loginid}/${password}@//${server}:${port}/${servicename} |&

while [[ $flag -eq 1 ]]
do
#obtain the $sql_stmt from a file here
print -p -- "${sql_stmt}"
# if it is select statement i use read -p to obtain the output
done
print -p -- exit

Question:
I tried :
Code:
${ORACLE_HOME}/bin/sqlplus -s ${loginid}/${password}@//${ server}:${port}/${servicename} > ${connect_output} 2>&1 |&

And it is capturing all the sql error logs instead of only connect error
Any ideas on how to capture the individual sql statement errors?

Thanks for your help
mirage0809

Last edited by jim mcnamara; 08-19-2010 at 07:54 PM.. Reason: code tags
# 2  
Old 08-19-2010
Clue perhaps.
The "sqlplus" switch "-s" means "silent".

Also
Quote:
${ORACLE_HOME}/bin/sqlplus -s ${loginid}/${password}@//${server}:${port}/${servicename} |&

Btw: The "|&" sequence in post #1 loooks weird. Did you mean "|\" (i.e pipeline then escaped new line) ?

Last edited by methyl; 08-19-2010 at 10:58 PM..
This User Gave Thanks to methyl For This Post:
# 3  
Old 08-20-2010
|& is to open the sqlplus thread asynchronously in the background, and we enter the sql statements in the pipe using print p.

removing -s is of no use as I am still not able to capture the errors for individual sql stmt and check the DB connection before proceeding further.

We want to capture any errors thrown dynamically

Last edited by mirage0809; 08-20-2010 at 07:40 AM..
# 4  
Old 08-20-2010
Maybe use Oracle "spool" statements to capture the output (rather than outputting to screen and then capturing in unix shell).

Code:
SPOOL <logfilename1>
.... connnect sequence ...
SPOOL OFF

SPOOL <logfilename2>
... sql statements
SPOOL OFF

EXIT;

# 5  
Old 08-24-2010
methyl,

I checked it now..Spool is working for the DB connection as well as for the sql statements.

Thanks!!

Previous Thread | Next Thread
Test Your Knowledge in Computers #497
Difficulty: Medium
A computer compiler or interpreter is just as smart as a human.
True or False?

10 More Discussions You Might Find Interesting

1. AIX

Connect to a SQL server from AIX

Hello aix community, After scouring the internet to find a step by step process, I've exhausted my efforts. Although I have learned a lot which brings me to this forum. I'm totally new and hope to ask the right questions. What is the easiest way to connect to a SQL server from aix? ... (2 Replies)
Discussion started by: TechStudent36
2 Replies

2. UNIX and Linux Applications

Identify a specific environment Oracle variable to connect a remote Oracle database ?

Good evening I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus. I tried to find out the user/passwd and service name by env variable and all Ive got is this: ORACLE_SID_REPCOL=SCL_REPCOL ORACLE_SID=xmeta ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
Discussion started by: alexcol
2 Replies

3. Shell Programming and Scripting

Extract Oracle sql queries from .fmb and .rdf files

Hi all, Thanks for your time! Regards, (1 Reply)
Discussion started by: a1_win
1 Replies

4. Shell Programming and Scripting

Connect once db disconnect after all execution

Hi All, Please see the below code. it is working fine when in 'test_file' have only one emplid. test_file contains only emplid and date, like below ... 0000221|1/12/2003 0000223|1/1/1996 Problem :- when test_file contains more then one records(emplids) it is not giving any errors... (3 Replies)
Discussion started by: krupasindhu18
3 Replies

5. SuSE

Connect to SQL server from Linux

I am trying to establish connection with SQL server 2008 through ODBC on Linux. First step would be 1. How to verify if ODBC driver for SQL server is installed on Linux OS. PLease let me know how to do it/ if there is any link which could be helpful in this context. thanks in advance (1 Reply)
Discussion started by: cvsanthosh
1 Replies

6. Shell Programming and Scripting

Switching user to oracle to connect Oracle 11g DB with 'sysdba'

I need to connect my Oracle 11g DB from shell script with 'sysdba' permissions. To do this I have to switch user from 'root' to 'oracle'. I've tried the following with no success. su - oracle -c "<< EOF1 sqlplus -s "/ as sysdba" << EOF2 whenever sqlerror exit sql.sqlcode;... (2 Replies)
Discussion started by: NetBear
2 Replies

7. Shell Programming and Scripting

Unable to connect to SQL through unix

I am trying to connect to SQL plus through unix but i am getting below error 'ksh: sqlplus: not found' note i am running this from /home/xxx do i need to set any environmental variable for this. below is my code echo " hello world" sqlplus -s apps/CAF78GEN<<EOF set heading... (4 Replies)
Discussion started by: karnatis
4 Replies

8. Shell Programming and Scripting

Perl connect to remote oracle db without local oracle installation

I want to use Perl to connect to a remote Oracle DB I have no oracle installation on my server (and dont plan on installing one) I am using solaris 9 on x86 server. Is this possible? I basically want to run some basic sql queries on the remote oracle db which I have access to using perl on my... (0 Replies)
Discussion started by: frustrated1
0 Replies

9. Shell Programming and Scripting

Oracle SQL Query & connect?

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best... (8 Replies)
Discussion started by: Dird
8 Replies

10. Solaris

Connect From VB to SQL Server

Dear All Now I can't connect from VB to Sybase on Unix. How could I do? Please help me. (0 Replies)
Discussion started by: Than Chanroeun
0 Replies

Featured Tech Videos