I want to execute an oracle function from unix script so for that I created a sample oracle function as below:
after that I executed this oracle func in the unix script as below:
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:
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
-dips
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:
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 -
And here's the failed execution -
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 -
Your function should've been this -
Have a look at the compilation and execution of the correct function below -
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:
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.
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.
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).
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:
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 -
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)