ksh and Oracle stored procedure output in logfile

Shell Programming and Scripting
1

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.

#!/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


=== 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;
/
2

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:-

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.

I hope that this helps,

Robin

3

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

my_script 10 30 50 20150114

problem is getting back result from stored procedure to display in logfile.

4

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:
exec pkg.proc($1, $2, $3, to_date($4,'YYYYMMDD'),:v_empid, :v_name, :v_last, :v_dt);
  • 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.

1 Like
5

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

6

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.

1 Like