Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

AWK unable to parse

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting AWK unable to parse
# 1  
Old 04-18-2008
[B]Unable to invoke sqlplus from AWK[/B]
Code:
awk -v new="                  " '
substr($0, 17, 3) == "ABC"  && substr($0, 52, 8) == "00000000" {
                  tr=substr($0, 20, 10);
                    ap=substr($0, 30, 2);
                    ver=substr($0, 32, 2);
                    irver=substr($0, 34, 2);
                    recdate=substr($0, 36, 8);
                    logdate=substr($0, 44, 8);
#                    printf ("%s %s %s %s %s %s\n", utr, ap, ver, irver, recdate, logdate);
            cd $ORACLE_HOME
            retval=$(sqlplus -s edfrgv/erfcdf <<eof
            exec abc_ert($tr , $ap, $ver, $irver, $recdate, $logdate);
            eof)
            printf ("%s\n", $retval);
 
                        next;
                        }
                        1
                        ' ert.dat > fff.dat

awk unable to parse this script:
Error is
Quote:
syntax error The source line is 12.
The error context is
retval=$(sqlplus -s abc/abc >f
awk: The statement cannot be correctly parsed.
The source line is 12.
syntax error The source line is 13.
Please advise
Last edited by COD; 04-18-2008 at 04:39 PM.. Reason: incorrect info
# 2  
Old 04-18-2008
Do not mix native awk commands with non-native ones like sqlplus. Use the system() function inside awk for that.
# 3  
Old 04-18-2008
I have tried to use system command but still it errored. Smilie Can you show how to call sqlplus from awk?
What i want to achieve is to read specific postions in the line from input file (because those position are the primary key value for the query written in Procdure); invoke sqlplus call procedure and pass the parameters just read using substring, get the value from procedure then replace a specific position in the line which is just read by the value provided by sqlplus procedure.

Bottleneck is that I am unable to invoke sqlplus from awk. Any more ideas?
Last edited by COD; 04-18-2008 at 04:25 PM..
# 4  
Old 04-19-2008
Try the following way (not tested) :
Code:
dir=$PWD
cd $ORACLE_HOME

awk '
BEGIN {
   sql_file = "/tmp/sqlplus_from_awk.sql";
   sql_cmde = "sqlplus -s edfrgv/erfcdf @" sql_file;
}
substr($0, 17, 3) == "ABC"  && substr($0, 52, 8) == "00000000" {
   tr      = substr($0, 20, 10);
   ap      = substr($0, 30, 2);
   ver     = substr($0, 32, 2);
   irver   = substr($0, 34, 2);
   recdate = substr($0, 36, 8);
   logdate = substr($0, 44, 8);
   printf("exec abc_ert(%s, %s, %s, %s, %s, %s);\n",
          $tr , $ap, $ver, $irver, $recdate, $logdate) > sql_file;
   close(sql_file)
   sql_cmde | getline sql_val
   close(sql_cmde)
   printf ("%s\n", $sql_val);
   next;
}
1
' $dir/ert.dat > $dir/fff.dat

cd -

Jean-Pierre.
# 5  
Old 04-19-2008
I would like to comment on this approach

As far as possible try to modularize your approach as there are so many advantages over something that achieves in a single shot

ease of maintenance
re usability
easy to debug
no need to test the entire application if its properly modularized
and of course, nobody would curse you if somebody else has to maintain your code

for your problem you could modularize it something like
( what I post is just an example )

fetching data from db into a file
processing the data from the file
redirecting output

now the problem space is broken down easily and you could easily challenge any of the module if there is a problem.

I have burnt my fingers many times since I don't have the habit of modularizing the code and I end up writing the same code again, do the same testing again, same debugging again.
I have to admit - its a shame ! Smilie
Waste of time for which I had already wasted.

Just thought of sharing this Smilie
# 6  
Old 04-21-2008
aigles,

I tried running your version of the script, the sql file is getting proper parameters and sqlplus is getting invoked but I am getting some wired error on the shell.
Quote:
Error 45 initializing SQL*Plus
Internal error
The above error is getting displayed the number of times it finds RT3 record and trying to call sqlplus.

I have checked the procedure in oracle and the output is expected when I copy and paste the exec command which is written in the sqlfile.

I have absolutely no idea why the error is being provided.

The sqlplus is in the PATH and I tired invoking sqlplus directly from the shell and it is working fine.

I tried using the command
Code:
sql_val=system(sql_cmde);
close(sql_cmde);

But then the shel script just freezes, but somehow
Quote:
PL/SQL procedure completed successfully
is getting written in the output file.

I have no idea what is happening here.
Last edited by COD; 04-21-2008 at 02:15 PM..
# 7  
Old 04-22-2008
It is working fine now, I changed the printf from printf("exec abc_ert(%s, %s, %s, %s, %s, %s);\n",
$tr , $ap, $ver, $irver, $recdate, $logdate) > sql_file;
to
printf("set serveroutput on format wrap;\nexec abc_ert(%s, %s, %s, %s, %s, %s);\n exit;\n", $tr , $ap, $ver, $irver, $recdate, $logdate) > sql_file;

The problem was exit statement was missing!!!

Amazing solution to very complex problem we encountered. Thanks to all.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Using awk to Parse File

Hi all, I have a file that contains a good hundred of these job definitions below: Job Name Last Start Last End ST Run Pri/Xit ________________________________________________________________ ____________________... (7 Replies)
Discussion started by: atticuss
7 Replies

2. Shell Programming and Scripting

awk to parse df output

Output of the below code includes unmatched date.Please correct it df -k|awk '$4>50 {print $1, "\t"$4,"\t" $7}' It gives output less than 50% also. (5 Replies)
Discussion started by: vinil
5 Replies

3. Shell Programming and Scripting

Parse a file using awk

Hi Experts, I am trying to parse the following file; FILEA a|b|c|c|c|c a|b|d|d|d|d e|f|a|a|a|a e|f|b|b|b|boutput expected: a<TAB>b <TAB><TAB>c<TAB>c<TAB>c<TAB>c<TAB> <TAB><TAB>d<TAB>d<TAB>d<TAB>d<TAB> e<TAB>f <TAB><TAB>a<TAB>a<TAB>a<TAB>a<TAB> <TAB><TAB>b<TAB>b<TAB>b<TAB>b<TAB>*... (7 Replies)
Discussion started by: rajangupta2387
7 Replies

4. Shell Programming and Scripting

Parse input -AWK

Input File Defined configuration: cfg: CLL_DCC_Fabric_A BTS00P21; BAU_AP00P01QC; BAU_LGSCNJP02; BAU_TS00P20; BAU_DSMSM14; BAU_HT00P02; BAU_DSMSM13; BAU_HT00P01; cfg: CX0014_list BAU_TS00P20; BAU_NYP_PRODIAD1_CJ;... (5 Replies)
Discussion started by: greycells
5 Replies

5. Shell Programming and Scripting

Parse a file with awk?

Hi guys (and gals). I need some help. I'm running an IVR purely on Asterisk where I capture the DTMFs. After pulsing each DTMF I have Asterisk write to a file with whatever was dialed (mostly used for record-keeping) and at the end of the survey I write all variables in a single line to a... (2 Replies)
Discussion started by: tulf210
2 Replies

6. Shell Programming and Scripting

parse xm entry with awk/sed

Hi folks, I have XML files with the following sections (section occurs once per file) in them: <AuthorList CompleteYN="Y"> <Author ValidYN="Y"> <LastName>Bernal</LastName> <ForeName>Federico</ForeName> ... (3 Replies)
Discussion started by: euval
3 Replies

7. Shell Programming and Scripting

AWK: Parse lvdisplay

Hi, I would like to parse the next output of an lvdisplay -v using awk: --- Logical volumes --- LV Name /dev/vg01/lvol3 VG Name /dev/vg01 LV Permission read/write LV Status available/syncd Mirror copies ... (2 Replies)
Discussion started by: RuBiCK
2 Replies

8. Shell Programming and Scripting

Parse file using awk and work in awk output

hi guys, i want to parse a file using public function, the file contain raw data in the below format i want to get the output like this to load it to Oracle DB MARWA1,BSS:26,1,3,0,0,0,0,0.00,22,22,22.00 MARWA2,BSS:26,1,3,0,0,0,0,0.00,22,22,22.00 this the file raw format: Number of... (6 Replies)
Discussion started by: dagigg
6 Replies

9. UNIX for Dummies Questions & Answers

awk to parse a directory name?

Hi, I have a directory file name: /auto/space/user/jen/CED/CED_01MZ/visit1/DCE_2eco/016/echo1 I would like to just get the following outputs into variables such that: variable1 = /auto/space/user/jen/CED/CED_01MZ/visit1/ and variable2 = DCE_2eco/016/echo1 I've tried it with... (2 Replies)
Discussion started by: nixjennings
2 Replies

10. UNIX for Dummies Questions & Answers

parse string with awk

Hi Guys, I spend half a day getting this to work with no luck, perhaps you guys can help.. I have a string from a file looking like this: module::name=test::type=generic_data::exec=snmpget.......::desc=A Little Test::interval=300 what I would like to split it, so I get a value for each... (3 Replies)
Discussion started by: hyber
3 Replies
Login or Register to Ask a Question