Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Petroula.

Asked: August 24, 2000 - 1:10 pm UTC

Last updated: February 22, 2007 - 8:57 am UTC

Version: 8.1.6.0.0

Viewed 1000+ times

You Asked

Hello Tom

I am working with external procedures and I have a question regarding Pro*C and an error message that I get. My code is as follows:

#include<stdio.h>
#include<stdlib.h>
#include<ociextp.h>
#include<oratypes.h>
#include<string.h>
#include<oci.h>
#include<sqlca.h>
#include<ocikp.h>
#include<ocikp.h>
#include<ori.h>
#include<oro.h>
#include<ort.h>
#include<ociap.h>
#include<sqlcpr.h>
#include<assert.h>

EXEC SQL include sqlca;
EXEC SQL include sqlda;


void __declspec(dllexport) c_insert(OCIExtProcContext *ctx, int id, short id_i, char *name1, short *name1_i, int name1_l,
char *phone, short *phone_i, int *phone_l)
{

EXEC SQL REGISTER CONNECT USING :ctx;

EXEC SQL BEGIN DECLARE SECTION;
int id_n;
VARCHAR *name2;
VARCHAR *ph;
EXEC SQL END DECLARE SECTION;



if ((id_i==OCI_IND_NULL)||(*name1_i==OCI_IND_NULL))
{
if (OCIExtProcRaiseExcp(ctx,(int)6502)==OCIEXTPROC_SUCCESS)
{ return;}
else{assert(0);}
}
id_n=id;
strcpy(name2,name1);
strcpy(ph,phone);

EXEC SQL INSERT INTO PETROULA.PERSON
VALUES(:id_n, :name2, :ph);

return;
}

As you can see I have desperately tried to include every possible library in my program to get rid of my problem. The problem is as follows, when I precompile I get the following message:

++: Release 8.1.6.0.0 - Production on Thu Aug 24 11:48:43 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

System default option values taken from: E:\Oracle\Ora81\precomp\admin\pcscfg.cfg

Semantic error at line 25, column 40, file c:\petroula\c_insert.pc:
EXEC SQL REGISTER CONNECT USING :ctx;
.......................................1
PCC-S-02322, found undefined identifier
Semantic error at line 25, column 40, file c:\petroula\c_insert.pc:
EXEC SQL REGISTER CONNECT USING :ctx;
.......................................1
PCC-S-02336, host variable expression has invalid type
Semantic error at line 25, column 40, file c:\petroula\c_insert.pc:
EXEC SQL REGISTER CONNECT USING :ctx;
.......................................1
PCC-S-02383, Expecting an expression of type OCIExtProcContext


Id this because I didn't add something to my options file or what?
Any help would be greatly appreciated.


and Tom said...



Add PARSE=FULL to the precompiler command line.

Without it, it is not recognizing ctx because ctx is not in a BEGIN/END declare section.

Here is a generic makefile I use with extprocs on NT with Pro*C. It is designed to be used on the command line with nmake. The precompile line you want is at the bottom.

The other alternative is to

exec sql begin declare section;
OCIExtProcContext * localCtx;
exec sql end declare section;

localCtx = ctx;
exec sql registion connect using :localCtx;

...

-------------------------------------------------
CPU=i386
WRB_DIR = .\

!include <c:\msdev\include\win32.mak>

MSDEV = c:\msdev
ORACLE_HOME = c:\oracle

TGTDLL = extproc.dll

OBJS = lobtofile.obj

NTUSER32LIBS = $(MSDEV)\lib\user32.lib \
$(MSDEV)\lib\msvcrt.lib \
$(MSDEV)\lib\oldnames.lib \
$(MSDEV)\lib\kernel32.lib \
$(MSDEV)\lib\advapi32.lib

SQLLIB = $(ORACLE_HOME)\precomp\lib\msvc\orasql8.lib \
$(ORACLE_HOME)\oci\lib\msvc\oci.lib

INCLS = -I$(MSDEV)\include \
-I$(ORACLE_HOME)\oci\include \
-I.

CFLAGS = $(INCLS) -DWIN32 -DWIN_NT -D_DLL

all: $(TGTDLL)

cleanup:
erase *.obj *.lib *.exp lobtofile.c

$(TGTDLL): $(OBJS)
$(link) -DLL $(dllflags) \
/NODEFAULTLIB:LIBC.LIB -out:$(TGTDLL) \
$(OBJS) \
$(NTUSER32LIBS) \
$(SQLLIB) \

lobtofile.c: lobtofile.pc
proc sqlcheck=semantics \
include=$(ORACLE_HOME)\network\public \
include=$(ORACLE_HOME)\proc\lib \
include=$(ORACLE_HOME)\rdbms\demo \
include=$(ORACLE_HOME)\oci\include \
include=\msdev\include \
lines=yes \
parse=full \
iname=lobtofile.pc




Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Could u give me the corresponding make file for Solaris -Urgent Request..

Srinivash Regupathi, August 30, 2001 - 9:49 am UTC

Hi ,

It was quite interesting to see this message. Iam geting the same error in the Sun solarus platform. Could u give me the sample make file for this platform. I tried including the parse=full option.. but in vain..

