Home » Developer & Programmer » Precompilers, OCI & OCCI » Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL (HP-UX 11.x C++ (ProC & aCC compiler) Oracle 10 )
Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL [message #577406] Thu, 14 February 2013 22:38 Go to next message
shashank_h
Messages: 3
Registered: February 2013
Junior Member
I am unable to execute the following command from my SQL Embedded C++ code

EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
FROM table_name ;

Compiler error as follows

Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
....1
PLS-S-00201, identifier 'ORA_ROWSCN' must be declared
Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
....1
PLS-S-00000, SQL Statement ignored
Semantic error at line 81, column 5, file FILENAME.PC:
EXEC SQL SELECT MAX(ORA_ROWSCN) INTO :scn_timestamp
....1
PCC-S-02346, PL/SQL found semantic errors


I've also tried variations of that command such as

EXEC SQL SELECT MAX(A.ORA_ROWSCN) INTO :scn_timestamp
FROM table_name A;

For which i get error saying that ORA_ROWSCN column doesn't exist.

I've verified that this sql query works in the SQLPlus console. Any particular reason why this isn't working when embedded?


[Updated on: Thu, 14 February 2013 23:11]

Report message to a moderator

Re: Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL [message #577409 is a reply to message #577406] Fri, 15 February 2013 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you:
1/ Post your pcc command line
2/ Post the first lines of: "pcc" (without any option)

Regards
Michel
Re: Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL [message #577412 is a reply to message #577409] Fri, 15 February 2013 01:59 Go to previous messageGo to next message
shashank_h
Messages: 3
Registered: February 2013
Junior Member
The changes i'm making are to an existing file. Following is an excerpt from the automated build script :

/data/tsd_oracle/hpux/10.2.0.2/bin/proc +z ireclen=200 ORECLEN=200 LRECLEN=208 code=cpp cpp_suffix=C lines=yes sqlcheck=semantics maxopencursors=10 parse=PARTIAL unsafe_null=yes mode=oracle dbms=V8 sys_include='(/opt/aCC,/opt/aCC/include/iostream,/opt/aCC/include/rw,/usr/include)' include=/data/tsd_oracle/hpux/10.2.0.2/precomp/public include=/usr/include include=/opt/aCC/include include=/opt/aCC/include/SC iname=FILENAME.PC;

Pro*C/C++: Release 10.2.0.2.0 - Production on Fri Feb 15 12:48:35 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

System default option values taken from: /data/tsd_oracle/hpux/10.2.0.2/precomp/admin/pcscfg.cfg

Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PLS-S-00201, identifier 'ORA_ROWSCN' must be declared
Error at line 81, column 5 in file FILENAME.PC
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PLS-S-00000, SQL Statement ignored
Semantic error at line 81, column 5, file FILENAME.PC:
EXEC SQL SELECT MAX(ora_rowscn) INTO :scn_timestamp
....1
PCC-S-02346, PL/SQL found semantic errors
*** Error code 1
clearmake: Error: Build script failed for "FILENAME.C"

The contents of the default option value file /data/tsd_oracle/hpux/10.2.0.2/precomp/admin/pcscfg.cfg are as follows
sys_include=(/data/tsd_oracle/hpux/10.2.0.2/precomp/public,/usr/include)
ltype=short
define=ORASTDARG


Regards
Shashank
Re: Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL [message #577418 is a reply to message #577412] Fri, 15 February 2013 04:43 Go to previous messageGo to next message
shashank_h
Messages: 3
Registered: February 2013
Junior Member
Successful workaround found on stackoverflow.com/questions/14888854/cannot-execute-select-maxora-rowscn-from-embedded-sql

Ok with this for now.
Re: Cannot execute SELECT MAX(ORA_ROWSCN) from Embedded SQL [message #577419 is a reply to message #577418] Fri, 15 February 2013 04:56 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
The workaround is to use the following:
EXEC SQL PREPARE timestamp_stmt FROM 'SELECT MAX(A.ora_rowscn) FROM table_name A';
EXEC SQL DECLARE timestamp_cur CURSOR FOR timestamp_stmt;
EXEC SQL OPEN timestamp_cur;    
EXEC SQL FETCH timestamp_cur INTO :scn_timestamp;   
EXEC SQL CLOSE timestamp_cur;

Regards
Michel
Previous Topic: Memory corruption from malloc after calling db connection routine
Next Topic: OCI+kerberos authentication
Goto Forum:
  


Current Time: Thu Mar 28 14:04:16 CDT 2024