Sponsored Content
Full Discussion: Sql with shell scripting
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
Old 02-15-2006
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.
 

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; ... (5 Replies)
Discussion started by: sendhilmani123
5 Replies

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 (2 Replies)
Discussion started by: naveeng.81
2 Replies

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... (3 Replies)
Discussion started by: handle123
3 Replies

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... (1 Reply)
Discussion started by: villain41
1 Replies

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... (3 Replies)
Discussion started by: sgmini
3 Replies

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... (3 Replies)
Discussion started by: sanjay833i
3 Replies

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;... (3 Replies)
Discussion started by: ramsavi
3 Replies

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... (11 Replies)
Discussion started by: ramsavi
11 Replies

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... (0 Replies)
Discussion started by: JSNY
0 Replies

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... (10 Replies)
Discussion started by: vinus
10 Replies
jrunscript(1)						      General Commands Manual						     jrunscript(1)

NAME
jrunscript - command line script shell SYNOPSIS
jrunscript [ options ] [ arguments... ] PARAMETERS
options Options, if used, should follow immediately after the command name. arguments Arguments, if used, should follow immediately after options or command name. DESCRIPTION
jrunscript is a command line script shell. jrunscript supports both an interactive (read-eval-print) mode and a batch (-f option) mode of script execution. This is a scripting language independent shell. By default, JavaScript is the language used, but the -l option can be used to specify a different language. Through Java to scripting language communication, jrunscript supports "exploratory programming" style. NOTE: This tool is experimental and may not be available in future versions of the JDK. OPTIONS
-classpath path Specify where to find the user's .class files that are accessed by the script. -cp path This is a synonym for -classpath path -Dname=value Set a Java system property. -Jflag Pass flag directly to the Java virtual machine on which jrunscript is run. -l language Use the specified scripting language. By default, JavaScript is used. Note that to use other scripting languages, you also need to spec- ify the corresponding script engine's jar file using -cp or -classpath option. -e script Evaluate the given script. This option can be used to run "one liner" scripts specified completely on the command line. -encoding encoding Specify the character encoding used while reading script files. -f script-file Evaluate the given script file (batch mode). -f - Read and evaluate a script from standard input (interactive mode). -help Output help message and exit. -? Output help message and exit. -q List all script engines available and exit. ARGUMENTS
If [arguments...] are present and if no -e or -f option is used, then the first argument is the script file and the rest of the arguments, if any, are passed as script arguments. If [arguments..] and -e or -f option are used, then all [arguments..] are passed as script argu- ments. If [arguments..], -e and -f are missing, interactive mode is used. Script arguments are available to a script in an engine variable named "arguments" of type String array. EXAMPLES
Executing inline scripts jrunscript -e "print('hello world')" jrunscript -e "cat('http://java.sun.com')" Use specified language and evaluate given script file jrunscript -l js -f test.js Interactive mode jrunscript js>print('hello world'); hello world js>34 + 55 89 js> thread(function() { print('hello world'); } hello world js> Run script file with script arguments jrunscript test.js arg1 arg2 arg3 test.js is script file to execute and arg1, arg2 and arg3 are passed to script as script arguments. Script can access these using "argu- ments" array. SEE ALSO
If JavaScript is used, then before evaluating any user defined script, jrunscript initializes certain built-in functions and objects. These JavaScript built-ins are documented in jsdocs. 06 Aug 2006 jrunscript(1)
All times are GMT -4. The time now is 02:42 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy