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