OCILIB (C Driver for Oracle) 3.9.1
Functions
PL/SQL Support

Detailed Description

OCILIB has a strong PL/SQL support :

Stored procedures/functions calls, blocks declarations are done like regular SQL calls using OCI_Prepare(), OCI_Execute(), OCI_ExecuteStmt() and OCI_ExecuteStmtFmt() functions.

All PL/SQL statements must:

Binding Host arrays to PL/SQL tables is done with OCI_BindArrayXXX() calls

Using a PL/SQL block with OCILIB
#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    int res = 0;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    /* pl/sql call */

    OCI_Prepare(st, MT("begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;"));
    OCI_BindInt(st, MT(":res"), &res);
    OCI_Execute(st);

    printf("result : %i\n", res);
 
    OCI_Cleanup();

    return EXIT_SUCCESS;
}
Binding host arrays to PL/SQL tables parameters of a stored procedure
#include "ocilib.h"

#define SIZE_ARRAY 10
#define SIZE_NAME  20
#define SIZE_VALUE 100

int main(void)
{
   OCI_Connection *cn;
   OCI_Statement *st;
   int i;

   char tab_names [SIZE_ARRAY][SIZE_NAME  + 1];
   char tab_values[SIZE_ARRAY][SIZE_VALUE + 1];

   if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
       return EXIT_FAILURE;

   cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
   st = OCI_StatementCreate(cn);

   /* set values */

   for (i = 0; i < SIZE_ARRAY; i++)
   {
       sprintf(tab_names[i],  "name  %03d", i+1); 
       sprintf(tab_values[i], "value %03d", i+1); 
   }

   /* prepare call and bind local arrays */

   OCI_Prepare(st, "BEGIN test.test(:tab_names, :tab_values); END;");
   OCI_BindArrayOfStrings(st, ":tab_names",  (char*) tab_names,  SIZE_NAME , SIZE_ARRAY);
   OCI_BindArrayOfStrings(st, ":tab_values", (char*) tab_values, SIZE_VALUE, SIZE_ARRAY);

   /* execute */

   OCI_Execute(st);
  
   /* cleanup */
   OCI_Cleanup();

   return EXIT_SUCCESS;
}
Retrieve the output generated by the dbms_output package on the server
#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    const dtext *p;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    /* pl/sql call */

    /* server output */

    OCI_ServerEnableOutput(cn, 32000, 5, 255);

    OCI_ExecuteStmt(st, "begin "
                        "   dbms_output.put_line('First  line'); "
                        "   dbms_output.put_line('Second line'); "
                        "   dbms_output.put_line('Third  line'); "
                        "end;"
                   );
 
    while (p = OCI_ServerGetOutput(cn)) 
    {
        printf(p);
        printf("\n");
    }
 
    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Functions

OCI_EXPORT boolean OCI_API OCI_ServerEnableOutput (OCI_Connection *con, unsigned int bufsize, unsigned int arrsize, unsigned int lnsize)
 Enable the server output.
OCI_EXPORT boolean OCI_API OCI_ServerDisableOutput (OCI_Connection *con)
 Disable the server output.
OCI_EXPORT const dtext *OCI_API OCI_ServerGetOutput (OCI_Connection *con)
 Retrieve one line of the server buffer.

Function Documentation

OCI_EXPORT boolean OCI_API OCI_ServerEnableOutput ( OCI_Connection con,
unsigned int  bufsize,
unsigned int  arrsize,
unsigned int  lnsize 
)

Enable the server output.

Parameters:
con- Connection handle
bufsize- server buffer max size (server side)
arrsize- number of lines to retrieve per server roundtrip
lnsize- maximum size of one line
Note:
This call is equivalent to the command 'set serveroutput on' in SQL*PLUS
'bufsize' minimum value is 2000, maximum 1000000 with Oracle < 10.2g and can be unlimited above
'lnsize' maximum value is 255 with Oracle < 10g R2 and 32767 above
Warning:
If OCI_ServerEnableOutput() is not called, OCI_ServerGetOutput() will return NULL
Returns:
TRUE on success otherwise FALSE

Definition at line 1709 of file connection.c.

References OCI_BindArrayOfStrings(), OCI_BindSetNull(), OCI_BindUnsignedInt(), OCI_Execute(), OCI_GetBind(), OCI_Prepare(), OCI_ServerDisableOutput(), and OCI_StatementCreate().

OCI_EXPORT boolean OCI_API OCI_ServerDisableOutput ( OCI_Connection con)

Disable the server output.

Parameters:
con- Connection handle
Note:
After this call, OCI_ServerGetOutput() will return NULL.
Returns:
TRUE on success otherwise FALSE

Definition at line 1821 of file connection.c.

References OCI_ExecuteStmt(), and OCI_StatementFree().

Referenced by OCI_ServerEnableOutput().

OCI_EXPORT const dtext* OCI_API OCI_ServerGetOutput ( OCI_Connection con)

Retrieve one line of the server buffer.

Parameters:
con- Connection handle
Note:
The current transaction is automatically stopped but the newly assigned is not started or resumed
Internally, OCILIB gets the server buffer through an array of lines in order to minimize roundtrips with the server
Returns:
return a server output buffer line or NULL if the server buffer is empty

Definition at line 1849 of file connection.c.

References OCI_Execute().