Login or Register to Ask a Question and Join Our Community


Programming

pass value from Oracle sql to Korn shell

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming pass value from Oracle sql to Korn shell
# 8  
Old 09-21-2012
You are saying "select to_char(sysdate,'MMDD') x from dual;" returns blank?
# 9  
Old 09-21-2012
Hi Cero,
Thank you for the reply.
Yes. I am very sure on that part.When I run the query in sqlplus , I get the userdate in the format that I am using in the query. ie, MMDD . I do get a value .
I still cant find out whats causing the problem.

Thanks
Megha

---------- Post updated at 09:41 AM ---------- Previous update was at 09:40 AM ----------

Hi DGPickett

When I run my SQL query from korn shell , the output is blank .
Megha.
# 10  
Old 09-21-2012
Fix that: The Oracle CURRENT_DATE Function
# 11  
Old 09-21-2012
Hi DGPickett,

I am facing this issue when I try to display the value of the sql query from the korn shell. even if its userdate or sysdate .

this is my code
Code:
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE << END
set pagesize 0 feedback off verify off heading off echo off
SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1;
exit;
END`
echo "----------------"
echo $VALUE
print "above line has the value"

The output is as follows
Code:
---------------- 
Connected. 0920
 above line has the value

Instead the output must be only 0920.
Is there a way I can suppress 'Connected.'

Please help.

Thanks
Megha

---------- Post updated at 03:46 PM ---------- Previous update was at 01:09 PM ----------

So I tried this .
Code:
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE | sed "s/[^0-9]//g" << END set pagesize 0 feedback off verify off heading off echo off SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1; exit; END` echo "----------------" echo $VALUE print "above line has the value"

Can anybody tell me why is sed not able to get rid of that string connected and just display those numbers 0920 ?

Thanks
Megha
# 12  
Old 09-21-2012
Code:
VALUE=`sqlplus -s /nolog @$PASSWORD_FILE <<END
        set pagesize 0 feedback off verify off heading off echo off
        SELECT to_char(userdate,'MMDD') userdate FROM tab1 where rownum=1;
        exit;
END`

set -- $VALUE

echo "----------------"
echo $2
print "above line has the value"

# 13  
Old 09-25-2012
Code:
$
$
$ cat f70.sh
VALUE=`sqlplus -s /nolog <<END
connect test/test@ora11g
set pagesize 0 feedback off verify off heading off echo off time off timing off
SELECT to_char(sysdate,'MMDD') FROM dual;
exit;
END`
echo $VALUE
$
$
$ ./f70.sh
0925
$
$
$

You may replace the query in red by your own, provided it returns exactly 1 row.

Another note: you may not require "set time off timing off" sqlplus commands.
I had to set them off because my login.sql sets them on everytime I log in to sqlplus. Experiment with the sqlplus commands to determine the bare minimum for your case.

tyler_durden
Last edited by durden_tyler; 09-25-2012 at 11:08 AM..
# 14  
Old 09-26-2012
Usually MAX() or the like is a good way to ensure there is only one row out, but rownum=1 might be cheaper and OK if there is only one value. On one hand, MAX() folds all the rows together inside the engine, but rownum=1 allows a cursor to be created and filled, then discards each following row one by one at the presentation side. There are sample functions cheaper than max(), which evaluates every row, like FIRST_VALUE(), but it might insist on a sort: FIRST_VALUE but doc says OVER() can be empty: SQL Functions
or FIRST (also needs a sort): FIRST
Hint for first rows 1 helps: Comments

On a no-problem day, the sqlplus options pile is a fine way to return just the value, but on those other days, often including day 1 as you debug, for errors it is a bit of a train wreck. The sed and dAtA...eNdAtA approach works portably on all SQL tools, and saves the informative stuff to a log file for production support and debug.
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