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 MOJAVE

locale::codes::script

Locale::Codes::Script(3pm)				 Perl Programmers Reference Guide				Locale::Codes::Script(3pm)

NAME

       Locale::Codes::Script - standard codes for script identification

SYNOPSIS

	  use Locale::Codes::Script;

	  $script  = code2script('phnx');		      # 'Phoenician'
	  $code    = script2code('Phoenician'); 	      # 'Phnx'
	  $code    = script2code('Phoenician',
				 LOCALE_CODE_NUMERIC);	      # 115

	  @codes   = all_script_codes();
	  @scripts = all_script_names();

DESCRIPTION

       The "Locale::Codes::Script" module provides access to standards codes used for identifying scripts, such as those defined in ISO 15924.

       Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 15924
       four-letter codes will be used.

SUPPORTED CODE SETS

       There are several different code sets you can use for identifying scripts. A code set may be specified using either a name, or a constant
       that is automatically exported by this module.

       For example, the two are equivalent:

	  $script = code2script('phnx','alpha');
	  $script = code2script('phnx',LOCALE_SCRIPT_ALPHA);

       The codesets currently supported are:

       alpha, LOCALE_SCRIPT_ALPHA
	   This is a set of four-letter (capitalized) codes from ISO 15924 such as 'Phnx' for Phoenician.  It also includes additions to this set
	   included in the IANA language registry.

	   The Zxxx, Zyyy, and Zzzz codes are not used.

	   This is the default code set.

       num, LOCALE_SCRIPT_NUMERIC
	   This is a set of three-digit numeric codes from ISO 15924 such as 115 for Phoenician.

ROUTINES

       code2script ( CODE [,CODESET] )
       script2code ( NAME [,CODESET] )
       script_code2code ( CODE ,CODESET ,CODESET2 )
       all_script_codes ( [CODESET] )
       all_script_names ( [CODESET] )
       Locale::Codes::Script::rename_script  ( CODE ,NEW_NAME [,CODESET] )
       Locale::Codes::Script::add_script  ( CODE ,NAME [,CODESET] )
       Locale::Codes::Script::delete_script  ( CODE [,CODESET] )
       Locale::Codes::Script::add_script_alias	( NAME ,NEW_NAME )
       Locale::Codes::Script::delete_script_alias  ( NAME )
       Locale::Codes::Script::rename_script_code  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Codes::Script::add_script_code_alias  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Codes::Script::delete_script_code_alias	( CODE [,CODESET] )
	   These routines are all documented in the Locale::Codes::API man page.

SEE ALSO

       Locale::Codes
	   The Locale-Codes distribution.

       Locale::Codes::API
	   The list of functions supported by this module.

       http://www.unicode.org/iso15924/
	   Home page for ISO 15924.

       http://www.iana.org/assignments/language-subtag-registry
	   The IANA language subtag registry.

AUTHOR

       See Locale::Codes for full author history.

       Currently maintained by Sullivan Beck (sbeck@cpan.org).

COPYRIGHT

	  Copyright (c) 1997-2001 Canon Research Centre Europe (CRE).
	  Copyright (c) 2001-2010 Neil Bowers
	  Copyright (c) 2010-2013 Sullivan Beck

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.18.2							    2013-11-04						Locale::Codes::Script(3pm)
All times are GMT -4. The time now is 07:48 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy