Cannot get dbms_output to work in PHP (connecting to Oracle database)

Question

EDIT// Problem solved - I have re-written my code into a PL/SQL package and function, so I can now simply call this using a standard select statement. I will leave my original query below and a couple of links I found useful for anyone in a similar position.

Links

Original query

I have been trying to a long long time to get my PHP code to successfully process my PL/SQL dbms_output. I cannot seem to get it to work. The rest of the code works, and in SQL developer I get the correct outputs. The error reporting works too as if I leave anything out of the code I get the errors reported (e.g. not caching the network). It is just not giving me my final output. Here is the dbms_output sections of my code in PHP.

// Fetch and display any dbms_output
function DisplayDbmsOutput($con)
{
  $r = GetDbmsOutput($con);

 if (!$r)
     print "<p>Error getting dbms_output</p>\n";
 else
    foreach ($r as $line)
      echo $line."<br>\n";
}

// Returns an array of dbms_output lines, or false.
 function GetDbmsOutput($con)
{
  $res = false;
  $stid = doParse($con, "BEGIN DBMS_OUTPUT.GET_LINE(:LN, :ST); END;");
  if ($stid) {
    if (doBind($stid, ":LN", $ln, 255) &&
        doBind($stid, ":ST", $st, "")) {
      $res = array();
      while ($succ = doExecute($stid)) {
        if ($st)
           break;
         $res[] = $ln;
      }
      if (!$succ)
        $res = false;
    }
    @OCIFreeStatement($stid);
  }
  return ($res);
} 

// Cache Network
CacheNetwork($con, true);  

 // turn serveroutput on
SetServerOutput($con, true);

 // Create dbms_output
$s = doParse($con, "
DECLARE
cost NUMBER;
path_id NUMBER;
res_numeric NUMBER;
res_array SDO_NUMBER_ARRAY;
Nav_Info Test_Turns.Navigation_Info%TYPE;
Walk_Dist chadwick_link$.cost%TYPE;
Starting_Node_ID chadwick_link$.Start_Node_ID%TYPE;
Ending_Node_ID chadwick_link$.End_Node_ID%TYPE;
start_node_id Number;
goal_node_id Number;
goal_node varchar(20);
txtArray dbms_output.chararr;
numLines integer := 4;

BEGIN
start_node_id := 34;
goal_node_id := 19;
goal_node := '%' || ' ' || (to_Char(goal_node_id)) || ',' || '%';

path_id := sdo_net_mem.network_manager.shortest_path('CHADWICK', start_node_id, goal_node_id);
cost := SDO_NET_MEM.PATH.GET_COST('CHADWICK', path_id);
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS('CHADWICK', path_id);
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
   Select Start_Node_ID INTO Starting_Node_ID from chadwick_link$ where Link_ID = res_array(indx);
   Select End_Node_ID INTO Ending_Node_ID from chadwick_link$ where Link_ID = res_array(indx);
   Select Navigation_Info INTO Nav_Info from Test_Turns_Two where Starting_Node = Starting_Node_ID and Finishing_Node = Ending_Node_ID  and possible_finish_nodes Like goal_node;
   select cost INTO Walk_Dist from chadwick_link$ where link_id = res_array(indx);
   DBMS_OUTPUT.PUT(Nav_Info || ' ' || Walk_Dist || ' meters');
END LOOP;
DBMS_OUTPUT.PUT('You have arrived at your destination');
DBMS_OUTPUT.PUT(' ');
END;
");
if ($s)
doExecute($s);

// Display the output
DisplayDbmsOutput($con);

Any suggestions would be wonderful!

Have you traced into this code to determine where it goes wrong? Particularly, does `DisplayDbmsOutput` ever get called, and does it ever step into the code which should be calling `DBMS_OUTPUT.GET_LINE`? — Bob Jarvis - Слава Україні, Jul 06 '13 at 23:14
When I run it it returns "Error getting dbms_output", so I think it must be a problem within getDmbsOutput as this is returned if (!$r) [$r = GetDbmasOutput]. I am not sure why it fails though. — Xanphis, Jul 07 '13 at 10:54
Looks to me like either `doParse` or `doExecute` are not returning a valid values, or one of the `doBind` calls is failing. Are these routines something you wrote? If so, perhaps you could add some code to print something which tells more about what's going wrong. — Bob Jarvis - Слава Україні, Jul 07 '13 at 13:31
The pl/sql i wrote, the rest is copied from a worked example. Someone else I have spoken to has said I shouldn't be trying to run pl/sql from PHP, that I should be creating a saved function in Oracle and run that in standard sql. I think I will try this and return to this problem if it does not work. Thanks for your comments though! — Xanphis, Jul 09 '13 at 13:08
Just thought I would update - I have re-written my code to create a PL/SQL package and function, I can simply call this using a standard Select statement and seems to be working properly. Thank you for commenting! — Xanphis, Jul 11 '13 at 19:41

score 2 · Answer 1 · answered Jul 06 '13 at 17:39

DBMS_OUTPUT writes to and reads from a buffer. The most common use of this package is to display text in a client such as SQL*Plus, which has built-in support for automatically displaying the buffer with its set serveroutput on command.

However we use the package programmatically.

we need to initialize the buffer: dbms_output.enable()
then we write to the buffer: dbms_output.put_line()
when we want to display what we have written we need to retrieve it from the buffer: dbms_output.get_line() - or dbms_output.get_lines() if there's a lot
finally close the buffer with dbms_output.disable()

The documentation has further information, and some worked examples. Find out more.

I suppose the real question is why are you using DBMS_OUTPUT. There are generally better mechanisms for passing messages between program units or architectural layers.

Thank you for answering. I already have each of your 4 points in my code, I had left the enable and disable out of the script shown. It still does not work... I have gone through the documentation and It has not really helped me. The reason for using it is simply because it is the only way I knew how to get an output from a PL/SQL subprogram (shortest path). — Xanphis, Jul 06 '13 at 20:49

Cannot get dbms_output to work in PHP (connecting to Oracle database)

1 Answers1