Oracle NUMBER manipulation (optional)

Detailed Description

OcilibCApiLongs

OCILIB encapsulates Oracle SQL all Numeric types using C native data types. But it also provides an optional OCI_Number handle for manipulating and accessing Oracle NUMBER type. OCI_Number provides management for some special value that cannot be addressed in C such as positive and negative infinity.

Example

#include "ocilib.h"
 
/* requires script demo/number.sql */
 
void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}
 
int main(void)
{
    OCI_Connection *cn = NULL;
    OCI_Statement  *st = NULL;
    OCI_Resultset  *rs = NULL;
    OCI_TypeInfo   *tc = NULL;
    OCI_TypeInfo   *to = NULL;
    OCI_Number     *nm = NULL;
    OCI_Coll       *cl = NULL;
    OCI_Elem       *el = NULL;
    OCI_Object     *ob = NULL;
    OCI_Number     *no = NULL;
    OCI_Number     *nt = NULL;
    OCI_Number    **ar = NULL;
 
    unsigned int i, n;
    otext buf[128] = "";
 
    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);
 
    tc = OCI_TypeInfoGet(cn, "test_num_coll_t", OCI_TIF_TYPE);
    to = OCI_TypeInfoGet(cn, "test_num_t", OCI_TIF_TYPE);
    cl = OCI_CollCreate(tc);
    ob = OCI_ObjectCreate(cn, to);
    el = OCI_ElemCreate(tc);
    nm = OCI_NumberCreate(cn);
    ar = OCI_NumberArrayCreate(cn, 5);
 
    // Testing fetching numbers (as number and using implicit conversion number to string)
    OCI_Prepare(st, "select value from test_number");
    OCI_Execute(st);
    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs))
    {
        OCI_Number  *n = OCI_GetNumber(rs, 1);
        const otext *s = OCI_GetString(rs, 1);
 
        OCI_NumberToText(n, NULL, sizeof(buf), buf);
 
        printf("%s - %s\n", buf, s);
    }
 
    // Testing binding external single number
    OCI_Prepare(st, "begin :1 := :1 *2 ; end;");
    OCI_BindNumber(st, ":1", nm);
    OCI_NumberFromText(nm, "1234.4321", NULL);
    OCI_Execute(st);
    OCI_NumberToText(nm, NULL, sizeof(buf), buf);
    printf("%s\n", buf);
 
    // Testing binding internal single number
    OCI_Prepare(st, "begin :1 := :1 *2 ; end;");
    OCI_SetBindAllocation(st, OCI_BAM_INTERNAL);
    OCI_BindNumber(st, ":1", NULL);
    nt = OCI_BindGetData(OCI_GetBind(st, 1));
    OCI_NumberFromText(nt, "1234.4321", NULL);
    OCI_Execute(st);
    OCI_NumberToText(nt, NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_SetBindAllocation(st, OCI_BAM_EXTERNAL);
 
    // Testing registering number for a returning into statement
    OCI_Prepare(st, "update test_number set value  = value *2 returning value into :1");
    OCI_RegisterNumber(st, ":1");
    OCI_Execute(st);
    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs))
    {
        printf("%s\n", OCI_GetString(rs, 1));
    }
 
    // testing Collections
    OCI_NumberFromText(nm, "1111.2222", NULL);
    OCI_ElemSetNumber(el, nm);
    OCI_CollAppend(cl, el);
    OCI_NumberFromText(nm, "3333.4444", NULL);
    OCI_ElemSetNumber(el, nm);
    OCI_CollAppend(cl, el);
    printf("%d\n", OCI_CollGetCount(cl));
 
    for (i = 1, n = OCI_CollGetCount(cl); i <= n; i++)
    {
        OCI_Elem   *e = OCI_CollGetElem(cl, i);
        OCI_Number *n = OCI_ElemGetNumber(e);
        OCI_NumberToText(n, NULL, sizeof(buf), buf);
        printf("%s\n", buf);
    }
 
    // Testing objects
    OCI_NumberFromText(nm, "5555.6666", NULL);
    OCI_NumberToText(nm, NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_ObjectSetNumber(ob, "value", nm);
    no = OCI_ObjectGetNumber(ob, "value");
    OCI_NumberToText(no, NULL, sizeof(buf), buf);
    printf("%s\n", buf);
 
    // Testing fetching infinite values to string
    OCI_Prepare(st, "SELECT utl_raw.cast_to_number('FF65'), utl_raw.cast_to_number('00') from dual");
    OCI_Execute(st);
    rs = OCI_GetResultset(st);
    while (OCI_FetchNext(rs))
    {
        printf("pos infinite = [%s]\n", OCI_GetString(rs, 1));
        printf("neg infinite = [%s]\n", OCI_GetString(rs, 2));
    }
 
    // Testing array of numbers
    OCI_NumberFromText(ar[0], "1.2", NULL);
    OCI_NumberFromText(ar[1], "~", NULL);
    OCI_NumberFromText(ar[2], "5.6", NULL);
    OCI_NumberFromText(ar[3], "-~", NULL);
    OCI_NumberFromText(ar[4], "9.2", NULL);
 
    OCI_NumberToText(ar[0], NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_NumberToText(ar[1], NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_NumberToText(ar[2], NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_NumberToText(ar[3], NULL, sizeof(buf), buf);
    printf("%s\n", buf);
    OCI_NumberToText(ar[4], NULL, sizeof(buf), buf);
    printf("%s\n", buf);
 
    OCI_NumberArrayFree(ar);
    OCI_NumberFree(nm);
    OCI_ElemFree(el);
    OCI_CollFree(cl);
    OCI_ObjectFree(ob);
 
    OCI_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}

Functions
OCI_SYM_PUBLIC OCI_Number *OCI_API	OCI_NumberCreate (OCI_Connection *con)
	Create a local number object.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberFree (OCI_Number *number)
	Free a number object.
OCI_SYM_PUBLIC OCI_Number **OCI_API	OCI_NumberArrayCreate (OCI_Connection *con, unsigned int nbelem)
	Create an array of number object.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberArrayFree (OCI_Number **numbers)
	Free an array of number objects.
OCI_SYM_PUBLIC int OCI_API	OCI_NumberAssign (OCI_Number *number, OCI_Number *number_src)
	Assign the value of a number handle to another one.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberToText (OCI_Number *number, const otext *fmt, int size, otext *str)
	Convert a number value from the given number handle to a string.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberFromText (OCI_Number *number, const otext *str, const otext *fmt)
	Convert a string to a number and store it in the given number handle.
OCI_SYM_PUBLIC unsigned char *OCI_API	OCI_NumberGetContent (OCI_Number *number)
	Return the number value content.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberSetContent (OCI_Number *number, unsigned char *content)
	Assign the number value content.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberSetValue (OCI_Number *number, unsigned int type, void *value)
	Assign the number value with the value of a native C numeric type.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberGetValue (OCI_Number *number, unsigned int type, void *value)
	Assign the number value to a native C numeric type.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberAdd (OCI_Number *number, unsigned int type, void *value)
	Add the value of a native C numeric type to the given number.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberSub (OCI_Number *number, unsigned int type, void *value)
	Subtract the value of a native C numeric type to the given number.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberMultiply (OCI_Number *number, unsigned int type, void *value)
	Multiply the given number with the value of a native C numeric.
OCI_SYM_PUBLIC boolean OCI_API	OCI_NumberDivide (OCI_Number *number, unsigned int type, void *value)
	Divide the given number with the value of a native C numeric.
OCI_SYM_PUBLIC int OCI_API	OCI_NumberCompare (OCI_Number *number1, OCI_Number *number2)
	Compares two number handles.

Function Documentation

OCI_NumberCreate()

OCI_SYM_PUBLIC OCI_Number *OCI_API OCI_NumberCreate

(

OCI_Connection *

con

)

#include <api.h>

Create a local number object.

Parameters

con	- Connection handle

Note

Parameter 'con' can be NULL in order to manipulate numbers independently from database connections

Returns

Return the number handle on success otherwise NULL on failure

OCI_NumberFree()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberFree

(

OCI_Number *

number

)

#include <api.h>

Free a number object.

Parameters

number

- Number handle

Warning

Only Numbers created with OCI_NumberCreate() should be freed by OCI_NumberFree()

Returns

TRUE on success otherwise FALSE

OCI_NumberArrayCreate()

OCI_SYM_PUBLIC OCI_Number **OCI_API OCI_NumberArrayCreate	(	OCI_Connection *	con,
		unsigned int	nbelem
	)

#include <api.h>

Create an array of number object.

Parameters

con	- Connection handle
nbelem	- number of elements in the array

Note

see OCI_NumberCreate() for more details

Returns

Return the number handle array on success otherwise NULL on failure

OCI_NumberArrayFree()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberArrayFree

(

OCI_Number **

numbers

)

#include <api.h>

Free an array of number objects.

Parameters

numbers

- Array of number objects

Warning

Only arrays of numbers created with OCI_NumberArrayCreate() should be freed by OCI_NumberArrayFree()

Returns

TRUE on success otherwise FALSE

OCI_NumberAssign()

OCI_SYM_PUBLIC int OCI_API OCI_NumberAssign	(	OCI_Number *	number,
		OCI_Number *	number_src
	)

#include <api.h>

Assign the value of a number handle to another one.

Parameters

number	- Destination number handle
number_src	- Source number handle

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Number::Clone().

OCI_NumberToText()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberToText	(	OCI_Number *	number,
		const otext *	fmt,
		int	size,
		otext *	str
	)

#include <api.h>

Convert a number value from the given number handle to a string.

Parameters

number	- source number handle
fmt	- Number format
size	- Destination string size in characters
str	- Destination date string

Note

Output string can be one the following 'magic strings':

• '~' for positive infinity
• '-~' for negative infinity

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Number::ToString().

OCI_NumberFromText()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberFromText	(	OCI_Number *	number,
		const otext *	str,
		const otext *	fmt
	)

#include <api.h>

Convert a string to a number and store it in the given number handle.

Parameters

number	- Destination number handle
str	- Source number string
fmt	- Number format

Note

Input string can be one the following 'magic strings':

• '~' for positive infinity
• '-~' for negative infinity

Returns

TRUE on success otherwise FALSE

Referenced by ocilib::Number::FromString().

OCI_NumberGetContent()

OCI_SYM_PUBLIC unsigned char *OCI_API OCI_NumberGetContent

(

OCI_Number *

number

)

#include <api.h>

Return the number value content.

Parameters

number

- number handle

Note

Returned content is a buffer of 22 bytes corresponding to Oracle C native representation of NUMBER values See oracle Documentation of its layout

Returns

TRUE on success otherwise FALSE

OCI_NumberSetContent()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberSetContent	(	OCI_Number *	number,
		unsigned char *	content
	)

#include <api.h>

Assign the number value content.

Parameters

number	- number handle
content	- raw number content

Note

See OCI_NumberSetContent() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberSetValue()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberSetValue	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Assign the number value with the value of a native C numeric type.

Parameters

number	- number handle
type	- native C type to assign
value	- pointer to value to set

Note

Argument param type can be :

• OCI_NUM_SHORT : value is a pointer to a signed short
• OCI_NUM_USHORT : value is a pointer to an unsigned short
• OCI_NUM_INT : value is a pointer to a signed int
• OCI_NUM_UINT : value is a pointer to an unsigned short
• OCI_NUM_BIGINT : value is a pointer to a signed big_int
• OCI_NUM_BIGUINT : value is a pointer to an unsigned big_uint
• OCI_NUM_FLOAT : value is a pointer to an float
• OCI_NUM_DOUBLE : value is a pointer to a double
• OCI_NUM_NUMBER : value is a pointer to the return value of OCI_NumberGetContent()

Returns

TRUE on success otherwise FALSE

OCI_NumberGetValue()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberGetValue	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Assign the number value to a native C numeric type.

Parameters

number	- number handle
type	- native C type to assign
value	- pointer to a native C variable

Note

See OCI_NumberSetValue() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberAdd()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberAdd	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Add the value of a native C numeric type to the given number.

Parameters

number	- number handle
type	- native C type of the variable
value	- pointer to a native C variable to add

Note

See OCI_NumberSetValue() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberSub()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberSub	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Subtract the value of a native C numeric type to the given number.

Parameters

number	- number handle
type	- native C type of the variable
value	- pointer to a native C variable to subtract

Note

See OCI_NumberSetValue() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberMultiply()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberMultiply	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Multiply the given number with the value of a native C numeric.

Parameters

number	- number handle
type	- native C type of the variable
value	- pointer to a native C variable to multiply by

Note

See OCI_NumberSetValue() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberDivide()

OCI_SYM_PUBLIC boolean OCI_API OCI_NumberDivide	(	OCI_Number *	number,
		unsigned int	type,
		void *	value
	)

#include <api.h>

Divide the given number with the value of a native C numeric.

Parameters

number	- number handle
type	- native C type of the variable
value	- pointer to a native C variable to divide by

Note

See OCI_NumberSetValue() for more information

Returns

TRUE on success otherwise FALSE

OCI_NumberCompare()

OCI_SYM_PUBLIC int OCI_API OCI_NumberCompare	(	OCI_Number *	number1,
		OCI_Number *	number2
	)

#include <api.h>

Compares two number handles.

Parameters

number1	- number1 handle
number2	- number2 handle

Returns

• -1 if number1 is smaller than number2,
• 0 if they are equal
• 1 if number1 is greater than number2.