Sql with shell scripting

Shell Programming and Scripting
1

Hi,

I want to extract data from database table to a flat file using shell scripting. For loading data from file to a table, I had used SQL Loader. How can I do the vice versa using shell scripting.

Thanks in advance

2

The best way I do it is to use DBI module in my Perl Script. But since yu want to use a shell script, this following snippet might help you move forward.

OUTPUT=$\(sqlplus -silent $\{DBUSER\}/$\{DBPASS\}@$\{ORACLE_SID\} << DONE

set pages 0 feedback off
select * from table_name;
DONE)
echo $OUTPUT

The $OUTPUT variable now contains a single line with all the records returned by the query. Values of subsequent fields are separaeted by one space. Thus if you know the number of fields you can get one record. But you might need some processing to be done to the $OUTPUT variable before writing it into a flat file.

Rahul.

3

There are many ways to do this.. First format the cloumns you want to select to make them as characters. then select all the columns from table and create a spool file. this will solve your purpose.

A sample file is mentioned below.. Try to get help from this..

cat <<EOD > $TmpFileName
spool $SpoolFileName

set heading off
set termout off
set feedback off
set pagesize 0
set linesize 240
set colsep ' '

-- set the column format.

col CUST_ID format a10
col HDR_ID format a10
col ITEM_NO format a10
col SAP_MTL_NBR format a18
col MTL_DSCR format a80
col MTL_GRP format a10
col PLNG_QTY format a30
col UOM format a4
col SLS_ORG format a10
col PLANT format a10
col OMAD_DTE format a10
col CREATE_DTE format a10

--**********************************************************************
-- selects the Heading
--**********************************************************************
SELECT distinct
'CUST_ID' CUST_ID,
'HDR_ID' HDR_ID,
'ITEM_NO' ITEM_NO,
'SAP_MTL_NBR' SAP_MTL_NBR,
'MTL_DSCR' MTL_DSCR,
'MTL_GRP' MTL_GRP,
'PLNG_QTY' PLNG_QTY,
'UOM' UOM,
'SLS_ORG' SLS_ORG,
'PLANT' PLANT,
'OMAD_DTE' OMAD_DTE,
'CREATE_DTE' CREATE_DTE
FROM dual;
--**********************************************************************
-- selects the records
--**********************************************************************
SELECT H.Trade_Cust_ID CUST_ID,
H.Ord_Hdr_ID HDR_ID,
I.Ord_Ln_Item_ID ITEM_NO,
I.SAP_Mtl_nbr SAP_MTL_NBR,
M.Mtl_Dscr MTL_DSCR,
M.Mtl_Grp_ID MTL_GRP ,
TO_CHAR(NVL(I.Ord_Plng_Unt_Qty,0),'099999999999.999') PLNG_QTY,
'PU' UOM,
H.SAP_SLS_ORG_ID SLS_ORG,
I.Plant_ID PLANT,
TO_CHAR(I.Orig_OMAD_Dte,'MM/DD/YYYY') OMAD_DTE,
TO_CHAR(I.Create_Dte,'MM/DD/YYYY') CREATE_DTE
FROM
tab1 H,
Tab2 I,
tab3 M
WHERE
-----;

spool off;
/
quit
EOD

${ORACLE_HOME}/bin/sqlplus user_id password $TmpFileName 1>> \
$LogFileName 2>&1

Hope it will help you to come out of your problem.

4

My favorite; the coprocess:

#! /usr/bin/ksh
sqlplus -s /nolog |&

print -p "
connect un/pw
set pagesize 0 feedback off verify off
select * from sometable;
PROMPT SQL-COMPLETE
"

typeset IFS='
'
while read -p LINE
do
    case $LINE in
        whatever*) ... ;;
        SQL-COMPLETE) break ;;
    esac
done

If you're doing a bulk extract, do not loop through each record, use a .sql script that spools to a file. The loop will be a performance bottleneck.

The advantages are that you maintain one database session that persists over sever queries. Logons take time and PL/SQL packages maintain their session state, which is very useful.