Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Object type not working in two schemas
Oracle Object type not working in two schemas [message #191359] Tue, 05 September 2006 21:36 Go to next message
annijos
Messages: 2
Registered: September 2006
Junior Member
Hi,

Our application has stored procedures which takes oracle object types (user defined) as in/out parameter.

now all these stored procedures works when we have only one schema with dba access rights. but if we create another schema lets say S2 (which has execute grant for all objects of S1)stored procedure stops working.....This is applicable only for the SPs with oracle object types....

any ideas? whats the issue?
Re: Oracle Object type not working in two schemas [message #191360 is a reply to message #191359] Tue, 05 September 2006 21:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>stops working
This does not appear to be a valid Oracle error message/code.

How did you arrive at this particular "design" without knowing how properly code the application to allow it to start working?

Ask the data architect how to implement his design.

[Updated on: Tue, 05 September 2006 22:02] by Moderator

Report message to a moderator

Re: Oracle Object type not working in two schemas [message #191364 is a reply to message #191359] Wed, 06 September 2006 00:00 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
annijos wrote on Tue, 05 September 2006 19:36

which has execute grant for all objects of S1)stored procedure stops working


Including execute grants for the types?
Re: Oracle Object type not working in two schemas [message #191491 is a reply to message #191364] Wed, 06 September 2006 10:37 Go to previous messageGo to next message
annijos
Messages: 2
Registered: September 2006
Junior Member
here is what i have done.

1. Created type and stored procedure in S1
2. In second schema i created synonym and then i did
grant execute on <<object type>> <<SP>> to S2.

now if connect to database with S1, my jave program works and SP gets executed.....but if connect to database using S2....I get following exception

ERROR: Exception: java.sql.SQLException: Internal Error: Inconsistent catalog view
java.sql.SQLException: Internal Error: Inconsistent catalog view
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.sql.StructDescriptor.initMetaData1_9_0(StructDescriptor.java:1838)
at oracle.sql.StructDescriptor.initMetaData1(StructDescriptor.java:1782)
at oracle.sql.StructDescriptor.isInstantiable(StructDescriptor.java:1027)
at oracle.sql.STRUCT.<init>(STRUCT.java:126)



Please help.

- Shriram
Re: Oracle Object type not working in two schemas [message #354184 is a reply to message #191491] Thu, 16 October 2008 21:29 Go to previous message
bgokulnath@gmail.com
Messages: 2
Registered: October 2008
Junior Member
Hi...
I was getting the same exception. Used the newer version of JDBC driver classes12_V10.1.0.4.jar and error gone.
Previous Topic: How To identify the tables from a dblinks seeing at a procedure
Next Topic: restrict user for input
Goto Forum:
  


Current Time: Sun Dec 04 14:56:32 CST 2016

Total time taken to generate the page: 0.11373 seconds