Home » Infrastructure » Unix » how to give runtime input to oracle stored proc with in Shell scripts
how to give runtime input to oracle stored proc with in Shell scripts [message #220988] Fri, 23 February 2007 01:15 Go to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,

here i want to pass runtime input to oracle SP with in the shell script.

see my below code ,

echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ] >> $logfilename
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export PATH=/usr/local/pbiace/current/bin
if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 5
fi

#echo `date` : Connecting to $DB_NAME using $ORACLE_USER and calling the >> $logfilename
#echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ] >> $logfilename
###
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect $ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
set serveroutput on size 100000
exec Accid_Handle('248 993-7452')
set feedback off
set echo on
set verify off
set heading off
set pagesize 100
set linesize 132
set termout off
exit
EOF

in the above code,im passing the input parameter as some numbers,instead this i want to pass it during runtime.....

thanks in advance...

with warm regards,
Lokesh

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221005 is a reply to message #220988] Fri, 23 February 2007 02:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

--create pl/sql function
SQL> create or replace function myproc( unix_par in varchar2) return varchar2
as
l_return varchar2(20);
begin
select unix_par into l_return from dual;
return l_return;
end;
 /
Function created.


--create sql script to call the function

[unix] vi myscript.sql
DECLARE
  RetVal varchar2(200);
  unix_parm varchar2(200);
BEGIN
  unix_parm:='&1';
  RetVal := MYPROC (unix_parm);
  DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/



--create unix script which sends in ORACLE_SID

[unix] vi unix_script.sh
i=`echo $ORACLE_SID`
sqlplus user/psw @myscript.sql $i


--run the unix script
[unix] . unix_script.sh


SQL*Plus: Release 9.2.0.6.0 - Production on Fri Feb 23 09:54:00 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   5:   unix_parm:='&1';
new   5:   unix_parm:='utv3';

PL/SQL procedure successfully completed.

[Updated on: Fri, 23 February 2007 03:57]

Report message to a moderator

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221009 is a reply to message #221005] Fri, 23 February 2007 03:46 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,

thanks a lot,got a new idea:p

one more doubt,here how can i execute stpre proc in that Shell script?
bcos now me trying to pass runtime input,,so can v give like this "exec SPname_Accid_Handle()"

regards,
Lokesh
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221015 is a reply to message #221009] Fri, 23 February 2007 04:14 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Im not really sure what you mean but

i=`echo $ORACLE_SID`
sqlplus user/psw <<-EOF
set serveroutput on
DECLARE
  RetVal varchar2(200);
BEGIN
  RetVal := MYPROC('${i}');
  DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/
EOF



OR

i=`echo $ORACLE_SID`
sqlplus user/psw <<-EOF
set serveroutput on
var  RetVal char(20);
exec  :RetVal := MYPROC('${i}');
print RetVal
exit
EOF

[Updated on: Fri, 23 February 2007 04:23]

Report message to a moderator

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221037 is a reply to message #221015] Fri, 23 February 2007 06:15 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
actually here wat im asking is,

ok,,i'll explain with my code,,,,,,

Create Stored Proc

SQL> CREATE OR REPLACE procedure ACCid_Handle(data in varchar2)
is
variable1 NUMBER;
HandleN varchar(20);
BEGIN
select BI_ACCOUNTID into variable1 from pb_bill_info where PBBTN=data;
do.pl('BI_ACCOUNTID');
do.pl('------------');
do.pl(variable1);
select pbhandlenamelower INTO HandleN from pb_handle where pbhandleid in(select max(pbhandleid)
from pb_handle where ha_customerentityid in (select acct_customerentityid from account
where acct_accountid =variable1)) AND pbmainhandleflag=1 ;
do.pl('==============================') ;
do.pl('Handle Name: ' || HandleN ) ;
do.pl('==============================') ;
EXCEPTION
WHEN OTHERS THEN
do.pl('No rows selected');
END;

Procedure Created.


