DataGrip unable to get DDL for Stored Procedures in a Package

@Spencer Underwood Do work with Oracle?
Could you disable `Introspect using JDBC metadata` option and then invoke `Database tools...` -> `Forget cached schemas` and resync?

@vasily_chernov I am indeed working with an oracle 10g database.

I have disabled the "Introspect using JDBC metadata" option, forgottten the schema and then re-sync'd but when I try to view the procedure it still shows the error messages from before. One new unusual behavior though is that I can right click the stored procedure in the package and "Request and copy Original DDL" and the stored procedure is copied perfectly into the clipboard, even though it's not shown properly,

I also get a new oracle error which might be the root of the problem:

Error encountered when performing Introspect the schema <<SCHEMA>> stuff completely: ORA-01652: unable to extend temp segment by 128 in TEMP03
.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP03 (1 m 33 s 229 ms)

I'll contact our DB team next and see if the DB has enough resources for this task.

Hello Spencer,

could you please turn the introspector logs ON, then reproduce the error ORA-01652 and provide us with the log files?

In order to turn logging ON, just select item "Debug Log Settings" from the menu Help, and add the following string:

#com.intellij.database.introspection.OraIntrospector:trace

(including the leading sharp sign).

For locating the log files you can select item "Show Log in Explorer". You can also move existing logs to another location, to get a log file containing entries related this problem only.

Thanks!

Greetings @Leonid ,

Unfortunately I am no longer able to replicate this issue :(

The DBA team indicated this morning that they have allocated additional temp space to the user I was trying to connect with, and ever since then the DataGrip has been working great.

My apologies,

Spencer Underwood

@ Spencer Underwood 
Let me know if there are any updates.

@Vasily Chernov.

Hello.

I got the same issue introspecting an Oracle v 11.2.0.4.0.
Introspecting with "Introspect using JDBC metadata" set to true, results in "Not supported facility" (see stacktrace below) when introspecting source code.

Introspecting with "Introspect using JDBC metadata" set to false, results in "ORA-0942 - table or view not found", when trying to select from sys.obj$.
That requires permissions, that most users does not have.

2018-11-06 10:53:59,920 [1042931692] WARN - lij.database.util.ErrorHandler - Ikke-understøttet facilitet
java.sql.SQLException: Ikke-understøttet facilitet
at oracle.jdbc.OracleDatabaseMetaData.getAttributes(OracleDatabaseMetaData.java:5761)
at com.intellij.database.remote.jdbc.impl.RemoteDatabaseMetaDataImpl.getAttributes(RemoteDatabaseMetaDataImpl.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346)
at sun.rmi.transport.Transport$1.run(Transport.java:200)
at sun.rmi.transport.Transport$1.run(Transport.java:197)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276)
at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253)
at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162)
at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:227)
at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:179)
at com.sun.proxy.$Proxy213.getAttributes(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:179)
at com.intellij.execution.rmi.RemoteUtil.access$300(RemoteUtil.java:39)
at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:275)
at com.sun.proxy.$Proxy214.getAttributes(Unknown Source)
at com.intellij.database.introspection.jdbcMetadataWrappers.DatabaseMetaDataWrapper.userDefinedTypeAttributesInner(DatabaseMetaDataWrapper.java:673)
at com.intellij.database.introspection.jdbcMetadataWrappers.DatabaseMetaDataWrapper.userDefinedTypeAttributes(DatabaseMetaDataWrapper.java:664)
at com.intellij.database.introspection.GenericIntrospector.introspectTypeAttributesInSchema(GenericIntrospector.java:379)
at com.intellij.database.introspection.GenericIntrospector.lambda$introspectSchemasByCatalogs$9(GenericIntrospector.java:230)
at java.lang.Iterable.forEach(Iterable.java:75)
at com.intellij.database.introspection.GenericIntrospector.lambda$forEachSchemaInCatalog$15(GenericIntrospector.java:258)
at com.intellij.database.introspection.GenericIntrospector.forEachCatalog(GenericIntrospector.java:253)
at com.intellij.database.introspection.GenericIntrospector.forEachSchemaInCatalog(GenericIntrospector.java:258)
at com.intellij.database.introspection.GenericIntrospector.introspectSchemasByCatalogs(GenericIntrospector.java:225)
at com.intellij.database.introspection.GenericIntrospector.introspectSchemas(GenericIntrospector.java:209)
at com.intellij.database.introspection.GenericIntrospector.lambda$null$0(GenericIntrospector.java:166)
at org.jetbrains.dekaf.core.BaseFacade.inSession(BaseFacade.java:125)
at com.intellij.database.introspection.GenericIntrospector.lambda$introspectAuto$1(GenericIntrospector.java:160)
at com.intellij.database.model.impl.BaseModel.modify(BaseModel.java:115)
at com.intellij.database.model.impl.BaseModel.modify(BaseModel.java:100)
at com.intellij.database.model.impl.BaseModel.modify(BaseModel.java:85)
at com.intellij.database.introspection.GenericIntrospector.introspectAuto(GenericIntrospector.java:160)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.introspectDatabases(DatabaseModelLoader.java:521)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.lambda$null$2(DatabaseModelLoader.java:340)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.withFacade(DatabaseModelLoader.java:648)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.lambda$introspect$3(DatabaseModelLoader.java:323)
at com.intellij.database.dataSource.DataSourceSyncManager.lambda$null$0(DataSourceSyncManager.java:44)
at com.intellij.database.dataSource.DatabaseConnectionManager$Executor.perform(DatabaseConnectionManager.java:387)
at com.intellij.database.dataSource.DatabaseConnectionManager$Executor.lambda$sync$2(DatabaseConnectionManager.java:326)
at com.intellij.database.dataSource.AsyncUtil.withAsyncFriendly(AsyncUtil.java:161)
at com.intellij.database.dataSource.DatabaseConnectionManager$Executor.sync(DatabaseConnectionManager.java:322)
at com.intellij.database.dataSource.DatabaseConnectionManager$Builder.sync(DatabaseConnectionManager.java:139)
at com.intellij.database.dataSource.DataSourceSyncManager.lambda$static$1(DataSourceSyncManager.java:43)
at com.intellij.database.dataSource.DataSourceSyncManager$SyncProcessor$1.perform(DataSourceSyncManager.java:271)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.introspect(DatabaseModelLoader.java:321)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.lambda$run$0(DatabaseModelLoader.java:301)
at com.intellij.database.dataSource.LocalDataSource.performBatch(LocalDataSource.java:1194)
at com.intellij.database.dataSource.DatabaseModelLoader$IntrospectionSession.run(DatabaseModelLoader.java:299)
at com.intellij.database.dataSource.DataSourceSyncManager$SyncProcessor.performSync(DataSourceSyncManager.java:273)
at com.intellij.database.dataSource.AsyncUtil.lambda$null$6(AsyncUtil.java:58)
at com.intellij.database.dataSource.AsyncUtil.lambda$underProgress$14(AsyncUtil.java:130)
at com.intellij.openapi.progress.impl.CoreProgressManager.registerIndicatorAndRun(CoreProgressManager.java:580)
at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:525)
at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:85)
at com.intellij.database.dataSource.AsyncUtil.underProgress(AsyncUtil.java:136)
at com.intellij.database.dataSource.AsyncUtil.underProgress(AsyncUtil.java:130)
at com.intellij.database.dataSource.AsyncUtil.lambda$captureIndicator$7(AsyncUtil.java:58)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
at com.intellij.openapi.application.impl.ApplicationImpl$1.run(ApplicationImpl.java:310)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

