| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Any Interest?
In some Pro*C productivity library I wrote about 10 years ago. I was
going through my archives and found it. Here are the docs I wrote for
the library.
NAME:
LOG_MESSAGE() - Inserts a log message to the MSGLOG table.
SYNOPSIS
procedure LOG_MESSAGE(P_TYPE number, P_PROCESS varchar2
P_FILTER varchar2, P_TEXT varchar2)
DESCRIPTION:
The LOG_MESSAGE() stored procedure is used to insert a log message
into the MSGLOG table with a unique sequence number and the current
date and time. Any exceptions are simply passed back to the caller.
This procedure is called by the Pro*C function sql_log(), but can
also be called by an Oracle(tm) Form or Report.
ARGUMENTS:
P_TYPE number - A value indicating the type of message being logged.
A value of 0 indicates an error message. A value of
1 indicates an informational message while a value of
3 indicates an activity message
P_PROCESS varchar2 - A string identifying the process generating the
message. Limited to 8 characters.
P_FILTER varchar2 - A string used classify messages within type and
process.
P_TEXT varchar2 - The actual text of the message to be logged.
RETURN VALUE:
None. However, exceptions are passed back to the caller.
------------------------------------------------------------------------
SQLCHECK
NAME:
SQLCHECK() - Macro to simplify call to sql_check().
SYNOPSIS:
#include "sqltools.h"
int SQLCHECK(int mask, char *process);
DESCRIPTION:
The SQLCHECK() macro simplifies the setup and calling of the
sql_check()
function. It is equivelent to calling sql_check() in the following
way: sql_check( mask, SQLCODE, SQLMSG, process );
ARGUMENTS:
int mask - An integer value used to mask out recoverable Oracle
error codes. (See sql_check() function for details. ).
char *process - The address of a string containing the process
indentifier.
RETURN VALUE:
The integer value from sql_check() which can be SQL_SUCCESS,
SQL_FAILURE,
or SQL_FATAL.
SEE ALSO:
SQLCODE, SQLMSG, sql_check().
EXAMPLE:
EXEC SQL BEGIN DECLARE SECTION;
varchar today[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL
SELECT SYSDATE
INTO :today
FROM DUAL;
switch ( SQLCHECK( SQL_OK, "SQLTOOLS" ) ) {
case SQL_FATAL :
sql_log( LOGERR, "SQLTOOLS","DEMO","Fatal error: %s\n",
SQLMSG );
exit( SQL_FAILURE );
case SQL_SUCCESS :
printf( "Today's date is: %s\n", VARTERM(today) );
break;
case SQL_FAILURE :
sql_log( LOGERR, "SQLTOOLS", "DEMO",
"Had trouble selecting today's date. "
"Oracle Error code is: %s\n", SQLMSG );
break;
}
------------------------------------------------------------------------
SQLCOUNT
NAME:
SQLCOUNT - Macro for sqlca.sqlerrd[2].
SYNOPSIS:
#include "sqltools.h"
DESCRIPTION:
The SQLCOUNT macro provides a convenient access to sqlca.sqlerrd[2].
This array member of the sqlca structure contains the count of rows
processed by the most recently executed SQL statement.
ARGUMENTS:
None.
RETURN VALUE:
A long integer containing the count of the number of rows processed
by the last successfully executed SQL statement. The value is
undefined if the SQL statement failed except when processing arrays.
See the "Programmers Guide to Pro*C" for details.
EXAMPLE:
EXEC SQL
UPDATE EMP
SET HIRE_DATE = SYSDATE;
if ( SQLCODE == SQL_SUCCESS )
sql_log( LOGMSG, "SQLTOOLS", "DEMO",
"Updated %ld employee records!\n", SQLCOUNT );
------------------------------------------------------------------------
SQLCODE
NAME:
SQLCODE - Macro for sqlca.sqlcode.
SYNOPSIS:
#include "sqltools.h"
DESCRIPTION:
This macro provides a convenient method of refering to the sqlcode
member of the sqlca structure. This value can be compared to the
following values defined in sqltools.h:
SQL_OK ( 0 )
SQL_NOT_FOUND ( 1403 )
SQL_INSRT_DPLCT ( -0001 )
SQL_LOCK_FAIL ( -0054 )
SQL_INSRT_NULL ( -1400 )
SQL_TOO_LARGE ( -1401 )
SQL_FETCH_NULL ( -1405 )
SQL_FETCH_TRUNC ( -1406 )
SQL_UPDAT_NULL ( -1407 )
SQL_NMRIC_OVRFL ( -1426 )
SQL_INVLD_MONTH ( -1843 )
SQL_INVLD_DAY ( -1847 )
SQL_CHECK_CNSTR ( -2290 )
SQL_RFRNL_INGRT ( -2291 )
SQL_RI_DELTE ( -2292 )
ARGUMENTS:
None.
RETURN VALUE:
None.
SEE ALSO:
SQLCHECK().
EXAMPLE:
EXEC SQL BEGIN DECLARE SECTION;
varchar today[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL
SELECT SYSDATE
INTO :today
FROM DUAL;
if ( SQLCODE == SQL_OK )
printf( "Today's system date is: %s\n", VARTERM(today) );
else {
sql_log( LOGERR, "SQLTOOLS", "DEMO", "ORACLE(tm) Error: %s\n",
SQLMSG );
exit(1)
}
exit(0);
------------------------------------------------------------------------
SQLMSG
NAME:
SQLMSG - Macro refering to sqlca.sqlerrm.sqlerrmc.
SYNOPSIS:
#include <string.h>
#include "sqltools.h"
DESCRIPTION:
A macro that provides a convenient method of refering to the error
message returned by Pro*C. This macro also uses sqlca.sqlerrm.sqlerrml
to correctly null terminate the string, then returns the address of
the first character of the string. Thus SQLMSG can be used as is
as the arguments to functions such as puts(), printf(), and strcpy().
ARGUMENTS:
None.
RETURN VALUE:
The address of sqlca.sqlerrm.sqlerrmc.
SEE ALSO:
SQLCODE, VARTERM().
EXAMPLE:
See example for SQLCODE.
------------------------------------------------------------------------
VARGET
NAME:
VARGET() - Get varchar data into a C style string variable.
SYNOPSIS:
#include <string.h>
#include "sqltools.h"
char *VARGET(char *destination, varchar source);
DESCRIPTION:
The VARGET macro copies the content of source.arr to the address
pointed to by destination, then null terminates the destination
string. The address of the string is the result of the entire
expression.
ARGUMENTS:
char *destination - Address of a C style string
varchar source - An Oracle varchar variable of any size.
RETURN VALUE:
The address of the destination string.
SEE ALSO:
VARTERM(), VARSET()
EXAMPLE:
char string[12];
EXEC SQL BEGIN DECLARE SECTION;
varchar today[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL
SELECT SYSDATE
INTO :today
FROM DUAL;
if ( SQLCODE == SQL_OK )
printf( "Today's system date is: %s\n", VARGET(string,today) );
else {
sql_log( LOGERR, "SQLTOOLS", "DEMO", "ORACLE(tm) Error: %s\n",
SQLMSG );
exit(1)
}
exit(0);
------------------------------------------------------------------------
VARINIT
NAME:
VARINIT() - Initialize a varchar variable.
SYNOPSIS:
#include <string.h>
#include "sqltools.h"
VARINIT( varchar var );
DESCRIPTION:
The VARINIT() macro initializes all elements of var.arr to binary
zeros and sets var.len equal to sizeof(var.arr)-1. This macro is
useful for initalizing VARCHAR variables used as OUT parameters in
stored procedures and functions.
ARGUMENT:
The VARCHAR variable to initialize.
RETURN VALUE:
None.
------------------------------------------------------------------------
VARSET
NAME:
VARSET() - Copies C style string variable to varchar variable.
SYNOPSIS:
#include <string.h>
#include "sqltools.h"
char *VARSET( varchar destination, char *source );
DESCRIPTION:
The VARSET macro copies the content of the address pointed to by
source to destination.arr and sets destination.len to the length
of the string stored in destination.arr. This macro will never
copy more characters than can fit into the destination array and
automatically null terminates the destination array.
ARGUMENTS:
varchar destination - The destination varchar variable.
char *source - The address of the source C string.
RETURN VALUE:
The address of destination.arr cast as character pointer.
SEE ALSO:
VARTERM, VARGET.
EXAMPLE:
NAME:
VARTERM() - Null terminates a varchar variable.
SYNOPSIS:
#include <string.h> /* For strncpy() */
#include "sqltools.h"
char *VARTERM(varchar var);
DESCRIPTION:
The VARTERM macro uses var.len to correctly null terminate var.arr,
then return the address of var.arr. Thus, VARTERM can be used as is
as the argument to functions such as printf() and strcpy().
ARGUMENTS:
varchar var - A varchar variable of any size.
RETURN VALUE:
The address of var.arr cast as a character pointer.
SEE ALSO:
SQLMSG, VARGET(), VARSET().
EXAMPLE:
See the example for SQLCHECK().
------------------------------------------------------------------------
VARTRIM
NAME:
VARTRIM() - Copies C style string variable to varchar variable and
right trims white space characters.
SYNOPSIS:
#include <string.h>
#include "sqltools.h"
char *VARTRIM( varchar destination, char *source );
DESCRIPTION:
The VARTRIM macro works exactly like the VARSET macro in its copy
operation but also trims white space characters from the right side
of the the varchar variable.
ARGUMENTS:
varchar destination - The destination varchar variable.
char *source - The address of the source C string.
RETURN VALUE:
The address of destination.arr cast as character pointer.
SEE ALSO:
VARTERM, VARGET, VARSET.
EXAMPLE:
EXEC SQL BEGIN DECLARE SECTION;
varchar myvar[100];
EXEC SQL END DECLARE SECTION;
char *workptr;
workptr = VARTRIM( myvar, "This string will be right trimmed " );
printf( "|%s|\n", workptr );
------------------------------------------------------------------------
sql_check()
NAME:
sql_check() - Simplify SQL error checking.
SYNOPSIS:
#include "sqltools.h"
int sql_check(int mask, int sqlcode, char *sqlerrmc, char *process );
DESCRIPTION:
The sql_check() function simplifies the process of checking SQL
codes returned to Pro*C. It also allows masking out a set of
return codes which are considered recoverable, but this feature
should be rarely used.
ARGUMENTS:
int mask - A bit mask value composed one or more values
logically ORed together. Each bit value set is
meant to ignore certain sqlcode error values which
are considered recoverable. The possible values
are:
SQL_OK ( 0 )
SQL_NOT_FOUND_OK 0x0001
SQL_INSRT_DPLCT_OK 0x0002
SQL_LOCK_FAIL_OK 0x0004
SQL_INSRT_NULL_OK 0x0008
SQL_TOO_LARGE_OK 0x0010
SQL_FETCH_NULL_OK 0x0020
SQL_FETCH_TRUNC_OK 0x0040
SQL_UPDAT_NULL_OK 0x0080
SQL_NMRIC_OVRFL_OK 0x0100
SQL_INVLD_MONTH_OK 0x0200
SQL_INVLD_DAY_OK 0x0400
SQL_CHECK_CNSTR_OK 0x0800
SQL_RFRNL_INGRT_OK 0x1000
SQL_RI_DELTE_OK 0x2000
int sqlcode - The code value returned from SQL in sqlca.sqlcode.
char *sqlerrmc - The string returned from SQL in
sqlca.sqlerrm.sqlerrmc.
char *process - Process identifier. Used for logging serious errors.
RETURN VALUE:
An integer value with the following meanings:
SQL_SUCCESS - This value is returned when the SQL statement was
successful or a recoverable error was masked.
SQL_FAILURE - This value is returned when a recoverable error occured.
SQL_FATAL - This value is returned when something is seriously
wrong with the database.
SEE ALSO:
SQLCHECK(), SQLCODE, SQLMSG.
EXAMPLE:
See example for SQLCHECK().
------------------------------------------------------------------------
sql_commit()
NAME:
sql_commit() - Commit a database transaction.
SYNOPSIS:
#include "sqltools.h"
int sql_commit( char *process );
DESCRIPTION:
The sql_commit() function performs an explicit database commit.
ARGUMENTS:
char *process - Process identifier string. Used to call sql_log().
RETURN VALUE:
An integer value, either SQL_SUCCESS or SQL_FAILURE.
SEE ALSO:
sql_rollback(), sql_release().
EXAMPLE:
EXEC SQL BEGIN DECLARE SECTION;
varchar vardate[10];
EXEC SQL END DECLARE SECTION;
VARSET( vardate, "26-Jul-96" );
EXEC SQL
UPDATE EMP
SET HIREDATE = :vardate;
if ( SQLCODE == SQL_OK )
sql_commit();
else {
rv = SQL_FAILURE;
sql_log( LOGERR, "SQLTOOLS", "DEMO",
"Could not update Employees hire date.\n" );
}
------------------------------------------------------------------------
sql_fatal_log()
NAME:
sql_fatal_log() - Logs program activity and error messages to a
flat file.
SYNOPSIS:
#include "sqltools.h"
int sql_fatal_log(int type,char *process,char *filter,char *format,
... );
DESCRIPTION:
The sql_fatal_log() function allows logging of messages to a text
file on the host file system in a manner similar to using the C
printf() family of functions. The file (and consequently the
filesystem)
which the message is logged is determined by the value of the FATAL_LOG
environment varible. This value should include the complete path to
the file where messages are to be written. If there is no value for
FATAL_LOG the messages are written to a file named "fatal.log" in the
current filesystem (directory). This function should only be used
when there is some reason which precludes logging messages to the
database with sql_log(). In the event that sql_log() fails to insert
a message into the database sql_fatal_log() will be called logging
the original message along with a message logging the failed insert
of the message. All messages in the text file are date and time
stamped
in the format: CCYY/MM/DD HH:MM:SS. The arguments of sql_fatal_log()
are exactly the same as sql_log(). However, when the message is
written
to the fatal log file the type code is transformed into a readable
string of "LOGACT", "LOGMSG", or "LOGERR". After each message is
successfully written to the fatal log the file buffers are flushed and
the file is closed. Also, each message in the flat file is terminated
with a newline character this it is not necessary to add newline
characters when using sql_fatal_log().
NOTE: It is not necessary to connect to the database to make calls to
sql_fatal_log().
ARGUMENTS:
See sql_log()
RETURN VALUE:
An integer value either SQL_SUCCESS or SQL_FAILURE. SQL_FAILURE
will be returned when a message cannot be written to the fatal
log.
EXAMPLE:
#include <stdlib.h>
#include "sqltools.h"
int main( void ) {
char format[] = "This message logged as %s";
sql_fatal_log( LOGACT, "SQLTOOLS", "DEMO", format, "An
activity." );
sql_fatal_log( LOGMSG, "SQLTOOLS", "DEMO", format, "A message." );
sql_fatal_log( LOGERR, "SQLTOOLS", "DEMO", format, "An error." );
return SQL_SUCCESS;
}
------------------------------------------------------------------------
sql_connect()
NAME:
sql_connect() - Connects a Pro*C program to the database.
SYNOPSIS:
#include "sqltools.h"
int sql_connect(char *process, char *login, char *passwd, char
*instance);
DESCRIPTION:
The sql_connect() function connects a PRO*C program to Oracle
database using a login/password and possibly a database instance.
ARGUMENTS:
char *process - The process identification string.
char *login - Address of a string containing a valid Oracle(tm)
login
id.
char *passwd - Address of a string containing a valid Oracle(tm)
password for the login id.
char *instance - Address of a string containing a valid Oracle(tm)
instance identifier. This argument may be omitted by
using a NULL pointer. When this is done Oracle(tm)
will use the value of the ORACLE_SID environment
variable.
RETURN VALUE:
An integer value, either SQL_SUCCESS or SQL_FAILURE.
SEE ALSO:
sql_release().
EXAMPLE:
#include <stdlib.h>
#include "sqltools.h"
int main( void ) {
int rv;
if ((rv=sql_connect("demo","scott", "tiger", NULL )) ==
SQL_FAILURE )
sql_fatal_log(LOGERR,"SQLTOOLS","DEMO",
"%s/%s Unable to connect to Oracle",
"scott", "tiger");
else {
sql_log(LOGACT, "SQLTOOLS", "DEMO",
"%s connected to Oracle.\n", "scott/tiger" );
sql_release();
}
return rv;
}
------------------------------------------------------------------------
sql_log()
NAME:
sql_log() - Logs program activity and error messages.
SYNOPSIS:
#include "sqltools.h"
int sql_log( int type, char *process, char *filter, char *format,
... );
DESCRIPTION:
The sql_log() function allows logging of messages to the database
which is similar to using the standard C printf() family of functions.
Furthermore, the messages are logged in such a manner that each
message has a type: an activity message, an informational message, or
an error message. Also, messages are logged so that they can be
identified by the process that generated them with an additional
filter code to identify classes of messages within a process. When
an error occurs inserting a message into the MSGLOG table sql_log()
resorts to calling the sql_fatal_log() function. In this case an
addition error message is generated by sql_log() identifying the
reason the insert failed. The message is logged using the same
process code, as that of the calling program used. The log type
is LOGERR, and the filter code is "SQLLOG". For more information
see the documentation for sql_fatal_log().
NOTE: Messages are inserted into the MSGLOG table by calling the
stored procedure LOG_MESSAGE.
ARGUMENTS:
int logtype - This is one of three values defined in sqltools.h
The symbolic name for these values and their meaning
are as follows:
LOGACT: This value is used to log activity type
messages.
Messages appropriate for this type are would those that
indicate program startup and completion along with an
indication of the program's success or failure.
LOGMSG: This value is used to log informational
messages.
concerning the program processing. Such messages might
include the number of records processed, rejected, and
updated, or any useful information that documents the
processing accomplished by the program.
LOGERR: This value is used to log error messages. This
type of message is reserved for logging unexpected
exceptions such as inability to open, read, or write to
or from a file, or inability to manipulate data in the
database.
char *process - A string identifying the process which generated the
message.
char *filter - A string used to classify messages within type and
process.
char *format - A printf() style format string
RETURN VALUE:
An integer value either SQL_SUCCESS or SQL_FAILURE. SQL_FAILURE
will be returned when a message cannot be written to the activity
log. When this happens the message will be written to the error log
using sql_fatal_log()
EXAMPLE:
#include <stdlib.h>
#include "sqltools.h"
int main( void ) {
char format[] = "This message logged as %s";
sql_connect( "scott", "tiger", NULL );
sql_log( LOGACT, "SQLTOOLS", "DEMO", format, "An activity." );
sql_log( LOGMSG, "SQLTOOLS", "DEMO", format, "A message." );
sql_log( LOGERR, "SQLTOOLS", "DEMO", format, "An error." );
sql_commit();
return SQL_SUCCESS;
}
------------------------------------------------------------------------
sql_release
NAME:
sql_release() - Commit transaction and release database resource.
SYNOPSIS:
#include "sqltools.h"
int sql_release ( char *process );
DESCRIPTION:
The sql_release() function commits any uncommited updates to the
database then releases the database resources. When uncommitted
updates are not desired the sql_rollback() function should be
called prior to calling sql_release().
ARGUMENTS:
char *process - A process identification string.
RETURN VALUE:
An integer value, SQL_SUCCESS or SQL_FAILURE.
SEE ALSO:
sql_connect(), sql_rollback().
------------------------------------------------------------------------
sql_rollback()
NAME:
sql_rollback() - Roll back uncommitted database updates.
SYNOPSIS:
#include "sqltools.h"
int sql_rollback( char *process ) ;
DESCRIPTION:
The sql_rollback() function will roll back any uncommitted database
updates to the last commit point.
ARGUMENTS:
char *process - A process identification string.
RETURN VALUE:
An integer value, SQL_SUCCESS or SQL_FAILURE.
SEE ALSO:
sql_release(), sql_commit().
EXAMPLE:
EXEC SQL FETCH get_sku
INTO :l_sku,
:l_gold_base:l_gold_base_indct,
:l_gold_base_cost:l_gold_base_cost_indct,
:l_gold_incr:l_gold_incr_indct;
switch( SQLCHECK( SQL_OK, "SQLTOOLS" ) ) {
case EXIT_FATAL :
sql_rollback("SQLTOOLS");
rv = SQL_FAILURE;
sql_log( LOGERR, "SQLTOOLS", "DEMO", message, SQLMSG );
case SQL_FAILURE :
if ( SQLCODE != SQL_NOT_FOUND ) {
sql_rollback("SQLTOOLS");
rv = SQL_FAILURE;
sql_log(LOGERR, "SQLTOOLS", "DEMO",
"An unexpected condition occured "
"when reading from the GALE_WIN_"
"SKU cursor. Oracle Message: %s\n",
SQLMSG );
}
}
------------------------------------------------------------------------
sql_rollback_to_savepoint()
NAME:
sql_rollback_to_savepoint() - Rolls back transactions to a specific
savepoint.
SYNOPSIS:
#include "sqltools.h"
int sql_rollback_to_savepoint( char *process, char *savepoint );
DESCRIPTION:
The sql_rollback_to_savepoint() function rolls back an Oracle
transaction to a specific savepoint set with the sql_savepoint()
function, or using EXEC SQL SAVEPOINT. For more information
concerning Oracle transaction processing and savepoints see the
"The Programmer's Guide to the Oracle Pro*C Precompiler", Chapter 7,
"Defining and Controlling Transactions".
ARGUMENTS:
char *process - A process identification string.
char *savepoint - Address of a string with the savepoint
identifier.
RETURN VALUE:
SQL_SUCCESS or SQL_FAILURE;
SEE ALSO:
sql_savepoint(), sql_commit(), sql_rollback().
------------------------------------------------------------------------
sql_savepoint()
NAME:
sql_savepoint() - Apply a savepoint identifier to an Oracle
transaction.
SYNOPSIS:
#include "sqltools"
int sql_savepoint( char *process, char *savepoint );
DESCRIPTION:
The sql_savepoint() function applies a savepoint identifier to an
Oracle transaction. Later, if deemed neccesary, a program can
rollback all updates to the database back to the savepoint.
For more information concerning Oracle transaction processing
and savepoints see the "The Programmer's Guide to the Oracle
Pro*C Precompiler", Chapter 7, "Defining and Controlling
Transactions".
ARGUMENTS:
char *process - The process identification string.
char *savepoint - Address of a string containing the savepoint
identifier.
RETURN VALUE:
SQL_SUCCESS or SQL_FAILURE.
SEE ALSO:
sql_savepoint(), sql_commit(), sql_rollback().
Received on Mon Feb 21 2005 - 08:22:24 CST
 |
 |