create sql script to call the function

vi accidhandle.sql

DECLARE
RetVal varchar2(200);
unix_parm varchar2(200);
BEGIN
unix_parm:='&1';
RetVal := ACCID_HANDLE (unix_parm);
DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/


create unix script which sends in ORACLE_SID

[unix] vi Accid_Handle.ksh
i=`echo $ORACLE_SID`
$ORACLE_HOME/bin/sqlplus -s /accidhandle.sql ##(This is correct syntax to calling my sql script????)

here how can i call my stored procedure(ACCid_Handle)??

and if i run the shell script(Accid_Handle.ksh),it will ask the input right??

please guide me,,,

regards,
Loganthan


Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221386 is a reply to message #220988] Mon, 26 February 2007 09:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

FIRST YOU DO
Quote:

SQL> CREATE OR REPLACE procedure ACCid_Handle(data in varchar2)



THEN

Quote:

vi accidhandle.sql

DECLARE
RetVal varchar2(200);
unix_parm varchar2(200);
BEGIN
unix_parm:='&1';
RetVal := ACCID_HANDLE (unix_parm);
DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/



Wont Work, I created a function in my example, you have a procedure
-Remove Retval


Quote:


[unix] vi Accid_Handle.ksh
i=`echo $ORACLE_SID`
$ORACLE_HOME/bin/sqlplus -s /accidhandle.sql ##(This is correct syntax to calling my sql script????)



sqlplus -s "path_to_script"/accidhandle.sql par1 par2 par3


Quote:

here how can i call my stored procedure(ACCid_Handle)??



You are calling ACCid_Handle with $ORACLE_HOME/bin/sqlplus -s /accidhandle.sql
ACCid_Handle is defined in accidhandle.sql RIGHT ?


Quote:

and if i run the shell script(Accid_Handle.ksh),it will ask the input right??



NO


I do a another example, you can do this in many ways.....

1.
create the shell script with input from a user

echo '################################################################ '
echo ' Enter the runtime parameter'
read par1
echo ' Enter the runtime parameter no2'
read par2
echo '################################################################ '
sqlplus user/psw @myscript.sql $par1 $par2


The shell script will "call myscript.sql" and send 2 parameters.

2. Create myscript.sql which calls the stored procedure with the
runtime parameters which the user gave as input.

DECLARE
  unix_par1 varchar2(20);
  unix_par2 varchar2(20);
BEGIN
  unix_par1:='&1';
  unix_par2:='&2';
  MYPROCEDURE(unix_par1,unix_par2);
END;
/



3. Create the stored Procedure which displays the parameter.

SQL>  create or replace procedure myprocedure(unix_par1 varchar2,unix_par2 varchar2)
  2  is
  3  begin
  4   dbms_output.put_line('My First Parameter:'||unix_par1);
  5  dbms_output.put_line(' My Second Parameter:'||unix_par2);
  6  end;
  7  /

Procedure created.


If you now run the shell script you will be prompted for the input, which will be displayed back by the stored procedure.
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #221531 is a reply to message #221386] Tue, 27 February 2007 05:49 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
thanks a lot for your response..

but still my code is giving some error.kindly look my code and tel me where i made the mistakes,,,pls


My Stored Procedure

SQL> CREATE OR REPLACE procedure ACCid_Handle(data in varchar2)
is
variable1 NUMBER;
HandleN varchar(20);
BEGIN
select BI_ACCOUNTID into variable1 from pb_bill_info where PBBTN=data;
do.pl('BI_ACCOUNTID');
do.pl('------------');
do.pl(variable1);
select pbhandlenamelower INTO HandleN from pb_handle where pbhandleid in(select max(pbhandleid)
from pb_handle where ha_customerentityid in (select acct_customerentityid from account
where acct_accountid =variable1)) AND pbmainhandleflag=1 ;
do.pl('==============================') ;
do.pl('Handle Name: ' || HandleN ) ;
do.pl('==============================') ;
EXCEPTION
WHEN OTHERS THEN
do.pl('No rows selected');
END;

