Using oracle.pl with Returned Info

Capturing Results of SQL Call into Variables

To use the sql_results routine, you must

return the variables separated by a distinct character
then split the vars inside Perl

Overview

You can capture a single field, a row of fields, or multiple rows. Each approach has some of the same requirements:

New SQL Select
Separate the variables by a distinct character that won't appear in any of the fields. I use the tilde ~ and just put it in between each field as a text constant. You could probably alter SQLPlus' RECSEPCHAR variable, also.
```
@sql_stmt= <<EOM;
  select student_name, '~', student_id, '~', class_name, '~', grade, '~'
  from tables .....;
EOM
```
Call the select stmt differently
You must use @sql_results($logon,$stmt); and assign the returned value to a new Perl variable:
```
$new_fields=&sql_results($connectstring,@sql_stmt);
```
Parse the returned values into individual fields
Use Perl's SPLIT routine
```
($stud_name,$stud_id,$class,$grade,$remainder)=split(/~/,$new_fields,5);
```
which in this case will separate the array $new_fields into 5 separate variables:
$stud_name,$stud_id,$class,$grade,$remainder
$remainder will have the rest of the array (more lines if returning multiple rows).

A Single Value Returned

In this example, I check the number of distinct records (which in this case translate into test dates) for a specific Prem_ID and Disease.

@sql_nr_dates = <<EOT;
    select count(seq_nr)
    from event_summary es, sample smp
    where es.es_nr=smp.es_nr
      and es.prem_id='$prem_id' $and_prem_state1
      and event_date between SYSDATE - (4*365) and SYSDATE
      and smp.disease='$disease'
EOT

I run the stmt and return the single value into the $nr_dates var:

  $nr_dates=&sql_results($connectstring,@sql_nr_dates);

Because there is only one value, I don't have to parse it, but I must remove the trailing cr/lf, ESPECIALLY IF I WANT TO USE THE VALUE TO VERIFY STUFF IS PASSED IN. Use the chop function.

  chop($nr_dates);

and then you can use the variable however you wish:

if ($nr_dates == 0) { do stuff }
elsif ($nr_dates == 1) { do other stuff }
elsif ($nr_dates > 1) { do other stuff }
else # nr_dates < 0 or undefined
{ display error}

To Verify Passed in Values

This subroutine checks that the user entered a valid Prem_ID:

sub CheckPremID {
  if (!defined $prem_state || length($prem_state) < 2)   {
    $and_state = " ";
  }
  else {$and_state = " and prem_state=upper('$prem_state')";}
  @sql_prem = <<EOT;
    select prem_id from premises
    where prem_id = '$prem_id' $and_state;
EOT

  $db_prem=&sql_results($connectstring,@sql_prem);
  chop($db_prem);
  $db_prem=&Trim($db_prem);
  if ($db_prem eq $prem_id) {$check=0;}
  else {$check=1;}
  return($check);
}

First, I alter the $and_state var depending on if the user entered a state or not
Then I create a sql statement simply returning the single prem_id (it's a key field) from the table supposedly containing the one the user entered
Then I assign the selected value to a new var, $db_prem, which I chop to get to the correct length
Then I Trim leading and trailing spaces with a routine.

Then I test for equality and return a value to the calling routine which looks like this:

  $bad_prem_id = &CheckPremID;
  if (!defined $in{'prem_id'} || $bad_prem_id > 0 || length($prem_id) < 1) {
    if ($bad_prem_id > 0) {
      print "ERROR: Invalid prem_id $prem_id submitted.  Try again";

Notice that I test not just for equality, but for existence of the variable to begin with.

Returning a Single Row

This is almost identical to returning multiple rows so I'll use the same example presented in the overview:

@sql_stmt= <<EOM;
  select student_name, '~', student_id, '~', class_name, '~', grade, '~'
  from tables .....;
EOM

 $new_fields=&sql_results($connectstring,@sql_stmt)

 ($stud_name,$stud_id,$class,$grade,$remainder)=split(/~/,$new_fields,5);

This separates the array $new_fields into 5 separate variables. Merely ignore $remainder and you can process the first row of variables returned.

The trick to the logic of returning multiple rows is to assign the initial collection of fields to the $remainder var, then do a while (remainder) {}[which operates as long as remainder is not null which means there will be one extra process for the final cr/lf]:

  $remainder=&sql_results($connectstring,@sql_stmt);
  $rec_nr=0;
  while ($remainder) {
    ($stud_name,$stud_id,$class,$grade,$remainder)=split(/~/,$new_fields,5);
    if ($remainder) { # avoid processing final bogus record 
      process the individual record
    $rec_nr +=1;
    }
  }

home

Using oracle.pl with Returned Info

Capturing Results of SQL Call into Variables

Overview

New SQL Select

Call the select stmt differently

Parse the returned values into individual fields

A Single Value Returned

To Verify Passed in Values

Returning a Single Row

Returning Multiple Rows