Dear Experts !!!
I have written a simple C program which I am able to call through Oracle Database as a PL/SQL function (using External Procedures). It works fine.
I am stuck with Pro*C program. This program basically inserts data into one of the tables when invoked. I am unable to create a shared library for this Pro*C program.
This is how I create a shared library for a simple C program:
vm.c
====
Code:
#include <stdio.h>
char* vm()
{
FILE *fp;
char line[130];
fp = popen("vmstat 1 1 |awk '{print $4}'|tail -1", "r");
while ( fgets( line, sizeof line, fp))
pclose(fp);
return(line);
}
cc -q64 -G vm.c -o vm.so
Following is the Pro*C program and the steps I follow
Pro*C code:
Code:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA; /* SQL Communications Area */
#define UNAME_LEN 30 /* VARCHAR limits */
#define PWD_LEN 30
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[UNAME_LEN]; /* Connection Username */
VARCHAR password[PWD_LEN]; /* Connection Password */
EXEC SQL END DECLARE SECTION;
/* ------------------------------------------------------------------------- */
void
sql_error (char *msg)
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n",msg);
printf("%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(1);
} /* sql_error() */
/* ------------------------------------------------------------------------- */
void sample (int argc, char **argv)
{
int tab_count;
char c_username[120];
char l_temp[1000];
char l_rem[500];
char l_sql[500];
int l_occurrence;
int i;
int l_run;
int l_width = 5;
strcpy(c_username, argv[1]);
EXEC SQL WHENEVER SQLERROR DO sql_error("Connection Failure!");
EXEC SQL CONNECT :c_username;
EXEC SQL DELETE FROM PSVG_LOG;
FILE *fp;
char line[500];
fp = popen("ps vg|awk '{printf \"insert into psvg_log values (1, \" $1 \",\" $7 \",\" $10 \",sysdate - mod(to_char(sysdate,%cMI%c), 10) /(
24*60) - to_char(sysdate,%cSS%c) /(24*60*60));\",39,39,39,39}'", "r");
EXEC SQL SELECT 'NULL;' INTO :l_rem FROM DUAL;
while ( fgets( line, sizeof line, fp))
{
strcpy(l_temp, line);
//printf("\n width : %d", l_width);
//printf("\n\n ORG LINE : %s", l_temp);
EXEC SQL SELECT rpad(trim(:l_rem), :l_width, ' ') || :l_temp INTO :l_temp FROM DUAL;
EXEC SQL SELECT length(regexp_replace(:l_temp,'[^;]')) into :l_occurrence FROM DUAL;
//printf("\n\n temp line : %s", l_temp);
//printf("\n\n Occurrence : %d", l_occurrence);
for(i=0; i<l_occurrence; i++)
{
EXEC SQL SELECT substr(:l_temp, 1, instr(:l_temp, ';') -1) into :l_sql FROM DUAL;
EXEC SQL SELECT decode(substr(trim(:l_sql), 1, 43), 'NULL', 0, 'insert into psvg_log values (1, PID,RSS,TRS', 0, 1) into :l_run FROM
DUAL;
// printf("\n run = %d sql =%s", l_run, l_sql);
if (l_run == 1)
EXEC SQL EXECUTE IMMEDIATE :l_sql;
EXEC SQL SELECT substr(:l_temp, instr(:l_temp, ';') + 1) into :l_temp FROM DUAL;
}
EXEC SQL SELECT :l_temp INTO :l_rem FROM DUAL;
EXEC SQL SELECT length(:l_rem) INTO tab_count FROM DUAL;
EXEC SQL SELECT 500 - instr(:line, ';', -1) -1 into :l_width FROM DUAL;
//printf("\n REMAINING %d %s", tab_count, l_rem);
// EXEC SQL SELECT substr(:line, 1, 10) into :l_line FROM DUAL;
// printf("%s\n\n\n", l_line);
}
pclose(fp);
// EXEC SQL SELECT COUNT(*) INTO :tab_count FROM psvg_log;
// printf("\n\n Table Count : %d\n\n", tab_count);
EXEC SQL delete from psvg_log where dt = sysdate - mod(to_char(sysdate,'MI'), 10) /(24*60) - to_char(sysdate,'SS') /(24*60*60)
and pid in (select spid from v$process);
EXEC SQL COMMIT;
EXEC SQL WHENEVER SQLERROR DO sql_error("Disconnection Failure!");
EXEC SQL COMMIT WORK RELEASE;
// return(0);
} /* main() */
Step 1:
proc iname=working.pc oname=working.c
Step 2:
xlc -q64 -I$ORACLE_HOME/precomp/public -L$ORACLE_HOME/lib working.c -o working.so -lclntsh -lsql10
Step 3:
sample user99/user99@testdb
Following are the steps I am performing in SQL*Plus:
Code:
SQL> create or replace library sample_lib as '/u3cms/c/working.so';
2 /
Library created.
SQL> create or replace procedure working
2 as language C
3 library sample_lib
4 name "main";
5 /
Procedure created.
SQL> exec working
BEGIN working; END;
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06522: Function not implemented (main)
ORA-06512: at "USER99.WORKING", line 1
ORA-06512: at line 1
I thinks while creating a shared library for a Pro*C program, I need to include couple of Oracle libraries, but I am unable to figure out which one's and how to do that.
Any help in this direction would be helpful.
Regards