Procedure created.


create sql script to call the Procedure

vi myscript.sql

DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;
/


create unix shell script

vi Accid_Handle.ksh

echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ] >> $logfilename
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data
exit
EOF


here if i run the shell script(Accid_Handle.ksh),it will ask the input like this(i have mentioned below),but if i given the 10 digit input it will not returning any rows,

$ Accid_Handle.ksh
Tue Feb 27 05:15:18 CST 2007 : Begin Process Accid Handle[ Accid_Handle.ksh ]
################################################################
Enter the WTN
'510 795-2554'
################################################################
SP2-0734: unknown command beginning "/usr/oracl..." - rest of line ignored.

this is output i got while running the script.

here actually if my procedure worked properly,it will returned rows like this as i mentioned below,this is simple stored proc output which i done in oracle database.

this is the expected output.
SQL> exec Accid_Handle('510 791-0972')
BI_ACCOUNTID

------------
8586642
==============================
Handle Name: qay2002s5107910972
==============================

PL/SQL procedure successfully completed.


kindly do the needful.


Regards,
Lokesh







Re: how to give runtime input to oracle stored proc with in Shell scripts [message #222494 is a reply to message #221531] Mon, 05 March 2007 03:29 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi again

Been sick so I havent been able to reply.

The error you are getting is because your code
 
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data
exit
EOF


You have placed the EOF wrong it should be

$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data <<-EOF
exit
EOF


Good Luck
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #223249 is a reply to message #222494] Thu, 08 March 2007 03:10 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,,sorry 4r the late reply:(

still my code is giving same error,

i think here the problem is export oracle database(Syntax error for calling oracle database)..

these below commands im using for calling oracle databse

export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin



this below error me getting while running the script..

SP2-0734: unknown command beginning "/usr/oracl..." - rest of line ignored.

please suggest me some idea if u know about this:)
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #223281 is a reply to message #223249] Thu, 08 March 2007 04:55 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well the export parameters themeself cant cause that error.

Paste the whole script in here
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #223434 is a reply to message #223281] Thu, 08 March 2007 21:46 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,

here i have pasted my Shell script,please go through once & do the needful.

vi Accid_Handle.ksh

echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ] >> $logfilename
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established >> $logfilename
echo `date` : -------------------------------- END $filename --------------------------------------- >> $logfilename
exit 5
fi

set serveroutput on size 1000000
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data << EOF
connect $ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
exit
EOF
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #223456 is a reply to message #223434] Fri, 09 March 2007 00:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

This wont work
set serveroutput on size 1000000
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data << EOF
connect $ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
exit
EOF


1.set serveroutput on size 1000000 it a SQLPLUS command, you have to start SQLPLUS first.

2.the second line $ORACLE_HOME/bin/sqlplus -s /nolog << EOF
wont work you will get ther error
SP2-0734: unknown command beginning "/usr/oracl..." - rest of line ignored.

You have to
$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
            exit
            EOF


3. I dont understand why you try to start sqlplus twice 2 ?
Any way
try to
export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF




[Updated on: Fri, 09 March 2007 00:25]

Report message to a moderator

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226031 is a reply to message #223456] Thu, 22 March 2007 06:20 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,

sorry for late reply,,,i was in sick leave for last 2weeks..

then still my code is throwing some error,

sorry for asking again & again.b'cos me very fresher for coding.

as per ur previous reply,i'll write the code like this,


vi Accid_Handle.ksh

"Accid_Handle.ksh" 40 lines, 1472 characters
echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ]
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 5
fi

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF



here if i run the script it will through error as i mentioned below

Thu Mar 22 06:11:41 CDT 2007 : Begin Process Accid Handle[ Accid_Handle.ksh ]
################################################################
Enter the WTN
'510 793-1879'
################################################################
SP2-0734: unknown command beginning "export CON..." - rest of line ignored.
SP2-0734: unknown command beginning "/usr/local..." - rest of line ignored.



