Edit Report a Bug

oci_new_descriptor

(PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

oci_new_descriptor — Инициализирует новый дескриптор объекта LOB или FILE

Описание

oci_new_descriptor(resource $connection, int $type = OCI_DTYPE_LOB): ?OCILob

Резервирует ресурсы, необходимые для хранения дескриптора объекта или LOB-локатора.

Список параметров

connection: Идентификатор соединения с сервером Oracle, возвращаемый функцией oci_connect() или oci_pconnect().
type: Допустимые значения параметра type: OCI_DTYPE_FILE, OCI_DTYPE_LOB и OCI_DTYPE_ROWID.

Возвращаемые значения

Возвращает новый LOB или дескриптор FILE в случае успешного выполнения или null в случае возникновения ошибки.

Примеры

Пример #1 Пример использования oci_new_descriptor()

<?php
/* Приведённый скрипт разработан для использования с HTML формами.
 * Это подразумевает, что $user, $password, $table, $where, и $commitsize
 * получены из формы. Скрипт удаляет записи с использованием
 * ROWID и закрывает транзакцию после каждого
 * $commitsize записей. (Используйте осторожно, откат изменений невозможен)
 */
$conn = oci_connect($user, $password);
$stmt = oci_parse($conn, "select rowid from $table $where");
$rowid = oci_new_descriptor($conn, OCI_D_ROWID);
oci_define_by_name($stmt, "ROWID", $rowid);
oci_execute($stmt);
while (oci_fetch($stmt)) {
    $nrows = oci_num_rows($stmt);
    $delete = oci_parse($conn, "delete from $table where ROWID = :rid");
    oci_bind_by_name($delete, ":rid", $rowid, -1, OCI_B_ROWID);
    oci_execute($delete);
    echo "$nrows\n";
    if (($nrows % $commitsize) == 0) {
        oci_commit($conn);
    }
}
$nrows = oci_num_rows($stmt);
echo "$nrows deleted...\n";
oci_free_statement($stmt);
oci_close($conn);
?>

<?php
    /* Данный скрипт реализует загрузку файлов в LOB-столбцы
     * HTML-форма для скрипта выглядит примерно так:
     * <form action="upload.php" method="post" enctype="multipart/form-data">
     * <input type="file" name="lob_upload" />
     * ...
     */
  if (!isset($lob_upload) || $lob_upload == 'none'){
?>
<form action="upload.php" method="post" enctype="multipart/form-data">
Загрузить файл: <input type="file" name="lob_upload" /><br />
<input type="submit" value="Загрузить" /> - <input type="reset" value="Сбросить" />
</form>
<?php
  } else {

     // $lob_upload содержит временное имя загруженного файла

     // смотрите также раздел особенностей загрузки файлов,
     // если необходимо реализовать безопасную загрузку

     $conn = oci_connect($user, $password);
     $lob = oci_new_descriptor($conn, OCI_D_LOB);
     $stmt = oci_parse($conn, "insert into $table (id, the_blob)
               values(my_seq.NEXTVAL, EMPTY_BLOB()) returning the_blob into :the_blob");
     oci_bind_by_name($stmt, ':the_blob', $lob, -1, OCI_B_BLOB);
     oci_execute($stmt, OCI_DEFAULT);
     if ($lob->savefile($lob_upload)){
        oci_commit($conn);
        echo "Blob успешно загружен\n";
     }else{
        echo "Не удалось загрузить Blob\n";
     }
     $lob->free();
     oci_free_statement($stmt);
     oci_close($conn);
  }
?>

Пример #2 Пример использования oci_new_descriptor()

<?php
/* Вызов хранимой процедуры PL/SQL, параметрами которой являются объекты CLOB
 * Пример PL/SQL процедуры:
 *
 * PROCEDURE save_data
 *   Имя аргумента                    Тип                     In/Out По умолчанию?
 *   ------------------------------ ----------------------- ------ ------------
 *   KEY                            NUMBER(38)              IN
 *   DATA                           CLOB                    IN
 *
 */

$conn = oci_connect($user, $password);
$stmt = oci_parse($conn, "begin save_data(:key, :data); end;");
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ':key', $key);
oci_bind_by_name($stmt, ':data', $clob, -1, OCI_B_CLOB);
$clob->write($data);
oci_execute($stmt, OCI_DEFAULT);
oci_commit($conn);
$clob->free();
oci_free_statement($stmt);
?>

Смотрите также

oci_bind_by_name() - Прикрепляет переменную PHP к соответствующей метке в SQL-выражении

add a note

User Contributed Notes 14 notes

down

kirt at diagonalsoftware dot com ¶

19 years ago


Here is an example of retrieving a CLOB as an output parameter from a stored procedure. This is a bit hack-y and maybe there's a cleaner way to do this, but I couldn't find one. The following definitely works with Oracle 9:



// the query to call the procedure, which includes declaring the

// output parameter and assigning the result to a variable to be bound.

$qry = '

declare clob_out clob;

begin

  myprocedure(someparam_in, clob_out); 

  :myclob := clob_out;

end;

';



// parse the query and bind the 'myclob' variable

$sth = OCIParse($conn,$qry);

$myclob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($sth,":myclob",$myclob,-1,OCI_B_CLOB);



OCIExecute($sth);



// display the results

echo $myclob->load();

down

<Serg Petrenko> pserg at inkfrog dot com ¶

19 years ago


How to insert big XML data as CLOB into table with XMLType field.



<?php



//CREATE TABLE sometable(

//id number(8) not null,

//record XMLType

//) XMLTYPE COLUMN record STORE AS OBJECT RELATIONAL

//XMLSCHEMA "someschema" ELEMENT "some_element";

//



$sql = "INSERT INTO sometable(id, record) VALUES(some_sequqnce.nextval, sys.xmltype.createxml(:rec)) RETURNING ID INTO :rid";

$stmt = OCIParse($ora_conn,$sql);

$clob = OCINewDescriptor($ora_conn, OCI_D_LOB);

$rowid = OCINewDescriptor($ora_conn,OCI_D_ROWID);

OCIBindByName($stmt, ':rec', &$clob, -1,OCI_B_CLOB);

OCIBindByName($stmt, ':rid', $rowid, -1);

$clob->WriteTemporary($xml,OCI_TEMP_CLOB);

$success = OCIExecute($stmt,OCI_DEFAULT);

if(!$success) {

OCICommit($ora_conn);

}

OCIFreeStatement($stmt);

OCIFreeDesc($lob);



?>



I hope it will help :)