Best regards

Otto Jacobsen

The root cause of this issue seem to be this heavy query:

with O1 as ( select object_id,
                    object_type,
                    object_name
             from sys.all_objects
             where owner = ?
               --  and last_ddl_time >= :since
               --  and object_name in ( :[*major_object_names] )
               and object_type in ('TYPE', 'TYPE BODY', 'PACKAGE', 'PACKAGE BODY',
                                   'PROCEDURE', 'FUNCTION',
                                   'MATERIALIZED VIEW', 'VIEW', 'TRIGGER') ),
     O2 as ( select object_id as referenced_id,
                    owner as referenced_schema_name,
                    object_type as referenced_type,
                    object_name as referenced_name
             from sys.all_objects ), -- don't use #CAT here
     DE as ( select type as object_type,
                    name as object_name,
                    referenced_owner as referenced_schema_name,
                    referenced_type,
                    referenced_name
             from sys.all_dependencies
             where owner = ?
                and not (owner = referenced_owner and name = referenced_name and type = referenced_type || ' BODY')
                and not (referenced_owner = 'SYS' and referenced_type = 'PACKAGE' and referenced_name = 'STANDARD')
                and type not in ('SYNONYM','UNDEFINED','JAVA CLASS','JAVA DATA')
                and referenced_type not like 'JAVA%' )
select object_id, referenced_id /* O1.object_id, O2.referenced_id *//* object_id, referenced_id */
from O1 natural join DE natural join O2;

Package sources cannot be loaded for any of our customer oracle installations - so I would consider this a "bug".
It should be possible to rewrite the sql to be less heavy on temporary storage.
Is this the correct place to report this request for improvement? Currently my colleagues want to use DataGrip but since they still have to use other tools to view package sources, they refrain from using it instead.

I should add that in our case - the customer installations have between 6000 and 12.000 packages. These packages are quite large... 
I'd rather have an  option for datagrip to lazy load sources as you request them:

select text from all_source where name = 'ABC_CLASS_API'
and type = 'PACKAGE BODY'
order by line;

Please consider! :)

@Aron thank you for root cause investigation