Home » Infrastructure » Unix » Unable to capture pl/sql dbms_output using shell script while running it through browser (pl/sql, shell scripting, unix(solaris))
Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284041] Wed, 28 November 2007 11:08 Go to next message
samba_siva_raju
Messages: 3
Registered: November 2007
Junior Member
Hi,

I am not sure whether I am posting it in right location as i cant see any shell scripting forum here. Below script works perfectly fine from command line, but when I run through browser I am not getting anything inside my $dt. Because of this its always going inside failure scenario. My pl/sql procedure is going to return either Success / Failed as dbms_output. Can anybody tell me where I am messing up.

#!/usr/bin/ksh

if [ $REQUEST_METHOD == "POST" ]
then
QUERY_STRING=`line <&0`
fi

export TEMP_STRING=$QUERY_STRING

User_ID=`echo $TEMP_STRING | awk 'BEGIN {FS="&"} { for (i=1; i<=NF; i++) {if($i ~ /userid/) {print $i;} } }' | cut -f2 -d'='`

SCRN_Token=`echo $TEMP_STRING | awk 'BEGIN {FS="&"} { for (i=1; i<=NF; i++) { if($i ~ /scrntoken/) {print $i;} } }' | cut -f2 -d'='`

SQLPLUS=`which sqlplus`
FMUSER=username
FMPASS=password
FMSID=orasid

REMOTE_ADDR=`env | grep "REMOTE_ADDR" | cut -f2 -d'='`
NewToken="${REMOTE_ADDR}${User_ID}"

dt=`$SQLPLUS -s $FMUSER/$FMPASS@$FMSID <<EOF
set head off
set pagesize 0
set feedback off
set serveroutput on feedback off
exec validate_fm_user('$SCRN_Token', '$NewToken')
quit
EOF`

echo "Response : $dt"

if [ "$dt" != "Success" ]
then
#some action for failure
exit 0
fi
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284045 is a reply to message #284041] Wed, 28 November 2007 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

i cant see any shell scripting forum here

There is a Unix forum and maybe ksh fit it, doesn't it?

And there are many answers to this kind of question in there.

Regards
Michel
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284046 is a reply to message #284041] Wed, 28 November 2007 11:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>when I run through browser I am not getting anything inside my $dt.
I am not surprised at what you report.
The root cause is that when the script gets invoked via a browser, the environment is not correct to invoke sqlplus.

add the following line as line #2 to the script
env > /tmp/fubar.env

After the script fails again inspect the contents of /tmp/fubar.env to see what you have & then realize what needs to be added.
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284050 is a reply to message #284046] Wed, 28 November 2007 11:34 Go to previous messageGo to next message
samba_siva_raju
Messages: 3
Registered: November 2007
Junior Member
my env is set properly. Otherwise I wont be in a position to run it from command prompt. this code works perfectly fine if i invoke it from command prompt. its only problem when it executes from browser...
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284051 is a reply to message #284041] Wed, 28 November 2007 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since you decided to NOT take my advice, You're On Your Own (YOYO)!

I can't help somebody who refuses to take my advice.
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284069 is a reply to message #284051] Wed, 28 November 2007 14:17 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
First, try what anacedent says to do and inspect the environment that your web server is running under. Just because YOU can run the script when you log in (because your environment is set) doesn't mean the web server USER has the right environment settings. A web page isn't run as you, it's run as the user who started the server daemon.

Another option would be to hard-code the path to the sqlplus command and see if that works for you. I too suspect that your web server user PATH variable does not include $ORACLE_HOME/bin/sqlplus.


Good luck!
Ron
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284072 is a reply to message #284041] Wed, 28 November 2007 14:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
On some/many/most modern *nix systems, the web server (apache?) runs as OS user 'nobody'; which has minimal environmental variables & no "privs" to do much of anything.

Been there, done THAT!

I spent a decent number of hours to have it so that my webforms could "do Oracle" from the command line.

It can be done, but you have to be smarter than than equipment you are working on/with!

Remember that the primary ingredient for integrated circuits is silcon; which is essentially sand or ground up rock.

So in order to solve this problem one only needs to be smarter than a ROCK!

GOOD LUCK!
Re: Unable to capture pl/sql dbms_output using shell script while running it through browser [message #284212 is a reply to message #284072] Thu, 29 November 2007 03:11 Go to previous message
samba_siva_raju
Messages: 3
Registered: November 2007
Junior Member
It was prolem with webserver default listener (TNS) which hasn't got entries. By overriding value against TNS_ADMIN with correct path solved the problem. Thanks all
Previous Topic: Running multiple Oracle database instance on single machine
Next Topic: installation error during OID
Goto Forum:
  


Current Time: Fri Mar 29 05:25:23 CDT 2024