down

Maxwell_Smart (at) ThePentagon (dot) com ¶

21 years ago


Just a note. When INSERTing a CLOB, if a VALUES clause is used, Oracle notes: You cannot initialize an internal LOB attribute in an object with a value other than empty or null. That is, you cannot use a literal.



That's why all the examples here INSERT an EMPTY_CLOB(), and use RETURNING to grab the pointer.



However, a CLOB can also be INSERTed via a SELECT statement, and that won't require any descriptors.



Example:



$Clob = Str_Replace("'", "''", $Clob);



OCIParse($DB, "INSERT INTO My_Table (My_Clob) SELECT '$Clob' FROM Dual");



This, of course, allows the use of a WHERE clause as well.

down

aidanpeiser at yahoo dot com ¶

22 years ago


another way to display your clob details !



    $query = "select * from Your_clob_table";

    $stmt = OCIParse($conn, $query);

    ociexecute($stmt); 

        

        while ( OCIFetch($stmt)) 

        { 

         $lob = OCIResult($stmt,"CLOB_MESSAGE");

         $CLOB_MESSAGE = $lob->load();

         echo $CLOB_MESSAGE;

        }



this works,

down

moom_mong at yahoo dot com ¶

23 years ago


To read a lob, other way: 



$sql = OCIParse("select * from table_with_lob_field"); 

OCIExecute($sql, OCI_DEFAULT); 

while ( OCIFetch($sql)) { 

 $o = ociresult($sql,"loc_field_name");

 $loc_field_name = $o->load();

 print $loc_field_name;

};

down

Nathan Rogers ¶

21 years ago


