How do I call an ORACLE function from OCI ?

Question

I can call an ORACLE stored procedure through OCI in a C program by constructing the SQL command for the command, here's a brief snippet from my code:

      /* build sql statement calling stored procedure */
      strcpy ( sql_stmt, "call get_tab_info(:x)" );
      rc = OCIStmtPrepare(p_sql, p_err, sql_stmt,
          (ub4) strlen (sql_stmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

But how do I construct a call (in my C program) to an ORACLE function with the following signature:

      CREATE OR REPLACE FUNCTION get_seq_number (p_table_name IN VARCHAR2, p_seq_type IN VARCHAR2) 
      RETURN NUMBER IS

To call the function in PL/SQL I would use for example:

      v_seq := get_seq_number(v_tabname, v_seqtype);

How do I construct the SQL character array (sql_stmt) to call the ORACLE function in my C program ?

Answer 1

You either issue:

SELECT my_udf()
FROM dual

and parse the result as in SELECT query, or call anonymous block:

BEGIN
   :test := my_udf();
END;

, and bind :test as an output parameter.

Answer 2

Construct your SQL statment as follows

strcpy ( sql_stmt, "BEGIN :v_seq := get_seq_number(:v_tabname, :v_seqtype); END;" );

prepare your statement as previously. Bind the variables by name (including the v_sew as previous in your code and execute the statement. When the procedure completes, the value of :v_seq will be set correctly.