Start a new topic

collection types

[This topic is migrated from our old forums. The original author name has been removed]

Hi Roger, Hans, in 11gR2 run create user x identified by y; grant create session, create procedure, dbfs_role to x; alter user x quota unlimited on users; connect x/y @?/rdbms/admin/dbfs_create_filesystem.sql users fs now as a dba run select * from sys.DBFS$_MOUNTS; and get: select * from sys.DBFS$_MOUNTS; 10:31:41 [SELECT - 0 row(s), 0.000 secs] [Error Code: 904, SQL State: 42000] ORA-00904: : invalid identifier from sqlplus: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning and Automatic Storage Management options SQL> desc DBFS$_MOUNTS Name Null? Type ----------------------------------------- -------- ---------------------------- S_OWNER NOT NULL VARCHAR2(32) S_NAME NOT NULL VARCHAR2(32) S_MOUNT VARCHAR2(32) CREATED NOT NULL TIMESTAMP(6) S_PROPS DBMS_DBFS_CONTENT_PROPERTIES _T without doubt, the problem is in the DBMS_DBFS_CONTENT_PROPERTIES_T, not a basic datatype. Is this something that can be solved? (just for comfort, Oracle SQL developer has the same problem) thanks, Ronald http://ronr.blogspot.com

[This reply is migrated from our old forums.]

Re: collection types
Hi Ronald, Thanks for your post. I will register a ticket for this and we will look into it. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Hi Roger, is this something that can be solved or is it a jdbc restriction ? R.
[This reply is migrated from our old forums.]

Re: collection types
Hi Ronald, I've done some debugging and the error is raised when DbVisualizer request the JDBC driver to execute the select statement. I had some hope that it would have been raised while fetching the result set. This is probably the reason you see the same behavior in SQL Developer as we are using the same JDBC driver. At this point I can't see how we could fix this. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Hi Roger, this was what I was afraid for. :-( I filed an oracle enhancement request for this. R.
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
The first reaction I got from support was: it is supported by the driver. Hopefully they van demonstrate it.
[This reply is migrated from our old forums.]

Re: collection types
Hi Ronald, If it helps in the discussion with Oracle support, the error is raised when executing the select statement using: java.sql.Statement.execute() Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Something is fooling me .... I made a little java prog that does this query and ..... it works. jdbc:oracle:thin:@(description=(address=(host=cibertrain)(protocol=tcp)(port=1521))(connect_data=(sid=ronr))) DriverVersion: [11.2.0.1.0] DriverMajorVersion: [11] DriverMinorVersion: [2] DriverName: [Oracle JDBC driver] Doing some query: Dumping output FOO FOO_FS DBFS_TEST FS_FST1 DBFS_TEST FS_FSTP1 Done. Code: // Create a Statement System.out.println( "Doing some query: " ); Statement stmt = conn.createStatement (); // Select the ENAME column from the EMP table ResultSet rset = stmt.executeQuery ( "select * from sys.DBFS$_MOUNTS"); // Iterate through the result and print the employee names System.out.println("Dumping output"); while (rset.next ()) { System.out.print ( rset.getString (1)); System.out.print ( '\t'); System.out.println ( rset.getString (2)); } if ( conn!=null ) conn.close(); System.out.println("Done."); Again, loaded the latest driver in dbvis /Library/Java/Extensions/ojdbc6.jar that in driver manager still shows as version 10.2. In my code as 11.2. Should I pull my hair? It looks like it is working now. dbvis is a bit unclear in the reporting of the version of the jdbc driver. Case closed?
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Roger, there is still something funny going on. Using one user it works ok, using a dba account it does not work: both users have the dbfs_role, that is required for using dbfs. both connections use the same driver,same url, only other username. 10:46:14 [DEBUG Thread-110 J.setCurrentSchema] DbConnection='dbfs_test@ronr' Catalog='null' Schema='DBFS_TEST' NewSchema='DBFS_TEST' 10:46:14 [DEBUG Thread-110 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:14 [DEBUG pool-1-thread-20 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:14 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:14 [DEBUG pool-1-thread-21 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:14 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.execute("select * from sys.dbfs$_mounts") 10:46:14 [DEBUG AWT-EventQueue-1 N.?] DbConnection='dbfs_test@ronr' Catalog='null' Schema='SYS' Table='DBFS$_MOUNTS' Column='%' 10:46:14 [DEBUG pool-1-thread-21 E.?] DefaultEditor-0: OracleDatabaseMetaData.getColumns(null, "SYS", "DBFS$_MOUNTS", "%") 10:46:14 [DEBUG AWT-EventQueue-1 N.?] DbConnection='dbfs_test@ronr' Catalog='null' Schema='SYS' Table='DBFS$_MOUNTS' Column='%' 10:46:14 [DEBUG pool-1-thread-21 E.?] DefaultEditor-0: OracleDatabaseMetaData.getColumns(null, "SYS", "DBFS$_MOUNTS", "%") 10:46:14 [DEBUG AWT-EventQueue-1 N.?] DbConnection='dbfs_test@ronr' Catalog='null' Schema='SYS' Table='DBFS$_MOUNTS' 10:46:14 [DEBUG pool-1-thread-21 E.?] RootConnection: OracleDatabaseMetaData.getPrimaryKeys(null, "SYS", "DBFS$_MOUNTS") 10:46:14 [DEBUG Thread-110 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:14 [DEBUG pool-1-thread-20 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:14 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:20 [DEBUG Thread-111 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:20 [DEBUG pool-1-thread-20 C.?] RootConnection: T4CConnection.createStatement() 10:46:20 [DEBUG pool-1-thread-20 B.?] RootConnection: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:20 [DEBUG Thread-112 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:20 [DEBUG pool-1-thread-20 C.?] RootConnection: T4CConnection.createStatement() 10:46:20 [DEBUG pool-1-thread-20 B.?] RootConnection: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:24 [DEBUG Thread-115 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:24 [DEBUG pool-1-thread-20 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:24 [DEBUG Thread-115 J.execute] Executing... 10:46:24 [DEBUG Thread-115 J.setCurrentCatalog] DbConnection='ronr' Catalog='null' Schema='null' NewCatalog='null' 10:46:24 [DEBUG Thread-115 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:24 [DEBUG pool-1-thread-20 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:24 [DEBUG Thread-115 J.setCurrentSchema] DbConnection='ronr' Catalog='null' Schema='DBA_USER' NewSchema='DBA_USER' 10:46:24 [DEBUG Thread-115 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:24 [DEBUG pool-1-thread-20 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 10:46:24 [DEBUG pool-1-thread-21 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.execute("select * from sys.dbfs$_mounts") 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: EXCEPTION -> java.sql.SQLException: ORA-00904: : invalid identifier 10:46:24 [DEBUG Thread-115 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 10:46:24 [DEBUG pool-1-thread-21 C.?] DefaultEditor-0: T4CConnection.createStatement() 10:46:24 [DEBUG pool-1-thread-21 B.?] DefaultEditor-0: T4CStatement.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") Do you see something here?
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Roger, I found it. It perfectly reproduces in sqlplus so no jdbc, no DbVis problem at all. create user z identified by z; grant create session, dba to z; connect z/z select * from sys.dbfs$_mounts; => error 904 grant dbfs_role to z; connect z/z select * from sys.dbfs$_mounts; => works. funny.
[This reply is migrated from our old forums.]

Re: collection types
Hi Ronald, Could it be a permission issue? Does a regular user have privileges to see/use the DBMS_DBFS_CONTENT_PROPERTIES_T declaration? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: collection types
Yes, it is, see my post of 3-sep-2010 11:24. Normally the dba role has it all .... R.