Variables of executed script available in executing script

Hi,

I have a script get_DB_var.ksh which do a data base call and get some variables as below:

 
sqlplus -silent $user/$pass@dbname <<END
select col1,
         col2,
         col3
from table_name where col4=$1;
exit;
END

Now I want to access all these variables i.e. col1,col2,col3... in the script that executes get_DB_var.ksh as below:

 
cat set_DB_var.ksh
 
ksh get_DB_var.ksh 
echo $col1
echo $col2
echo $col3

is this possible? or do I have to write values of col1,col2,col3... in a file and then read it in set_DB_var.ksh?

 
get_DB_var.ksh 
---------------
sqlplus -silent $user/$pass@dbname <<END > var_file.txt
select col1,
         col2,
         col3
from table_name where col4=$1;
exit;
END
 
set_DB_var.ksh
---------------
while read var_file
do
echo $col1
echo $col2
echo $col3
done

What I want to know is that do we have some command to see the variables of the executed script?? the similar way we have "export" command to have variables available in the called script!

-dips

Do you have variable assignments in get_DB_var.ksh ?

from table_name where col4=$1;

this doesn't imply that col4 is a variable and contains some value.

you must have something like this to get the values as echo $col4

col4="some_value"

if so, you can source the get_DB_var.ksh within set_DB_var.ksh.

inside set_DB_var.ksh,

. get_DB_var.ksh
echo $col4

Not sure if I fully understood you, but if you can make sure in your SQL-Query, that there is no header and footer information in the output, you can execute it inside a for loop for example and work through the elements each like:

RESULT=`sqlplus -silent $user/$pass@dbname <<END
select col1,
         col2,
         col3
from table_name where col4=$1;
exit;
END`

for COL in $RESULT; do
     echo $COL #do something with them here
done

If you have spaces in the output per col, you might want to use a while read construct instead.

Hi anchal_khare,

By

from table_name where col4=$1;

I mean that script get_DB_var.ksh script takes an argument

./get_DB_var.ksh some_file_info_var

so the sql query

select col1,
         col2,
         col3
from table_name where col4=some_file_info_var;

will give all the information from the table_name about this file; which I have to access it in another scripts. And I am not allowed (by the design team) to source the script get_DB_var.ksh ! just allowed to execute it within the scripts which need those DB variables.

Hi zaxxon,
I some how want to have access to those variables in the script executing the get_DB_var.ksh script.
-dips

Sorry to say that I am still confused about what you want.

Another trial,

select a,
         b,
         c
from table_name where col4=$1;

from above text, which is inside get[...].ksh, and you are calling this from set[...].ksh:

Do you want something like that in the set[...].ksh ?

echo $col1 is a
echo $col2 is b
echo $col3 is c
echo $col4 is some_file_info_var # you are already having this.

if yes, you could do this with normal text processing and patter matching.
e.g grep,sed,awk,cut etc.

but please tell if this is your scenario so that we can help accordingly.

If you want to use those variables in a subsequent script, you have to either export them and start the subsequent script from your main script or, you have to write those variables to a file, so that the 2nd script can read them.

Hi,
now I am writing the sqlplus output to a file; as below:

sqlplus -s user/pass@DB <<END > result_file
set pagesize 0 linesize 10000 feedback off verify off heading off echo off
select
'EMP='||EMP||','||
'DEPT='||DEPT||','||
'JOB_NAME='||JOB_NAME
from employees where emp_id=$1;
exit;
END

The format of result_file will be as below:

EMP=1,DEPT=IT,JOB_NAME=SOFTWARE ENGINEER

but is there a way to get the format as below:

EMP=1
DEPT=IT
JOB_NAME=SOFTWARE ENGINEER

i.e. a new line char I tried something like this:

sqlplus -s user/pass@DB <<END > result_file
set pagesize 0 linesize 10000 feedback off verify off heading off echo off
select
'EMP='||EMP||\n||
'DEPT='||DEPT||\n||
'JOB_NAME='||JOB_NAME
from employees where emp_id=$1;
exit;
END

also <CR> instead of \n but to no avail.

One way would be to use tr to translate the commas in the results file into new lines i.e.

$ echo "EMP=1,DEPT=IT,JOB_NAME=SOFTWARE ENGINEER" | tr , \\n
EMP=1
DEPT=IT
JOB_NAME=SOFTWARE ENGINEER
$

Thanks fpmurphy. It works!
-dips

That's because Oracle doesn't consider either "\n" or "<CR>" as newlines. The newline character is returned by the builtin function "chr()" when it is invoked with 10 as the argument - 10 being the ASCII decimal position of the newline.

So your query should've been something like this -

SQL> 
SQL> 
SQL> select 'EMPID = '||employee_id||CHR(10)||'DEPTID = '||department_id||CHR(10)||'JOBID = '||job_id x from employees where employee_id = 194;

X
--------------------------------------------------------------------------------
EMPID = 194
DEPTID = 50
JOBID = SH_CLERK

1 row selected.

SQL> 
SQL> 

tyler_durden