OCILIB (C and C++ Driver for Oracle)  4.7.5
Open source and cross platform Oracle Driver delivering efficient access to Oracle databases.
Loading...
Searching...
No Matches
PL/SQL Support

Detailed Description

OcilibCApiFetching

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"
void err_handler(OCI_Error *err)
{
printf("%s\n", OCI_ErrorGetString(err));
}
int main(void)
{
int res = 0;
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
/* pl/sql call */
OCI_Prepare(st, "begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;");
OCI_BindInt(st, ":res", &res);
printf("result : %i\n", res);
return EXIT_SUCCESS;
}
OCI_SYM_PUBLIC boolean OCI_API OCI_BindInt(OCI_Statement *stmt, const otext *name, int *data)
Bind an integer variable.
OCI_SYM_PUBLIC boolean OCI_API OCI_ConnectionFree(OCI_Connection *con)
Close a physical connection to an Oracle database server.
OCI_SYM_PUBLIC OCI_Connection *OCI_API OCI_ConnectionCreate(const otext *db, const otext *user, const otext *pwd, unsigned int mode)
Create a physical connection to an Oracle database server.
struct OCI_Connection OCI_Connection
Oracle physical connection.
Definition: types.h:124
struct OCI_Statement OCI_Statement
Oracle SQL or PL/SQL statement.
Definition: types.h:136
struct OCI_Error OCI_Error
Encapsulates an Oracle or OCILIB exception.
Definition: types.h:390
OCI_SYM_PUBLIC const otext *OCI_API OCI_ErrorGetString(OCI_Error *err)
Retrieve error message from error handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_Cleanup(void)
Clean up all resources allocated by the library.
OCI_SYM_PUBLIC boolean OCI_API OCI_Initialize(POCI_ERROR err_handler, const otext *lib_path, unsigned int mode)
Initialize the library.
OCI_SYM_PUBLIC OCI_Statement *OCI_API OCI_StatementCreate(OCI_Connection *con)
Create a statement object and return its handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_StatementFree(OCI_Statement *stmt)
Free a statement and all resources associated to it (resultsets ...)
OCI_SYM_PUBLIC boolean OCI_API OCI_Prepare(OCI_Statement *stmt, const otext *sql)
Prepare a SQL statement or PL/SQL block.
OCI_SYM_PUBLIC boolean OCI_API OCI_Execute(OCI_Statement *stmt)
Execute a prepared SQL statement or PL/SQL block.
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)
{
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);
/* 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 */
for (i = 0; i < SIZE_ARRAY; i++)
{
printf("%d => %s\n", i+1, tab_results[i]);
}
/* cleanup */
return EXIT_SUCCESS;
}
OCI_SYM_PUBLIC OCI_Bind *OCI_API OCI_GetBind(OCI_Statement *stmt, unsigned int index)
Return the bind handle at the given index in the internal array of bind handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_BindSetDirection(OCI_Bind *bnd, unsigned int direction)
Set the direction mode of a bind handle.
OCI_SYM_PUBLIC boolean OCI_API OCI_BindArrayOfStrings(OCI_Statement *stmt, const otext *name, otext *data, unsigned int len, unsigned int nbelem)
Bind an array of strings.
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)
{
const char *p;
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
/* 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");
}
return EXIT_SUCCESS;
}
OCI_SYM_PUBLIC const otext *OCI_API OCI_ServerGetOutput(OCI_Connection *con)
Retrieve one line of the server buffer.
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_ExecuteStmt(OCI_Statement *stmt, const otext *sql)
Prepare and Execute a SQL statement or PL/SQL block.

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