쉘에서 ORACLE SQL 실행하여 결과 받아오기

SELECT  결과를 shell에서 참조해야 하는 경우 다음과 같이 하면 된다.

방법 1

#!/bin/ksh
VALUE=`sqlplus -silent "user/passwd@sid" < 
  set pagesize 0 feedback off verify off heading off echo off 
  select max(c1) from t1; 
  exit; 
END` 
 
if [ -z "$VALUE" ]; then 
  echo "No rows returned from database" 
  exit 0 
else 
  echo $VALUE 
fi
 

방법 2

#!/bin/ksh
sqlplus -s >null "usr/passwd@sid" < column num_rows new_value num_rows format 9999 
  select count(*) num_rows 
  from table_name; 
  exit num_rows 
EOF 
 
echo "Number of rows are: $?" 

http://ntalbs.blogspot.com  에서  퍼옴

실제 사용된 예제를 참고한다.  프로프레임을 알아야 로직을 이해할 수 있기 때문에  설명은 생략한다.

#*****************************************************************#
#                                                                 #
#  목 적  : IN/OUT 구조체 컴파일 및 dlupdate                        #
#                                                                 #
#  Usage  : make_io.sh svc_name                                   #
#                                                                 #
#-----------------------------------------------------------------#
#  2008.03.12 : Ko Yun Jeong                                      #
#*****************************************************************#
#!/usr/bin/ksh
dbinfo="ID1/PASSWD1@DBNAME"
dbcon=`echo $dbinfo|cut -f 2 -d "="` 
get_input_struture()
{
    input=`sqlplus -s $dbcon << EOF 
        set serveroutput on 
        set feedback off
        set heading off
        set feedback off
        set linesize 32767
    declare
        v_struct_name     varchar2(64);
    begin
        select physical_name into v_struct_name
            from dev_property
            where resource_id in 
                (select resource_id 
                    from dev_resource 
                    where physical_name = '$txcode')
                and seq = '0001'
                and field_type = 'STRUCT';
                
        dbms_output.put_line(v_struct_name);
    end;
    /
    exit
    EOF`
    echo "IN_MST : [$input]\n"
}
get_output_struture()
{
    output=`sqlplus -s $dbcon << EOF 
        set serveroutput on 
        set feedback off
        set heading off
        set feedback off
        set linesize 32767
    declare
        v_struct_name     varchar2(64);
    begin
        select physical_name into v_struct_name
            from dev_property
            where resource_id in 
                (select resource_id 
                    from dev_resource 
                    where physical_name = '$txcode')
                and seq = '0002'
                and field_type = 'STRUCT';
                
        dbms_output.put_line(v_struct_name);
    end;
    /
    exit
    EOF`
    echo "OUT : [$output]\n"
}
update_status()
{
    dbinfo="ID/PASSWD@DBID"
    dbcon=`echo $dbinfo|cut -f 2 -d "="`
    in_msg=$input"MsgFld"
    out_msg=$output"MsgFld"
    update=`sqlplus -s $dbcon << EOF 
        set serveroutput on 
        set feedback off
        set heading off
        set feedback off
        set linesize 32767
    update dev_resource set status ='C' where physical_name = '$input';
    update dev_resource set status ='C' where physical_name = '$in_msg';
    update dev_resource set status ='C' where physical_name = '$output';
    update dev_resource set status ='C' where physical_name = '$out_msg';
    exit
    EOF`
}
# ------------------------------------------------------------ #
# input argument check
# ------------------------------------------------------------ #
func_arg_chk()
{
    echo "\nusage : $0 [-c] svc_name"
    echo "\noptions : "
    echo "       -c : 입출력구조체의 컴파일, dlupdate 확인"
    exit 1
}
# ----------------------------------------------------------- #
# make in, out structure
# ----------------------------------------------------------- #
#if [ $# -lt 1 || $# -gt 2 ]; then
if [ $# -lt 1 ]; then
    echo "\nusage : $0 [-c] svc_name]"
    echo "\noptions : "
    echo "       -c : 입출력구조체의 컴파일, dlupdate 확인"
    exit 1
fi
case $1 in
 -c)
        if [ $# -ne  2 ]
        then
            func_arg_chk
        fi
        txcode=$2
        get_input_struture
        get_output_struture
        echo "=> 생성된 소스 확인\n"
        ls -lrt $PMAPINCDIR/pfmMapper$input*.h
        ls -lrt $PMAPINCDIR/pfmMapper$output*.h
        ls -lrt $PMAPSRCDIR/pfmMapper$input*.c
        ls -lrt $PMAPSRCDIR/pfmMapper$output*.c
        echo "\n=> 컴파일한 라이브러리 확인\n"
        ls -lrt $PMAPLIBDIR/libpfmMapper$input.sl
        ls -lrt $PMAPLIBDIR/libpfmMapper$output.sl
        echo "\n=> dlupdate한 라이브러리 확인\n"
        ls -lrt $TDLDIR/mod/pfmMapper$input.sl
        ls -lrt $TDLDIR/mod/pfmMapper$output.sl
     ;;
 *)
        # -------------------------------#
        # IN_MST
        # -------------------------------#
        txcode=$1
        get_input_struture
        cp $PMAPINCDIR/pfmMapper$input.h /tmp
        cp $PMAPSRCDIR/pfmMapper$input.c /tmp
        cd /tmp
        make_pmap.sh $input
        #pfmdlupdate libpfmMapper$input.sl
        rm -f /tmp/pfmMapper$input.h
        rm -f /tmp/pfmMapper$input.c
        rm -f /tmp/pfmMapper$input.o
        rm -f /tmp/libpfmMapper$input.sl
        # -------------------------------#
        # OUT
        # -------------------------------#
        get_output_struture
        cp $PMAPINCDIR/pfmMapper$output.h /tmp
        cp $PMAPSRCDIR/pfmMapper$output.c /tmp
        make_pmap.sh $output
        #pfmdlupdate libpfmMapper$output.sl
        rm -f /tmp/pfmMapper$output.h
        rm -f /tmp/pfmMapper$output.c
        rm -f /tmp/pfmMapper$output.o
        rm -f /tmp/libpfmMapper$output.sl
        cd -
        update_status
        ;;
esac