I found another method of inserting/updating lob data.  It works the same was as passing lob parameters to a stored procedure and avoids the need for a RETURNING clause.

    $lob = OCINewDescriptor($conn, OCI_D_LOB);

    $stmt = OCIParse($conn,"insert into $table (id, the_blob) 

               values(my_seq.NEXTVAL, :the_blob)");

    OCIBindByName($stmt, ':the_blob', &$lob, -1, OCI_B_BLOB);

    $lob->WriteTemporary($data);

    OCIExecute($stmt, OCI_DEFAULT);

    $lob->close();

    $lob->free();

    OCICommit($conn);



There are some cases involving triggers where you can't use a RETURNING clause, so this method can come in handy.  The case where I needed it was updating a view that had an instead-of update trigger.

down

jcd at iddg dot com ¶

24 years ago


[Editor's note: don't use '&' for parameters in bind calls in PHP 5]



The code up above is somewhat correct... here's an example of how I got a CLOB to work



<?php

function insert_adinfo($AdInfoID, $MagazineType, $Publish, $DatePost, $BodyText) 

{

   global $db;



    // Insert record into database

    $clob = OCINewDescriptor($db, OCI_D_LOB);

    $stmt = OCIParse($db,"insert into tblAdInfo values ($AdInfoID,                  $MagazineType, '$Publish', to_date('$DatePost', 'YYYY-MM-DD'),                  EMPTY_CLOB()) returning BodyText into :the_blob");

    OCIBindByName($stmt, ':the_blob', &$clob, -1, OCI_B_CLOB);

    OCIExecute($stmt, OCI_DEFAULT);

    if($clob->save($BodyText)){

        OCICommit($db);

    }else{

        echo "Problems: Couldn't upload Clob\n";

    }

   

   OCIFreeDescriptor($clob);

   OCIFreeStatement($stmt);

}

?>

down

cyrill@_malevanov_dot_spb_dot_ru ¶

20 years ago


Passing CLOB to stored procedure and retrieve CLOB too (function lobinout(a in clob) return clob)



<?

    error_reporting(1+2+4+8);

    $conn = OCILogon('batdtd', 'batdtd', 'batxml');

    

    $lobin = OCINewDescriptor($conn, OCI_D_LOB);

    $lobout = OCINewDescriptor($conn, OCI_D_LOB);

    

    $stmt = OCIParse($conn, "declare rs clob; begin :rs := lobinout(:par); end;");

    $lob_data = 'abcdefgh';

    

    echo "binding lobin...";

    OCIBindByName($stmt, ':par', $lobin, -1, OCI_B_CLOB);

    

    echo "done<br>binding rs...";

    

    OCIBindByName($stmt, ':rs', $lobout, -1, OCI_B_CLOB);

    

    echo "done<br>writing temp lob...";

             // here we pass data to func

    $lobin -> WriteTemporary($lob_data);

    echo "done<br>executing...";

    

    OCIExecute($stmt, OCI_DEFAULT);

             // here we load data returned from func

    echo "done<br>rs = ".$lobout->load();

    OCICommit($conn);

    $lobin -> free();

    $lobout -> free();

    OCIFreeStatement($stmt);

    OCILogoff($conn);

?>

down

cjbj at hotmail dot com ¶

20 years ago


In PHP5 the way Example 2 passes a CLOB bind variable as an input

parameter to a PL/SQL procedure can be extended to BLOBs.



The critical change is:



    OCIBindByName($stmt, ':data', $blob, -1, OCI_B_BLOB);

    $blob->WriteTemporary($data, OCI_B_BLOB);



This doesn't work for me in PHP4.  I believe it is because the

implementation of OCIWriteTemporaryLob() always binds as a CLOB.

(This is true as of php4-STABLE-200403170230).  In PHP5 the interface

has changed and a type parameter is permitted.

down

tca at engineer dot com ¶

22 years ago


Two examples of retrieving CLOBs from the database.  They are almost identical.  The first is using a package(and cursor) which is how I interface to Oracle at work, and the second is using straight SQL, which most people post examples in.



I also convert the case from upper to lower, since that is how I prefer to work with assoc arrays...



Instead of using the get_class() function you could use the OCIColumnType() function which (in this case) would return 'CLOB' as a result...



/**

* Example 1

*

* Using a PL/SQL package and cursor

*

*/

$cursor=':p_cur';

$sql2="begin clobPackage.getClob($cursor); end;";

$curs=OCINewCursor($conn);

$stmt=OCIParse($conn,$sql2);

OCIBindByName($stmt,$cursor,&$curs,-1,OCI_B_CURSOR);

OCIExecute($stmt,OCI_DEFAULT);

OCIExecute($curs,OCI_DEFAULT);

$x=0;

while(OCIFetch($curs)){ 

  $cols=OCINumCols($curs);

  for($i=1;$i<=$cols;$i++){

    $column_name=OCIColumnName($curs,$i);

    if(is_object($tmp=OCIResult($curs,$i))&&get_class($tmp)=='OCI-Lob'){

      $column_value=$tmp->load();

    }else{

      $column_value=$tmp;

    }

    $result[$x][strtolower($column_name)]=trim($column_value);

  }

  $x++;

}

OCICommit($conn);



/**

* Example 2

*

* Using a SELECT

*

*/

$query="SELECT a_num, a_clob FROM clob_test";

$stmt=OCIParse($conn,$query);

OCIExecute($stmt,OCI_DEFAULT); 

$x=0;

while(OCIFetch($stmt)){ 

  $ncols=OCINumCols($stmt);

  for($i=1;$i<=$ncols;$i++){

    $column_name=OCIColumnName($stmt,$i);

    if(is_object($tmp=OCIResult($curs,$i))&&get_class($tmp)=='OCI-Lob'){

      $column_value=$tmp->load();

    }else{

      $column_value=$tmp;

    }

    $result[$x][strtolower($column_name)]=trim($column_value);

  }

  $x++;

}

OCICommit($conn);



I hope someone finds this useful.



Cheers,

Keith.

down

Mike ¶

12 years ago


If you're passing a clob variable to oracle stored procedure, you could:



<?php

$qry = 'begin my_sp(:largetext); end;';

$stmt = oci_parse($conn, $qry); //definition of $conn is not included here

$clob = oci_new_descriptor($conn, OCI_D_LOB);

oci_bind_by_name($stmt, ":largetext", $clob, -1, OCI_B_CLOB);

$clob->writetemporary($mylargedata);

oci_execute($stmt);

$clob->free();

oci_free_statement($stmt);

?>



Hopefully this will help!

down

ajitsingh4u at gmail dot com ¶

16 years ago


<?php
// calling stored procedure to get clob data type (we use to get xml from oracle)

error_reporting(E_ALL ^ E_NOTICE);

$conn = oci_connect($user, $password);
        
$sql = "BEGIN sp_employee_xml_data_select(:result); END;";
$stmt = oci_parse($conn , $sql);

$objClob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ':result', $objClob, -1, OCI_B_CLOB);

