Sponsored Content
Top Forums Shell Programming and Scripting Problems with storing oracle sqlplus query output shell script Post 302579076 by rockf1bull on Sunday 4th of December 2011 12:21:16 PM
Old 12-04-2011
Problems with storing oracle sqlplus query output shell script

Hello everyone,

I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected.

I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't work for me.

Syntax -
Code:
testvar=`sqlplus -s foo/bar@SCHM <<EOF
set pages 0
set head off
set feed off
@test.sql
exit
EOF`

Now, I have access to the sqlplus command from the oracle bin folder and have also set the ORACLE_HOME and ORACLE_PATH variables exported (I echoed them just to ensure they are actually working).

However, I keep getting the error saying - "you need to set EXPORT for ORACLE_HOME" even though I have confirmed from everyone that I am indeed using the correct path.

Another question I have is once I get the script output (which is numeric number in bits or bytes) value in a variable (There will be 4-5 variables as there are 4-5 scripts), how do I convert into human readable output in either MBs or GBs.

Please guide me on this and I assure you that I will post everything here so that someone in future if gets stuck at the same issue, doesn't have waste time. (and your precious time won't go bad either...)

Thanks in advance,
Brian

Last edited by Scott; 12-04-2011 at 02:19 PM.. Reason: Added code tags
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

run oracle query remotly with shell script

