Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: unix capture oracle function error
Top Forums Shell Programming and Scripting unix capture oracle function error Post 302421447 by dips_ag on Friday 14th of May 2010 10:10:29 AM
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
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

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

8. 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

9. 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

10. 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

LEARN ABOUT SUNOS

capture-schema

capture-schema(1m)					    Application Server Utility						capture-schema(1m)

NAME

       capture-schema -  stores the database metadata (schema) in a file for use in mapping and execution

SYNOPSIS

       capture-schema-dburl url-username name- password password-driver a_jdbc_driver [-schemaname name] [-table tablename]* [-out filename]

       Stores  the  database metadata (schema) in a file. You can also use the Sun Java System Studio IDE to capture the database schema. Run cap-
       ture-schema as the same database user that owns the tables, so that the -username and -schemaname options  are  given  the  same  username.
       Additionally, you can grant the database user running the capture-schema command the ANALYZE ANY TABLE privilege.

OPTIONS

       -dburl		       JDBC URL expected by the driver for accessing a database.

       -username	       user name for authenticating access to a database.

       -password	       password for accessing the selected database.

       -driver		       JDBC driver classname in your CLASSPATH.

       -schemaname	       name of the user schema being captured. If not specified, the default will capture metadata for all tables from all
			       the schemas accessible to this user. Specifying this parameter is highly recommended. If more than  one	schema	is
			       accessible to this user, more than one table with the same name may be captured which will cause problems.

       -table		       name of the table; multiple table names can be specified.

       -out		       output target; defaults to stdout. This parameter corresponds to the schema sub-element of the sun-cmp-mapping ele-
			       ment in the sun-cmp-mapping_1_1.dtd file.

EXAMPLES

       Example 1: Using capture-schema

       capture-schema -dburl jdbc:oracle:thin:@sadbuttrue:1521:ora817
       -schemaname cantiflas -username CANTIFLAS -password enigma -driver
       oracle.jdbc.driver.OracleDriver

       asadmin(1M)

Sun Java System Application Server				    March 2004							capture-schema(1m)
All times are GMT -4. The time now is 09:57 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy