Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: any possible solution on sql calling scripts
Top Forums Shell Programming and Scripting any possible solution on sql calling scripts Post 302146548 by manas_ranjan on Wednesday 21st of November 2007 05:16:40 AM
Old 11-21-2007
any possible solution on sql calling scripts
hi all,

i have a function which will take i/p as a ddl sctipt as i/p and execute it,
let
HTML Code:
function execute_sql
{
db_var="$1"
v_cnt=`sqlplus -s XXXXX/XXXXX@aXXX << ENDSQL | sed -e "s/Connected\.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off  serveroutput on size 10000
whenever sqlerror exit SQL.SQLCODE;
$db_var
commit;
exit ;
ENDSQL`
db_var=""
}
if i am executing a wrong ddl script, then i am not able to catch the retrun code as 1 rather than it throws me 0 if i am executing the ddl thru the script function.

let my ddl which is wrong is ,
INSERT INTO CBF_CONFIG ( CS_ID, CS_NAME, FIELD_SEP, REC_SEP, FIELDS_NUM, FTPS_FILE, DECSN_FMT_TYPE, DECSN_FIELDS, DECSN_SCHDL_TYPE, DECSN_SCHDL, DECSN_TIME, DECSN_UNITWS, UNIQ_FILE_CONVNS, OUTBOX_RETN, UPDATE_TIME ) VALUES
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N',' Y','1',SYSDATE) ;
the o/p will as follows if i am executing it thru simple sqlplus environment,
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N',' Y','1',SYSDATE)
*
ERROR at line 2:
ORA-12899: value too large for column "XXXXX"."CBF_CONFIG"."CS_ID"
(actual: 11, maximum: 10)

but if i am going to call it thru the above shell functions,
HTML Code:
insert_sql="INSERT INTO CBF_CONFIG ( CS_ID, CS_NAME, FIELD_SEP, REC_SEP, FIELDS_NUM, FTPS_FILE, DECSN_FMT_TYPE, DECSN_FIELDS, DECSN_SCHDL_TYPE, DECSN_SCHDL, DECSN_TIME, DECSN_UNITWS, UNIQ_FILE_CONVNS, OUTBOX_RETN, UPDATE_TIME ) VALUES
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N','Y','1',SYSDATE) ; "
echo $insert_sql

execute_sql "$insert_sql"
rc=$?
echo "exit code $rc"
then rc is 0, but v_cnt is "ERROR at line 2:
ORA-12899: value too large for column "CLIFBACONFIG"."CBF_CONFIG"."CS_ID"
(actual: 11, maximum: 10)"

my question is why i am not able to catch SQL.SQLCODE even though in the function there is a line whenever sqlerror exit SQL.SQLCODE...
any suggestion to improve the error catching session...........
Last edited by manas_ranjan; 11-22-2007 at 05:06 AM.. Reason: remove login/pwd@dbname
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calling SQL scripts through Shell Script

Oracle and Scripting gurus, I need some help with this script... I am trying to add the query SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = 'METADATA' in the current script.... Read the result set and look for the TABLE_NAME field. If the field is pointing to one table eg.... (18 Replies)
Discussion started by: madhunk
18 Replies

2. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

3. Shell Programming and Scripting

calling .sql in .sh file

Hi All , I have created .sql script in unix.this script contains 4 sql queries. Now I want to craete .sh file based on .sql script. So please let me know the steps to craete .sh file Thank you. (1 Reply)
Discussion started by: user71408
1 Replies

4. Shell Programming and Scripting

Difference between calling the sub scripts

What is the difference between calling the sub scripts of below two line. /home/scripts/devdb.sh . /home/scripts/devdb.sh sh /home/scripts/devdb.sh We are using the suse 2.0 version (4 Replies)
Discussion started by: kingganesh04
4 Replies

5. Shell Programming and Scripting

Calling scripts within script

Hi, I have written a some six scripts to move large files and re-size them. This has been done step by step, taking backup, creating the new files, merging the files, removing the temporary files created. Since these files are around 500 MB, each step takes somewhere between 1 to 5 mins. ... (1 Reply)
Discussion started by: baanprog
1 Replies

6. Shell Programming and Scripting

Calling scripts from other script.

I need to call 3 different shell scripts from 2 different scripts, one is a perl script and other is Shell script. In Case -1 : The perl script is myperlscript.pl and the name of three shell scripts which need to be called from the perl script are a1.sh, a2.sh and a3.sh. Each shell script... (1 Reply)
Discussion started by: siba.s.nayak
1 Replies

7. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

8. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

9. Shell Programming and Scripting

Calling multiple scripts from another scripts

Dear all, I am working on script which call other shell scripts in a loop but problem is from second script am not able to come out. Here is the snippet:- #!/bin/bash HSFILE=/root/Test/Components.txt LOGFile=/opt/domain/AdminDomain/application/logs... (3 Replies)
Discussion started by: sharsour
3 Replies

10. Shell Programming and Scripting

UNIX Solution - Calling SQL*Plus scripts

I have a requirement of bash shell script calling SQL*Plus script. Shell Script sqlplus <user>/<pwd> @sqlscript1 parameters sqlplus <user>/<pwd> @sqlscript2 parameters Now I need the values generated by sqlscript1 in sqlscript2. But it is more like a huge dataset in a table and not one... (7 Replies)
Discussion started by: uuuunnnn
7 Replies

LEARN ABOUT CENTOS

datetime::locale::pa_arab

DateTime::Locale::pa_Arab(3)				User Contributed Perl Documentation			      DateTime::Locale::pa_Arab(3)

NAME

       DateTime::Locale::pa_Arab

SYNOPSIS

	 use DateTime;

	 my $dt = DateTime->now( locale => 'pa_Arab' );
	 print $dt->month_name();

DESCRIPTION

       This is the DateTime locale package for Punjabi Arabic.

