How to fetch dynamic SQL output in Perl DBI?

Question

I have a dynamic SQL SELECT statement generated using a PL/SQL code.

declare
  sql_query clob;
begin
  sql_query := 'select * from ...........';
  execute immediate sql_query;
end;

I want to retrieve the output of the above dynamically generated SELECT statement into an array in Perl. What I have already tried in Perl is,

$sql = "declare
    sql_query clob;
  begin
    sql_query := 'select * from ...........';
    execute immediate sql_query;
  end;";

$sql_prep = $dbh->prepare($sql) or die "Cannot prepare.";
$sql_prep->execute() or die "Cannot execute.";

while (@row = $sql_prep->fetchrow_array ()) {
  print "@row\n";
}

And the error I'm getting is,

DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "declare
........
........
"] at ./script.pl line 60.

I'm a newbie for PL/SQL and I'm not sure if I have followed the proper path. Can someone please advise me on this?

Thanks in advance!

-- Shaakunthala

why didn't you construct the dynamic SQL at the Perl side of your code? It's easier than mantain an ugly PL/SQL block — Miguel Prz
– Miguel Prz, Commented Feb 1, 2013 at 8:09
Agree w/Miguell Prz...construct a SELECT statement in your Perl code, then just execute() it. Must the SQL be dynamically created in the PL/SQL? If so, then you can either bind an in/out variable for the SQL string, or bind an ORA_RSET in/out variable to effectively return a statement handle...see the DBD::Oracle docs. — runrig
– runrig, Commented Feb 1, 2013 at 17:03

bohica · Accepted Answer · 2013-02-01 08:59:19Z

3

In the future when you post it may help if you show a little more of the code - in this case the full SQL might help and at least identify which line threw the error (it says line 60 and you don't have a line 60).

You cannot do what you are trying to do. You can either dynamically create the SQL in Perl and pass it to prepare, execute and then fetchXXX (or use one of the select* methods) or if you need to dynamically create the SQL in PL/SQL you'll need to create a procedure which returns a SYS_REFCURSOR (see DBD::Oracle for examples).

As a previous poster mentioned the do method and it seems you've misunderstood him you should realise the difference between do and prepare/execute. do does not return a statement handle (it returns a status indicating the success of the operation or the number of rows affected). As a result, you cannot fetch any rows back with do as there is no statement handle to call fetch on. The do method is usually used for DDL or for insert/update/delete statements.

The prepare method returns a statement handle so you can fetch rows from it IF the SQL was a select statement.

If I've misunderstood what you are attempting to do let me know and I can give you some examples.

answered Feb 1, 2013 at 8:59

bohica

6,0103 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaakunthala Over a year ago

Sorry about not posting the entire code. It's too long. Basically I wanted to show that I'm generating a SELECT dynamically and executing it using EXECUTE IMMEDIATE at the DB. I'll look for some SYS_REFCURSOR examples as you have suggested. Also, dynamically generating the SELECT using Perl is a good idea, which looks clean for a newbie. :) Thanks!

Dave Sherohman · Accepted Answer · 2013-02-01 07:57:21Z

2

I'm not particularly familiar with PL/SQL, but I do know that your SQL string looks an awful lot like multiple statements and, as a security precaution, DBI will only execute one statement at a time. Perhaps it's only running the declare and ignoring the rest.

Is there a reason why you're doing it this way instead of just running the select directly? (Identical code, except with $sql set to select * from .......... without the extra bits around it.) If you're trying to dynamically declare a stored procedure in the more general case, you may (or may not) have better luck using DBI's do method instead of prepare/execute to declare it and then running it with execute as a separate step.

answered Feb 1, 2013 at 7:57

Dave Sherohman

46.4k14 gold badges67 silver badges104 bronze badges

2 Comments

Shaakunthala Over a year ago

Thanks for the response. I have to dynamically generate the SQL query which will yield different number of columns time to time. I'll give the do method a try.

runrig Over a year ago

Multiple statements are allowed within PL/SQL blocks. But so far, PL/SQL seems unnecessary here, seems like the OP should just be executing a SELECT statement, not PL/SQL.

Collectives™ on Stack Overflow

How to fetch dynamic SQL output in Perl DBI?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related