joec_ has asked for the wisdom of the Perl Monks concerning the following question:

Hi, Monks, I have a PLSQL script which should return a cursor, see below:

$plsql = $dbh->prepare(q{DECLARE hd_cur Pacdesignset.data_header_cur; d_cur Pacdesignset.data_cur;
       BEGIN Pacdesignset.get_data(5000,1,hd_cur,:s_cur,d_cur); END;});
$plsql->bind_param_inout(":s_cur",\$sth2,0,{ora_type => ORA_RSET});
$plsql->execute;

I want to return the :s_cur, which is what the bind_param_inout() statement should do, correct? According to the perldoc for Oracle::DBD, i just do:

while (@recs->$sth2->fetchrow_array){}

[download]
to get the rows from the cursor as sth2 is a valid statement handle now. However, when i run it, i get a 'Can't call method "DBI::st=HASH(0x9e455b8)" without a package or object reference at reg_service.pl line 128.' Line 128 is the while statement. All i would like to do in this loop is print out the cursor contents.

Any ideas would be very much appreciated.

Thanks

Joe

Replies are listed 'Best First'.
Re: DBI & Cursors
by almut (Canon) on Nov 21, 2008 at 12:57 UTC
    while (@recs->$sth2->fetchrow_array){}

    Wouldn't that rather be 

    while (@recs = $sth2->fetchrow_array){}

    [download]
[reply]
[d/l]
[select]
      Sorry, dont know why that was there! Anyway, i now get this error:

      DBD::Oracle::st fetchrow_array failed: ORA-20100: Error creating molfi
+le string
ORA-06512: at "C$MDLIMOL51.CDCAUXOP", line 606
ORA-06512: at "C$MDLIMOL51.CDCAUXOP", line 36 (DBD ERROR: OCIStmtFetch
+) at reg_service.pl line 128.

      [download]

      Now, if i run my plsql procedure directly in TOAD, it runs fine. I do not have access to the procedures directly to edit them. A molfile is a CLOB that should be returned by the cursor (contains two columns - id, molfile). I am also assuming that the 6512 errors are to do with a cartridge that converts data into molfiles. If that is the case, they should resolve automatically once i manage to create the molfile. How do i associate a CLOB to a string in Perl?

      UPDATE:: If you look at my original post, i have defined cursors as Pacdesignset.data_header_cur etc, however i now realise that i need to define :s_cur as a Pacdesignset.structure_cur to get the conversion to occur. How can i define it as something and bind it? END UPDATE

      Thanks.

      Joe

[reply]
[d/l]

        Not quite sure what is going on here but I thought it worth noting that DBD::Oracle by default attempts to get lobs (see ora_auto_lob setting) instead of just returning the lob locator. May be worth trying with ora_auto_lob => 0 at the end of your execute.

[reply]
Re: DBI & Cursors
by tmaly (Monk) on Nov 21, 2008 at 15:00 UTC

    Joe, what version of DBI are you using? I had problems using cursors in Oracle with older versions as there were some bugs.

    Where in your code is the cursor opened? I did not see that part, is it done in the PLSQL not shown?

    Are you importing the ORA_RSET into your program? I usually have to do a use DBD::Oracle qw(:ora_types) explicitly.

[reply]
      hi, yes the cursor is opening in the pl/sql and i am using DBD::Oracle qw(:ora_types). I think the problem here is that in pl/sql when i run the procedure with the declare section and all, i have to use the line declare s_cur Pacdesignset.structure_cur because within that, a cartridge is used to convert the molecule structure from one format into a CLOB called a molfile. Now in order for me to bind that as an out parameter i would do Pacdesignset.get_data(5000,1,:s_cur), but and this is the killer but, can i do declare s_cur Pacdesign.structure_cur; begin Pacdesignset.get_data(5000,1,:s_cur) to still get at it as a bind variable even though it has been declared as a specific thing and not a placeholder?

      How do i know what version of DBI im using?

      Thanks

      Joe

      P.S. Sorry for being so verbose.

[reply]
[d/l]
[select]
        perl -M"DBI 9.99"

        Or open up the DBI.pm file and look for $VERSION

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom