pass value from Oracle sql to Korn shell


 
Thread Tools Search this Thread
Top Forums Programming pass value from Oracle sql to Korn shell
# 1  
Old 09-20-2012
pass value from Oracle sql to Korn shell

Hi All ,
I am trying to pass a value from sqlplus to korn shell .
There is a table tab1 in Oracle that has a column userdate.
I need to pass the userdate to the korn shell . This is what I am doing .

Code:
VALUE=`sqlplus -silent username/password << END  
set pagesize 0 feedback off verify off heading off echo off termout off
set serveroutput on
variable retval number;
begin
SELECT to_CHAR(userdate,'MMDD') into :retval FROM tab1 where rownum=1; 
end;
/
exit :retval;
END`
echo "MMDD IS $VALUE"

But the output is only
Code:
MMDD is

I am not getting a value . For example if the userdate in the table tab1 is 9/20/2012 I must be getting the output of my korn shell script as
0920 . All I am getting is, MMDD is
Please help.

Thanks
Megha.
# 2  
Old 09-20-2012
Usually I get my data from stdout. Often I filter it out like this, so I get exactly what I want and all messages go to a log for debug:
Code:
export MMDD=$( echo "select
  'dAtA' || to_char(userdate,'MMDD') || 'eNdATa' x
 from dual ;
 " | sqlplus ... 2>&1 | sed '
  w logfile
  s/.*dAtA\(.*\)eNdAtA.*/\1/
  t
  d
 ' )

UNIX exit() returns a character 0-255 status, not enough for MMDD, and it is for error checking with:
Code:
some_command some_args
RET=$?
if [ $RET != 0 ]
 then
  echo error return: $RET >&2
 fi


Last edited by DGPickett; 09-20-2012 at 07:06 PM..
# 3  
Old 09-21-2012
Hi

The tried the above and I still get the same output.
MMDD is
The value is not being displayed.

Thanks
Megha
# 4  
Old 09-21-2012
Must be an SQL issue, and I do not have an Oracle this gig. Try CURRENT_DATE or SYSDATE inplkace of userdate. Google says userdate is not defined. Maybe knock off the -silent, as my sed moves all that to the log?
# 5  
Old 09-21-2012
Try something like this:
Code:
VALUE=`sqlplus -silent username/password << END  
set pagesize 0 feedback off verify off heading off echo off termout off
SELECT to_CHAR(userdate,'MMDD') FROM tab1 where rownum=1; 
END
`
echo "MMDD IS $VALUE"

No need for PL/SQL here and stdout fits your needs better than the returnvalue of SQL*Plus (leading zero is not stripped...).
# 6  
Old 09-21-2012
Still no luck . Am not sure why the process is unable to display the value.
Please let me know what I am missing here.

Thanks
Megha
# 7  
Old 09-21-2012
What does the query return when you execute it directly in sqlplus? Are you sure that userdate in the returned row is not null?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Execute sql statment in korn shell

I am fairly new to writing scripts, and have gotten a lot of help from this site in the past with many of the posts. I have a question/issue with a script I am attempting to write and have a question regarding executing an sql statement inside of a loop (do while). I have in the past written... (1 Reply)
Discussion started by: josbor01
1 Replies

2. Shell Programming and Scripting

Need to pass Oracle SQL output to Linux and back... Help!

Hi all, Hopefully you can help. This is what I'm trying to achieve: Obtain a list of usernames out of an Oracle Database Based on this list, link each username with an Oracle Internet Directory (OID) GUID Using the username and GUID perform a database update for all users Here are the... (7 Replies)
Discussion started by: exm
7 Replies

3. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

4. Shell Programming and Scripting

Pass values to case statement in a function korn shell

I'm in the process of writng a function that consists of a case statement is there a way of calling the function and passing a value to it? ie function1 () { case opt1 do ..... opt2 do..... esac } function opt1 I'm aware the syntax is not correct, but you get the general idea. (1 Reply)
Discussion started by: squrcles
1 Replies

5. Shell Programming and Scripting

Pass perl array to SQL oracle

Hello, Could someone please suggest if there is a way to pass an perl array(pass @v_array) to SQL as in below eg : #!/usr/bin/perl @v_array = (1,2,4,5,6,8); $db_userid = 'ni71/ni711'; $bufTPO = qx{ sqlplus -s << EOF $db_userid set verify off set feedback off set... (1 Reply)
Discussion started by: arunshankar.c
1 Replies

6. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

7. Shell Programming and Scripting

Korn Shell and Oracle

Hi Guys, I haven't worked on oracle much but I have a situation where I have to do bdf in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell. SERVER_ID ... (9 Replies)
Discussion started by: pareshan
9 Replies

8. UNIX for Advanced & Expert Users

Accessing PL/SQL OUT variables in Korn Shell Script

Hello All, I was just wondering if there is any direct way to access PL/SQL OUT variables from Korn Shell Script. I could already figure out how to return a single value back from PL/SQL to Shell Script (using bind variable). But, what if we want to return multiple values? One option I... (4 Replies)
Discussion started by: bright_future
4 Replies

9. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies

10. UNIX for Advanced & Expert Users

Oracle To Korn Shell Array

I'm attempting to populate an array in ksh using the following command: set -A $(SELECT_INVOICE | sed '/^$/d') SELECT_INVOICE is a function that executes the SQL query. Problem: Some of the invoice numbers have alpha characters with spaces(example: OVEN MICRO). The Korn shell is treating... (1 Reply)
Discussion started by: kdst
1 Replies
Login or Register to Ask a Question

Featured Tech Videos