hello how to run shell script to excute oracle queries on remote db ? i have tried as following sqlplus -s user/password@remote_server "select query;" but not working also this one sqlplus -s user/password@remote_server `select query;` not working :( i add this line to run another... (4 Replies)
Discussion started by: mogabr
4 Replies

2. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

3. Shell Programming and Scripting

Formatting Oracle sqlplus output

a job extracts orcle data into unix as flat file. a single record breaks into two record in unix flat file. This is the case only for 6 records out of 60 lack records. (its not single record in two line. but its single record into record. ie., \n come into picture) can you tell me what... (6 Replies)
Discussion started by: Gopal_Engg
6 Replies

4. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./script.sh sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

5. Shell Programming and Scripting

Shell script to query Oracle table

Hi, unix gurnis I need help for following requirement for writing a shell scritp. log in to oracle database, query one table total records (select count(*) from table1), pass the return value to a file. Thanks in advance (2 Replies)
Discussion started by: ken002
2 Replies

6. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

7. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

8. Shell Programming and Scripting

sed not storing output in shell script

Hello, Following my learning of shell scripting.. I got stuck yet again. If I execute this command on terminal: $ sed "s/off/on/g" file > fileAUX I successfully change the text off to on from file to fileAUX. But the same command is not working inside a shell script. I tested and... (2 Replies)
Discussion started by: quinestor
2 Replies

9. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

10. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies
Warn(3) 						User Contributed Perl Documentation						   Warn(3)

NAME
Test::Warn - Perl extension to test methods for warnings SYNOPSIS
use Test::Warn; warning_is {foo(-dri => "/")} "Unknown Parameter 'dri'", "dri != dir gives warning"; warnings_are {bar(1,1)} ["Width very small", "Height very small"]; warning_is {add(2,2)} undef, "No warnings for calc 2+2"; # or warnings_are {add(2,2)} [], "No warnings for calc 2+2"; # what reads better :-) warning_like {foo(-dri => "/")} qr/unknown param/i, "an unknown parameter test"; warnings_like {bar(1,1)} [qr/width.*small/i, qr/height.*small/i]; warning_is {foo()} {carped => "didn't find the right parameters"}; warnings_like {foo()} [qr/undefined/,qr/undefined/,{carped => qr/no result/i}]; warning_like {foo(undef)} 'uninitialized'; warning_like {bar(file => '/etc/passwd')} 'io'; warning_like {eval q/"$x"; $x;/} [qw/void uninitialized/], "some warnings at compile time"; warnings_exist {...} [qr/expected warning/], "Expected warning is thrown"; DESCRIPTION
A good style of Perl programming calls for a lot of diverse regression tests. This module provides a few convenience methods for testing warning based code. If you are not already familiar with the Test::More manpage now would be the time to go take a look. FUNCTIONS warning_is BLOCK STRING, TEST_NAME Tests that BLOCK gives the specified warning exactly once. The test fails if the BLOCK warns more than once or does not warn at all. If the string is undef, then the tests succeeds if the BLOCK doesn't give any warning. Another way to say that there are no warnings in the block is "warnings_are {foo()} [], "no warnings"". If you want to test for a warning given by Carp, you have to write something like: "warning_is {carp "msg"} {carped => 'msg'}, "Test for a carped warning"". The test will fail if a "normal" warning is found instead of a "carped" one. Note: "warn "foo"" would print something like "foo at -e line 1". This method ignores everything after the "at". Thus to match this warning you would have to call "warning_is {warn "foo"} "foo", "Foo succeeded"". If you need to test for a warning at an exactly line, try something like "warning_like {warn "foo"} qr/at XYZ.dat line 5/". warning_is and warning_are are only aliases to the same method. So you also could write "warning_is {foo()} [], "no warning"" or something similar. I decided to give two methods the same name to improve readability. A true value is returned if the test succeeds, false otherwise. The test name is optional, but recommended. warnings_are BLOCK ARRAYREF, TEST_NAME Tests to see that BLOCK gives exactly the specified warnings. The test fails if the warnings from BLOCK are not exactly the ones in ARRAYREF. If the ARRAYREF is equal to [], then the test succeeds if the BLOCK doesn't give any warning. Please read also the notes to warning_is as these methods are only aliases. If you want more than one test for carped warnings, try this: "warnings_are {carp "c1"; carp "c2"} {carped =" ['c1','c2'];> or "warnings_are {foo()} ["Warning 1", {carped =" ["Carp 1", "Carp 2"]}, "Warning 2"]>. Note that "{carped =" ...}> must always be a hash ref. warning_like BLOCK REGEXP, TEST_NAME Tests that BLOCK gives exactly one warning and it can be matched by the given regexp. If the string is undef, then the tests succeeds if the BLOCK doesn't give any warning. The REGEXP is matched against the whole warning line, which in general has the form "WARNING at __FILE__ line __LINE__". So you can check for a warning in the file Foo.pm on line 5 with "warning_like {bar()} qr/at Foo.pm line 5/, "Testname"". I don't know whether it's sensful to do such a test :-( However, you should be prepared as a matching with 'at', 'file', 'd' or similar will always pass. Think to the qr/^foo/ if you want to test for warning "foo something" in file foo.pl. You can also write the regexp in a string as "/.../" instead of using the qr/.../ syntax. Note that the slashes are important in the string, as strings without slashes are reserved for warning categories (to match warning categories as can be seen in the perllexwarn man page). Similar to "warning_is", you can test for warnings via "carp" with: "warning_like {bar()} {carped =" qr/bar called too early/i};> Similar to "warning_is"/"warnings_are", "warning_like" and "warnings_like" are only aliases to the same methods. A true value is returned if the test succeeds, false otherwise. The test name is optional, but recommended. warning_like BLOCK STRING, TEST_NAME Tests whether a BLOCK gives exactly one warning of the passed category. The categories are grouped in a tree, like it is expressed in perllexwarn. Note, that they have the hierarchical structure from perl 5.8.0, wich has a little bit changed to 5.6.1 or earlier versions (You can access the internal used tree with $Test::Warn::Categorization::tree, although I wouldn't recommend it) Thanks to the grouping in a tree, it's simple possible to test for an 'io' warning, instead for testing for a 'closed|exec|layer|newline|pipe|unopened' warning. Note, that warnings occuring at compile time, can only be catched in an eval block. So warning_like {eval q/"$x"; $x;/} [qw/void uninitialized/], "some warnings at compile time"; will work, while it wouldn't work without the eval. Note, that it isn't possible yet, to test for own categories, created with warnings::register. warnings_like BLOCK ARRAYREF, TEST_NAME Tests to see that BLOCK gives exactly the number of the specified warnings and all the warnings have to match in the defined order to the passed regexes. Please read also the notes to warning_like as these methods are only aliases. Similar to "warnings_are", you can test for multiple warnings via "carp" and for warning categories, too: warnings_like {foo()} [qr/bar warning/, qr/bar warning/, {carped => qr/bar warning/i}, 'io' ], "I hope, you'll never have to write a test for so many warnings :-)"; warnings_exist BLOCK STRING|ARRAYREF, TEST_NAME Same as warning_like, but will warn() all warnings that do not match the supplied regex/category, instead of registering an error. Use this test when you just want to make sure that specific warnings were generated, and couldn't care less if other warnings happened in the same block of code. warnings_exist {...} [qr/expected warning/], "Expected warning is thrown"; warnings_exist {...} ['uninitialized'], "Expected warning is thrown"; EXPORT "warning_is", "warnings_are", "warning_like", "warnings_like", "warnings_exist" by default. BUGS
Please note that warnings with newlines inside are making a lot of trouble. The only sensible way to handle them is to use are the "warning_like" or "warnings_like" methods. Background for these problems is that there is no really secure way to distinguish between warnings with newlines and a tracing stacktrace. If a method has it's own warn handler, overwriting $SIG{__WARN__}, my test warning methods won't get these warnings. The "warning_like BLOCK CATEGORY, TEST_NAME" method isn't extremely tested. Please use this calling style with higher attention and tell me if you find a bug. TODO
Improve this documentation. The code has some parts doubled - especially in the test scripts. This is really awkward and must be changed. Please feel free to suggest improvements. SEE ALSO
Have a look to the similar modules: Test::Exception, Test::Trap. THANKS
Many thanks to Adrian Howard, chromatic and Michael G. Schwern, who have given me a lot of ideas. AUTHOR
Janek Schleicher, <bigj AT kamelfreund.de> COPYRIGHT AND LICENSE
Copyright 2002 by Janek Schleicher Copyright 2007-2011 by Alexandr Ciornii, <http://chorny.net/> This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. perl v5.18.2 2012-03-31 Warn(3)
All times are GMT -4. The time now is 12:13 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy