Home » Developer & Programmer » Precompilers, OCI & OCCI » PLS-S-00201, identifier 'STATS_MODE' must be declared (Pro*C/C++: Release 12.1.0.2.0)
PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666690] Mon, 20 November 2017 13:30 Go to next message
WarrenDB
Messages: 4
Registered: November 2017
Junior Member
My precompiler is giving an error:

Pro*C/C++: Release 12.1.0.2.0 - Production on Mon Nov 20 13:05:30 2017

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /oracle/product/12102SE/precomp/admin/pcscfg.cfg

Error at line 757, column 4 in file code.pc
                        EXEC SQL
...1
PLS-S-00201, identifier 'STATS_MODE' must be declared
Error at line 757, column 4 in file code.pc
                        EXEC SQL
...1
PLS-S-00000, SQL Statement ignored
Semantic error at line 757, column 4, file code.pc:
                        EXEC SQL
...1
PCC-S-02346, PL/SQL found semantic errors
make: 1254-004 The error code from the last command is 1.


Stop.
make: 1254-004 The error code from the last command is 2.


on the following query:

SELECT STATS_MODE(DECODE(data_type,'DATE',21,
    DECODE(data_precision,NULL,data_length,data_precision))), count(*)
INTO :llCurColMode, :llCurColCnt
FROM user_tab_columns
WHERE column_name = :lsColNme;

Replacing STATS_MODE with MEDIAN or AVG will let it build, but obviously not the result I want. Is there a specific header set I need to use to enable the extended aggregate functions, or a way to manually declare it as a valid function even though it's built in? It runs just fine when I run it directly against the database with sqlplus.
Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666691 is a reply to message #666690] Mon, 20 November 2017 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What was your proc command line?

Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666692 is a reply to message #666691] Mon, 20 November 2017 14:19 Go to previous messageGo to next message
WarrenDB
Messages: 4
Registered: November 2017
Junior Member
make -f code.mk code MAKERULES=/user/project/Compile/project.mk BINDIR=/user/project/tuxedo/bin INCDIR=/user/project/tuxedo/include CFLAGS=" -I/user/project/include -I/oracle/product/12102SE/precomp/public -I/user/project/tuxedo/include -I/user/project/include/FML -I/user/project/include/VIEW  -I. -g " COMMONINC=" -f /user/project/include/shlutils.c -r Oracle_XA_PROJECT"

proc iname=code.pc Ireclen=132 SQLCHECK=SEMANTIC USERID=redacted/redacted DEFINE=__64BIT__ LINES=YES  include=/user/project/tuxedo/include include=/user/project/include/FML  include=/user/project/include include=. include=/user/project/include/VIEW

Code header has
#include <sqlca.h>
#include <oraca.h>
#include <sqlcpr.h>
plus our own .h files
Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666693 is a reply to message #666692] Mon, 20 November 2017 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post the result of:
sqlplus redacted/redacted <<EOF
select * from v$version;
exit
EOF
Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666694 is a reply to message #666693] Mon, 20 November 2017 14:35 Go to previous messageGo to next message
WarrenDB
Messages: 4
Registered: November 2017
Junior Member
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production                0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production                         0
NLSRTL Version 12.1.0.2.0 - Production                                                    0
Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666695 is a reply to message #666694] Mon, 20 November 2017 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does it compile if you specify SQLCHECK=SYNTAX instead of SEMANTICS?

Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666696 is a reply to message #666695] Mon, 20 November 2017 15:38 Go to previous messageGo to next message
WarrenDB
Messages: 4
Registered: November 2017
Junior Member
Yes, that does fix it! Though that's in our general build rules so I'll need a justification for changing it to get an internal tool working as it normally protects us from building changes against database updates that haven't been made yet. What would be the reason it can't find the reference to that built-in function? I was under the impression that STATS_MODE was a globally available aggregate function. Is this a bug, or a deeper permissions error?
Re: PLS-S-00201, identifier 'STATS_MODE' must be declared [message #666703 is a reply to message #666696] Tue, 21 November 2017 00:34 Go to previous message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The difference between the 2 settings is that with SYNTAX pcc does not connect to the database to check if the function is valid or not in the user environment.
Check if there is no object with name STATS_MODE in your database.
If no, then I tend to think it is a bug (or maybe a restriction of Standard Edition?).

Previous Topic: sqlcxt getting hanged
Goto Forum:
  


Current Time: Wed Dec 13 05:31:38 CST 2017

Total time taken to generate the page: 0.01636 seconds