Sql with shell scripting Post: 99167

Sponsored Content

Top Forums Shell Programming and Scripting Sql with shell scripting Post 99167 by Manish Jha on Wednesday 15th of February 2006 09:04:24 AM

02-15-2006

Registered User

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.

Manish Jha

View Public Profile for Manish Jha

Find all posts by Manish Jha

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell scripting and SQL

Hi, I have a file that has contents like this: SQL> select * from details; NAME REG -------------------- ---------- Mani 1 Santosh 2 Manju 3 Mukesh 4 SQL> spool off; ...

2. Shell Programming and Scripting

how towrite sql in shell scripting

hi iam new of scipiting.give me the some Material of regarding sql in scripting. and i have no backup's in 3 severs.how to write script in dirctory. please give me some usefull information cheers Naveen.g

3. Shell Programming and Scripting

Sql & Shell scripting book

Hi, I am looking for some books that have examples of both Oracle SQL and Shell script being used together. For example, to insert 500 rows into a table using a script. I already have books that cover Shell scripting or SQL, but as separate subjects. I am looking for books that have both of...

4. Shell Programming and Scripting

SQL commands in a shell scripting

Hi, I need to write a shell script file that does 1)Connects to DB using SQL plus 2)Do some SQL query like select * from.... 3)Based on the output that I got from the sql query I will have to write a if else loop. Plz let me know how to write the shell scripting for this. Thanks for...

5. Shell Programming and Scripting

Assigning value of SQL output to a variable in shell scripting

I am trying to assgn the output of the select statement to a variable, like this "VARIABLE_NAME=$ db2 "select COLUMN_NAME_1 from TABLE_NAME where COLUMN_NAME_2='VALUE_TO_CHECK'"; " but the value that is getting into VARIABLE_NAME is "COLUMN_NAME_1 ----------------- VALUE 1...

6. Shell Programming and Scripting

Sql issue in shell scripting

HI friends , i am also facing an issue in mysql i ma trying to insert detail in a variable but not got success #!/bin/sh mysql -u<username> -p<password> <dbname> << EOF DEV=`mysql --skip-column-names <dbname> -e "SELECT timestamp from process_record where id = 1"` EOF echo $DEV ERROR...

7. Shell Programming and Scripting

Shell Scripting question with SQL

hey i have to connect to sql through shell script , then store the value of the query in a variable and then compare it after some time after running a process. i have used this code but it is not working. #!/bin/sh Val = (sqlplus -s rte/rted2@rel76d2 <<! SELECT MAX(STAT_ID) FROM CVT_STATS;...

8. UNIX for Dummies Questions & Answers

How to compare to values returned from sql in shell scripting?

hey i am using this code to connect to sql , store the value in variable and then compare it with another variable after some time by executing the same query but the desired result is not coming #!/bin/bash val=$(sqlplus -s rte/rted2@rel76d2 <<ENDOFSQL set heading off set feedback off...

9. Shell Programming and Scripting

Using shell scripting for making queries on postgres sql

I have a situation where I have a list of airplanes that make a series of flights. Therefore I am looking up the different flights that each airplane makes based on a postgres sql query: select flightid from plane where airplane='DELTAx' As a result I get a series of flight numbers...

10. Shell Programming and Scripting

Generate sql statement using shell scripting

Can anyone please assist me? I have attached 2 input files and one output file. I need to generate the sql update statements using the above 2 input files. if inputfile2 has 5 rows, then we should generate 5 update statements because column1 is unique. inputfile1 and inputfile2 may contain more...

LEARN ABOUT DEBIAN

snprimedb

snprimedb,v0.3.8(8)					      System Manager's Manual					       snprimedb,v0.3.8(8)

NAME

       snprimedb - feed the sn database.

SYNOPSIS

       snprimedb [-i] (no arguments)

DESCRIPTION

       snprimedb reads records from its standard input and enters them into the database.  The database is appended to; if you want to start a new
       database, you would delete the database files .chain, .table, .newsgroup before running this program.

       The main purpose of snprimedb is to rebuild the ID database.  Then its input is usually obtained from snscan.

       If the option -i is given, simply initialize the database files if they do not exist, and exit.

       Input lines are of the format
       newsgroup id serial where newsgroup is the newsgroup the article may be found in, id is its message id, and serial is the local serial num-
       ber of that article in that newsgroup.  If newsgroup is the special name =junk, then the whole line is ignored.

       You will need to be root or the owner of /var/spool/sn to do this.

BUGS

       snprimedb does not check to see that the articles really exist.	Also the size of the hash table is fixed at compile time.

       Under  degenerate conditions, the hash table file .chain (see below) could grow very large, with reclaimed space remaining unused.  This is
       due to the very simple file space allocator, which doesn't know how to coalesce adjacent free areas.  It doesn't know  how  to  split  them
       either.	The allocator assumes that the shape of the distribution of record lengths remains quite constant over time.

       The hash table database doesn't make any attempt to reduce its footprint.  This should be acceptable, since the database is shared.

ENVIRONMENT VARIABLES

       SNROOT If this is set and is not empty, the value is used in place of /var/spool/sn, the default news spool directory.

FILES

       /var/spool/sn/.table
	      snprimedb uses this file as the index of the hash table, and ...

       /var/spool/sn/.chain
	      as the hash chains, and ...

       /var/spool/sn/.newsgroup
	      to attach an integer identifier to each newsgroup name.  This file is a human-readable flat text file.

N.B.								    Harold Tay						       snprimedb,v0.3.8(8)