09-20-2002
Unix call to Oracle PL/SQL pkg/store.proc
HI,
I'm trying to get this right, please can you help. In my unix korn shell script, I call an oracle stored proc within a package and I specify 3 parameters, 2 of which are IN OUT parameters (i.e. I expect the stored proc to change them and return them back to me).
Does the unix code look ok and I using the bind variable in the right way syntactically???
Here's the code:
In Unix:
======
ERROR_NO_OUT=0
ERROR_TEXT_NO="N/A"
sqlplus -s ${DB_UNAME}/${DB_UPSWD}@${DB_NAME} << END_OF_SQL
exec psit_ba_upload_pkg.apply_jobs('18/09/2002',:$ERROR_NO_OUT, :$ERROR_TEXT_NO)
END_OF_SQL
echo "ERROR_NO_OUT=$ERROR_NO_OUT\n"
echo "ERROR_TEXT_NO=$ERROR_TEXT_NO\n"
In Oracle:
=======
CREATE OR REPLACE PACKAGE chee_psit_ba_upload_pkg AS
PROCEDURE apply_jobs(p_business_date IN VARCHAR,
error_no_out IN OUT INT,
error_text_out IN OUT VARCHAR2);
END chee_psit_ba_upload_pkg;
/
CREATE OR REPLACE PACKAGE BODY chee_psit_ba_upload_pkg AS
PROCEDURE apply_jobs(p_business_date IN VARCHAR,
error_no_out IN OUT INT,
error_text_out IN OUT VARCHAR2)
IS
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
error_no_out := 1;
error_text_out := 'Error retrieving data for the business units. ' || TO_CHAR(SQLCODE) || ' : ' || SQLERRM;
ROLLBACK;
RAISE;
END apply_jobs;
END psit_ba_upload_pkg;
/
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
sample code as following:
test_sql(){
#test#echo test_sql
str=`$ORACLE_BIN/sqlplus -s $user/$passwd <<EOM
set verify off
set heading off
set feedback off
#--------start pl/sql
{
DECLARE
CURSOR pah_cs IS
select id from table
where letter = 'abcd';... (6 Replies)
Discussion started by: YoYo
6 Replies
2. UNIX for Advanced & Expert Users
Hi All
I would be thankful to you all if you will guide me the steps to call a stored proc. from unix shell script. that stored proc. could be parameterised or parameterless developed in SQL.
Any info. in this topic would help me.....
Thanks in advance.... (1 Reply)
Discussion started by: varungupta
1 Replies
3. Shell Programming and Scripting
Hi,
I need to make SQL Server procedure call (exec <proc name>)from unix shell script. First of all I would like to know if it is possible. I know we can do it from Oracle but not sure about SQL Server.
Version:
SunOS 5.8
SQL 8.0
I have made the below entry in the interface file.
NSXNA267
... (0 Replies)
Discussion started by: sspreethi
0 Replies
4. Shell Programming and Scripting
hi,
I want to know how to call a pl sql function testfunction(param1,..) that returns a value and grab that value in a shell variable.
Thnx in advance
---------- Post updated 03-30-10 at 11:58 AM ---------- Previous update was 03-29-10 at 03:49 PM ----------
thnx a lot jim (0 Replies)
Discussion started by: austinhell3_16
0 Replies
5. Shell Programming and Scripting
Hi Guys,
Even though, i have called db2 sql file earlier, this is my first time to call a oracle sql file.
I need to make a database(oracle) connection and then call the sql file in a loop.
Can you please help me out.
Thanks for your help and time.
Regards,
Magesh (4 Replies)
Discussion started by: mac4rfree
4 Replies
6. Solaris
Hi Solaris Experts,
The pkg utility on one of my non-global zones has stopped working, it's trying to connect to port 1008 at pkg.oracle.com
I was using pkg successfully from this zone, but now it's showing this error: lzone1 $ pkg search xterm
pkg: Some repositories failed to respond... (4 Replies)
Discussion started by: ad101
4 Replies
7. UNIX for Dummies Questions & Answers
HI Experts,
I am pretty new to scripting and i need to create a perl or shell script which should fetch a file from local directory and insert the data into a table using sql loader. This will be later added to chron job to run daily and fetch all files and load them into
the table.
Also i... (1 Reply)
Discussion started by: sam1234
1 Replies
8. UNIX for Dummies Questions & Answers
HI Experts,
I am pretty new to scripting and i need to create a perl or shell script which should fetch a file from local directory and insert the data into a table using sql loader. This will be later added to chron job to run daily and fetch all files and load them into
the table.
Also i... (1 Reply)
Discussion started by: sam1234
1 Replies
9. Shell Programming and Scripting
Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File.somehow i try to find a solution but whwn i try to run the script i am not getting the output file with... (1 Reply)
Discussion started by: Arun619
1 Replies
10. Shell Programming and Scripting
Hi,
Trying to run the Oracle Proc in parallel, but not working.. below code runs each call of the procedure and its coming out of the loop before its completion and printing the last statement! not waiting for the sqlplus to complete.
for name in `cat abc.txt`
do
(sqlplus -s ... (3 Replies)
Discussion started by: ATWC
3 Replies
LEARN ABOUT DEBIAN
radsqlrelay
RADSQLRELAY(8) FreeRADIUS helper program RADSQLRELAY(8)
NAME
radsqlrelay - relay SQL queries to a central database server
SYNOPSIS
radsqlrelay [-?] [-d sql_driver] [-b database] [-f file] [-h host] [-u user] [-P port] [-p password] [-1] [-x] file_path
DESCRIPTION
radsqlrelay tails a SQL logfile and forwards the queries to a database server. Used to replicate accounting records to one (central) data-
base, even if the database has extended downtime.
The SQL logfile is created by the rlm_sql_log module. The module must be configured in the radiusd server before you can use radsqlrelay.
OPTIONS
-? Print usage help information.
-d sql_driver
Driver to use: mysql, pg, oracle.
-b database
Name of the database to use.
-f file
Read password from file, instead of command line.
-h host
Connect to host.
-u user
User for login.
-P port
Port number to use for connection.
-p password
Password to use when connecting to server.
-1 One-shot mode: push the file to database and exit.
-x Turn on debugging.
file_path
The pathname of the SQL logfile to use.
NOTES
Oracle driver
The command "radsqlrelay -d oracle -b db.domain.tld sql-relay" reads the database description stored in $TNS_ADMIN/tnsnames.ora:
db.domain.tld =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db.domain.tld)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = <DB SID>)
)
)
SEE ALSO
rlm_sql_log(5)
AUTHOR
Nicolas Baradakis <nicolas.baradakis@cegetel.net>
19 June 2005 RADSQLRELAY(8)