Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

unix capture oracle function error

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting unix capture oracle function error
# 1  
Old 05-14-2010
unix capture oracle function error
Hi,

I want to execute an oracle function from unix script so for that I created a sample oracle function as below:

Code:
 
create or replace
function test_fn(test_date out varchar2)
RETURN varchar2
IS
BEGIN
select to_char(sysdate,'DD-MON-YY') into test_date from dual;   
return test_date;
END test_fn;

after that I executed this oracle func in the unix script as below:
Code:
 
var=`sqlplus -s <<EndOfLine
user/pass@DB
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT FAILURE
set serveroutput on
set echo off
set feedback off
set verify off
set linesize 500
DECLARE
test_var varchar2(100);
BEGIN
test_var:=test_fn(test_var);
dbms_output.put_line(test_var);
END;
/
quit;
EndOfLine`
 
echo $var

and it executed fine and gave result as "14-MAY-10". But just to know how this will capture database errors I gave a wrong function name
instead of test_fn I gave fn_test. Now a very strange thing happened it listed all the files in the current directory and then gave the real database error:
Code:
 
test_var:=test_fn(test_var); file1 file2 file3 file4 ERROR at line 4: ORA-06550: line 4, column 11: PLS-00201: identifier 'fn_test' must be declared ORA-06550: line 4, column 1: PL/SQL: Statement ignored

Here files in red are the files present in the current folder where I am executing this script. I am really baffled by this. Do anyone suggest how to rectify the script so that on error it shows the actual error?

NOTE: I cannot direct the result of the oracle function to a file I've capture in a variable only i.e. I cannot do anything like this
Code:
sqlplus -s <<EndOfLine > result_file

-dips
# 2  
Old 05-14-2010
Quote:
Originally Posted by dips_ag
NOTE: I cannot direct the result of the oracle function to a file I've capture in a variable only i.e. I cannot do anything like this
Code:
sqlplus -s <<EndOfLine > result_file

-dips
Still, something like below should work.

Code:
sqlplus -s <<EndOfLine 2 > error_file

Have you tried that?
# 3  
Old 05-14-2010
Quote:
Still, something like below should work.


Code:
sqlplus -s <<EndOfLine 2 > error_file

Have you tried that?
Still the error_file showed the same error I metioned earlier.

Quote:
test_var:=test_fn(test_var); file1 file2 file3 file4 ERROR at line 4: ORA-06550: line 4, column 11: PLS-00201: identifier 'fn_test' must be declared ORA-06550: line 4, column 1: PL/SQL: Statement ignored
-dips
# 4  
Old 05-14-2010
Quote:
Originally Posted by dips_ag
... I gave a wrong function name
instead of test_fn I gave fn_test. Now a very strange thing happened it listed all the files in the current directory and then gave the real database error:
Code:
 
test_var:=test_fn(test_var); file1 file2 file3 file4 ERROR at line 4: ORA-06550: line 4, column 11: PLS-00201: identifier 'fn_test' must be declared ORA-06550: line 4, column 1: PL/SQL: Statement ignored

Here files in red are the files present in the current folder where I am executing this script. ...
What exactly is the text in bold, blue above ?
You haven't specified "test_fn" in your script anywhere.
And Oracle wouldn't return that either.
So why is "test_fn" seen over there ?

Can you copy/paste the exact Terminal session that shows what exactly you executed and what exactly you encountered as a result ?

(I am unable to reproduce the "listing-of-files-in-current-directory" behavior, but then my environment - Cygwin Bash -> Windows sqlplus -> Oracle server on WinXP may be drastically different from yours.)

Quote:
Do anyone suggest how to rectify the script so that on error it shows the actual error?
The "failure" in case of sqlerror or oserror will be reflected in the command return value of *nix i.e. $?

The exception message will be populated in the "var" shell variable.

Here's the successful execution -

