No option to edit package body

Hello Magnus,

What database are you working with? Please attach screenshot, showing the problem.

Please also make sure your Data Source is not marked as Read-Only in Data Source settings dialog.

I had the same problem. It looks like Data Grip needs specific privilege to display package body.

I have Oracle 11g database. Dev server grants everything - so I can see package bodies. But QA server gives my user limited rights. So I can't see package bodies, only headers.

However if I install plugin to Datagrip - DataBase Navigator - I can see pacakge body using the same user.

In addition to that. Accessing same QA database with SQL Developer - shows me package bodies.

to Andrey,

Is it possible to know which permission should a user have in order to see package bodies in DataGrip?

Thanks to both for your replies - apologies also for not getting back sooner.

I too am connecting to Oracle 11g and the data source is not opened as read only - I can see and edit the package bodies using the same user in SQL Developer but, again, in Datagrip I can only edit the package declaration.

Kind regards

I had had the same problem with IntelliJ IDEA 2016.3 EAP. Turning off "Introspect using JDBC Metadata" checkbox in the datasource's options panel fixed it.

I have completely same issue as Magnus.
Connecting to 12c, Only option available is package editor that opens only package specification. No way to get to package body editor. 

Introspect using JDBC metadata is off
Grants does not matter (user has DBA role even)
Data source is not read-only

IJIdea 2016.3

I believe Idea/DataGrip queries all_source to get package body text, and it should query dba_source if it's available.

For instance, PL/SQL Developer has a checkbox "use dba views if available", Idea/DataGrip should have the same, or, better yet, use dba views if available by default.

I have this same issue.  As one user I cannot see the package body, but as the schema owner I can.  It would be nice to have a fix or work around.

This is actually a pretty big deal.  I cannot get my team to adopt DataGrip because this is such a hindrance in day to day work.

I solve my problem with Generate DDL to Console on package body and compile it!

Any updates here? 
Having the same issue.

Having the same issue. Any fix for this or is DataGrip just unusable for seeing package bodies? I've tried everything listed here multiple times with multiple people and have had no luck.

So, any news on this?

This support ticket is around for 4 years and no response from JetBrains?

I would accept it not showing package body in case it was a community vs ultimate version kind of issue, but since there is no community version of DataGrip, it should as well be fully funcional.

Please take  a look at screenshot:

Do you see 'bodies'? Have you clicked here?

I have noticed that this issue seems to occur for me when I'm using a schema with a large amount of objects. When using a schema with a smaller amount of objects the issue goes away after it finishes doing the "Introspecting schema <SCHEMA> completely..."

When I go to Help -> Show SQL Log in <File Browser> and look in the database.log file I notice there are queries like the following, which take a long time to run.

select type, name, text
from sys.DBA_source
where owner = ?
and type in ('TYPE','TYPE BODY','PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
and name in ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
order by type, name, line

On one of my databases and a schema that has 211 items with ~30k lines it takes about 30 seconds to run, but on one of the larger databases which has 760 items and ~360k lines in a schema it takes something like 30+ minutes to run.

I've tried every setting in the options that would change how this introspection works, the JDBC metadata stuff, auto sync, load sources for, use pre-introspected objects. Nothing really seems to affect this behavior.

I suspect the answer to this issue lies somewhere in that query and the way this data is generated for schemas that are large. Ideally there would be an option that allows the IDE to just get the names of all the stuff and to get the source code whenever you open that specific item because trying to introspect a very large schema with that query makes the IDE practically unusable.

Brandonmills could you please share DataGrip and Oracle versions?

@... DataGrip version is 2020.2 and Oracle version is 12.2.0.1.0

Hello Brandonmills, could you please perform the following query in your database and provide us with results?

with S as ( select owner, name, type, count(*) as lines, sum(length(text)) as amount
         from dba_source
         group by owner, name, type )
select type, count(*) as cnt,
       round(avg(lines),0) as lines_avg, max(lines) as lines_max, sum(lines) as lines_sum,
       round(avg(amount),0) as amount_avg, max(amount) as amount_max, sum(amount) as amount_sum
from S
group by type
order by cnt desc
/

I commented above, regarding all_source vs dba_source.

I can see package bodies in IntelliJ Idea 2020.1. So I think the issue I had is fixed. I know it is not quite DataGrip, but still.

Hello Brandonmills,

thank you for statistics.

Your database is really huge, we've never tested DataGrip with such ones.

Right now I can recommend the following options:

1. Disable obtaining source in the data source configuration (page Options, section Introspection, Load sources for: None)

2. Grant the user you're connection to the Oracle with, both the "select_catalog_role" role and "select any dictionary" permission

3. Uncheck syncing with unnecessary schemata from the data source

I hope all this helps.

We're discussing how to change DataGrip to get ability to work with so huge databases, but the implementation will be (I hope) in 2021.

Hi Leonid

just in case it's useful, here you have another example of a real BIG database (Oracle 12c):

Thanks

Having the same issue.  We have a very large database with lot of code, so this is a critical feature for me. 

As with other posters, I'm able to access and edit type/package/procedure bodies using SQLDeveloper.  I made the Options changes suggested by Leonid, but I was unable to run the diagnostic, it returned the error:

[42000][942] ORA-00942: table or view does not exist
Position: 100

I can see the dba_source 'synonym' in the PUBLIC schema, but opening it also results in an error (SYS is in my datasource view):

SYS> SELECT * FROM (
         SELECT t.*
         FROM SYS.DBA_SOURCE t
     ) WHERE ROWNUM <= 501
[2021-09-04 09:09:03] [42000][942] ORA-00942: table or view does not exist
[2021-09-04 09:09:03] Position: 44

----

edit/update: User error.  Only the user/schema can see sources in that schema.  I recreated the "connection per schema" scheme we use with SQLDeveloper and sources are there.

Oracle is expensive therefore databases big, I'm tried 2 years before (Oracle EBS databadse) the same story today with 'incremental' introspection. It 'introspect' from 8am till 20pm still no results, not able to open any package to edit. They say that Oracle is slow and about limits. Why just not admit that this product fits toy like databases? 

Rudenko S,

Could you describe your flow? What DataGrip version do you use? Have you tried introspection levels?

You can e-mail me vasily.chernov@jetbrains.com directly for further assistance.