DATA

       This locale inherits from the DateTime::Locale::pa locale.

       It contains the following data.

   Days
       Wide (format)

	 XXX
	 XXXX
	 XXXX
	 XXXXXX
	 XXXX
	 XXXX
	 XXXXX

       Abbreviated (format)

	 XXX
	 XXXX
	 XXXX
	 XXXXXX
	 XXXX
	 XXXX
	 XXXXX

       Narrow (format)

	 XX
	 XX
	 XXX
	 XX
	 XXXX
	 XX
	 X

       Wide (stand-alone)

	 XXX
	 XXXX
	 XXXX
	 XXXXXX
	 XXXX
	 XXXX
	 XXXXX

       Abbreviated (stand-alone)

	 XXX
	 XXXX
	 XXXX
	 XXXXXX
	 XXXX
	 XXXX
	 XXXXX

       Narrow (stand-alone)

	 XX
	 XX
	 XXX
	 XX
	 XXXX
	 XX
	 X

   Months
       Wide (format)

	 XXXXX
	 XXXXX
	 XXXX
	 XXXXX
	 XX
	 XXX
	 XXXXXX
	 XXXX
	 XXXXX
	 XXXXXX
	 XXXXX
	 XXXXX

       Abbreviated (format)

	 XXXXX
	 XXXXX
	 XXXX
	 XXXXX
	 XX
	 XXX
	 XXXXXX
	 XXXX
	 XXXXX
	 XXXXXX
	 XXXXX
	 XXXXX

       Narrow (format)

	 X
	 X
	 XX
	 X
	 X
	 XX
	 XX
	 X
	 X
	 X
	 X
	 X

       Wide (stand-alone)

	 XXXXX
	 XXXXX
	 XXXX
	 XXXXX
	 XX
	 XXX
	 XXXXXX
	 XXXX
	 XXXXX
	 XXXXXX
	 XXXXX
	 XXXXX

       Abbreviated (stand-alone)

	 1
	 2
	 3
	 4
	 5
	 6
	 7
	 8
	 9
	 10
	 11
	 12

       Narrow (stand-alone)

	 X
	 X
	 XX
	 X
	 X
	 XX
	 XX
	 X
	 X
	 X
	 X
	 X

   Quarters
       Wide (format)

	 XXXXXX XXXXX
	 XXXXXX XXXX
	 XXXXXX XXXX
	 XXXXXX XXXXX

       Abbreviated (format)

	 XXXXXX XXXXX
	 XXXXXX XXXX
	 XXXXXX XXXX
	 XXXXXX XXXXX

       Narrow (format)

	 1
	 2
	 3
	 4

       Wide (stand-alone)

	 XXXXXX XXXXX
	 XXXXXX XXXX
	 XXXXXX XXXX
	 XXXXXX XXXXX

       Abbreviated (stand-alone)

	 XXXXXX XXXXX
	 XXXXXX XXXX
	 XXXXXX XXXX
	 XXXXXX XXXXX

       Narrow (stand-alone)

	 1
	 2
	 3
	 4

   Eras
       Wide

	 XXXXXXXX
	 XX

       Abbreviated

	 XXXXXXXX
	 XX

       Narrow

	 XXXXXXXX
	 XX

   Date Formats
       Full

	  2008-02-05T18:30:30 = XXXX, 05 XXXXX 2008
	  1995-12-22T09:05:02 = XXXX, 22 XXXXX 1995
	 -0010-09-15T04:44:23 = XXXX, 15 XXXXX -10

       Long

	  2008-02-05T18:30:30 = 5 XXXXX 2008
	  1995-12-22T09:05:02 = 22 XXXXX 1995
	 -0010-09-15T04:44:23 = 15 XXXXX -10

       Medium

	  2008-02-05T18:30:30 = 5 XXXXX 2008
	  1995-12-22T09:05:02 = 22 XXXXX 1995
	 -0010-09-15T04:44:23 = 15 XXXXX -10

       Short

	  2008-02-05T18:30:30 = 05/02/2008
	  1995-12-22T09:05:02 = 22/12/1995
	 -0010-09-15T04:44:23 = 15/09/-010

       Default

	  2008-02-05T18:30:30 = 5 XXXXX 2008
	  1995-12-22T09:05:02 = 22 XXXXX 1995
	 -0010-09-15T04:44:23 = 15 XXXXX -10

   Time Formats
       Full

	  2008-02-05T18:30:30 = 6:30:30 XXXX UTC
	  1995-12-22T09:05:02 = 9:05:02 XXXXX UTC
	 -0010-09-15T04:44:23 = 4:44:23 XXXXX UTC

       Long

	  2008-02-05T18:30:30 = 6:30:30 XXXX UTC
	  1995-12-22T09:05:02 = 9:05:02 XXXXX UTC
	 -0010-09-15T04:44:23 = 4:44:23 XXXXX UTC

       Medium

	  2008-02-05T18:30:30 = 6:30:30 XXXX
	  1995-12-22T09:05:02 = 9:05:02 XXXXX
	 -0010-09-15T04:44:23 = 4:44:23 XXXXX

       Short

	  2008-02-05T18:30:30 = 6:30 XXXX
	  1995-12-22T09:05:02 = 9:05 XXXXX
	 -0010-09-15T04:44:23 = 4:44 XXXXX

       Default

	  2008-02-05T18:30:30 = 6:30:30 XXXX
	  1995-12-22T09:05:02 = 9:05:02 XXXXX
	 -0010-09-15T04:44:23 = 4:44:23 XXXXX

   Datetime Formats
       Full

	  2008-02-05T18:30:30 = XXXX, 05 XXXXX 2008 6:30:30 XXXX UTC
	  1995-12-22T09:05:02 = XXXX, 22 XXXXX 1995 9:05:02 XXXXX UTC
	 -0010-09-15T04:44:23 = XXXX, 15 XXXXX -10 4:44:23 XXXXX UTC

       Long

	  2008-02-05T18:30:30 = 5 XXXXX 2008 6:30:30 XXXX UTC
	  1995-12-22T09:05:02 = 22 XXXXX 1995 9:05:02 XXXXX UTC
	 -0010-09-15T04:44:23 = 15 XXXXX -10 4:44:23 XXXXX UTC

       Medium

	  2008-02-05T18:30:30 = 5 XXXXX 2008 6:30:30 XXXX
	  1995-12-22T09:05:02 = 22 XXXXX 1995 9:05:02 XXXXX
	 -0010-09-15T04:44:23 = 15 XXXXX -10 4:44:23 XXXXX

       Short

	  2008-02-05T18:30:30 = 05/02/2008 6:30 XXXX
	  1995-12-22T09:05:02 = 22/12/1995 9:05 XXXXX
	 -0010-09-15T04:44:23 = 15/09/-010 4:44 XXXXX

       Default

	  2008-02-05T18:30:30 = 5 XXXXX 2008 6:30:30 XXXX
	  1995-12-22T09:05:02 = 22 XXXXX 1995 9:05:02 XXXXX
	 -0010-09-15T04:44:23 = 15 XXXXX -10 4:44:23 XXXXX

   Available Formats
       d (d)

	  2008-02-05T18:30:30 = 5
	  1995-12-22T09:05:02 = 22
	 -0010-09-15T04:44:23 = 15

       EEEd (d EEE)

	  2008-02-05T18:30:30 = 5 XXXX
	  1995-12-22T09:05:02 = 22 XXXX
	 -0010-09-15T04:44:23 = 15 XXXX

       HHmmss (HH:mm:ss)

	  2008-02-05T18:30:30 = 18:30:30
	  1995-12-22T09:05:02 = 09:05:02
	 -0010-09-15T04:44:23 = 04:44:23

       Hm (H:mm)

	  2008-02-05T18:30:30 = 18:30
	  1995-12-22T09:05:02 = 9:05
	 -0010-09-15T04:44:23 = 4:44

       hm (h:mm a)

	  2008-02-05T18:30:30 = 6:30 XXXX
	  1995-12-22T09:05:02 = 9:05 XXXXX
	 -0010-09-15T04:44:23 = 4:44 XXXXX

       Hms (H:mm:ss)

	  2008-02-05T18:30:30 = 18:30:30
	  1995-12-22T09:05:02 = 9:05:02
	 -0010-09-15T04:44:23 = 4:44:23

       hms (h:mm:ss a)

	  2008-02-05T18:30:30 = 6:30:30 XXXX
	  1995-12-22T09:05:02 = 9:05:02 XXXXX
	 -0010-09-15T04:44:23 = 4:44:23 XXXXX

       M (L)

	  2008-02-05T18:30:30 = 2
	  1995-12-22T09:05:02 = 12
	 -0010-09-15T04:44:23 = 9

       Md (d/M)

	  2008-02-05T18:30:30 = 5/2
	  1995-12-22T09:05:02 = 22/12
	 -0010-09-15T04:44:23 = 15/9

       MEd (E, M-d)

	  2008-02-05T18:30:30 = XXXX, 2-5
	  1995-12-22T09:05:02 = XXXX, 12-22
	 -0010-09-15T04:44:23 = XXXX, 9-15

       MMM (LLL)

	  2008-02-05T18:30:30 = 2
	  1995-12-22T09:05:02 = 12
	 -0010-09-15T04:44:23 = 9

       MMMd (MMM d)

	  2008-02-05T18:30:30 = XXXXX 5
	  1995-12-22T09:05:02 = XXXXX 22
	 -0010-09-15T04:44:23 = XXXXX 15

       MMMEd (E MMM d)

	  2008-02-05T18:30:30 = XXXX XXXXX 5
	  1995-12-22T09:05:02 = XXXX XXXXX 22
	 -0010-09-15T04:44:23 = XXXX XXXXX 15

       MMMMd (MMMM d)

	  2008-02-05T18:30:30 = XXXXX 5
	  1995-12-22T09:05:02 = XXXXX 22
	 -0010-09-15T04:44:23 = XXXXX 15

       MMMMEd (E MMMM d)

	  2008-02-05T18:30:30 = XXXX XXXXX 5
	  1995-12-22T09:05:02 = XXXX XXXXX 22
	 -0010-09-15T04:44:23 = XXXX XXXXX 15

       mmss (mm:ss)

	  2008-02-05T18:30:30 = 30:30
	  1995-12-22T09:05:02 = 05:02
	 -0010-09-15T04:44:23 = 44:23

       ms (mm:ss)

	  2008-02-05T18:30:30 = 30:30
	  1995-12-22T09:05:02 = 05:02
	 -0010-09-15T04:44:23 = 44:23

       y (y)

	  2008-02-05T18:30:30 = 2008
	  1995-12-22T09:05:02 = 1995
	 -0010-09-15T04:44:23 = -10

       yM (y-M)

	  2008-02-05T18:30:30 = 2008-2
	  1995-12-22T09:05:02 = 1995-12
	 -0010-09-15T04:44:23 = -10-9

       yMEd (EEE, y-M-d)

	  2008-02-05T18:30:30 = XXXX, 2008-2-5
	  1995-12-22T09:05:02 = XXXX, 1995-12-22
	 -0010-09-15T04:44:23 = XXXX, -10-9-15

       yMMM (y MMM)

	  2008-02-05T18:30:30 = 2008 XXXXX
	  1995-12-22T09:05:02 = 1995 XXXXX
	 -0010-09-15T04:44:23 = -10 XXXXX

       yMMMEd (EEE, y MMM d)

	  2008-02-05T18:30:30 = XXXX, 2008 XXXXX 5
	  1995-12-22T09:05:02 = XXXX, 1995 XXXXX 22
	 -0010-09-15T04:44:23 = XXXX, -10 XXXXX 15

       yMMMM (y MMMM)

	  2008-02-05T18:30:30 = 2008 XXXXX
	  1995-12-22T09:05:02 = 1995 XXXXX
	 -0010-09-15T04:44:23 = -10 XXXXX

       yQ (y Q)

	  2008-02-05T18:30:30 = 2008 1
	  1995-12-22T09:05:02 = 1995 4
	 -0010-09-15T04:44:23 = -10 3

       yQQQ (y QQQ)

	  2008-02-05T18:30:30 = 2008 XXXXXX XXXXX
	  1995-12-22T09:05:02 = 1995 XXXXXX XXXXX
	 -0010-09-15T04:44:23 = -10 XXXXXX XXXX

       yyMMM (MMM yy)

	  2008-02-05T18:30:30 = XXXXX 08
	  1995-12-22T09:05:02 = XXXXX 95
	 -0010-09-15T04:44:23 = XXXXX -10

       yyQ (Q yy)

	  2008-02-05T18:30:30 = 1 08
	  1995-12-22T09:05:02 = 4 95
	 -0010-09-15T04:44:23 = 3 -10

   Miscellaneous
       Prefers 24 hour time?

       No

       Local first day of the week

       XXX

SUPPORT

       See DateTime::Locale.

AUTHOR

       Dave Rolsky <autarch@urth.org>

COPYRIGHT

       Copyright (c) 2008 David Rolsky. All rights reserved. This program is free software; you can redistribute it and/or modify it under the
       same terms as Perl itself.

       This module was generated from data provided by the CLDR project, see the LICENSE.cldr in this distribution for details on the CLDR data's
       license.

perl v5.16.3							    2014-06-10					      DateTime::Locale::pa_Arab(3)
All times are GMT -4. The time now is 02:19 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy