collection types

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

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

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

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.

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?

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?

The first reaction I got from support was: it is supported by the driver. Hopefully they van demonstrate it.

Hi Roger, this was what I was afraid for. :-( I filed an oracle enhancement request for this. R.

Hi Roger, is this something that can be solved or is it a jdbc restriction ? R.