Oracle SQL functions

In SQL statements, you can use any function that Oracle supports (such as aggregate or mathematical functions).

For example, you can use the Oracle function UPPER in a SELECT statement:

SELECT UPPER(emp_name)
	INTO :emp_name_var
	FROM employee;

While PowerBuilder provides access to a large percentage of the features within Oracle, in some cases you may want to call one or more OCI functions directly. In PowerBuilder you can use external function declarations to access any Windows DLL.

The OCI calls qualify for this type of access. Most OCI calls require a pointer to an LDA_DEF structure as their first parameter. If you want to call OCI functions without reconnecting to the database to get an LDA_DEF pointer, use the PowerScript DBHandle function.

DBHandle takes a transaction object as a parameter and returns a long variable, which is the handle to the database for the transaction. This handle is actually the LDA_DEF pointer that PowerBuilder uses internally to communicate with the database. You can use the returned value in your DLLs and pass it as one of the parameters in your function.

This example shows how to use DBHandle. Assume a successful connection has occurred using the default transaction object (SQLCA):

// Define a variable to hold our DB handle.
long    OracleHandle

// Get the handle.
OracleHandle = SQLCA.DBHandle( )

// Now that you have the LDA_DEF pointer,
// call the DLL function.
MyDLLFunction( OracleHandle, parm1, parm2, ... )

In your DLL, cast the incoming long value into a pointer to an ORA_CSA:

VOID FAR PASCAL MyDLLFunction( long lOracleHandle,
	parm1_type parm1,
	parm2_type parm2, ... )
{

// pLda will provide addressability to the Oracle
// logon data area
Lda_Def FAR *pLda = (Lda_Def FAR *)lOracleHandle;

// pCda will point to an Oracle cursor
Cda_Def FAR *pCda = &
		GlobalAllocPtr(GMEM_MOVEABLE,sizeof(Cda_Def));
if(! pCda )

// handle error...
if(open(pCda, pLda,NULL, -1, -1, NULL, -1))

// handle error...
#ifdef Oracle7

// parse the DELETE statement
if(osql3(pCda, 
	"DELETE FROM EMPLOYEE WHERE Emp_ID = 100", -1);

#else
if(oparse(pCda,
	"DELETE FROM EMPLOYEE 
		WHERE Emp_ID = 100", -1, 0, 1) :
#endif

// handle error...
	if(oclose(pCda))

// handle error...
	GlobalFreePtr(pCda);
}