Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro*C - how to compile with SQLCHECK when access to meta-information requires a SET ROLE (Pro*C/C++)
icon8.gif  Pro*C - how to compile with SQLCHECK when access to meta-information requires a SET ROLE [message #496491] Mon, 28 February 2011 10:37 Go to next message
Agitatra
Messages: 2
Registered: February 2011
Junior Member
Hi!
I have a quite unique problem but maybe someone can help me anyhow.
I have to access a corporate database (no chance of altering any parameters) with Pro*C/C++ using both: "EXEC SQL SELECT" and: "EXEC SQL EXECUTE" statement. Therefore Pro*C requires the activation of at least: "SQLCHECK=semantics". However, to access the tables/stored procedures in question, the activation of a specific role is required, otherwise neither the items nor its meta-information is visible.
SQLplus complains when the role is not set:
   SQL> SELECT * FROM roleOrientedTable;
   SELECT * FROM roleOrientedTable;
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist

However in SQLplus the solution is fairly straightforward:
   SQL> SET ROLE bla IDENTIFIED by bla;
   SQL> SELECT * FROM roleOrientedTable;
   output from roleOrientedTable...

On the other hand Pro*C reacts similar:
      EXEC SQL SELECT * FROM roleOrientedTable;
   PLS-S-00201, identifier 'roleOrientedTable' must be declared

but I don't know how to persuade Pro*C to request the necessary role before trying to access the meta-information.
Any ideas? Any suggestions will be appreciated!
Cheers
Mark.
Re: Pro*C - how to compile with SQLCHECK when access to meta-information requires a SET ROLE [message #496495 is a reply to message #496491] Mon, 28 February 2011 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, in the development server, to allow compilation, activate all the roles by default.

Regards
Michel
Re: Pro*C - how to compile with SQLCHECK when access to meta-information requires a SET ROLE [message #496504 is a reply to message #496495] Mon, 28 February 2011 11:14 Go to previous messageGo to next message
Agitatra
Messages: 2
Registered: February 2011
Junior Member
Dear Michael,
thanks a lot for the fast reply!
However...
O.K. this is a bit embarrassing - outing me as a newbie, but what is the: "development server" and how do I activate roles in it?
I currently call: "proc" form the command line, having the database access parameters in the default configuration file. Do I have to put any parameters in there, into another configuration file or some place else?
Do you mean by: "development server" the actual database? In this case I'm doomed, I'm afraid. The only database that I can use for this purpose is the production database, and I neither have the slightest chance to alter it, nor is it possible to get get its schema. Even if something like it might exist, I won't get access to it.
Again, thanks a lot.
Cheers
Mark.
Re: Pro*C - how to compile with SQLCHECK when access to meta-information requires a SET ROLE [message #496509 is a reply to message #496504] Mon, 28 February 2011 11:31 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what is the: "development server"

it is the one on which you develop and compile your program.
Quote:
how do I activate roles in it?

Ask your DBA to grant the roles by default to the account you compile the program with.
Quote:
Do I have to put any parameters in there, into another configuration file or some place else?

You have to give the connection parameter either in the configuration file or on the proc command line.
Quote:
The only database that I can use for this purpose is the production database

Developer should have NO access to the production server.
Create a database to develop on it.

Regards
Michel
Previous Topic: OCI client do not receive notification upon successful registration (Continuous Query Notification)
Next Topic: OCCI AQ Enqueue of object with blob
Goto Forum:
  


Current Time: Fri Mar 29 03:06:46 CDT 2024