Friends,
I pass some runtime arguments (date, number) through ksh script to Oracle procedure, use input value and pass it on to procedure.
Oracle procedure gets input value, run query and logs everything in the logfile.
I'm facing with couple of challenges
1. Even though I pass all required parameter for procedure variable still script run complains about wrong number or type of arguments.
2. How to get all rows stored into logfile
3. Is this correct approach? looks like getting Oracle stored procedure output in logfile is really challenging?
Query is SELECT query but connecting KSH and Oracle seems challenging.
Code:
#!/bin/ksh
$1=10; #runtime input value
$2=30; #runtime input value
$3=50; #runtime input value
$4='20150114'; #yyyymmdd input value
echo "running Oracle procedure"
(
$ORACLE_HOME/bin/sqlplus -s -l
set linesize 100
var v_empid number,
var v_name varchar2;
var v_last varchar2;
var v_dt varchar2;
alter session set nls_date_format = 'mm-dd-yyyy hh24:mi:ss';
exec pkg.proc($1, $2, $3, $4,:v_empid, :v_name, :v_last, :v_dt);
) > $logFile
Code:
=== Oracle package/procedure ===
CREATE OR REPLACE PACKAGE pkg
AS
procedure proc (lFirst IN number,
lSecond IN number,
lThird IN number,
lFour IN date,
v_empid OUT number,
v_name OUT varchar2,
v_last OUT varchar2,
v_dt OUT date);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
AS
PROCEDURE proc (lFirst IN number,
lSecond IN number,
lThird IN number,
lFour IN date,
v_empid OUT number,
v_name OUT varchar2,
v_last OUT varchar2,
v_dt OUT date)
IS
BEGIN
SELECT dt, emp_id, first_name, last_name
INTO v_dt, v_empid, v_name, v_last
FROM employees
WHERE emp_id in (lFirst, lSecond)
AND dt >= to_date(lFour, 'yyyymmdd') - 1
AND date < date + INTERVAL '(lThird)' MINUTE;
END proc;
END pkg;
/
You don't assign values to the special variables $1, $2, $3, etc. in ksh, these are on the command line, so if your script is called my_script, you would run it like this:-
Code:
my_script 10 30 50 201440114
I would then presume that you need to pass them to the sqlplus command in the same way so that it can read them in too, although I thought that they were referred to as &1, &2, &3, etc. within SQL.
rbatte1, yes $1-4 are just example I showed of what will be runtime parameter will look like... I don't use them in actual script... yes script gets run like
Code:
my_script 10 30 50 20150114
problem is getting back result from stored procedure to display in logfile.
There are some issues:
* you never actually connect to any database (have a look at the SQL*Plus command connect)
* you need to feed the commands you wish to run to SQL*Plus. Here-documents or sql-commandfiles are common practice for this.
* in your procedure you declare parameter lFour as date but you pass a string. Call it like this:
* you read some values into variables but never print them.
* If your query does not return exactly one row you'll see an error message.
What is your final goal?
Spooling output of a query to a file does not have to be done via PL/SQL, actually it is a lot easier without.
The dbms_ouput package can be used to display results if PL/SQL is needed.
There are lots of ways to accomplish what you are trying to do, but from your example it is hard to tell which would fit.
Thanks cero... user has OS authentication so it can login without password.
I have a SELECT query which I need to put in a package (procedure or function) so other can use it. Query accepts some input parameter
What I'm trying
1. Execute pkg via ksh and pass four parameters (number, number, number, date)
2. Call pkg execute SELECT query, replace input parameters with variable in query
3. Get result and store it in logfile.
4. Output result will have 4 columns (:v_empid, :v_name, :v_last, :v_dt)
---------- Post updated at 02:09 PM ---------- Previous update was at 01:51 PM ----------
Also sometimes I get error message
ERROR at line 1:
ora-01403 no data found
ora-06512 at pkg.proc, line 16
ora-06512 at line1
Will the other users use the ksh script or will they access the database via other tools?
If the users access the database via other tools: is it enough to display the results so the users can redirect the output to a file (like in your example where the script takes care of storing the results)?
Your current package will throw ora-01403 if the query does not return any rows and ora-01422 if it returns more than 1 row.
Friends,
I'm newbie with ksh so wanting some help....
1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure.
2. Put name1 and name2 value from script run replacing $3 & $4
I'm trying to put name1 in... (4 Replies)
Hi Everyone,
I want to create a script where i need to run the oracle stored procedure from unix script and get the output(sequence number ) into a variable which i will pass in my datastage job.
Below is my stored procedure:-
DECLARE
P_TRANSTYPE VARCHAR2(20);
... (4 Replies)
Hi,
Can you assist me in how to redirect the output of oracle stored procedure from unix script?
Something similar to what i did for sybase
isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %%
proc_my_test 8
go
%%
Thanks in advance - jak (0 Replies)
Hi
i want to call a oracle stored procedure from unix (using bash shell).
consider this is my oracle stored procedure with parameter
create procedure testproc(name IN varchar, age IN Number, id OUT Number )
AS
begin
id=1;
dbms_output.put.line('successfull validation')
end;... (6 Replies)
Hi
i am calling a stored procedure from unix shell like this call
test_proc('0002','20100218');
the stored procedure was giving output
like this dbms_output.put_line(' processed earlier');
i want to see the output in the unix shell where i called.
Thanks
barani (6 Replies)
Here's a shell script snippet.....
cd $ORACLE_HOME/bin
Retval=`sqlplus -s <<eof
$TPDB_USER/april@$TPD_DBCONN
whenever SQLERROR exit 2 rollback
whenever OSERROR exit 3 rollback
set serveroutput on
set pages 999
var status_desc char(200)
var status_code... (1 Reply)
ksh and Oracle stored procedure output in logfile
