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"

void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}

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

    if (!OCI_Initialize(err_handler, 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_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Binding host arrays to PL/SQL tables parameters of a stored procedure

#include "ocilib.h"

/* requires script demo/plsql_table.sql */

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

void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}

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];
    char tab_results[SIZE_ARRAY][SIZE_VALUE + 1];

    if (!OCI_Initialize(err_handler, 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);

        tab_results[i][0] = 0;
    }

    /* prepare call and bind local arrays */

    OCI_Prepare(st, "BEGIN test_plsqltables.combine_values(:tab_names, :tab_values, :tab_results); 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);
    OCI_BindArrayOfStrings(st, ":tab_results", (char*)tab_results, SIZE_VALUE, SIZE_ARRAY);

    OCI_BindSetDirection(OCI_GetBind(st, 1), OCI_BDM_IN);
    OCI_BindSetDirection(OCI_GetBind(st, 2), OCI_BDM_IN);
    OCI_BindSetDirection(OCI_GetBind(st, 3), OCI_BDM_OUT);

    /* execute */

    OCI_Execute(st);

    for (i = 0; i < SIZE_ARRAY; i++)
    {
        printf("%d => %s\n", i+1, tab_results[i]);
    }

    /* cleanup */
    
    OCI_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();

    return EXIT_SUCCESS;
}

Retrieve the output generated by the dbms_output package on the server

#include "ocilib.h"

void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}

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

    if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
    {
        return EXIT_FAILURE;
    }

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

    /* server output */

    OCI_ServerEnableOutput(cn, 32000, 5, 255);

    /* pl/sql call */
 
    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_StatementFree(st);
    OCI_ConnectionFree(cn);
    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_ServerEnableOutput()

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_ServerDisableOutput()

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_ServerGetOutput()

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().