Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using shell variable in sql script

orclrunnerJan 22 2015 — edited Jan 26 2015

OS: Oracle Linux 5.10

DB Oracle 11.2.0

I need to query the database and assign the return value to a shell variable, but it's not working.

create table imr_env (key varchar2(1000), value varchar2(1000) ;

insert into imr_env values('TblspcUsagePct','90');

commit;

Here is the shell script:

#!/bin/bash

echo "in script"

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ; export ORACLE_HOME

ORACLE_SID=IMR1 ; export ORACLE_SID

export PATH=$PATH:$ORACLE_HOME/bin

pct=`sqlplus -S app/manager <<END

       set serveroutput on

       declare

          output_val number ;

       BEGIN

          select value into output_val from imr_env where key = 'TblspcUsagePct' ;

          dbms_output.put_line('output_val: ' || to_char(output_val)) ;

       END ;

       /

       exit;

END`

## another sqlplus connection, use $pct in the where clause

echo "var value is $pct"

Here is the output:

SQL> ./test.sh

in script

var value is

output_val: 90

Why isn't the shell variable being populated with the value retrieved from the database?

I need to use $pct in another query that will tablespaces that are at least 90% full.

This post has been answered by Paul M. on Jan 25 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 22 2015
Added on Jan 22 2015
20 comments
11,812 views