Korn shell script - SQL statement challenges


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Korn shell script - SQL statement challenges
# 1  
Old 08-29-2013
Korn shell script - SQL statement challenges

Hi scripting experts.

I have some coding challenges that I'm hoping you can help me out.

I have one file#1 that contains the following sql statement that spans over multiple lines:

Code:
sql Select /*+ use_has(a,b) */ *
    from customer a,
         customer_address b
    where a.id = b.id
    -- and a.id is not null
    and b.address is not null

First step - Cleanup the file to remove blank lines, comments (#) and convert sql statement to span over 1 line only

Code:
cat file#1 | sed '/^$/d' | sed '/^#/d' | tr '\n\r' ' ' > tmp_file#1
echo >>tmp_file#1

tmp_file#1 now contains:
Code:
sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null

Challenge#1:
The condition "and b.address is not null" should not be commented out.
I'm thinking the comment part "-- and a.id is not null" should become "/* and a.id is not null */ in order to comment that condition only
How can I easily handle this?
Note: Comments can appear anywhere in the sql statement


Next, I have a second file#2 as follows which I need to replace $$SQL with the sql statement above:

Code:
Existing
...
$$SQL=select count(*) from dual
....

New
...
$$SQL=sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null
....

Code:
sSqlStmt="sql Select /*+ use_has(a,b) */ *     from customer a,          customer_address b     where a.id = b.id -- and a.id is not null and b.address is not null"


sed -e "s/\$\$SQL=.*/\$\$SQL=$sSqlStmt/"

Challenge#2:
The "/" and "*" in the sql statment is not producing the desired results by the sed command

I get an error such as "sed function cannot be parsed".


Thank you for any help or insight that you can provide
# 2  
Old 08-29-2013
This is one example:
Code:
$ cat sql_file.sql
sql Select /*+ use_has(a,b) */ *
    from customer a,
         customer_address b
    where a.id = b.id
    -- and a.id is not null
    and b.address is not null

$ cat sql_file.sql | tr -d '\n' | sed 's/ \{2,\}/ /g' | sed 's/-- //g'
sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null

$ cat file2
$$SQL=select count(*) from dual

$ sql='sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null'

# This just replaces the $$SQL with the contents of the variable($sql) in the file as you requested not the rest of the line.
$ sed "s#\$\$SQL#$sql#g" file2
sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id and a.id is not null and b.address is not null=select count(*) from dual

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oop to copy and rename files through SQL Statement in shell Script

#!/bin/sh sqlplus -s "/ as sysdba" << EOF SET HEADING OFF SET FEEDBACK OFF Select pt.user_concurrent_program_name , OUTFILE_NAME FROm apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests f where pt.concurrent_program_id = f.concurrent_program_id and pt.application_id =... (1 Reply)
Discussion started by: usman_oracle
1 Replies

2. Shell Programming and Scripting

How to create SQL statement out of data using shell script?

Table TAB1 contains following example data (its a tree sitting in table data format & its driven based CHILD & PARENT column pick the RULE condition to generate the below SQL: CHILD PARENT SS MID MNM VNM RULE FLG 1 ? S1 ? ? V1 rule004 I 2 1 S1 ? ? V1 0 Z 3 1 S1 ? ? V1 1 Z ... (6 Replies)
Discussion started by: gksenthilkumar
6 Replies

3. Shell Programming and Scripting

Execute sql statment in korn shell

I am fairly new to writing scripts, and have gotten a lot of help from this site in the past with many of the posts. I have a question/issue with a script I am attempting to write and have a question regarding executing an sql statement inside of a loop (do while). I have in the past written... (1 Reply)
Discussion started by: josbor01
1 Replies

4. Shell Programming and Scripting

Pass values to case statement in a function korn shell

I'm in the process of writng a function that consists of a case statement is there a way of calling the function and passing a value to it? ie function1 () { case opt1 do ..... opt2 do..... esac } function opt1 I'm aware the syntax is not correct, but you get the general idea. (1 Reply)
Discussion started by: squrcles
1 Replies

5. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

6. Programming

create a spool file based on values passed from korn shell to sql script

this is my issue. 4 parameters are passed from korn shell to sql script. parameter_1= varchar2 datatype or no value entered my user. parameter_2= number datatype or no value entered my user. parameter_3= number datatype or no value entered my user. parameter_4= number datatype or no... (5 Replies)
Discussion started by: megha2525
5 Replies

7. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

8. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

9. UNIX for Advanced & Expert Users

Accessing PL/SQL OUT variables in Korn Shell Script

Hello All, I was just wondering if there is any direct way to access PL/SQL OUT variables from Korn Shell Script. I could already figure out how to return a single value back from PL/SQL to Shell Script (using bind variable). But, what if we want to return multiple values? One option I... (4 Replies)
Discussion started by: bright_future
4 Replies

10. UNIX for Dummies Questions & Answers

korn shell to bash - statement not working

Everything else seems to be working, but this isn't. Is it the "cat..." that is wrong of the condition? Thanks. cat tc_result.txt | while read LINE do if then let "files_run += 1"; echo "inside the if loop" # save current filetype case $LINE... (5 Replies)
Discussion started by: brdholman
5 Replies
Login or Register to Ask a Question