Code:
$
$
$ cat -n test_function.sh
     1  #!/usr/bin/bash
     2  var=`sqlplus -s test/test <<EndOfLine
     3  WHENEVER SQLERROR EXIT FAILURE
     4  WHENEVER OSERROR  EXIT FAILURE
     5  set serveroutput on
     6  set echo off
     7  set feedback off
     8  set verify off
     9  set linesize 500
    10  DECLARE
    11  test_var varchar2(100);
    12  BEGIN
    13  test_var:=test_fn(test_var);
    14  dbms_output.put_line(test_var);
    15  END;
    16  /
    17  quit;
    18  EndOfLine`
    19  echo "RETURN VALUE = $?"
    20  echo "VALUE OF VAR = $var"
    21
$
$ ./test_function.sh
RETURN VALUE = 0
VALUE OF VAR = 14-MAY-10
$
$

And here's the failed execution -

Code:
$
$
$ cat -n test_function.sh
     1  #!/usr/bin/bash
     2  var=`sqlplus -s test/test <<EndOfLine
     3  WHENEVER SQLERROR EXIT FAILURE
     4  WHENEVER OSERROR  EXIT FAILURE
     5  set serveroutput on
     6  set echo off
     7  set feedback off
     8  set verify off
     9  set linesize 500
    10  DECLARE
    11  test_var varchar2(100);
    12  BEGIN
    13  test_var:=fn_test(test_var);
    14  dbms_output.put_line(test_var);
    15  END;
    16  /
    17  quit;
    18  EndOfLine`
    19  echo "RETURN VALUE = $?"
    20  echo "VALUE OF VAR = $var"
    21
$
$ ./test_function.sh
RETURN VALUE = 1
VALUE OF VAR = test_var:=fn_test(test_var);
          *
ERROR at line 4:
ORA-06550: line 4, column 11:
PLS-00201: identifier 'FN_TEST' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

$
$

Note that the text in blue is the entire exception message stored in "var". That's exactly what you'd have seen in command-line sqlplus as well.

Finally, as a side note - your Oracle function code should not have OUT parameters. An Oracle function returns one and only one value, the datatype of which must be specified by the RETURN clause. Parameters passed to an Oracle function should be IN parameters, and you do not have to specify the keyword "IN".

Notice how the OUT parameter mode forces the function call to fail from a SELECT statement -

Code:
test@ORA11G>
test@ORA11G> -- check my function code
test@ORA11G> select text from user_source where name = 'TEST_FN' order by line;
TEXT
----------------------------------------------------------------------
function test_fn(test_date out varchar2)
RETURN varchar2
IS
BEGIN
select to_char(sysdate,'DD-MON-YY') into test_date from dual;
return test_date;
END test_fn;
7 rows selected.
test@ORA11G>
test@ORA11G> -- invoke this function from a SELECT statement
test@ORA11G> var x varchar2(20)
test@ORA11G>
test@ORA11G> select test_fn(:x) dt from dual;
select test_fn(:x) dt from dual
       *
ERROR at line 1:
ORA-06572: Function TEST_FN has out arguments

test@ORA11G>
test@ORA11G>

Your function should've been this -

Code:
CREATE OR REPLACE FUNCTION test_fn
RETURN VARCHAR2
IS
BEGIN
  RETURN TO_CHAR(SYSDATE,'dd-mon-yy');
END test_fn;
/

Have a look at the compilation and execution of the correct function below -

Code:
test@ORA11G>
test@ORA11G> --
test@ORA11G> CREATE OR REPLACE FUNCTION test_fn
  2  RETURN VARCHAR2
  3  IS
  4  BEGIN
  5    RETURN TO_CHAR(SYSDATE,'dd-mon-yy');
  6  END test_fn;
  7  /
Function created.
test@ORA11G>
test@ORA11G> show errors
No errors.
test@ORA11G>
test@ORA11G>
test@ORA11G>
test@ORA11G> column x format a30
test@ORA11G> -- call the function from a SELECT statement
test@ORA11G>
test@ORA11G> select test_fn x from dual;
X
------------------------------
14-may-10
1 row selected.
test@ORA11G>
test@ORA11G> -- and now call the function in an anonymous PL/SQL block
test@ORA11G>
test@ORA11G> declare
  2    x  varchar2(20);
  3  begin
  4    x := test_fn;
  5    dbms_output.put_line('Value of x = '||x);
  6  end;
  7  /
Value of x = 14-may-10
PL/SQL procedure successfully completed.
test@ORA11G>
test@ORA11G>

HTH,
tyler_durden
# 5  
Old 05-15-2010
Hi durden_tyler,

Quote:
Finally, as a side note - your Oracle function code should not have OUT parameters. An Oracle function returns one and only one value, the datatype of which must be specified by the RETURN clause. Parameters passed to an Oracle function should be IN parameters, and you do not have to specify the keyword "IN".
Actually this is just a sample function in real there would be an IN parameter and I can forgo with OUT parameter but not the SELECT clause; the function will have to use an SELECT clause as it's going to query a table based on the IN parameter.

As you suggested I modified the Oracle function without OUT parameter now it looks like:

Code:
create or replace
function test_fn
RETURN varchar2
IS
test_date VARCHAR2(100);
BEGIN

select to_char(sysdate,'DD-MON-YY') into test_date from dual;   
return test_date ;
END test_fn;

But still the problem persists.

Quote:
Can you copy/paste the exact Terminal session that shows what exactly you executed and what exactly you encountered as a result ?
Here's it is. I deliberately gave a wrong funciton name (test_fn1)(in blue) NOTE: I created a "testing" directory and touched "file1", "file2" & "file3" which (in red) can be seen listed when error occurs.

Code:
$ ./calltest_fn.ksh
var test_var:=test_fn1; calltest_fn.ksh file1 file2 file3 ERROR at line 4: ORA-06550: line 4, column 11: PLS-00201: identifier 'test_fn1' must be declared ORA-06550: line 4, column 1: PL/SQL: Statement ignored

Quote:
(I am unable to reproduce the "listing-of-files-in-current-directory" behavior, but then my environment - Cygwin Bash -> Windows sqlplus -> Oracle server on WinXP may be drastically different from yours.)
Yeah even I am perplexed! I am surely doing something silly. Here's the m/c on which I am running the script.
Code:
$ uname -a
Linux <machine name> 2.6.9-89.ELsmp ####################


The script calltest_fn.ksh is as follows:
Code:
#!/usr/bin/ksh

#set -x

var=`sqlplus -s <<EOF
user/pass@DB
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR  EXIT FAILURE
set serveroutput on
set feedback off
set linesize 250
DECLARE
test_var varchar2(100);
BEGIN
test_var:=test_fn1;
dbms_output.put_line(test_var);
END;
/
quit;
EOF`

echo var $var

-dips
# 6  
Old 05-15-2010
Ok, I tested this out in my Linux machine (Fedora 11 with Oracle 11gR2), and am able to reproduce your error.

Very interesting error, but the reason is perfectly credible.

In one sentence, this is what is happening - Oracle returns "*" as a part of its error message; your shell does a "*"-expansion and that results in a listing of files in current directory.

Here's how Oracle throws an exception. (I have your function "test_fn" in my test schema, and it returns a VARCHAR2).

Code:
SQL> 
SQL> -- check the code of TEST_FN in my schema
SQL> select text from user_source where name='TEST_FN' order by line;

TEXT
--------------------------------------------------------------------------------
function test_fn
RETURN varchar2
IS
test_date VARCHAR2(100);
BEGIN

select to_char(sysdate,'DD-MON-YY') into test_date from dual;
return test_date ;
END test_fn;

9 rows selected.

SQL> 
SQL> -- check if there's a function (or any object for that matter) called "TEST_FN1"
SQL> select object_type, object_name, status from user_objects where object_name = 'TEST_FN1';

no rows selected

SQL> 
SQL> -- now try to call the (supposed) function "TEST_FN1"
SQL> declare
  2  x varchar2(20);
  3  begin
  4  x := test_fn1;
  5  end;
  6  /
