in reply to Re: Re: Retrieving result sets using stored procedures (DBI, DBD::Oracle)
in thread Retrieving result sets using stored procedures (DBI, DBD::Oracle)

I am suspicious of the Oracle 9 client...

...installing an 8.1.x client would be my next guess: everything else looks OK. In fact I'm surprised that you got as far as you did with a 9i client :)

rdfield

  • Comment on Re: Re: Re: Retrieving result sets using stored procedures (DBI, DBD::Oracle)

Replies are listed 'Best First'.
Re: Re: Re: Re: Retrieving result sets using stored procedures (DBI, DBD::Oracle)
by gsiems (Deacon) on Jul 03, 2002 at 15:40 UTC
    FYI,

    It appears that things are just a bit pickier using DBI than I'm used to...

    In the package description, I changed the where clause of the select: 
       s/job = job_in/RTRIM (job) = RTRIM (job_in)/;

    [download]
    and...success (using the oracle 9 client no less!). :-)

    gsiems
[reply]
[d/l]
      Taking a closer look at the results in you original question it looks like your emp table was populated with space padded data (i.e. 'CLERK     ' rather than 'CLERK'). The change to the SQL using RTRIM will stop the optimizer from using indexes - my suggested change would be run an update statement first to remove the extra spaces. There is a function called 'dump' that can be applied to returned column (e.g. dump(job)) that can help in these situations: it shows the actual size and internal representation of the data, which would have immediately shown that the returned data was too long (10 characters for 'CLERK     ' v 5 characters for 'CLERK')

      rdfield

[reply]
[d/l]
[select]
        Thanks,

        The test table has the columns as CHAR rather than VARCHAR2 so, as you know, the padding spaces *just happen*. Funny thing, had the datatype been other than CHAR I might never have had to ask the original question...

        gsiems
[reply]

In Section Seekers of Perl Wisdom