Home » Developer & Programmer » Precompilers, OCI & OCCI » Problems running query in PRO*C that works in SQLPLUS (Oracle 10g/Oracle 8i)
Problems running query in PRO*C that works in SQLPLUS [message #345792] Thu, 04 September 2008 16:33 Go to next message
casirback
Messages: 6
Registered: September 2008
Junior Member
Hello,

I have a query similar to the one below which is embedded in a PRO*C program. The query works fine when run in the PRO*C program against a 8i database but fails with an "ORA-02015: cannot select FOR UPDATE from remote table" error when run against a 10g database. The PRO*C program is executing the sql using "EXEC SQL".

QUERY: Select last_name, first_name from Member
where ....
FOR UPDATE OF LAST_NAME;

The other thing to note is this SQL query works fine via sqlplus in a 10g environment.

ADDITIONAL DETAILS: The above query is selecting data from a base table via a user view
VIEW: select * from
otherschema.member@connection_identifier;

This view was created in this manner to allow the user
account access to the underlaying table without creating
explicit permissions.

If anyone has encountered a similar issue before, I would greatly appreciate any assistance or advice.

Thanks!
Re: Problems running query in PRO*C that works in SQLPLUS [message #345827 is a reply to message #345792] Thu, 04 September 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to give versions with at least 3 decimals.

Regards
Michel
Re: Problems running query in PRO*C that works in SQLPLUS [message #346027 is a reply to message #345827] Fri, 05 September 2008 11:40 Go to previous messageGo to next message
casirback
Messages: 6
Registered: September 2008
Junior Member
Here is the information you requested:

The process works fine as mentioned before when running against Oracle 8i with this configuration:

oracle 8i 8.1.7.4.0
PRO*C/C++ 9.0.1.0.0
(SUN Server)


But fails on Oracle 10g, configuration:
oracle 10g 10.2.0.1.0
PRO*C/C++ 10.2.0.1.0
(IBM Server)

Any information or suggestions is welcome...
Re: Problems running query in PRO*C that works in SQLPLUS [message #346031 is a reply to message #346027] Fri, 05 September 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Client 8.1.7 <-> Server 10.2 is not supported.
You have to recompile/relink your program and use it with at least 9.2 client.

Regards
Michel
Re: Problems running query in PRO*C that works in SQLPLUS [message #346033 is a reply to message #346031] Fri, 05 September 2008 12:23 Go to previous messageGo to next message
casirback
Messages: 6
Registered: September 2008
Junior Member
The program has been recompiled and relinked to the Oracle 10g client. The issue is that the program works for our main user account since the application is directly interacting with table structures.

However, when the program is run under user "NEMO", the program is accessing the same tables via a user-owned view. This view is created with a connection_identifier which under Oracle 8i allowed us to bypass the requirement to create explicit permissions to the table.

This setup does not work when running the PRO*C program in Oracle 10g environments. The query embedded in the program does work when running through a Oracle 10g SQLPLUS session.

Any other suggestions?

Re: Problems running query in PRO*C that works in SQLPLUS [message #346036 is a reply to message #346033] Fri, 05 September 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Does not work" is not an Oracle error.

Now I'm lost, you are using it with 8i or 10g client?

Regards
Michel

[Updated on: Fri, 05 September 2008 12:35]

Report message to a moderator

Re: Problems running query in PRO*C that works in SQLPLUS [message #346066 is a reply to message #346036] Fri, 05 September 2008 16:35 Go to previous messageGo to next message
casirback
Messages: 6
Registered: September 2008
Junior Member
Hi Michel,

We are using the application on one server with Oracle 8i (program is compiled/linked to Oracle 8i on this server) No issues with the application on this server.

On another server that has oracle 10g, a copy of the same application has been recompiled/relinked to use ORACLE 10g. When running this program on the Oracle 10g machine, we get the ORA-02015 error message as described in my preceding messages.

We are trying to figure out a workaround to this issue and would welcome any suggestions from any programmers who have encountered this situation/error...
Re: Problems running query in PRO*C that works in SQLPLUS [message #347149 is a reply to message #345792] Wed, 10 September 2008 21:02 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
The ORA-02015 error (which it happens I have never run into before) suggests strongly that it is only raised if you are trying to do a SELECT FOR UPDATE accross a database link. It appears that this restriction has been removed in 10g. And it is not at all clear from the discussion so far if database links are or are not involved.

As I see it, here are the possibilities (you decide which represent reality):

1) client is on an 8i machine connecting to an 8i database, containing the table to be updated. Should work (and I think you have indicated that it does).

2) Client is on a 10g machine connecting to a 10g database containing the table to be updated. Should work. If this is your problem case, please let us know.

3) Client is on a 10g machine connecting to a 10g database, and the table to be updated is on a different 10g database. Should still work.

4) Client is on a 10g or 8i database, connecting to a 10g or 8i database, and the table to be updated is on a different 8i database. I don't think this can ever work. If this is the situation you have, your problem is answered (and you don't like my answer).
Re: Problems running query in PRO*C that works in SQLPLUS [message #348161 is a reply to message #347149] Mon, 15 September 2008 17:41 Go to previous messageGo to next message
casirback
Messages: 6
Registered: September 2008
Junior Member
Hi!

The client is on a 10g machine connecting to a 10g database. The main transaction application utilizes tables for the SELECT for UPDATE statement being run. This works fine in both 8i and 10g environments. The issue we are having is with a particular account which accesses the tables via user account-owned views. These views use database links as connection_identifers

example: Create VIEW MYDATAVIEW as Select * from mytable@POS99 (FYI: this is also running on the POS99 machine)

These views were created in this manner to allow us to bypass explicit permission grants to this user and works fine in 8i, but not in 10g.

Any suggestions? If we remove the connection identifiers from the view script, we then have to create explicit permissions but do not want to do this as this account is used by multiple departments.
Re: Problems running query in PRO*C that works in SQLPLUS [message #348178 is a reply to message #345792] Mon, 15 September 2008 21:30 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Well.....

The docs are quite clear. ORA-02015 is raised when one tries to update a table across a database link. But this message does not appear in the 10g error manual. Suggesting that this restriction has been relaxed somewhat.

I actually have some ideas, but first I must know exactly what is happening. Let me ask the questions a different way. What you are doing is

1) Connecting to an Oracle Instance. For now, suppose it is an 8i instance. You then issue a SELECT...FOR UPDATE against a table, members, also in an 8i instance. Is this the same or a different instance?

2) In another case, you first connect to a 10g instance, then issue a SELECT...FOR UPDATE. Is this table in the same 10g instance, or a different one? Or, is this table in the 8i instance above?

For now, I don't care about the client tool or the database link, I just want to know which instances are which.



Re: Problems running query in PRO*C that works in SQLPLUS [message #348443 is a reply to message #348178] Tue, 16 September 2008 15:38 Go to previous messageGo to next message
casirback
Messages: 6
Registered: September 2008
Junior Member

Hello,

For both of the scenarios you outlined, this is what I am trying to say:

For scenario 1, the SELECT...FOR UPDATE statement is being run against the target table or a view based upon the same target table in the same 8i instance.

For scenario 2, the SELECT...FOR UPDATE statement is being run against a table in the same 10g instance. However, when running the SELECT...FOR UPDATE statement against a VIEW based upon the same target table in the same 10g instance, I get the ORA-02015 message.

Does this clarify things?
Re: Problems running query in PRO*C that works in SQLPLUS [message #348480 is a reply to message #345792] Tue, 16 September 2008 22:02 Go to previous message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Yes, this clarifies things tremendously! I don't know for sure why you are getting the 2015 error, but I think I know how you can evade it.

Here's what I think you are doing. In your 10g instance, you have a schema (I'll call it PROD10) which contains a whole bunch (that's a technical DBA term) of tables, indexes, etc. One of these is the MEMBERS table. Your problem is to share it with other users. And to keep this brief, I'll assume you are comfortable with the difference between a 'schema' and a 'user'.

Now, the solution your site has chosen is to create a database link to this schema. And you have stated that this link is used by a number of applications. And you have stated that your site is very concerned about security, and that the reason you are using this link in a view (even though you don't have to use a database link) is because doing the necessary grants is too much work.

But, because you are so concerned about security, your link can't be to PROD10, because if it were, then PROD10 would be wide open. So, even though you haven't said as much, I know your link must be to a proxy ID which has received the necessary grants. And I can understand that trying to repeat these grants to your ID would have maintenance problems.

The solution is to create a role, grant the needed privileges to this role, and then grant this role to your proxy ID and also to you. Remote users are unaffected, and you no longer need to use the database link.

As to why you are getting the 2015 error ... my guess is that you have some remnants of the 7.3/8i library routines linked in to your pro*c task, and these routines don't like the database link.

Previous Topic: OCI - ORA-1403 & ORA-1405 Errors
Next Topic: Problem, how to connect c++ and Oracle
Goto Forum:
  


Current Time: Thu Mar 28 16:09:00 CDT 2024