get dbms output message from oracle in php

Question

      CREATE OR REPLACE PACKAGE BODY simpleState IS

      PROCEDURE selectFromTable(tableName VARCHAR2, columnName VARCHAR2) IS
      TYPE c1 IS TABLE OF VARCHAR2(30);
      Notes c1;
      BEGIN
        EXECUTE IMMEDIATE 'Select ' || columnName || ' FROM ' || tableName BULK COLLECT INTO Notes;
        FOR idx IN Notes.FIRST .. Notes.LAST LOOP
          DBMS_OUTPUT.PUT_LINE(Notes(idx));
        END LOOP;
      END selectFromTable;


    $conn = oci_connect("student", "STUDENT", "localhost");

    if (!$conn) {
        $m = oci_error();
        echo $m['message'], "\n";
        exit;
    }

else {
    print "Connected to Oracle!";
}


    if(isset($_POST["readSubmit"])){

        $table = $_POST['ReadTableName'];
        $column = $_POST['ReadColumn'];

        $stid = oci_parse($conn, 'begin simpleState.selectFromTable(:a,:b); end;');

        ocibindbyname($stid, 'a', $table);
        ocibindbyname($stid, 'b', $column);

        if (!$stid) {
            $e = oci_error($conn);
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
        }


            $r = oci_execute($stid);
            echo "$r";
        oci_free_statement($stid);
        oci_close($conn);    
        }   

        ?>

I am trying to display the message in the DBMS_OUPUT.PUT_LINE in the browser using PHP. But it doesn't display anything. The PL/SQL code works and displays what it has to display in SQL Developer. Can anyone help me with this?

Why are you trying to do this? DBMS_OUTPUT is something that's used in general only for adhoc messages - eg. for testing purposes - rather than a method of passing data from one system to another. If I were you, I'd investigate ref cursors - this is the preferred solution for passing data from select statements back to the calling code. — Boneist
– Boneist, Commented May 17, 2016 at 19:09
Well, needed a fast solution to integrate a pl/sql procedure with an user interface (something simple like a html page) — student0495
– student0495, Commented May 17, 2016 at 19:27
Yes, and I don't have the code where I've tried (deleted it to trysomething else) — student0495
– student0495, Commented May 17, 2016 at 19:42

PianoSong · Accepted Answer · 2016-05-18 05:47:43Z

2

I had the same problem few days ago but I was lazy and I stopped searching for a solution, but, I found a solution now.

Instead a procedure, I've created a function that returns a SYS_REFCURSOR.

CREATE OR REPLACE FUNCTION selectFromTable(tableName VARCHAR2, columnName VARCHAR2) 
  RETURN SYS_REFCURSOR 
AS
  my_cursor SYS_REFCURSOR;
  s_query varchar2(500);
BEGIN

  s_query := 'SELECT ' || columnName || ' FROM ' || tableName;

  OPEN my_cursor FOR s_query;

  RETURN my_cursor;
END selectFromTable;
/

PHP code is pretty simple.

<?php
   $conn = oci_connect('student', 'STUDENT', 'localhost/XE');
   if (!$conn) {
      $e = oci_error();
      trigger_error(htmlentities($e('message'), ENT_QUOTES), E_USER_ERROR);
   }

   $tablename = 'STUDENTI';
   $columnname = 'NR_MATRICOL';

   $query = "begin 
               :cursor := selectFromTable(:tabl, :colm);
             end;";

   $stid = oci_parse($conn, $query);

   $p_cursor = oci_new_cursor($conn);

   oci_bind_by_name($stid, ":tabl", $tablename);
   oci_bind_by_name($stid, ":colm", $columnname);

   oci_bind_by_name($stid, ":cursor", $p_cursor, -1, OCI_B_CURSOR);

   oci_execute($stid);
   oci_execute($p_cursor, OCI_DEFAULT);

   while (($row = oci_fetch_array($p_cursor, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
      echo $row['NR_MATRICOL'] . "<br />\n";
   }
?>

Edit: If you need to print more columns, you can just simply add column names here:

$columnname = 'NR_MATRICOL, NUME, PRENUME';

and you need to add echos too:

 echo $row['NR_MATRICOL'] . " - ";
 echo $row['NUME'] . " - ";
 echo $row['PRENUME'] . "<br >";

The output should look like the following:

111 - Popescu - Bogdan
112 - Prelipcean - Radu
123 - Bucur - Andreea
131 - Santa - Claus

edited May 18, 2016 at 5:47

answered May 18, 2016 at 5:38

PianoSong

3405 silver badges19 bronze badges

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

2 Comments

student0495 Over a year ago

I've alreadt tried with ref cursors, I'll try your solution also. Thank you!

student0495 Over a year ago

Worked perfectly! Thank you!

student0495 · Accepted Answer · 2016-05-17 19:22:01Z

0

After deep research I found out that i can't actually get something from dbms_output, so I've made a kind of a solution to my problem. I've added an OUT parameter to my PL/SQL procedure, where I've concatenated all my results. I've added the chr(10) to have newlines and simulate different row results in oracle.

so I've added PROCEDURE selectFromTable(tableName VARCHAR2, columnName VARCHAR2, p_out OUT VARCHAR2); and modified DBMS_OUTPUT.PUT_LINE(Notes(idx)); into p_out := p_out || Notes(idx) || chr(10);. Also I've added anoter :c variabile to my PHP function and added ocibindbyname($stid, 'c', $outVal, 300); for it.

answered May 17, 2016 at 19:22

student0495

1813 silver badges17 bronze badges

5 Comments

student0495 Over a year ago

As a note to this solution, chr(10) works only in SQL Developer... in PHP it doesen't display it as a new line unfortunately.

Boneist Over a year ago

Perhaps you need chr(13)||chr(10) instead of just chr(10)?

student0495 Over a year ago

In SQL Developer chr(10) is enough, I've tried also with chr(13)||chr(10), as you've said but it's the same in PHP (all the results are displayed in one line)

Martin Schapendonk Over a year ago

That's because your browser renders it as HTML. Newlines mean nothing in HTML.

student0495 Over a year ago

I've thought about that, just hoped it'll work if i'll pass it from oracle through php

Collectives™ on Stack Overflow

get dbms output message from oracle in php

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related