How to store the data retrived by a select query into variables?

Shell Programming and Scripting
1

Hi Friends,

Can u please help mw with the following query .

I need to run a database sql statement to select particular fields from a table.I need to store the retrieved filed values into variables so that i can print it in a specific format. But the particular select query retrieves more than 1 row .I have redirected the result of the query to a temporary text file.Lets say there are 10 rows retrieved. These retrieved values(filed_name value) are written into say 1.txt.
Now 1.txt looks some what like this :

fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value fld_name value

I thought of using a grep command to get the values and store it in a variable.But the format of this text file makes it impossible.

Can please help me to put the data as below in 1.txt

fld_name value
fld_name value
fld_name value
fld_name value
fld_name value
fld_name value

or
When we run a select statement the rows are retrieved in the below format :
fld_name value
fld_name value
fld_name value

fld_name value
fld_name value
fld_name value

fld_name value
fld_name value
fld_name value

3 rows retrieved

Is there any way to store exactly this way to 1.txt?

Thanks in advance
Jisha

2

You can write the following statement on the SQL prompt, before the Select query

3

Thank you gauravgoel. .
My script is like this :

`isqlrf <database_name> - <<EOFSQL
select ....
from .....
where .....;
EOFSQl`

How i put the output of the above query was as below:

values=`isqlrf <database_name> - <<EOFSQL
select ....
from .....
where .....;
EOFSQl`

echo $values > 1.txt

I dont know anything about spools.Do u think my way of approach is correct?

Many Thanks
Jisha

4

as I mentioned before your select put

spool 1.txt

and after select put

spool off;

and after this you neednot redirect the output to 1.txt

5

I did like this :
`isqlrf <database_name> - spool 1.txt;<<EOFSQL
select ....
from .....
where .....;
spool off;
EOFSQl`

I executed & nothing happened .The file was also not created.

Thanks
Jisha

6

try like this

`isqlrf <database_name>;<<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`

you may need to tweak it around a bit,
Sorry cant check right now for you as dont have access to UNIX system right now

7

Thanks again.
I tried it like this:
`isqlrf <database_name> <<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`

It gave me a "syntax error has occured"

Then i tried it :
`isqlrf <database_name>;<<EOFSQL
spool 1.txt
select ....
from .....
where .....;
spool off;
EOFSQl`

This didnt give me any answer at all ..The command prompt was also not there after execution.I had to pred ctrl+c to go to the command prompt..Do u know y this happens?

Thanks,
jisha

8

EOFSQL != EOFSQl

9

Hi,

My requirement is :
We are calling an sql statement from a UNIX session, and fetching data into some variables from a table .. now we are unable to access these variables from outside the SQL part. Please let me know how can I achieve this.

Can you please share a code snippet which elucidates this.

Thanks & Regards,
Venkatesh.

10

venkatesh_sasi,
pls don't 'piggy-back' on top of the existing threads - create your own thread.
Also search the forums for existing solutions FIRST!

11

Hi,

 Sorry for this. I was actually trying to post it in a new thread, but coz of some browser problem I wasn't able to do that. That's why I just replied to this thread.

Regards,
Venkatesh.

12

Here is how I have done it with and ORACLE DB (code severly mangled to protect the innocent and error checking/logging removed.)

 {
    sqlplus -s /nolog << EOFSQL
        set feedback off
        set head off
        set verify off
        set linesize 5000
        @${DIR_COMMON}/ADMIN.conn
        
        select 
            'array_SQLValues1[' ||to_number(rownum-1)|| ']="' ||FieldValule1|| '";'
          ||'array_SQLValues2[' ||to_number(rownum-1)|| ']="' ||FieldValue2|| '";'
        from
            THIS_TABLE a,
EOFSQL
    } | while read line
    do
        if $DIR_CONTROL_SUBROUTINES/SUB_chk_sql.ksh ${line:-BLANK} "Retrieve_File_List"
        then {
            if printf "${line:-BLANK}" | grep = > /dev/null
            then {
                ######################################
                # use "eval" to initialize the arrays
                # take a count of the records found
                #######################################
                eval ${line} && fileCounter=$((${fileCounter}+1)) 
            }
            fi
        }
        fi
    done
}
13

Hi vgersh99,

Thanks for pointing it out. But in my script there was no such mistake.I think it happened when i copied it to here ..

Am still not able get a solution for this. I have played around with it .But still no improvement ..

Thanks
Jisha