Home » SQL & PL/SQL » SQL & PL/SQL » I need return value from PL/SQL Block (Oracle 8.1.0)
I need return value from PL/SQL Block [message #277884] Thu, 01 November 2007 01:49 Go to next message
kprasanna_79
Messages: 5
Registered: October 2007
Junior Member
Hi,
I am using perl to run a PL/SQL block, where i need to get the rows deleted after running the PL/SQL block. When i use ordinary sql query, it returns the number of rows deleted, but in PL/SQL even in use DBMS_OUTPUT.put_line, it returns only the value 1.

$sql=PL/SQL block with DBMS_OUTPUT.put_line(to_char(rowcnt));
$ret=$db->execute($sql);


PLease advice

UPDATE:-
BEGIN
            OPEN c_delete;
            LOOP
            FETCH c_delete BULK COLLECT
            INTO t_delete LIMIT l_delete_buffer;
            FORALL i IN 1..t_delete.COUNT
            DELETE $TRACKDBTABLE
            WHERE ROWID = t_delete (i);
    --      rowcnt:=rowcnt+SQL%ROWCOUNT;
            FOR j IN 1..t_delete.COUNT
            LOOP
                rowcnt:=rowcnt+SQL%BULK_ROWCOUNT(j);
            END LOOP;
            EXIT WHEN c_delete%NOTFOUND;
            COMMIT;
            END LOOP;
            CLOSE c_delete;
            DBMS_OUTPUT.put_line(to_char(rowcnt));
        END;


-Prasanna.K

[Updated on: Thu, 01 November 2007 01:57]

Report message to a moderator

Re: I need return value from PL/SQL Block [message #277885 is a reply to message #277884] Thu, 01 November 2007 01:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does this PL/SQL block look like?
Re: I need return value from PL/SQL Block [message #277887 is a reply to message #277884] Thu, 01 November 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which interface with database do you use? ODBC? DBI/DBD? Other?

Regards
Michel
Re: I need return value from PL/SQL Block [message #277889 is a reply to message #277884] Thu, 01 November 2007 02:00 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DBMS_OUTPUT doesn't work in Perl the way it does in SQL*Plus.

All it does is to write data into a PL/SQL array; SQL*Plus has an integrated interface that flushes that array to STDOUT at the completion of a PL/SQL block. Perl has no such inbuilt capability to flush the array.

If you want to return individual variables, you should use a BIND VARIABLE. Look for bind variables in your Perl documentation (perldoc DBI).

If you want to return a whole stream of PL/SQL output, you could use a TABLE FUNCTION. Search the PL/SQL documentation.

Ross Leishman
Previous Topic: Mutating Error Problem
Next Topic: Dynamic SQL SP
Goto Forum:
  


Current Time: Fri Dec 09 01:46:00 CST 2016

Total time taken to generate the page: 0.13599 seconds