Oracle also ships a Pro*C Precompiler that can convert a Pro*C source file to a C source file which can then be compiled using a C Compiler (e.g using "gcc"). Of course, you need the Pro*C Developer Licence to use this product.
Here is a quick demo with the command line display and then the actual code below.
oracle19c>ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rwxr--r--. 1 oracle oinstall 786 May 1 18:14 Compile_my_ProC.SH
-rwxr--r--. 1 oracle oinstall 356 May 1 18:15 Run_my_ProC.SH
oracle19c>./Compile_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set C_INCLUDE_PATH
*****PreCompile Pro*C program file
Pro*C/C++: Release 19.0.0.0.0 - Production on Sat May 1 18:15:17 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /opt/oracle/product/19c/dbhome_1/precomp/admin/pcscfg.cfg
*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so
*****Compiled files:
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rw-r--r--. 1 oracle oinstall 0 May 1 18:15 instancedbinfo.lis
-rw-r--r--. 1 oracle oinstall 11875 May 1 18:15 instancedbinfo.c
-rwxr-xr-x. 1 oracle oinstall 14424 May 1 18:15 instancedbinfo
oracle19c>
oracle19c>
oracle19c>
oracle19c>./Run_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set Connection String
*****Execute the program
Connected to ORACLE
At ORCLCDB which is on oracle-19c-vagrant running 19.0.0.0.0 and is OPEN, started at 01-MAY-21 17:54:52
This is ORCLPDB1 database running in READ WRITE mode since 01-MAY-21 05.55.21.573 PM +08:00
oracle19c>
The file "instancedbinfo.pc" is my Pro*C source code.
I Precompile it using the "proc" precompiler into "instancedbinfo.c". Any compilation errors would have been logged into "instancedbinfo.lis"
Then, the same script "Compile_my_ProC.SH" compiles the C program source code into an executable "instancedbinfo" using "gcc"
Finally, I use "Run_my_ProC.SH" to execute the file "instancedbinfo" (which is now an executable) and the execution displays information about the Pluggable database it is connected to.
Here is the code for the two shell scripts :
oracle19c>cat Compile_my_ProC.SH
echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH
echo "*****Set C_INCLUDE_PATH"
C_INCLUDE_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib:/opt/oracle/product/19c/dbhome_1/precomp/public
export C_INCLUDE_PATH
echo "*****PreCompile Pro*C program file"
proc instancedbinfo.pc
echo "*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so"
gcc instancedbinfo.c -o instancedbinfo -L /opt/oracle/product/19c/dbhome_1/lib -l clntsh
echo "*****Compiled files:"
ls -ltr instancedbinfo*
oracle19c>
oracle19c>cat Run_my_ProC.SH
echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH
echo "*****Set Connection String"
CNCTSTRING=hemant/hemant@orclpdb1
export CNCTSTRING
echo "*****Execute the program"
./instancedbinfo
oracle19c>
The Compilation script specifies the LD_LIBRARY_PATH and the Paths to the Include (.h Header) files.
It then executes "proc" (which is in $ORACLE_HOME/bin) to precompile the "instancedbinfo.pc" source file.
Finally, it calls "gcc" to compile the c-language source code file (generated by the Precomipler), also specifiying the client shared library file libclntsh.so in $ORACLE_HOME/lib (only "-l clntsh" is sufficient to identify the file name). The compiled executable is called "instancedbinfo" with Execute Permission.
The Run script specifies the Connect-String that the executable will be reading from the environment and executes it.
Here is the code of the source Pro*C file :
oracle19c>cat instancedbinfo.pc
/* standard C includes */
#include << stdio.h >>
#include << stdlib.h >>
#include << string.h >>
/* Oracle Pro*C includes from $ORACLE_HOME/precomp/public */
#include << sqlca.h >>
#include << sqlda.h >>
#include << sqlcpr.h >>
/* my variables */
varchar MYinstanceName[16];
varchar MYhostName[64];
varchar MYversion[17];
varchar MYstatus[12];
varchar MYinstanceStartupTime[18];
varchar MYdbName[128];
varchar MYdbOpenMode[10];
varchar MYdbOpenTime[32];
/* function for error handling */
void sql_error(msg)
char msg[200];
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
/* MAIIN program */
int main(argc,argv)
int argc;
char *argv[];
{
/* read Connection String from environment -- or, it could have been hardcoded here */
const char *conn = getenv("CNCTSTRING");
if (!conn) {
printf("! require CNCTSTRING env variable\n");
return (1);
}
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
/* connect to targe database */
EXEC SQL CONNECT :conn ;
printf("Connected to ORACLE \n");
/* execute query and populate variables */
/* NOTE : This expects to connect to a PDB ! */
/* If the target is a Non-PDB, change references from v$pdbs to V$database */
EXEC SQL SELECT instance_name,host_name, version,
to_char(startup_time,'DD-MON-RR HH24:MI:SS'), status,
name, open_mode, to_char(open_time)
INTO :MYinstanceName, :MYhostName, :MYversion,
:MYinstanceStartupTime, :MYstatus,
:MYdbName, :MYdbOpenMode, :MYdbOpenTime
FROM v$instance, v$pdbs ;
/* display query results */
printf("At %s which is on %s running %s and is %s, started at %s \n",
MYinstanceName.arr, MYhostName.arr, MYversion.arr, MYstatus.arr, MYinstanceStartupTime.arr);
printf("This is %s database running in %s mode since %s \n",
MYdbName.arr, MYdbOpenMode.arr, MYdbOpenTime.arr);
printf("\n");
/* end of MAIN */
}
oracle19c>
Pro*C allows embedding of SQL calls into a C program be including the Proc include files and then running the source code through a Precompiler.
My Pro*C source code file is 2,255 bytes and the C source code is 11,875 bytes.
Note that the variables defined as varchar in my Pro*C source file are actually become C structures :
/* my variables */
/* varchar MYinstanceName[16]; */
struct { unsigned short len; unsigned char arr[16]; } MYinstanceName;
/* varchar MYhostName[64]; */
struct { unsigned short len; unsigned char arr[64]; } MYhostName;
/* varchar MYversion[17]; */
struct { unsigned short len; unsigned char arr[17]; } MYversion;
/* varchar MYstatus[12]; */
struct { unsigned short len; unsigned char arr[12]; } MYstatus;
/* varchar MYinstanceStartupTime[18]; */
struct { unsigned short len; unsigned char arr[18]; } MYinstanceStartupTime;
/* varchar MYdbName[128]; */
struct { unsigned short len; unsigned char arr[128]; } MYdbName;
/* varchar MYdbOpenMode[10]; */
struct { unsigned short len; unsigned char arr[10]; } MYdbOpenMode;
/* varchar MYdbOpenTime[32]; */
struct { unsigned short len; unsigned char arr[32]; } MYdbOpenTime;
Similarly, my EXEC SQL query also gets re-written :
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 13;
sqlstm.arrsiz = 8;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select instance_name ,host_name ,version ,to_char(startup\
_time,'DD-MON-RR HH24:MI:SS') ,status ,name ,open_mode ,to_char(open_time) int\
o :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 from v$instance ,v$pdbs ";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.sqlpfmem = (unsigned int )0;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = (unsigned char *)&MYinstanceName;
sqlstm.sqhstl[0] = (unsigned long )18;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( short *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned long )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = (unsigned char *)&MYhostName;
and so on .....
Pro*C is a very good way of combining C programming with SQL and creating an executable binary instead of an interpreted file (like a Java or Python program outside the database).