How can i get the result from oci_execute()?

Question

It gives true but when I use oci_fetch($stmt) it shows error.

oci_fetch(): ORA-24374: define not done before fetch or execute and fetch

$sql = "DECLARE
            C1  KTI_OPPL_DB.MH_ONLINE_PACKAGE_DB.TABLE_OF_LOV;
        BEGIN
        KTI_OPPL_DB.MH_ONLINE_PACKAGE_DB.GET_VESSEL_TYPE_LOV(C1);
          
         
        END;";
$stmt = oci_parse($conn, $sql);
$r = oci_execute($stmt);
   
while (oci_fetch($stmt)) {
    $nrows = oci_num_rows($stmt);
}

Answer 1

Without knowing the exact PL/SQL to create your type, we can only guess what TABLE_OF_LOV is. Here is an example that shows getting records from a TABLE OF VARCHAR2, which seems a feasible guess.

<?php

error_reporting(E_ALL); 
ini_set('display_errors', 'On');

$c = oci_connect("hr", "welcome", "localhost/XE");
if (!$c) {
    $m = oci_error();
    trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}

//
// Create a PL/SQL package that has a 'TABLE OF' OUT parameter
//

$create_pkg = "
    CREATE OR REPLACE PACKAGE mypackage AS
        TYPE TABLE_OF_LOV IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
        PROCEDURE GET_VESSEL_TYPE_LOV(p1 OUT TABLE_OF_LOV);
    END mypackage;";
$s = oci_parse($c, $create_pkg);
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}
$r = oci_execute($s);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

$create_pkg_body = "
    CREATE OR REPLACE PACKAGE BODY mypackage AS
        PROCEDURE GET_VESSEL_TYPE_LOV(p1 OUT TABLE_OF_LOV) IS
        BEGIN
            p1(1) := 'one';
            p1(2) := 'two';
            p1(3) := '';
            p1(4) := 'four';
            p1(5) := 'five';
        END GET_VESSEL_TYPE_LOV;
    END mypackage;";
$s = oci_parse($c, $create_pkg_body);
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}
$r = oci_execute($s);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

//
// Call the PL/SQL procedure
//

$s = oci_parse($c, "BEGIN mypackage.get_vessel_type_lov(:bv); END;");
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

$r = oci_bind_array_by_name($s, ":bv", $array, 5, 20, SQLT_CHR);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);

}
$r = oci_execute($s);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

var_dump($array);

?>

Output is:

$ php so3.php 
array(5) {
  [0]=>
  string(3) "one"
  [1]=>
  string(3) "two"
  [2]=>
  string(0) ""
  [3]=>
  string(4) "four"
  [4]=>
  string(4) "five"
}

You may find other solutions such as writing 'PL/SQL wrappers' in the USING PL/SQL WITH OCI8 chapter on p187 of the free book The Underground PHP and Oracle Manual.

Answer 2

As I was telling you in the comment section, oci_fetch will not provide any result because the statement you are executing is not a sql statement, but a pl/sql procedure.

How to use OCI_FETCH

Fetches the next row from a query into internal buffers accessible either with oci_result(), or by using variables previously defined with oci_define_by_name().

An example using oci_result

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$sql = 'SELECT location_id, city FROM locations WHERE location_id < 1200';
$stid = oci_parse($conn, $sql);
oci_execute($stid);

while (oci_fetch($stid)) {
    echo oci_result($stid, 'LOCATION_ID') . " is ";
    echo oci_result($stid, 'CITY') . "<br>\n";
}

// Displays:
//   1000 is Roma
//   1100 is Venice

oci_free_statement($stid);
oci_close($conn);

?>

An example with oci_define_by_name

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$sql = 'SELECT location_id, city FROM locations WHERE location_id < 1200';
$stid = oci_parse($conn, $sql);

// The defines MUST be done before executing
oci_define_by_name($stid, 'LOCATION_ID', $locid);
oci_define_by_name($stid, 'CITY', $city);

oci_execute($stid);

// Each fetch populates the previously defined variables with the next row's data
while (oci_fetch($stid)) {
    echo "Location id $locid is $city<br>\n";
}

// Displays:
//   Location id 1000 is Roma
//   Location id 1100 is Venice

oci_free_statement($stid);
oci_close($conn);

?>

In your case, you are executing a PROCEDURE which is providing as output a user defined type. In this case you might try oci_fetch_array to get the result of the procedure passed as an array of three values ( which is what you get from your output ). PHP and Oracle user defined types are tricky, so I'd try this ( Adapt to your code ):

<?php
  

$stid = oci_parse($conn, 'BEGIN yourprocedure(:rc); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);

// Execute the returned REF CURSOR and fetch from it like a statement identifier
oci_execute($refcur);  
echo "<table border='1'>\n";
while (($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";

oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);

?>