Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk with variable from sqlplus

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk with variable from sqlplus
# 1  
Old 02-26-2013
awk with variable from sqlplus
Hi, I'm a it stuck on the below code where a variable is pulled from sqlplus and used in awk. It runs with no errors but still pulls back all records in the input file.

It should pull the max reference from sql plus and then only print those records where the reference value in column 1 is greater than the max reference value from the db.

Input file is a comma delimited and reference is a 14 digit number.

Thanks for any help in pointing me in the right direction!


Code:
value=`sqlplus -s user/pass << EOF
      set heading off;
      select max(reference)  from cred_table;
      exit;
EOF`

awk -F "," -v ref=$value'{if ($1 >= ref) printf("%-16s%-20s%-50s%-20s%-60s%-12s%-9s%-20s%-20s%-20s%-30s%-20s%-20s%-20s%-20s%-50s%-20s%-50s%-20s%-20s%-100s%-4s%-4s%-20s%-20s%-10s%-10s%-10s%-25s%-25s%-100s%-40s%-20s%-20s%-40s%-20s%-20s%-25s%-3s%-10s%-10s%-20s%-10s%-1s%-10s%-4s%-4s%-4s%-4s%-3s%-4s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61)}' appfile_$Day.out > C_LIQ_CR_APP_RT_$Day.DAT

# 2  
Old 02-26-2013
Can you wrap variable: $value in double quotes and give it another try?
Code:
awk -F, -v ref="$value" ' { if ($1 >= ref) ...

If that does not help, post sample content from your input file: appfile_$Day.out in code tags
# 3  
Old 02-26-2013
Wrapping the variable in quotes caused an error:

awk: String cannot contain a newline character.
The source line is 1.
The error context is
>>> <<<
awk: String 2013057090 cannot be longer than 399 bytes.
The source line is 1.
awk: String 2013057090 cannot be longer than 399 bytes.
The source line is 1.

Here is a sample of the appfile_$Day.out, I'm only including the first couple columns but it has 61 columns.

Code:
20130570900508,I,kevin,,
20130570900507,I,ARTUR,,
20130570900506,I,Rachel

Also for this test the value selected from sqlplus is 20130570900508

And by the way I'm running Unix on AIX 1

---------- Post updated at 10:45 PM ---------- Previous update was at 10:35 PM ----------

I also wanted to point out I'm able to echo the variable from sqlplus and it looks ok

Code:
echo $value

20130570900508

I've tried ltriming it and substring to 14 just to make sure there are no spaces in it. The field format in Oracle is character but I also tried a temp table with the value in numeric format.
# 4  
Old 02-27-2013
Put below line instead of set heading off; and try
Code:
set echo off head off feed off pagesize 0 trimspool on linesize 1000;

BTW I don't see any issues with your awk code.
This User Gave Thanks to Yoda For This Post:
jonathanb30
# 5  
Old 02-27-2013
Yes that worked perfectly. Thanks for your help!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pass a VARIABLE to sqlplus script

Hi Team, I am trying to run a sqlplus script against several databases via a FOR/LOOP and also passing the loop variable to a sqlplus script I am calling, as follows: #!/bin/bash export ORACLE_SID=plgc1 export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1 export... (1 Reply)
Discussion started by: jonnyd
1 Replies

2. UNIX for Beginners Questions & Answers

Sqlplus variable UNIX

hi guys i have a sqlplus : sqlplus -s username/password << EOF @mysql.sql EOF in mysql.sql there is a count of a table, i want to write in a variabile unix. how can i do? Thanks a lot Regards Francesco. (3 Replies)
Discussion started by: Francesco_IT
3 Replies

3. Shell Programming and Scripting

Scirpt to fetch the variable from sqlplus

Hi Gurus, I am stuck with the step where i need to fetch the location & sales from the below procedure by taking it from table field using the for loop. any idea how this can be done in unix. From one column both the location and sales are taken out. create or replace procedure newyork... (2 Replies)
Discussion started by: arun888
2 Replies

4. Shell Programming and Scripting

Sending sqlplus output to a shell variable

I am trying to import a sqlplus output into a shell variable but it doesnt seem to be working. set -x export DEPENDENT_CR_NO=`sqlplus -s /nolog <<EOF conn username/passwd set heading off select dependency from custom_patches where patch_name='PATCH.zip'; exit; EOF` echo $DEPENDENT_CR_NO ... (2 Replies)
Discussion started by: beginer314
2 Replies

5. Shell Programming and Scripting

store sqlplus output in variable

hi how can i store sqlplus output to a variable in sh script (not bash) Thanks MM (1 Reply)
Discussion started by: murtymvvs
1 Replies

6. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 Replies

7. UNIX for Advanced & Expert Users

passing unix variable to sqlplus without a file name

Hi, I want to input unix variable to sqlplus.The following is working fine sqlplus username/password @dummy.sql param1 param2 << EOF create user $1 identified by $2; EOF But I dont want any file name to be passed,I just want to pass the parameter. Is there any way to that?? Thanks... (3 Replies)
Discussion started by: sakthi.abdullah
3 Replies

8. Shell Programming and Scripting

Using a variable in sqlplus

Hello, I'm trying to write a script that will loop a sql statement through an external list. Basically, the script enters a loop and runs the sql statement for each entry in the list file. Currently, the script will stop at a cursor where I can then manually enter the SQL statment. This is... (11 Replies)
Discussion started by: MadHatter
11 Replies

9. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies

10. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies
Login or Register to Ask a Question