Re: Can a procedure contain only a SELECT statement?

From: Mladen Gogala <>
Date: Thu, 25 Mar 2010 15:10:07 +0000 (UTC)
Message-ID: <>

On Thu, 25 Mar 2010 13:01:58 +0000, Jonathan Lewis wrote:

> Would anyone care to make a technical comment on my earlier comment
> I'm still interested to hear how the front-end code can handle the
> output from a procedure when it doesn't have any information about what
> that output might look like.

Well, there must be some information. If the output type is a cursor, one can describe the cursor. In Perl, which is my front end of choice, a statement handle has the following properties which describe the output rather well: NUM_OF_FIELDS,NAME,TYPE,PRECISION and SCALE. I can always construct my output of choice, based on the handle description, even if I don't know what the original statement looks like, which is frequently the case with the dynamic SQL. However, I have to know that the output type is a cursor, not a scalar variable. So, if my output is a statement handle called $sth, I would do the following:

my $nf = $sth->{NUM_OF_FIELDS};

 for ( my $i = 0; $i < $nf; $i++ ) {
    my $name = $sth->{NAME}[$i];
    my $type = $sth->{TYPE}[$i];
    my $prec = $sth->{PRECISION}[$i];
    my $scle = $sth->{SCALE}[$i];

    my $tn=$db->type_info($type)->{TYPE_NAME};     print
        "Field number $i: name $name of type $tn". 
        "with precision $prec,$scle\n";


That would give me the exact description of the output. One cannot know nothing of the output, that would make any programming impossible. Executing a procedure which you know nothing about is like the Russian roulette: you never know when the chamber will be loaded and what will come out of the barrel.

Received on Thu Mar 25 2010 - 10:10:07 CDT

Original text of this message