Sponsored Content
Top Forums Shell Programming and Scripting Passing PL/SQL variable value to Shell Varible Post 302105034 by radoulov on Tuesday 30th of January 2007 10:02:18 AM
Old 01-30-2007
Do it all in the database, if possible.
If you _really_ have to mix sql/plsql and shell programming:

- as array in bash:

Code:
$ var1="($(printf "%s \n" "set pages 0 feed off" \
> "select ename from emp;"|sqlplus -s scott/tiger))"; \
> printf "The first element in \$var1 array is: %s, \
> the second: %s and so on ...\n" \
> "${var1[1]}" "${var1[2]}"
The first element in $var1 array is: ALLEN, the second: WARD and so on ...

- as a single variable:

Code:
$ var1="$(printf "%s \n" "set pages 0 feed off" \
> "select ename from emp;"|sqlplus -s scott/tiger)"; \
> echo "Quoted: $var1"; \
> echo "Unquoted:" $var1
Quoted: SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Unquoted: SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

I'm sure that it will be better
if you use only one programming language.
 

10 More Discussions You Might Find Interesting

1. Linux

Passing variables to sql from batch shell in linux

Hi, I need to put this command in a batch shell. sqlplus -s user/password @test.sql and in the test.sql I have this command select * from pbempl where pebempl_id = $1; How I can pass the variable $1 from the batch shell??? Thanks (2 Replies)
Discussion started by: rama71
2 Replies

2. UNIX for Advanced & Expert Users

passing value to a variable in a SQL