oci_execute($stmt, OCI_DEFAULT);
$xmlData = $objClob->load($result);

$objClob->free();
oci_free_statement($stmt);

echo $xmlData;

?>

down

sozturk at emediamillworks dot com ¶

22 years ago


I had the same problem with updating the lobs with shorter content as in one of the notes above. The addition of "\0" at the end of the replacement text didn't help either. But the following worked perfectly:



$sql = "UPDATE sometable SET lob_col = EMPTY_LOB() WHERE key_col = $key RETURNING lob_col INTO :lob";

$stmt = OCIParse($conn,$sql);

$lob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':lob',&$lob,-1,OCI_B_BLOB);

OCIExecute($stmt,OCI_DEFAULT);

$lob->save($sometext);

$lob->free();

down

-1

Ben Hubbard ben at vonik dot com ¶

18 years ago


Here is another example of how to insert a BLOB into table using a PL/SQL function.

Oracle Database Code:

create table blob_table ( the_blob blob);

create or replace function insert_blob(out_blob out blob) 
    return integer is
    begin
       insert into blob_table values (EMPTY_BLOB()) 
         return the_blob into out_blob;
       return 0; /* Success */
    end insert_blob;

PHP Code:

<?php
  $iResult = -1;
  $strTestData = 'Testing 123';
  $conn = oci_connect($user, $password);
  $stmt = oci_parse($conn, "begin :RES := insert_blob(:OUT_BLOB); end;");
 
  $objBlob = oci_new_descriptor($conn, OCI_D_LOB);
  oci_bind_by_name($stmt, ":RES", $iResult);
  oci_bind_by_name($stmt, ":OUT_BLOB", $objBlob, -1, OCI_B_BLOB);
  oci_execute($stmt, OCI_DEFAULT);
  $objBlob->write($strTestData);
  oci_commit($conn);
  $objBlob->free();
  oci_free_statement($stmt);
?>

add a note