Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to get Oracle variable in UNIX?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to get Oracle variable in UNIX?
# 8  
Old 02-27-2013
Try this
Code:
$ cat x
#!/bin/ksh

##  The command in $() returns COUNT=<nbr> which is then eval'd to create
##  and set a shell variable called COUNT. There is a carriage return after the
##  set heading off; command.
eval $(print "set heading off;
select 'COUNT='||count(*)||';SYSDATE='||sysdate from dual;" | sqlplus -s / )

print "The shell variable COUNT is   [$COUNT]"
print "The shell variable SYSDATE is [$SYSDATE]"

exit 0
$ ./x
The shell variable COUNT is   [1]
The shell variable SYSDATE is [27-FEB-2013]
$

See this for more info:
https://www.unix.com/shell-programmin...variables.html
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing variable from file to Oracle

cat a1 scott robert tom test script : #!/usr/bin/ksh for NAME in `cat a1` do VALUE=`sqlplus -silent "nobody/bobody01@testq" <<END set pagesize 0 feedback off verify off heading off echo off select username from dba_users where username=upper('$NAME'); END` if ; then echo... (3 Replies)
Discussion started by: jhonnyrip
3 Replies

2. UNIX and Linux Applications

Identify a specific environment Oracle variable to connect a remote Oracle database ?

Good evening I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus. I tried to find out the user/passwd and service name by env variable and all Ive got is this: ORACLE_SID_REPCOL=SCL_REPCOL ORACLE_SID=xmeta ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
Discussion started by: alexcol
2 Replies

3. Shell Programming and Scripting

How to get a numeric value from Oracle to UNIX variable without spaces?

Hi, I am using the below code to get a numeric value from oracle to unix variable: BD_RC_CNT=`sqlplus -s ${WMD_DM_CONNECT} <<EOF set heading off set pagesize 0 Select count(*) from wmd_bad_data where proc_id = ${PROC_ID} and file_id = ${FILE_ID} and file_dt =... (7 Replies)
Discussion started by: Arun Mishra
7 Replies

4. Programming

Oracle Variable Passing Test

Hi, I am trying to get the oracle variables and pass the values in sql placed in procedure. VARIABLE vstat='ASDS,FGDS,VCGD,VCXC' Query : select distinct dept from College where section in ('C','D') AND CODES ='' AND NAMES IN ('RAJ','SAM'); I want CODES values to be taken from vstat... (1 Reply)
Discussion started by: Perlbaby
1 Replies

5. Shell Programming and Scripting

set oracle variable in function

Hi, I have a function that is suposed to generate a AWR report: #-----------------------# gen_awr() #-----------------------# { sqlplus -s admin/admin@OCEAN11<<ENDOFSQL define num_days = '' define report_type = "html" define begin_snap =$snap1 define end_snap =%snap2 define... (1 Reply)
Discussion started by: amitlib
1 Replies

6. Shell Programming and Scripting

Passing unix variable to oracle parameters

Please help me how to pass some unix vairable to oracle. I have used below , but not displaying passed (inval) value. calling() { sqlplus -s $1/$2@$3 <<EOF begin exec call_sql($4); end; exit EOF } calling user pwd inst value1... (17 Replies)
Discussion started by: Jairaj
17 Replies

7. Shell Programming and Scripting

passing variable to oracle procedure

using the script below I want to pass a parameters thorugh my sql call(@/unixsxxx/xxxx/helpenv.sql emptab ) as input into an oracle procedure xxxx_package.proc1(%1,emptab); . I tried %1 but it does not work. Any suggestions. #!bin/ksh set -x # export... (0 Replies)
Discussion started by: TimHortons
0 Replies

8. Shell Programming and Scripting

To get value from oracle & assign it in UNIX variable

Hi Team, I need to get data from oracle table & need to assign that value to unix variable. I have serched the same in other threads. I found the following code. I have tried code to get the value from oracle. but it is not working. The error shows invalid identifier "NAM" & then list all... (5 Replies)
Discussion started by: Amit.Sagpariya
5 Replies

9. UNIX for Dummies Questions & Answers

How to pass unix variable to oracle

hi , how to pass unix variable to oracle code is ............. #! /bin/ksh echo enter date vale read date1 sqlplus x/y@oracle select * from emp where statrt_date= $date1 is this is correct way... (1 Reply)
Discussion started by: chiru
1 Replies

10. UNIX for Dummies Questions & Answers

Get Oracle fuction return value in a variable

Hi All, :confused: I have the following code. var=' ' sqlplus user/pass@DB <<EOF whenever sqlerror exit 1 select package.func() into $var from dual; EOF echo $var But, this code does not work to display the value returned by the oracle function. Do we have to bind variables before... (3 Replies)
Discussion started by: rahulrathod
3 Replies
Login or Register to Ask a Question

LEARN ABOUT DEBIAN

convert::nls_date_format

Convert::NLS_DATE_FORMAT(3pm)				User Contributed Perl Documentation			     Convert::NLS_DATE_FORMAT(3pm)

NAME

       Convert::NLS_DATE_FORMAT - Convert Oracle NLS_DATE_FORMAT <-> strftime Format Strings

SYNOPSIS

	 use Convert::NLS_DATE_FORMAT qw(oracle2posix posix2oracle);
	 my $strptime = oracle2posix($NLS_DATE_FORMAT);
	 $NLS_DATE_FORMAT = posix2oracle($strftime);

DESCRIPTION

       Convert Oracle's NLS_DATE_FORMAT string into a strptime format string, or the reverse.

   Functions
       oracle2posix
	   Takes an Oracle NLS_DATE_FORMAT string and converts it into formatting string compatible with "strftime" or "strptime".

	     my $format = oracle2posix('YYYY-MM-DD HH24:MI:SS'); # '%Y-%m-%d %H:%M:%S'

	   Character sequences that should not be translated may be enclosed within double quotes, as specified in the Oracle documentation.

	     my $format = oracle2posix('YYYY-MM-DD"T"HH24:MI:SS'); # '%Y-%m-%dT%H:%M:%S'

       posix2oracle
	   Takes a "strftime" or "strptime" formatting string and converts it into an Oracle NLS_DATE_FORMAT string. It is possible to create
	   strings which Oracle will not accept as valid NLS_DATE_FORMAT strings.

	     my $format = posix2oracle('%Y-%m-%d %H:%M:%S'); # 'YYYY-MM-DD HH24:MI:SS'

   EXPORT
       None by default. "oracle2posix" and "posix2oracle" when asked.

SEE ALSO

       DateTime::Format::Oracle.

AUTHOR

       Nathan Gray, <kolibrie@cpan.org>

COPYRIGHT AND LICENSE

       Copyright (C) 2005, 2006, 2011, 2012 Nathan Gray

       This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.4 or,
       at your option, any later version of Perl 5 you may have available.

perl v5.14.2							    2012-01-18					     Convert::NLS_DATE_FORMAT(3pm)