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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 6, 2013 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
– Xanphis, Commented Jul 7, 2013 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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 7, 2013 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
– Xanphis, Commented Jul 9, 2013 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
– Xanphis, Commented Jul 11, 2013 at 19:41

APC · Accepted Answer · 2013-07-06 17:39:29Z

2

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.

answered Jul 6, 2013 at 17:39

APC

147k20 gold badges180 silver badges289 bronze badges

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

1 Comment

Xanphis Over a year ago

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).

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related