here my 1st doubt is,if im not giving this line "$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF",it will trow big error as i copied this code from other file where we had used the same database connection.

2nd doubt is,according to ur quote,u said me have to give code like this,

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
exit
EOF


export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

as per ur comments, i have to give two times exit&EOF in the same file???


then my last doubt is in my sqlscript file,

vi myscript.sql

DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;


in the above code,i have to give 'exec ACCID_HANDLE(data);' or simply 'ACCID_HANDLE(data);'

pls guide me...

thanks,
Lokesh

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226034 is a reply to message #220988] Thu, 22 March 2007 06:36 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You mess things up!

This is your code

"Accid_Handle.ksh" 40 lines, 1472 characters
echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ]
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 5
fi

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF


this is my code
"Accid_Handle.ksh" 40 lines, 1472 characters
echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ]
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 5
fi

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF


[Updated on: Thu, 22 March 2007 06:38]

Report message to a moderator

Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226370 is a reply to message #226034] Fri, 23 March 2007 06:56 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Any luck ?
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226633 is a reply to message #226034] Mon, 26 March 2007 00:11 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,,

now i was used ur code but this time its giving some big error.

just see the below error message,i got this while running the script.

csdev49> $ Accid_Handle.ksh
Sun Mar 25 23:46:30 CDT 2007 : Begin Process Accid Handle[ Accid_Handle.ksh ]
################################################################
Enter the WTN
'510 793-1879'
################################################################

SQL*Plus: Release 10.2.0.2.0 - Production

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

Usage 1: sqlplus -H | -V

-H Displays the SQL*Plus version and the
usage help.
-V Displays the SQL*Plus version.

Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]

<option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

-C <version> Sets the compatibility of affected commands to the
version specified by <version>. The version has
the form "x.y[.z]". For example, -C 10.2.0
-L Attempts to log on just once, instead of
reprompting on error.
-M "<options>" Sets automatic HTML markup of output. The options
have the form:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
-R <level> Sets restricted mode to disable SQL*Plus commands
that interact with the file system. The level can
be 1, 2 or 3. The most restrictive is -R 3 which
disables all user commands interacting with the
file system.
-S Sets silent mode which suppresses the display of
the SQL*Plus banner, prompts, and echoing of
commands.

<logon> is: (<username>[/<password>][@<connect_identifier>] | /)
[AS SYSDBA | AS SYSOPER] | /NOLOG

Specifies the database account username, password and connect
identifier for the database connection. Without a connect
identifier, SQL*Plus connects to the default database.

The AS SYSDBA and AS SYSOPER options are database administration
privileges.

The /NOLOG option starts SQL*Plus without connecting to a
database.

<start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.

When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run. The files may
contain SQL*Plus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

if im using this code "$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF" got below error message while running the script.

csdev49> $ Accid_Handle.ksh
Sun Mar 25 23:52:26 CDT 2007 : Begin Process Accid Handle[ Accid_Handle.ksh ]
################################################################
Enter the WTN
'510 793-1879'
################################################################
SP2-0734: unknown command beginning "/usr/local..." - rest of line ignored.

one more think,in my sql script ,

vi myscript.sql

DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;

here without giving 'exec' (like exec ACCID_HANDLE(data)) how it will work?pls clarify

regards,
Lokesh


Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226671 is a reply to message #226633] Mon, 26 March 2007 02:45 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Replace your
export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
with
export CONNSTRING=${ORACLE_USER}/${ORACLE_PASSWORD}@${DB_NAME}
icon14.gif  Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226713 is a reply to message #226671] Mon, 26 March 2007 05:01 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
same result,,,

