Getting ORA-01008 error when trying to execute a function via OCI

Question

We are trying to execute an oracle function that requires parameters and we are getting the error ORA-01008- Not all variables are bound. We are pretty sure the problem is in how we are binding the variable that is supposed to received the result of the function. First we tried the following (a method without parameters):

$tSql:="select staging.FUNC_ORAOCI_TEST() from dual"
$iStatus:=OCIHandleAlloc (envhp;$stmthp;OCI_HTYPE_STMT)
$iStatus:=OCIHandleAlloc (envhp;$errhp;OCI_HTYPE_ERROR)
$iStatus:=OCIStmtPrepare ($stmthp;$errhp;$tSql;OCI_DEFAULT)
$iStatus:=OCIDefineByPos ($stmthp;$bindpp;$errhp;1;->atResults;SQLT_STR;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;OCI_DEFAULT)
$iStatus:=OCIStmtExecute (svchp;$stmthp;$errhp;1;0;0;0;OCI_DEFAULT)

And it worked perfectly...

Where we got stuck was when trying to pass parameters to the function (which we had modified on purpose to now accept parameters)

We thought it was because we now had to make the binding by name, but it just did not work. We have tried running a PL/SQL block and still we get the error. Here are our failed attempts:

1. Using a SQL Statement:

$tSql:="select staging.FUNC_ORAOCI_TEST(:tParamText,:iParamNum) from dual"
$iStatus:=OCIBindByName ($stmthp;$bindpp;$errhp;":tParamText";-   >tParamText;SQLT_STR;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;1;OCI_DEFAULT;BIND_IN)
$iStatus:=OCIBindByName ($stmthp;$bindpp;$errhp;":iParamNum";->iParamNum;SQLT_INT;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;1;OCI_DEFAULT;BIND_IN)
$iStatus:=OCIDefineByPos ($stmthp;$bindpp;$errhp;1;->atResults;SQLT_STR;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;OCI_DEFAULT)
$iStatus:=OCIStmtExecute (svchp;$stmthp;$errhp;1;0;0;0;OCI_DEFAULT)

2. Using a PL/SQL Anonymous Block:

$tSql:="DECLARE vResult VARCHAR2:=''; BEGIN vResult := FUNC_ORAOCI_TEST(:tParamText,:iParamNum); End;"
$iStatus:=OCIBindByName ($stmthp;$bindpp;$errhp;":tParamText";->tParamText;SQLT_STR;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;1;OCI_DEFAULT;BIND_IN)
$iStatus:=OCIBindByName ($stmthp;$bindpp;$errhp;":iParamNum";->iParamNum;SQLT_INT;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;1;OCI_DEFAULT;BIND_IN)
$iStatus:=OCIBindByName ($stmthp;$bindpp;$errhp;":vResult";->tResult;SQLT_STR;$ORANullIndicator;$ORANullLenArray;$ORANullReturnCodeArray;1;OCI_DEFAULT;BIND_OUT)
$iStatus:=OCIStmtExecute (svchp;$stmthp;$errhp;1;0;0;0;OCI_DEFAULT)

We have looked at similar questions but no one was trying to use the OCI interface to execute the function the way we are doing it.

We are coding in 4D so ignore the weird syntax. We just need guidance as to how build the statement and what would be the proper OCI command to reach a successful binding.

Answer 1

We partially resolved this issue.

We have got it to work only for SQL statements not for PL/SQL. But we got rid of the ORA-01008 error.

The code in case 1 referenced above works as is. We did some clean up of comments and lines that were added for debugging, but they might have altered the sqlstmt somehow, thus affecting the binds.

The code in case 2 now gives an error "Invalid SQL Statement" which we will open a new inquiry for in separate thread.

One very useful information that we found to help us resolve the initial inquiry can be found here: https://docs.oracle.com/database/121/LNOCI/oci05bnd.htm#LNOCI16368