Ora8BindLob Function

Action

Binds a placeholder for a LOB parameter in a SQL statement or PL/SQL block to a program variable.

Include file

Ora8.bdh

Syntax

Ora8BindLob( in  hStmt        : number,
             in  sSqlVar      : string,
             in  nDatatype    : number,
             in  nSize        : number,
             in  nArraySize   : number,
             in  nBindOption  : number,
             out hDescriptor  : number ): boolean;

Return value

  • true if successful

  • false otherwise

Parameter Description
hStmt Statement handle.
sSqlVar Name of the place holder in the SQL statement or PL/SQL block. This parameter must include the preceding colon identifying it as a place holder.
nDatatype Oracle external data type of the program variable. Oracle converts the program variable from external to internal format when the variable is sent to the database and vice versa when it is retrieved from the database. See Oracle 8 data types for a list of data types.
nSize Size of the program variable or the array element in bytes.
nArraySize

Maximum number of array elements to bind.

The default option is scalar binding.

nBindOption

Specifies how to bind the place holder. The following are the possible options for binding a place holder:

  • ORA_DEFAULT. This is the default option. A place holder is bound to a scalar or to an array program variable.

  • ORA_PIECEWISE. The program variable is bound to the place holder in multiple pieces. In this case, nSize specifies the piece size, and nArraySize the maximum column size. You have to call the Ora8SetPieces function in order to set the values for the pieces.

  • ORA_PLSQLTABLE_IN. The program variable is bound to a PL/SQL table that is used as an in parameter within a stored procedure.

  • ORA_PLSQLTABLE_OUT. The program variable is bound to a PL/SQL table that is used as an out parameter in a stored procedure.

  • ORA_PLSQLTABLE_INOUT: The program variable is bound to a PL/SQL table that is used as an inout parameter in a stored procedure.

  • ORA_PLSQL_IN. The program variable is bound to a PL/SQL scalar parameter that is used as an in parameter within a stored procedure.

  • ORA_PLSQL_OUT. The program variable is bound to a PL/SQL scalar parameter that is used as an out parameter in a stored procedure.

  • ORA_PLSQL_INOUT. The program variable is bound to a PL/SQL scalar parameter that is used as an inout parameter in a stored procedure.

hDescriptor Variable that is bound to the placeholder in the SQL statement or PL/SQL block.

Example

var
  ghEnv0        : number;
  ghError0      : number;
  ghStmt0       : number;
  ghSvcCtx0     : number;
  ghDescriptor0 : number;

dcltrans
  transaction TMain
  begin
    Ora8Init(ghEnv0, OCI_DEFAULT);
    Ora8HandleAlloc(ghEnv0, ghError0, OCI_HTYPE_ERROR);
    Ora8Logon(ghEnv0, ghSvcCtx0, "user", "password", "orclnet2");

    Ora8HandleAlloc(ghEnv0, ghStmt0, OCI_HTYPE_STMT);
    Ora8StmtPrepare(ghStmt0, TMain_SQL001, OCI_NTV_SYNTAX);
    Ora8DescriptorAlloc(ghEnv0, ghDescriptor0, OCI_DTYPE_LOB);

    Ora8Bind(ghStmt0, "1", SQLT_INT, 0, 1, ORA_PLSQL_IN);                     //p_productid, in, number
    Ora8BindLob(ghStmt0, "2", SQLT_BLOB, 4, 1, ORA_PLSQL_OUT, ghDescriptor0); //p_productimage, out, blob

    Ora8SetInt(ghStmt0, "1", 2254);  //p_productid

    Ora8StmtExecute(ghSvcCtx0, ghStmt0, 1, 0, 0, 0, OCI_COMMIT_ON_SUCCESS);

    Ora8LobRead(ghSvcCtx0, ghDescriptor0, 9894, 9894, 1, 178);

    Ora8HandleFree(ghStmt0, OCI_HTYPE_STMT);
    Ora8Logoff(ghSvcCtx0);
    Ora8HandleFree(ghError0, OCI_HTYPE_ERROR);
    Ora8HandleFree(ghEnv0, OCI_HTYPE_ENV);
  end TMain;

dclsql
  TMain_SQL001:
    [BEGIN getproductimage(:1,:2); END;]

See also

Oracle's Programmer's Guide to Oracle Call Interface for Wrapped Oracle functions: OCIBindByName, OCIBindByPos