05-23-2008
Hello... First off I do not understand to what variable you are assigning the result in your script?
I can tell you that INSIDE sql (in a pl/sql procedure or sql script) you assign to a variable (assuming you will get only a single row from your select) with:
SELECT a.COLx into VARy from TABLEz a, where.... etc. If the variable comes from outside then it must be a "bind variable" and would be expressed as :VARy I believe... For multiple columns (still one row) you would do:
SELECT a.COLb, a.COLc, a.COLd INTO VARx, VARy, VARz from TABLE... etc.
But you have a big problem if more than ONE row is returned from your query because you get a TOO MANY ROWS error. If you will return more than one row you use BULK COLLECT as in:
SELECT a.COLb, a.COLc, BULK COLLECT INTO VARx, VARy from TABLE... This gives now an array (in PL/SQL terms, a list) for VARx[], VARy[], etc. and there are functions for looping through these bulk collected arrays.
I know this all works inside a PL/SQL procedure and also in a sql script, but I don't know about the way you are doing it as a "here document" in a shell script.
Hope this helps though...
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi Guys !
I am new to unix and want to find out how we can make sql statement data to shell script variable?
Any help/suggestion is greatly appreciated
-Chandra (1 Reply)
Discussion started by: kattics
1 Replies
2. Shell Programming and Scripting
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
3. Shell Programming and Scripting
How do I assign values to variables made in a script?
e.g.
for ((x=0;x<=5;i+=1)); do
Xm$i=$var
done (0 Replies)
Discussion started by: gelitini
0 Replies
4. Shell Programming and Scripting
Hi Friends...
Please assist me to assign the result of a SQL query that results two column, to two variables.
Pls find the below code that I write for assigning one column to one variable. and please correct if anything wrong..
#! /bin/sh
no='
sqlplus -s uname/password@DBname... (4 Replies)
Discussion started by: little_wonder
4 Replies
5. Shell Programming and Scripting
I tried searching the forum for similar posts but its closed now.
Would appreciate any help on this.
I am trying to capture return value from a select query into a variable.
DB is Oracle
I am able to spool it to a file but I donot intend to use it.
Here is my script that does not work ;)
I... (27 Replies)
Discussion started by: monie2717
27 Replies
6. Shell Programming and Scripting
hi
I have two tables in oracle DB and am using a joining query which will result in the output as follows.
i need to assign it to a two dimensional array and use it for my further calculations.
the way i tried is as follows.
#!/bin/ksh
export... (1 Reply)
Discussion started by: aemunathan
1 Replies
7. UNIX for Dummies Questions & Answers
I have a log file that has certain fields that I want to evaluate, and depending on the value in those fields, I want to put the value of a different field in that line in a particular variable that I'll use later on down the log file. Sort of like setting a switch to change what I do with a bunch... (5 Replies)
Discussion started by: pts2
5 Replies
8. Shell Programming and Scripting
For eg: I have sample.txt file with 4 rows of record like:
user1|password1
user2|password2
user3|password3
user4|password4
The username and password is sepsrated by '|'
I want to get the 1st row value from the file and assign it to two different variables(username and password)
in my... (1 Reply)
Discussion started by: priya001
1 Replies
9. Shell Programming and Scripting
I have a file containing multiple values, some of them are pipe separated which are to be read as separate values and some of them are single value all are these need to store in variables.
I need to read this file which is an input to my script
Config.txt
file name, first path, second... (7 Replies)
Discussion started by: ketanraut
7 Replies
10. Shell Programming and Scripting
The file1 contains mistakes and looks like
1 No one have never become rich by giving.
Anne Dickens
2 No one is worthless in this globe who lightens the weights of other.
Charles_Dickens
file2 contains the correction of words and looks like
rich poor
have has
never ever... (3 Replies)
Discussion started by: sammy777888
3 Replies
LEARN ABOUT REDHAT
select_into
SELECT
INTO(7) SQL Commands SELECT INTO(7)
NAME
SELECT INTO - create a new table from the results of a query
SYNOPSIS
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
INPUTS
TEMPORARY
TEMP If specified, the table is created as a temporary table. Refer to CREATE TABLE [create_table(7)] for details.
new_table
The name (optionally schema-qualified) of the table to be created.
All other inputs are described in detail for SELECT [select(7)].
OUTPUTS
Refer to CREATE TABLE [create_table(7)] and SELECT [select(7)] for a summary of possible output messages.
DESCRIPTION
SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal
SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.
Note: CREATE TABLE AS [create_table_as(7)] is functionally equivalent to SELECT INTO. CREATE TABLE AS is the recommended syntax,
since SELECT INTO is not standard. In fact, this form of SELECT INTO is not available in PL/pgSQL or ecpg(1), because they interpret
the INTO clause differently.
COMPATIBILITY
SQL92 uses SELECT ... INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This
indeed is the usage found in PL/pgSQL and ecpg(1). The PostgreSQL usage of SELECT INTO to represent table creation is historical. It's
best to use CREATE TABLE AS for this purpose in new code. (CREATE TABLE AS isn't standard either, but it's less likely to cause confu-
sion.)
SQL - Language Statements 2002-11-22 SELECT INTO(7)