x := test_fn1;
     *
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00201: identifier 'TEST_FN1' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


SQL> 
SQL> -- notice the "*" in the second line of the error message ?
SQL> -- now try another error - assign the return value of "TEST_FN" to a *NUMBER* instead of a *VARCHAR2*
SQL> 
SQL> declare
  2  x number;
  3  begin
  4  x := test_fn;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4


SQL> 
SQL> -- notice the "*" in the second line of the error message ?
SQL> 
SQL>

Imagine if that "*" is assigned to a Unix variable. If you try to print it without double-quotes, the shell will expand the "*" to the file list in current directory. (That's the special meaning of "*" to a shell actually.)

I have a very simple testcase here to illustrate this point:

Code:
$ 
$ # First, try out the error condition in the shell
$ echo "declare
> x varchar2(20);
> begin
> x := test_fn1;
> end;
> /
> " | sqlplus -s test/test
x := test_fn1;
     *
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00201: identifier 'TEST_FN1' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


$ # This was as expected. The text in blue is being assigned to shell
$ # variable "var" in your script. Let's try something like that -
$ 
$ my_variable=`echo "declare
> x varchar2(20);
> begin
> x := test_fn1;
> end;
> /
> " | sqlplus -s test/test`
$ 
$ # ok, so now the entire error message would've been assigned to "my_variable"
$ # Let's print it with double quotes
$ 
$ echo "Value of my_variable is ==>|$my_variable|<=="
Value of my_variable is ==>|x := test_fn1;
     *
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00201: identifier 'TEST_FN1' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored|<==
$ 
$ # That's all fine and dandy. 
$ # Note 1 - newlines are printed as they are.
$ # Note 2 - the "*" is printed as it is.
$ # Now try without double quotes. I am printing 3 strings here
$ 
$ echo "Value of my_variable is ==>|" $my_variable "|<=="
Value of my_variable is ==>| x := test_fn1; calltest_fn.ksh create_function.sql file1 file2 file3 ERROR at line 4: ORA-06550: line 4, column 6: PLS-00201: identifier 'TEST_FN1' must be declared ORA-06550: line 4, column 1: PL/SQL: Statement ignored |<==
$ 
$ 
$ # Note 1 - newlines in 2nd string were removed by the *shell*
$ # Note 2 - the first and third strings were printed faithfully
$ # Note 3 - first string is "Value of my_variable is ==>|", third string is "|<=="
$ # Note 4 - the shell started printing the value of "my_variable" till "x := test_fn1", but then it encountered a "*", so it expanded it
$ # Note 5 - and after that it printed "ERROR at line 4:" and so on removing newlines as it went
$ 
$

Moral of the story - always use double quotes when you want to print the value of a shell variable that is assigned the output of an Oracle session.

Here's your modified script tested for a failed execution -

Code:
$ 
$ cat -n calltest_fn.ksh
     1    #!/bin/ksh
     2    #set -x
     3    
     4    var=`sqlplus -s /nolog <<EOF
     5    connect test/test
     6    WHENEVER SQLERROR EXIT FAILURE
     7    WHENEVER OSERROR  EXIT FAILURE
     8    set serveroutput on
     9    set feedback off
    10    set linesize 250
    11    DECLARE
    12    test_var varchar2(100);
    13    BEGIN
    14    test_var:=test_fn1;
    15    dbms_output.put_line(test_var);
    16    END;
    17    /
    18    quit;
    19    EOF`
    20    
    21    echo "Value of var is $var"
    22    
$ 
$ 
$ ./calltest_fn.ksh
Value of var is test_var:=test_fn1;
          *
ERROR at line 4:
ORA-06550: line 4, column 11:
PLS-00201: identifier 'TEST_FN1' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
$ 
$

And tested for a successful execution:

Code:
$ 
$ 
$ cat -n calltest_fn.ksh
     1    #!/bin/ksh
     2    #set -x
     3    
     4    var=`sqlplus -s /nolog <<EOF
     5    connect test/test
     6    WHENEVER SQLERROR EXIT FAILURE
     7    WHENEVER OSERROR  EXIT FAILURE
     8    set serveroutput on
     9    set feedback off
    10    set linesize 250
    11    DECLARE
    12    test_var varchar2(100);
    13    BEGIN
    14    test_var:=test_fn;
    15    dbms_output.put_line(test_var);
    16    END;
    17    /
    18    quit;
    19    EOF`
    20    
    21    echo "Value of var is $var"
    22    
$ 
$ ./calltest_fn.ksh
Value of var is 15-MAY-10
$ 
$

HTH,
tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Red Hat

Unable to capture value from function

Hi Experts, Am writing a code which need to check for the previous day date and pickup the file as per the previous day date. Problem: Why variable "YDATE" is empty ? O/S: RHEL 5.6 Shell: BASH Desired O/P: ls -lrt /opt/test/user/atsuser.NHU/out/demon.08272017 When I checked the... (3 Replies)
Discussion started by: pradeep84in
3 Replies

2. Homework & Coursework Questions

How to Dynamically Pass Parameter to plsql Function & Capture its Output Value in a Shell Variable?

Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted! 1. The problem statement, all variables and given/known data: 2. Relevant commands, code, scripts, algorithms: #! /bin/ksh v="ORG_ID" ... (2 Replies)
Discussion started by: sujitdas2104
2 Replies

3. Shell Programming and Scripting

How to capture system() function output in variable

How to capture system() function output in awk variable and the print that awk variable..... (8 Replies)
Discussion started by: bharat1211
8 Replies

4. Shell Programming and Scripting

Capture rows for a column in file from delete sql -Oracle

Hi, This may not be the right forum but i am hoping someone knows an answer to this. I have to capture rows for a column that was deleted. How can i do that without having to write a select query? delete from myschema.mytable where currentdatetimestamp > columnDate this should delete 5... (4 Replies)
Discussion started by: jakSun8
4 Replies

5. Shell Programming and Scripting

Need to capture error of sybase isql in unix

Hi Gurus, I am very new in Unix, I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table. every thing is working fine, but the problem is with Error. I made some hanges in my insert statement so... (3 Replies)
Discussion started by: aksar
3 Replies

6. Shell Programming and Scripting

Need to capture error of sybase sql in unix

Hi Gurus, I am very new in Unix, I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table. every thing is working fine, but the problem is with Error. I made some hanges in my insert statement so... (1 Reply)
Discussion started by: aksar
1 Replies

7. Shell Programming and Scripting

how to capture oracle function returning 2 values in unix

i have an oracle function which returns two values, one is the error message if the function encounters anything and another one which returns a number i need to capture both and pass it on to unix shell script how to do it (2 Replies)
Discussion started by: trichyselva
2 Replies

8. Shell Programming and Scripting

How to capture value in shell variable from oracle sql?

Hi Friends, Do someone know how to capture value in a shell variable from oracle sql? Requirement : In a table we want to count the number of records and want to pass this value to a shell variable where it can be manipulated later. In ksh shell we open oracle connection from sqlplus. For... (1 Reply)
Discussion started by: sourabhsharma
1 Replies

9. Solaris

how to capture oracle export log while running as background process

I ran the Oracle 9i export command from a terminal to export out a big table using "exp andrew/password file=andrew.dmp log=andrew.log" From the terminal I can see that the export is running as there is some output from the oracle export job. The export job is not complete yet. When i go check... (4 Replies)
Discussion started by: hippo2020
4 Replies

10. Shell Programming and Scripting

Capture Oracle return code in shell script

I am using the following code in my shell script list=`sqlplus -s $user/$pwd@$dbms<<EOF WHENEVER SQLERROR EXIT SQL.SQLCODE set pagesize 0 feedback off verify off heading off echo off select * from control_tbl where src_nm=$3 and extrct_nm=$4; exit SQL.SQLCODE; EOF` ERROR=$?... (1 Reply)
Discussion started by: Vikas Sood
1 Replies
Login or Register to Ask a Question