Example 3-1 PL/SQL API for Temporary LOBs

DECLARE
  blob1 BLOB;
  clob1 CLOB;
  clob2 CLOB; 
  nclob1 NCLOB;
BEGIN
  -- create a temp LOB using CREATETEMPORARY and fill it with data
  DBMS_LOB.CREATETEMPORARY(blob1,TRUE, DBMS_LOB.SESSION);
  writeDataToLOB_proc(blob1);

  -- create a temp LOB using SQL built-in function
  SELECT substr(ad_sourcetext, 5) INTO clob1 FROM print_media WHERE product_id=1 AND ad_id=1;

  -- create a temp LOB using a PLSQL built-in function
  nclob1 := TO_NCLOB(clob1);

  -- create a temp LOB using a PLSQL procedure. Assume foo creates a temp lob and it's parameter is IN/OUT
  foo(clob2);

  -- Other APIs
  CALL_LOB_APIS(blob1, clob1, clob2, nclob1);

  -- free temp LOBs
  DBMS_LOB.FREETEMPORARY(blob1);
  DBMS_LOB.FREETEMPORARY(clob1);
  DBMS_LOB.FREETEMPORARY(clob2); 
  DBMS_LOB.FREETEMPORARY(nclob1);

END;
/
show errors;

Example 3-2 JDBC API for Temporary LOBs

public class listempc
{
  public static void main (String args [])
    throws Exception
  {
    Connection conn = LobDemoConnectionFactory.getConnection();
   
    // SELECT TEMPORARY LOB USING SQL
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery
          ("SELECT SUBSTR(ad_sourcetext, 5) FROM Print_media WHERE product_id = 3106 AND ad_id = 1");
    if (rset.next())
    {
      Clob clob = rset.getClob (1);
      System.out.println("Is lob temporary: " + ((CLOB)clob).isTemporary());
  
      call_other_apis_to_read_write_from_lob(clob);
      clob.free();
    }
    stmt.close();
 
    // CREATE TEMPORARY LOB VIA API
    Clob clob = conn.createClob();
    
    System.out.println( "Is clob temporary: " + ((oracle.jdbc.OracleClob)clob).isTemporary());
 
    call_other_apis_to_read_write_from_lob(clob);
 
     // ALWAYS FREE THE TEMPORARY LOB WHEN DONE WITH IT
    clob.free();
 
    conn.close();
  }
}

Example 3-3 OCI APIs for Temporary LOBs

void temp_lob_operations()
{
  OCILobLocator *temp_clob1;
  OCILobLocator *temp_clob2;
  OCIStmt       *stmhp = (OCIStmt *) 0;
  OCIDefine     *dfnhp1;
  ub1            bufp[BUFLEN];
  ub4            amtp = 0;
  ub8            bamtp = 0;
  ub8            camtp = 0;
  ub2            retl1, rcode1;
  sb4            ind_ptr1 = 0;
  boolean        istemp = FALSE;
  char          *sel_stmt = "SELECT SUBSTR(ad_sourcetext, 5) FROM Print_media WHERE product_id = 3106 AND ad_id = 1";
 
  /* allocate lob descriptors */
  checkerr(errhp, OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &temp_clob1,
                                     (ub4) OCI_DTYPE_LOB, (size_t) 0,
                                     (dvoid **) 0));
  checkerr(errhp, OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &temp_clob2,
                                     (ub4) OCI_DTYPE_LOB, (size_t) 0,
                                     (dvoid **) 0));
 
  /* statement handle */
  checkerr(errhp, OCIHandleAlloc( (dvoid *)envhp, (dvoid **) &stmhp,
                   (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  checkerr(errhp, OCIHandleAlloc( (dvoid *)stmhp, (dvoid **) &dfnhp1,
                   (ub4) OCI_HTYPE_DEFINE, (size_t) 0, (dvoid **) 0));
 
  /*-------------------- SELECT TEMPORARY LOB USING SQL ------------------------*/
  checkerr(errhp, OCIStmtPrepare(stmhp, errhp, (text *) sel_stmt,
          (ub4) strlen(sel_stmt), OCI_NTV_SYNTAX, OCI_DEFAULT));
 
  checkerr(errhp, OCIDefineByPos(stmhp, &dfnhp1, errhp, (ub4) 1, &temp_clob1,
                  (sb4) -1, SQLT_CLOB, &ind_ptr1, &retl1, &rcode1,
                  (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmhp, errhp, (ub4) 0, (ub4) 0,
                     (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
  checkerr(errhp, OCIStmtFetch(stmhp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT));
 
  checkerr(errhp, OCILobWriteAppend2(svchp, errhp, temp_clob1,
           (oraub8 *)&bamtp, (oraub8 *) &camtp, bufp, (oraub8)BUFLEN,
            OCI_ONE_PIECE, (dvoid*)0, (OCICallbackLobWrite2)0, (ub2)0,
            (ub1)SQLCS_IMPLICIT));
 
  /*-------------------- CREATE TEMPORARY LOB USING API ------------------------*/
  checkerr(errhp, OCILobCreateTemporary(svchp, errhp, temp_clob2,
                  (ub2) 0, OCI_DEFAULT, OCI_TEMP_CLOB,
                  FALSE, OCI_DURATION_SESSION));
 
  /* write into bufp */
  strcpy((char *)bufp, (const char *)"Demo program for testing temp lobs");
  bamtp = amtp = (ub4) strlen((char *)bufp);
 
  /* write bufp contents to temp lob */
  checkerr(errhp, OCILobWrite2(svchp, errhp, temp_clob2, &amtp, 1,
                  (dvoid *)bufp, (ub4)bamtp , OCI_ONE_PIECE, (dvoid *)0,
                  (OCICallbackLobWrite) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT));
 
  /*--------------------- ALWAYS FREE TEMPORARY LOBS -------------------------*/
  checkerr(errhp, OCILobIsTemporary(envhp, errhp, temp_clob1, &istemp));
  if (istemp)
    checkerr(errhp, OCILobFreeTemporary(svchp, errhp, temp_clob1));
 
  checkerr(errhp, OCILobIsTemporary(envhp, errhp, temp_clob2, &istemp));
  if (istemp)
    checkerr(errhp, OCILobFreeTemporary(svchp, errhp, temp_clob2));
 
 /* Free lob descriptors */
  checkerr(errhp, OCIDescriptorFree ((dvoid *)temp_clob1, (ub4) OCI_DTYPE_LOB));
  checkerr(errhp, OCIDescriptorFree ((dvoid *)temp_clob2, (ub4) OCI_DTYPE_LOB));
}