[PHP-DB] Oracle: text output from procedures (Tricky ;) From: David Newcomb (davidn <email protected>)
Date: 08/15/00

Hi,

Bit of a tricky one this!

Idea:
    I want to move all the delete checking of out php and into
    an Oracle procedure; so something along the lines of:

        <?php

/* run_sql() takes care of login, parse,excute...etc... */
            $result = run_sql("begin p_delete_category($catid); end;")

/* and returns results of OCIError() */
            if ($result == FALSE)
            {
                echo "$catid sucessfully deleted<br>\n";
            }
            else
            {
                echo "Unable to delete $catid: $result['message']<br>\n";
            }
        ?>

Inside Oracle I have a procedure which checks for several conditions
before the delete (note: I can not use a trigger because the trigger
would be mutating!):

    procedure p_delete_category(p_cat_seqno in number) is

        cursor con_docs is
            select count(*)
                from categories
                where cat_parent_seqno = p_cat_seqno;

        l_docs := number;

   begin

        open con_docs;
        fetch con_docs into l_docs;
        close con_docs; 

--
-- This bit
--
        if l_docs > 0 then
            output "can not delete: contains category children";
            return bad;
        endif;
--
-- condition 2
--

    delete from categories where cat_seqno = p_cat_seqno;
    return good;
 
   end;

Basically I would like to output though php why the procedure
could not run to completion.

A work mate suggested that "set serveroutput on" and
dbms_output.(putline,new_line,...) may help.
Is there any way of making this work, or are there any user definable
error messages that can be used.

Any ideas, I'm stuck?

Regards,
David.