Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : Oracle Functions : ocinewdescriptor

ocinewdescriptor

Alias of oci_new_descriptor (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)

Examples ( Source code ) » ocinewdescriptor

<?php
/* Calling PL/SQL stored procedures which contain clobs as input
 * parameters (PHP 4 >= 4.0.6).
 * Example PL/SQL stored procedure signature is:
 *
 * PROCEDURE save_data
 *   Argument Name                  Type                    In/Out Default?
 *   ------------------------------ ----------------------- ------ --------
 *   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($connOCI_D_LOB);
oci_bind_by_name($stmt':key'$key);
oci_bind_by_name($stmt':data'$clob, -1OCI_B_CLOB);
$clob->write($data);
oci_execute($stmtOCI_DEFAULT);
oci_commit($conn);
$clob->free();
oci_free_statement($stmt);
?>


Note: In PHP versions before 5.0.0 you must use ocinewdescriptor() instead.
 This name still can be used, it was left as alias of oci_new_descriptor() for downwards compatability.
 This, however, is deprecated and not recommended. 

Code Examples / Notes » ocinewdescriptor

tca

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.


moom_mong

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;
};


jcd

The code up above is somewhat correct... here's an example of how I got a CLOB to work:
<pre>
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);
}
</pre>


dave dot dunkin

PHP3 has ocifreedescriptor($desc) and PHP4 has ocifreedesc($desc), but both support $desc->free().

maxwell_smart

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.


cjbj

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.


gpayne

If you just want to retrieve an auto-increment field like an integer, you don't have to use the oci_new_descriptor function:
  $dbh = ociplogon("username","pw","sid");
  $query = "INSERT INTO employees (name) VALUES ('Jones') RETURNING employee_no INTO :employee_no";
  $stmt = oci_parse($dbh, $query);
  // oci_new_descriptor not necessary...
  // Bind the variable to the parsed statement.
  // I used '8' as the minimum length - otherwise it was setting the default length to '1', so when an employee_no like 18366 was returned, the variable would be set to '1' (the first digit), so make sure you set a length.
  oci_bind_by_name($stmt, ":employee_no", $employee_no, 8);
  oci_execute($stmt, OCI_DEFAULT);
  // Commit the change
  oci_commit($dbh);
  // Free resources associated with the statement
  oci_free_statement($stmt);
 print "new employee no is: $employee_no";


sozturk

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();


nathan rogers

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.


pserg

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


kirt

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();


aidanpeiser

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,


Change Language


Follow Navioo On Twitter
oci_bind_array_by_name
oci_bind_by_name
oci_cancel
oci_close
OCI-Collection->append
OCI-Collection->assign
OCI-Collection->assignElem
OCI-Collection->free
OCI-Collection->getElem
OCI-Collection->max
OCI-Collection->size
OCI-Collection->trim
oci_commit
oci_connect
oci_define_by_name
oci_error
oci_execute
oci_fetch_all
oci_fetch_array
oci_fetch_assoc
oci_fetch_object
oci_fetch_row
oci_fetch
oci_field_is_null
oci_field_name
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type_raw
oci_field_type
oci_free_statement
oci_internal_debug
OCI-Lob->append
OCI-Lob->close
oci_lob_copy
OCI-Lob->eof
OCI-Lob->erase
OCI-Lob->export
OCI-Lob->flush
OCI-Lob->free
OCI-Lob->getBuffering
OCI-Lob->import
oci_lob_is_equal
OCI-Lob->load
OCI-Lob->read
OCI-Lob->rewind
OCI-Lob->save
OCI-Lob->saveFile
OCI-Lob->seek
OCI-Lob->setBuffering
OCI-Lob->size
OCI-Lob->tell
OCI-Lob->truncate
OCI-Lob->write
OCI-Lob->writeTemporary
OCI-Lob->writeToFile
oci_new_collection
oci_new_connect
oci_new_cursor
oci_new_descriptor
oci_num_fields
oci_num_rows
oci_parse
oci_password_change
oci_pconnect
oci_result
oci_rollback
oci_server_version
oci_set_prefetch
oci_statement_type
ocibindbyname
ocicancel
ocicloselob
ocicollappend
ocicollassign
ocicollassignelem
ocicollgetelem
ocicollmax
ocicollsize
ocicolltrim
ocicolumnisnull
ocicolumnname
ocicolumnprecision
ocicolumnscale
ocicolumnsize
ocicolumntype
ocicolumntyperaw
ocicommit
ocidefinebyname
ocierror
ociexecute
ocifetch
ocifetchinto
ocifetchstatement
ocifreecollection
ocifreecursor
ocifreedesc
ocifreestatement
ociinternaldebug
ociloadlob
ocilogoff
ocilogon
ocinewcollection
ocinewcursor
ocinewdescriptor
ocinlogon
ocinumcols
ociparse
ociplogon
ociresult
ocirollback
ocirowcount
ocisavelob
ocisavelobfile
ociserverversion
ocisetprefetch
ocistatementtype
ociwritelobtofile
ociwritetemporarylob
eXTReMe Tracker