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
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
... 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>
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
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
$
$
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)
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)
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)
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)
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)
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)
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)
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)
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)