Pro*C Compilation.

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&#37;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

Code:

char line[130]; 
fp = popen("vmstat 1 1 |awk '{print $4}'|tail -1", "r"); 
while ( fgets( line, sizeof line, fp))
pclose(fp);
return(line);

1. You're returning a pointer to a local variable. When the function returns, the variable goes out of scope, and with it goes the validity of the pointer.

Also, putting pclose() inside the scope of the while loop is somewhat dubious, even if you are just reading one line.

Thanks Salem for the tip.

Any solution to my real problem ?

The most common solution is that the caller passes in a buffer via a pointer that the function can fill.

Returning a pointer to a local wasn't a tip, it was a real problem.
Fix it and report.