Shell Script passing parameters to sqlplus code

Shell Programming and Scripting
1

Hello All,

I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible?

Thanks

2

it is - search these forums.

3

Quite probably, but more information is needed from you too. What have you tried, and what info you need, what the query would look like, etc?

4

From the HP Unix $ prompt, I want to run a shell script that prompted the operator to input multiple values. The sqlplus code runs fine when tested at the prompt thru SQLPLUS command.

So I am creating a table based on the inputed value

Create table X as
select doc_code, doc_amt, doc_trans_date
where doc_code = '&1';

I want the shell script to prompt for a value and then pass parmeters to sqlplus for processing.

I am looking at modifying the shell script so it can communicate with the sqlplus code.

I saw an example using this code in the shell script but I am not getting it to communicate.

 
sqlplus -s ${USER}/${PASS} <<EOF 1>>${LOG1} 
whenever sqlerror exit 1
        set serveroutput off;
        set echo off;
        set termout off;
        set linesize 600;
        set line 1000;
        set pagesize 5000;
        set newpage 0;
        set feedback off;
        set trimspool on;
        set trimout off;
        set define on;
        set verify off;
        set escape off;
        set colsep "|"
        set doc off;
        set define on;
        @jour360.sql
        quit;
EOF
5

In what way does it "not communicate"?

Showing the principles of what you need:

#!/bin/sh

printf "Enter the database name: "
read DATABASENAME

sqlplus -s ${USER}/${PASS} <<EOF
        USE ${DATABASENAME};
        SHOW TABLES;
        QUIT;
EOF