PL/SQL Support

Detailed Description

OCILIB has a strong PL/SQL support :

• Blocks, procedures and function can be used with OCILIB statements.
• Ref cursors
• Nested tables
• Tables (indexed by integer types)
• Access to the server side output generated by the DBMS_OUTPUT package

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:

• start with a 'begin' or 'declare' keyword
• end with a 'end;' keyword

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, "begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;");
    OCI_BindInt(st, ":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 char *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. More...
OCI_EXPORT boolean OCI_API	OCI_ServerDisableOutput (OCI_Connection *con)
	Disable the server output. More...
OCI_EXPORT const otext *OCI_API	OCI_ServerGetOutput (OCI_Connection *con)
	Retrieve one line of the server buffer. More...

Function Documentation

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

#include <ocilib.h>

Enable the server output.

Parameters

con	- Connection handle
bufsize	- server buffer max size (server side)
arrsize	- number of lines to retrieve per server round-trip
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

Referenced by ocilib::Connection::EnableServerOutput().

OCI_EXPORT boolean OCI_API OCI_ServerDisableOutput

(

OCI_Connection *

con

)

#include <ocilib.h>

Disable the server output.

Parameters

con	- Connection handle

Note

After this call, OCI_ServerGetOutput() will return NULL.

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Connection::DisableServerOutput().

OCI_EXPORT const otext* OCI_API OCI_ServerGetOutput

(

OCI_Connection *

con

)

#include <ocilib.h>

Retrieve one line of the server buffer.

Parameters

con	- Connection handle

Note

Internally, OCILIB gets the server buffer through an array of lines in order to minimize round-trips with the server

Returns

return a server output buffer line or NULL if the server buffer is empty

Referenced by ocilib::Connection::GetServerOutput().