Hi Folks, This is a small chunk of the bigger problem which i am facing and some help here will help me resolve the rest of the issue. Problem is that i need to pass the value of a variable from a shell script to a SQL query (infact a lot of SQL's) i have the following solution but somehow... (4 Replies)
Discussion started by: kamitsin
4 Replies

3. Shell Programming and Scripting

Passing argumnets from shell script to sql

hi I all , I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment example : runsql.sh "1" "2" sql : updat tables_x set y=0 where A=:x should subsituted by "1" and B=:y shuold subsituted bt "2"... (1 Reply)
Discussion started by: habuzahra
1 Replies

4. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

5. UNIX for Advanced & Expert Users

Passing Hash variable in to sql query in perl

Hi Everyone, Can anyone help me how do i call hash variable in to sql query in perl. Please see the script below i have defined two Hash %lc and %tab as below $lc{'REFF'}='V_RES_CLASS'; $lc{'CALE'}='V_CAP_CLASS'; $lc{'XRPD'}='V_XFMR_CLASS'; $tab{'V_RES_CLASS'}='V_MFR_SERS';... (6 Replies)
Discussion started by: jam_prasanna
6 Replies

6. Shell Programming and Scripting

Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Hi all, I am executing shell script in which I am using SQLLDR In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following Spool... (2 Replies)
Discussion started by: shekharjchandra
2 Replies

7. Shell Programming and Scripting

Passing a string variable from Unix to Sql Plus

Hi Guys, I am trying to pass a string variable from Unix shell script to sqlplus as a parameter. I have tried using single quotes with the variable name but it does not work. Please help me with it. I am using BASH. My code: Your help is much appreciated. Thanks, shil (2 Replies)
Discussion started by: infintenumbers
2 Replies

8. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

9. Shell Programming and Scripting

Passing variable to sql

How to pass variable to sql file. Im tryin in two ways, Method 1: my.sql select * from table where col1 = '$1' and col2 = 'text'; Method 1execute: dbaccess database my.sql $var Method2: select * from table col1 in (`cat inputfile`) and col2 = 'text'; method... (2 Replies)
Discussion started by: Roozo
2 Replies

10. Shell Programming and Scripting

Passing variable from file to sql from script

Hi Friend, I have one file in which some number are mentioned and number of lines are vary every time And i need to pass that number to my sql command from script. Suppose i have file acc.txt 45456546456 45464564565 67854353454 67657612132 Number of records are vary every time.... (20 Replies)
Discussion started by: pallvi_mahajan
20 Replies
binary(n)						       Tcl Built-In Commands							 binary(n)

__________________________________________________________________________________________________________________________________________________

NAME
binary - Insert and extract fields from binary strings SYNOPSIS
binary format formatString ?arg arg ...? binary scan string formatString ?varName varName ...? _________________________________________________________________ DESCRIPTION
This command provides facilities for manipulating binary data. The first form, binary format, creates a binary string from normal Tcl val- ues. For example, given the values 16 and 22, on a 32 bit architecture, it might produce an 8-byte binary string consisting of two 4-byte integers, one for each of the numbers. The second form of the command, binary scan, does the opposite: it extracts data from a binary string and returns it as ordinary Tcl string values. BINARY FORMAT
The binary format command generates a binary string whose layout is specified by the formatString and whose contents come from the addi- tional arguments. The resulting binary value is returned. The formatString consists of a sequence of zero or more field specifiers separated by zero or more spaces. Each field specifier is a sin- gle type character followed by an optional numeric count. Most field specifiers consume one argument to obtain the value to be formatted. The type character specifies how the value is to be formatted. The count typically indicates how many items of the specified type are taken from the value. If present, the count is a non-negative decimal integer or *, which normally indicates that all of the items in the value are to be used. If the number of arguments does not match the number of fields in the format string that consume arguments, then an error is generated. Each type-count pair moves an imaginary cursor through the binary data, storing bytes at the current position and advancing the cursor to just after the last byte stored. The cursor is initially at position 0 at the beginning of the data. The type may be any one of the fol- lowing characters: a Stores a character string of length count in the output string. If arg has fewer than count bytes, then additional zero bytes are used to pad out the field. If arg is longer than the specified length, the extra characters will be ignored. If count is *, then all of the bytes in arg will be formatted. If count is omitted, then one character will be formatted. For example, binary format a7a*a alpha bravo charlie will return a string equivalent to alpha0000bravoc. A This form is the same as a except that spaces are used for padding instead of nulls. For example, binary format A6A*A alpha bravo charlie will return alpha bravoc. b Stores a string of count binary digits in low-to-high order within each byte in the output string. Arg must contain a sequence of 1 and 0 characters. The resulting bytes are emitted in first to last order with the bits being formatted in low-to-high order within each byte. If arg has fewer than count digits, then zeros will be used for the remaining bits. If arg has more than the specified number of digits, the extra digits will be ignored. If count is *, then all of the digits in arg will be formatted. If count is omitted, then one digit will be formatted. If the number of bits formatted does not end at a byte boundary, the remaining bits of the last byte will be zeros. For example, binary format b5b* 11100 111000011010 will return a string equivalent to x07x87x05. B This form is the same as b except that the bits are stored in high-to-low order within each byte. For example, binary format B5B* 11100 111000011010 will return a string equivalent to xe0xe1xa0. h Stores a string of count hexadecimal digits in low-to-high within each byte in the output string. Arg must contain a sequence of characters in the set ``0123456789abcdefABCDEF''. The resulting bytes are emitted in first to last order with the hex digits being formatted in low-to-high order within each byte. If arg has fewer than count digits, then zeros will be used for the remaining dig- its. If arg has more than the specified number of digits, the extra digits will be ignored. If count is *, then all of the digits in arg will be formatted. If count is omitted, then one digit will be formatted. If the number of digits formatted does not end at a byte boundary, the remaining bits of the last byte will be zeros. For example, binary format h3h* AB def will return a string equivalent to xbax00xedx0f. H This form is the same as h except that the digits are stored in high-to-low order within each byte. For example, binary format H3H* ab DEF will return a string equivalent to xabx00xdexf0. c Stores one or more 8-bit integer values in the output string. If no count is specified, then arg must consist of an integer value; otherwise arg must consist of a list containing at least count integer elements. The low-order 8 bits of each integer are stored as a one-byte value at the cursor position. If count is *, then all of the integers in the list are formatted. If the number of elements in the list is fewer than count, then an error is generated. If the number of elements in the list is greater than count, then the extra elements are ignored. For example, binary format c3cc* {3 -3 128 1} 260 {2 5} will return a string equivalent to x03xfdx80x04x02x05, whereas binary format c {2 5} will generate an error. s This form is the same as c except that it stores one or more 16-bit integers in little-endian byte order in the output string. The low-order 16-bits of each integer are stored as a two-byte value at the cursor position with the least significant byte stored first. For example, binary format s3 {3 -3 258 1} will return a string equivalent to x03x00xfdxffx02x01. S This form is the same as s except that it stores one or more 16-bit integers in big-endian byte order in the output string. For exam- ple, binary format S3 {3 -3 258 1} will return a string equivalent to x00x03xffxfdx01x02. i This form is the same as c except that it stores one or more 32-bit integers in little-endian byte order in the output string. The low-order 32-bits of each integer are stored as a four-byte value at the cursor position with the least significant byte stored first. For example, binary format i3 {3 -3 65536 1} will return a string equivalent to x03x00x00x00xfdxffxffxffx00x00x01x00 I This form is the same as i except that it stores one or more one or more 32-bit integers in big-endian byte order in the output string. For example, binary format I3 {3 -3 65536 1} will return a string equivalent to x00x00x00x03xffxffxffxfdx00x01x00x00 f This form is the same as c except that it stores one or more one or more single-precision floating in the machine's native representa- tion in the output string. This representation is not portable across architectures, so it should not be used to communicate floating point numbers across the network. The size of a floating point number may vary across architectures, so the number of bytes that are generated may vary. If the value overflows the machine's native representation, then the value of FLT_MAX as defined by the system will be used instead. Because Tcl uses double-precision floating-point numbers internally, there may be some loss of precision in the conversion to single-precision. For example, on a Windows system running on an Intel Pentium processor, binary format f2 {1.6 3.4} will return a string equivalent to xcdxccxccx3fx9ax99x59x40. d This form is the same as f except that it stores one or more one or more double-precision floating in the machine's native representa- tion in the output string. For example, on a Windows system running on an Intel Pentium processor, binary format d1 {1.6} will return a string equivalent to x9ax99x99x99x99x99xf9x3f. x Stores count null bytes in the output string. If count is not specified, stores one null byte. If count is *, generates an error. This type does not consume an argument. For example, binary format a3xa3x2a3 abc def ghi will return a string equivalent to abc00def0000ghi. X Moves the cursor back count bytes in the output string. If count is * or is larger than the current cursor position, then the cursor is positioned at location 0 so that the next byte stored will be the first byte in the result string. If count is omitted then the cursor is moved back one byte. This type does not consume an argument. For example, binary format a3X*a3X2a3 abc def ghi will return dghi. @ Moves the cursor to the absolute location in the output string specified by count. Position 0 refers to the first byte in the output string. If count refers to a position beyond the last byte stored so far, then null bytes will be placed in the unitialized locations and the cursor will be placed at the specified location. If count is *, then the cursor is moved to the current end of the output string. If count is omitted, then an error will be generated. This type does not consume an argument. For example, binary format a5@2a1@*a3@10a1 abcde f ghi j will return abfdeghi0000j. BINARY SCAN
The binary scan command parses fields from a binary string, returning the number of conversions performed. String gives the input to be parsed and formatString indicates how to parse it. Each varName gives the name of a variable; when a field is scanned from string the result is assigned to the corresponding variable. As with binary format, the formatString consists of a sequence of zero or more field specifiers separated by zero or more spaces. Each field specifier is a single type character followed by an optional numeric count. Most field specifiers consume one argument to obtain the variable into which the scanned values should be placed. The type character specifies how the binary data is to be interpreted. The count typically indicates how many items of the specified type are taken from the data. If present, the count is a non-negative decimal integer or *, which normally indicates that all of the remaining items in the data are to be used. If there are not enough bytes left after the current cursor position to satisfy the current field specifier, then the corresponding variable is left untouched and binary scan returns immediately with the number of variables that were set. If there are not enough arguments for all of the fields in the format string that consume arguments, then an error is generated. It is important to note that the c, s, and S (and i and I on 64bit systems) will be scanned into long data size values. In doing this, values that have their high bit set (0x80 for chars, 0x8000 for shorts, 0x80000000 for ints), will be sign extended. Thus the following will occur: set signShort [binary format s1 0x8000] binary scan $signShort s1 val; # val == 0xFFFF8000 If you want to produce an unsigned value, then you can mask the return value to the desired size. For example, to produce an unsigned short value: set val [expr {$val & 0xFFFF}]; # val == 0x8000 Each type-count pair moves an imaginary cursor through the binary data, reading bytes from the current position. The cursor is initially at position 0 at the beginning of the data. The type may be any one of the following characters: a The data is a character string of length count. If count is *, then all of the remaining bytes in string will be scanned into the variable. If count is omitted, then one character will be scanned. For example, binary scan abcde00fghi a6a10 var1 var2 will return 1 with the string equivalent to abcde00 stored in var1 and var2 left unmodified. A This form is the same as a, except trailing blanks and nulls are stripped from the scanned value before it is stored in the variable. For example, binary scan "abc efghi 00" A* var1 will return 1 with abc efghi stored in var1. b The data is turned into a string of count binary digits in low-to-high order represented as a sequence of ``1'' and ``0'' characters. The data bytes are scanned in first to last order with the bits being taken in low-to-high order within each byte. Any extra bits in the last byte are ignored. If count is *, then all of the remaining bits in string will be scanned. If count is omitted, then one bit will be scanned. For example, binary scan x07x87x05 b5b* var1 var2 will return 2 with 11100 stored in var1 and 1110000110100000 stored in var2. B This form is the same as b, except the bits are taken in high-to-low order within each byte. For example, binary scan x70x87x05 B5B* var1 var2 will return 2 with 01110 stored in var1 and 1000011100000101 stored in var2. h The data is turned into a string of count hexadecimal digits in low-to-high order represented as a sequence of characters in the set ``0123456789abcdef''. The data bytes are scanned in first to last order with the hex digits being taken in low-to-high order within each byte. Any extra bits in the last byte are ignored. If count is *, then all of the remaining hex digits in string will be scanned. If count is omitted, then one hex digit will be scanned. For example, binary scan x07x86x05 h3h* var1 var2 will return 2 with 706 stored in var1 and 50 stored in var2. H This form is the same as h, except the digits are taken in high-to-low order within each byte. For example, binary scan x07x86x05 H3H* var1 var2 will return 2 with 078 stored in var1 and 05 stored in var2. c The data is turned into count 8-bit signed integers and stored in the corresponding variable as a list. If count is *, then all of the remaining bytes in string will be scanned. If count is omitted, then one 8-bit integer will be scanned. For example, binary scan x07x86x05 c2c* var1 var2 will return 2 with 7 -122 stored in var1 and 5 stored in var2. Note that the integers returned are signed, but they can be converted to unsigned 8-bit quantities using an expression like: expr ( $num + 0x100 ) % 0x100 s The data is interpreted as count 16-bit signed integers represented in little-endian byte order. The integers are stored in the cor- responding variable as a list. If count is *, then all of the remaining bytes in string will be scanned. If count is omitted, then one 16-bit integer will be scanned. For example, binary scan x05x00x07x00xf0xff s2s* var1 var2 will return 2 with 5 7 stored in var1 and -16 stored in var2. Note that the integers returned are signed, but they can be converted to unsigned 16-bit quantities using an expression like: expr ( $num + 0x10000 ) % 0x10000 S This form is the same as s except that the data is interpreted as count 16-bit signed integers represented in big-endian byte order. For example, binary scan x00x05x00x07xffxf0 S2S* var1 var2 will return 2 with 5 7 stored in var1 and -16 stored in var2. i The data is interpreted as count 32-bit signed integers represented in little-endian byte order. The integers are stored in the cor- responding variable as a list. If count is *, then all of the remaining bytes in string will be scanned. If count is omitted, then one 32-bit integer will be scanned. For example, binary scan x05x00x00x00x07x00x00x00xf0xffxffxff i2i* var1 var2 will return 2 with 5 7 stored in var1 and -16 stored in var2. Note that the integers returned are signed and cannot be represented by Tcl as unsigned values. I This form is the same as I except that the data is interpreted as count 32-bit signed integers represented in big-endian byte order. For example, binary x00x00x00x05x00x00x00x07xffxffxffxf0 I2I* var1 var2 will return 2 with 5 7 stored in var1 and -16 stored in var2. f The data is interpreted as count single-precision floating point numbers in the machine's native representation. The floating point numbers are stored in the corresponding variable as a list. If count is *, then all of the remaining bytes in string will be scanned. If count is omitted, then one single-precision floating point number will be scanned. The size of a floating point number may vary across architectures, so the number of bytes that are scanned may vary. If the data does not represent a valid floating point number, the resulting value is undefined and compiler dependent. For example, on a Windows system running on an Intel Pentium processor, binary scan x3fxccxccxcd f var1 will return 1 with 1.6000000238418579 stored in var1. d This form is the same as f except that the data is interpreted as count double-precision floating point numbers in the machine's native representation. For example, on a Windows system running on an Intel Pentium processor, binary scan x9ax99x99x99x99x99xf9x3f d var1 will return 1 with 1.6000000000000001 stored in var1. x Moves the cursor forward count bytes in string. If count is * or is larger than the number of bytes after the current cursor cursor position, then the cursor is positioned after the last byte in string. If count is omitted, then the cursor is moved forward one byte. Note that this type does not consume an argument. For example, binary scan x01x02x03x04 x2H* var1 will return 1 with 0304 stored in var1. X Moves the cursor back count bytes in string. If count is * or is larger than the current cursor position, then the cursor is posi- tioned at location 0 so that the next byte scanned will be the first byte in string. If count is omitted then the cursor is moved back one byte. Note that this type does not consume an argument. For example, binary scan x01x02x03x04 c2XH* var1 var2 will return 2 with 1 2 stored in var1 and 020304 stored in var2. @ Moves the cursor to the absolute location in the data string specified by count. Note that position 0 refers to the first byte in string. If count refers to a position beyond the end of string, then the cursor is positioned after the last byte. If count is omit- ted, then an error will be generated. For example, binary scan x01x02x03x04 c2@1H* var1 var2 will return 2 with 1 2 stored in var1 and 020304 stored in var2. PLATFORM ISSUES
Sometimes it is desirable to format or scan integer values in the native byte order for the machine. Refer to the byteOrder element of the tcl_platform array to decide which type character to use when formatting or scanning integers. SEE ALSO
format(n), scan(n), tclvars(n) KEYWORDS
binary, format, scan Tcl 8.0 binary(n)
All times are GMT -4. The time now is 10:33 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy