PL/SQL Support

Detailed Description

OcilibCApiFetching

OCILIB has strong PL/SQL support:

• Blocks, procedures, and functions 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 procedure/function calls and block declarations are performed 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 an '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 table 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_SYM_PUBLIC boolean OCI_API	OCI_ServerEnableOutput (OCI_Connection *con, unsigned int bufsize, unsigned int arrsize, unsigned int lnsize)
	Enable the server output.
OCI_SYM_PUBLIC boolean OCI_API	OCI_ServerDisableOutput (OCI_Connection *con)
	Disable the server output.
OCI_SYM_PUBLIC const otext *OCI_API	OCI_ServerGetOutput (OCI_Connection *con)
	Retrieve one line of the server buffer.

Function Documentation

OCI_ServerEnableOutput()

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

#include <api.h>

Enable the server output.

Parameters

con	- Connection handle
bufsize	- Server buffer maximum 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 is 1000000 with Oracle < 10.2g, and unlimited above.

'lnsize' maximum value is 255 with Oracle < 10gR2, 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_SYM_PUBLIC boolean OCI_API OCI_ServerDisableOutput

(

OCI_Connection *

con

)

#include <api.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_SYM_PUBLIC const otext *OCI_API OCI_ServerGetOutput

(

OCI_Connection *

con

)

#include <api.h>

Retrieve one line of the server buffer.

Parameters

con	- Connection handle

Note

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

Returns

A server output buffer line, or NULL if the server buffer is empty.

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