i know,the problem is data base connectivity only but dont know how to clear that:(
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226731 is a reply to message #226713] Mon, 26 March 2007 06:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

replace /@myscript.sql to @myscript.sql
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226740 is a reply to message #226731] Mon, 26 March 2007 06:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Just out of curiosity i want to know why do you need to specify the oracle_home, oracle_sid and the following :
$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

If the environment is set correctly all these things will be populated. If not i will attempt to correct the enviroment setting rathers than overcome the problem in the script.
Instead can't this be suffice
sqlplus -s $CONNSTRING @myscript.sql

You want to set serveroutput on size 100000 and you want to exit out of it. Can't these two statements be added to the sql file, because these statements are constant they are not varying based on any input or output conditions.



Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226752 is a reply to message #226731] Mon, 26 March 2007 07:52 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
hi,

now im getting some different error.

kindly look at below one & guide me further

Accid_Handle.ksh
Mon Mar 26 07:54:41 CDT 2007 : Begin Process Accid Handle[ Accid_Handle.ksh ]
################################################################
Enter the WTN
'510 793-1879'
################################################################
old 4: data:='&1';
new 4: data:='510 793-1879';
END;
*
ERROR at line 6:
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( % ;
The symbol ";" was substituted for "END" to continue.



Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226767 is a reply to message #226752] Mon, 26 March 2007 08:46 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

O my god you finally made the call to the sql script Razz

Post the code of the procedure you are calling
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #226914 is a reply to message #226767] Mon, 26 March 2007 22:34 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
this my procedure,

CREATE OR REPLACE procedure ACCid_Handle(data in varchar2)
is
variable1 NUMBER;
HandleN varchar(20);
BEGIN
select BI_ACCOUNTID into variable1 from pb_bill_info where PBBTN=data;
do.pl('BI_ACCOUNTID');
do.pl('------------');
do.pl(variable1);
select pbhandlenamelower INTO HandleN from pb_handle where pbhandleid in(select max(pbhandleid)
from pb_handle where ha_customerentityid in (select acct_customerentityid from account
where acct_accountid =variable1)) AND pbmainhandleflag=1 ;
do.pl('==============================') ;
do.pl('Handle Name: ' || HandleN ) ;
do.pl('==============================') ;
EXCEPTION
WHEN OTHERS THEN
do.pl('No rows selected');
END;
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #227027 is a reply to message #226914] Tue, 27 March 2007 02:59 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

And you havent changed your script ?

DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;
/
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #227038 is a reply to message #227027] Tue, 27 March 2007 03:31 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member

now my code is running successfully but not showing the output,
see the output below while running the script,

################################################################
Enter the WTN
'510 793-1879'
################################################################
old 4: data:='&1';
new 4: data:='510 793-1879';

PL/SQL procedure successfully completed.


i have a doubt in sql script,

see my sql script below,

vi myscript.sql
DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;
/

while giving the procedure name i need to give, like exec ACCID_HANDLE(data); or simply ACCID_HANDLE(data);?
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #227065 is a reply to message #227038] Tue, 27 March 2007 04:23 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

It's nothing wrong with the sql script....you got to know some basics ........



For the output issue
just set serveroutput on, no use in the calling script

vi myscript.sql

set serveroutput on
DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;
/


or skip the calling your damn script and call the procedure direct instead as suggested earlier.

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING <<-EOF
set serveroutput on size 1000000
exec ACCID_HANDLE('${data}')
exit
EOF


Re: how to give runtime input to oracle stored proc with in Shell scripts [message #227094 is a reply to message #227065] Tue, 27 March 2007 05:59 Go to previous messageGo to next message
lokeshg82
Messages: 23
Registered: February 2007
Location: chennai
Junior Member
now its worked fine,thanks a lot Cool
Re: how to give runtime input to oracle stored proc with in Shell scripts [message #227097 is a reply to message #227094] Tue, 27 March 2007 06:03 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Exellent, I almost cant believe it !

Good Luck
Previous Topic: ORA-07445
Next Topic: Passing String Arguments to SQL*Plus from a Unix Shell
Goto Forum:
  


Current Time: Thu Mar 28 07:13:49 CDT 2024