Also Could u help me out with sample from calling Pro*C from Pl/sql using external routines. I was very successful in calling a C from pl/sql. But the Pro*C code is failing in the connect itself. (Though ruuning successfully from the commandline). This is a very urgent requirement.

Thanx in advance..

Tom Kyte
August 30, 2001 - 10:12 am UTC

Here is a unix one:

MAKEFILE= $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk

INCLUDE= -I$(ORACLE_HOME)/rdbms/demo \
-I$(ORACLE_HOME)/rdbms/public \
-I$(ORACLE_HOME)/plsql/public \
-I$(ORACLE_HOME)/network/public

TGTDLL= extproc.so
OBJS = extproc.o

all: extproc.so

clean:
rm *.o

extproc.so: $(OBJS)
$(MAKE) -f $(MAKEFILE) extproc_callback \
SHARED_LIBNAME=$(TGTDLL) OBJS=$(OBJS)

CC=cc
CFLAGS= -g -I. $(INCLUDE) -Wall

from Solaris.

If you get my book -- i have a 70 page chapter on nothing BUT external procedures in C using OCI and Pro*C, you may find that useful.

In order to use Pro*C with extprocs you must use Oracle8i and up (not supported/implemented in 8.0) and you use the following syntax to "connect" :


static void init( OCIExtProcContext * ctx )
{

EXEC SQL REGISTER CONNECT USING :ctx;
......





Still problem Exists.. PCC-S-02383, Expecting an expression of type OCIExtProcContext

Srinivash Regupathi, August 31, 2001 - 8:51 am UTC

Hi,

I tried ur solution. As suggsted by u , I have used parse=full but I end up with the same error message.


Semantic error at line 14, column 34, file sample.pc:
exec sql register connect USING :epctx;
.................................1
PCC-S-02383, Expecting an expression of type OCIExtProcContext

I have written a simple code

#include<stdio.h>
#include <oci.h>

EXEC SQL INCLUDE SQLCA.H;
EXEC SQL include sqlda;

exec sql begin declare section;
OCIExtProcContext *epctx;
exec sql end declare section;

void extp1(epctx)
{
char name[15];
exec sql register connect USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE empno = 9999;
return;
err:
SQLExtProcError(SQL_SINGLE_RCTX,sqlca.sqlerrm.sqlerrmc,sqlca.sqlerrm.sqlerrml);
return;
}

And Iam compiling using :

proc sqlcheck=semantics parse=full include=$ORACLE_HOME/network/public \ include=$ORACLE_HOME/proc/lib \
include=$ORACLE_HOME/rdbms/demo \ include=$ORACLE_HOME/oci/include \
include=/usr/include \
include=$ORACLE_HOME/rdbms/public/ \
lines=yes parse=full iname=sample.pc

Also.. Could u kindly tell me from where Can i ask questions to u ?


Thanx

Tom Kyte
August 31, 2001 - 9:12 am UTC

Your code is way wrong:

exec sql begin declare section;
OCIExtProcContext *epctx;
exec sql end declare section;

void extp1(epctx)
{
char name[15];
exec sql register connect USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE empno = 9999;
return;
err:
SQLExtProcError(SQL_SINGLE_RCTX,sqlca.sqlerrm.sqlerrmc,sqlca.sqlerrm.sqlerrml);
return;
}

You have TWO variables epctx. One is global (a big no-no in an extproc btw -- do NOT use globals), the other is local (and untyped).

In the procedure extp1 the ariable epctx is a parameter passed to it with a default type - a type that is explicitly not "OCIExtProcContext *" (its an int or a char or something, I forget what the default type in C is -- mostly because I would never rely on it).

Code:

void extp1(OCIExtProcContext * epctx)
{
char name[15];
exec sql register connect USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE empno = 9999;
return;


as I did and lose that global variable that is out of scope in that function anyway.


You can ask questions of me from the homepage on </code> http://asktom.oracle.com/ <code>but as there is only one of me and many people out there -- I am frequently in the mode of "not accepting questions, try later".





Pro*C Compilation Error

Kalpesh Patel, July 11, 2002 - 5:32 pm UTC

Hi Tom,
This is my PL/SQL query in my Pro*C code:
select max(trade_date) into temp_trade_date from tkdaily.tbminutes partition(day_4)
WHERE day = 4 AND symbol_id = (SELECT symbol_id FROM tkmaster.tbsymbols WHERE symbol_name_reuters = :ric_name) AND trade_date between (hour_date-0.0416) and hour_date;

I am getting following errors while compilation of my pro*C Code.

Semantic error at line 10564, column 77, file rtrqd02_amexstk.i:
select ( ( trade_date ) > ( ) ? ( trade_date ) : ( )
) into temp_trade_date from tkdaily.tbminutes partition(day_4)
............................................................................1
PCC-S-02322, found undefined identifier
select ( ( trade_date ) > ( ) ? ( trade_date ) : ( )
) into temp_trade_date from tkdaily.tbminutes partition(day_4)
.............................................................1
PLS-S-00103, Encountered the symbol ")" when expecting one of the following:

( - + mod not null others select <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute cast trim forall
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

Semantic error at line 10511, column 2, file rtrqd02_amexstk.i:
DECLARE
.1
PCC-S-02347, PL/SQL found syntax errors
*** Error code 1
make: Fatal error: Command failed for target `/export/home/dpos/offshore/dprimary/obj/rtrqd02_amexst
k.o'
Failed to Compile

If I replace max(trade_date) with trade_date in my query.
It compiles and executes successfully.
What could be the reason and what is the fix.
Please let me know
Thanks
Kalpesh


Alternate code from your book when using ProC

Gman, February 21, 2007 - 4:26 pm UTC

Tom,
The code in your book(p. 798) that you have commented out and instruct to replace the OCIExtProcGetEnv() call with returns and error for me on AIX.

"proc_mgr.c", line 116.22: 1506-277 (S) Syntax error: possible missing ';' or ',
'?
"proc_mgr.c", line 116.17: 1506-045 (S) Undeclared identifier EXEC.
"proc_mgr.c", line 117.22: 1506-045 (S) Undeclared identifier sqlca.

here are the lines of concern

+103
+104 #define SQLCA_INIT \
+105 EXEC SQL INCLUDE sqlca;
+106



+114 ub4 key = 1;
+115
+116 EXEC SQL REGISTER CONNECT USING :ctx;
+117 if ( sqlca.sqlcode < 0 )
+118 {
+119 OCIExtprocRaiseExcpWithMsg(ctx,20000,sqlca.sqlerrm.s
qlerrmc,70);
+120 return NULL;
+121 }




Tom Kyte
February 21, 2007 - 7:29 pm UTC

you did not run pro*c on it, you need pro*c to turn that .pc file into a .c file.

Thanks, my issue is.....

Gman, February 22, 2007 - 8:22 am UTC

Tom,
This is what I used to compile my file

make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk build EXE=proc_mgr OBJS=proc
_mgr.o

I could not find the .mk file that you recommend on my server tried to ask the dBA's for install source but to no avail.
Could you post your .mk file that you suggest us to use.

I think it is demo_rdbms.mk. Im on AIX 9i release 2.
Im assuming that that make file is what I should use first.

Thanks,
Tom Kyte
February 22, 2007 - 8:57 am UTC

given that I do not have AIX, that'll not really "be possible"

look on the companion CD for the demos.

Ignoring oci.h

A reader, July 09, 2009 - 10:53 am UTC

Tom i used the below :

make -f debug_plsql.mk

and got the below:

cc -O -c debugf.c

"debugf.pc", line 8.10: 1506-296 (S) #include file <oci.h> not found.

but I confirmed that $ORACLE_HOME/rdbms/demo/oci.h exists

This is also used :
/u2/app/oracle/product/9.2.0.8/precomp/admin/pcscfg.cfg

sys_include=(/usr/include)
ltype=short
define=__64BIT__
define=_IBM_C
define=_LONG_LONG

my debug_plsql.mk consists of:
MAKEFILE= $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk

INCLUDE= -I$(ORACLE_HOME)/rdbms/demo \
-I$(ORACLE_HOME)/rdbms/public \
-I$(ORACLE_HOME)/plsql/public \
-I$(ORACLE_HOME)/network/public

TGTDLL=extproc.so
OBJS =debugf.o

all: $(TGTDLL)

clean:
rm *.0

debugf.c: debugf.pc
proc \
include=$(ORACLE_HOME)/network/public \
include=$(ORACLE_HOME)/proc/lib \
include=$(ORACLE_HOME)/rdbms/demo \
include=$(ORACLE_HOME)/rdbms/public \
lines=yes \
iname=debugf.pc

$(TGTDLL): debugf.c $(OBJS)
$(MAKE) -f $(MAKEFILE) extproc_callback \
SHARED_LIBNAME=$(TGTDLL) OBJS="debugf.o"

CC=cc
CGLAGS= -g -I. $(INCLUDE) -Wall

any ideas why its ignoring oci.h

PCC-S-02322 when pre-processor definitions used

russellh, February 20, 2014 - 6:19 pm UTC

After upgrading client software to 11.2 (from 10.2), I now get PCC-S-02322 errors when pre-compiling pro*c files whenever a pre-processor substitution should occur.

In a very simple example (this is all in the same file), I have:

#define CONSZ 500

void getOracleData()
{
EXEC SQL BEGIN DECLARE SECTION;
char connect[CONSZ];
EXEC SQL END DECLARE SECTION;
...
}

When I run proc against the file I get,

Semantic error at line 24, column 16, file hello.pc:
char connect[CONSZ];
...............1
PCC-S-02322, found undefined identifier

When I use

char connect[500];

in the above, it pre-compiles without errors. Did something change to not allow pre-processor substitutions in pro*c declaration sections?

Environment: Windows 7 x64

short names in Windows

russellh, February 20, 2014 - 7:27 pm UTC

The problem above was that I could no longer use quoted path names to deal with spaces in path names. I had to use

INCLUDE=C:\PROGRA~2\MICROS~2.0\VC\include

instead of

INCLUDE="C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\include"

With that substitution, everything works.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.