AWK unable to parse

Shell Programming and Scripting
1 
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

Please advise

2

Do not mix native awk commands with non-native ones like sqlplus. Use the system() function inside awk for that.

3

I have tried to use system command but still it errored. :frowning: 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?

4

Try the following way (not tested) :

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

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 ! :frowning:
Waste of time for which I had already wasted.

Just thought of sharing this :slight_smile:

6

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.

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

sql_val=system(sql_cmde);
close(sql_cmde);

But then the shel script just freezes, but somehow

is getting written in the output file.

I have no idea what is happening here.

7

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.

8

To prevent this kind of problem due to missing exit statement, when calling sqlplus redirect stdin to null device.

For example, in the awk program :

   sql_cmde = "sqlplus -s edfrgv/erfcdf @" sql_file " </dev/null";

Jean-Pierre.

9

I have another problem with this script which is doing what it is suppose to do but now the problem is with AWK which is unable to read input line more than 3000 bytes.

The error I am getting from AWK is

Is this a restriction in awk that it cannot read input line which is greater than 3000 bytes?

Please advise.

10

In the logic of your awk program, there is no need to read the output of sqlplus command as you print it only, hust execute the command.
In that case, the awk restriction doesn't matter

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);
   system(sql_cmde)
   next;
}
1
' $dir/ert.dat > $dir/fff.dat

cd -

Jean-Pierre.

11

Aigles,
The problem is not writing or reading the input line which matches the pattern criteria. Some of the lines in the file is greater than 3000 bytes (characters). AWK is unable to read these lines and provides error before it could even read the line to check for the pattern.

Is it possbile to set max of the input line i.e $0 ?

Thanks,

12

You need only the first 60 chars of the input file, so you can truncate the inputfile for awk :

dir=$PWD
cd $ORACLE_HOME

cut -c1-100 $dir/ert.dat | \
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/fff.dat

cd -

Jean-Pierre.

13

Aigles,
I want the content of new file to be same as original file. What I want is to replace the text (for the line having RT3 content) at position at 2651 by the value being passed by Oracle Stored procedure. If the line which is not RT3 is found then awk has to re-write the line as it is to new file.

The problem is that this line which is not RT3 some times has more than 3000 bytes of data and awk is unable to parse it.

I hope i have made myself clear.