No option to edit package body

Hi,

 

I must be doing something very wrong but I can't seem to be able to edit packages - or rather, I can't seem to be able to edit the package body. In the "Database" pane, I can expand the packages folder with this listing all of the packages and also the functions that are defined within. I can also right-click and the context menu allows me to select "Package Editor", which opens up the package definition in the editor. I am, however, unable to edit the body of the package and can't find an option to do that anywhere within datagrip.

Strangely enough, a colleague that also has datagrip shows a slightly different Database pane in that when he expands a package, he not only sees the package definition but also a second hierarchy where he can drill into the package body (whereas in my pane, I can only expand the top level of the package). Any help with what I'm missing would be much appreciated.

 *edit - DataGrip 2016.2*

 

Thanks heaps

20 comments
Comment actions Permalink

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.

1
Comment actions Permalink

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?

2
Comment actions Permalink

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

 

0
Comment actions Permalink

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.

 

0
Comment actions Permalink

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

0
Comment actions Permalink

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.

0
Comment actions Permalink

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.

0
Comment actions Permalink

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

0
Comment actions Permalink

Any updates here? 
Having the same issue.

0
Comment actions Permalink

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.

1
Comment actions Permalink

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.

0
Comment actions Permalink

Please take  a look at screenshot:

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

-1
Comment actions Permalink

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.

0
Comment actions Permalink

Brandonmills could you please share DataGrip and Oracle versions?

0
Comment actions Permalink

Yuriy Vinogradov DataGrip version is 2020.2 and Oracle version is 12.2.0.1.0

0
Comment actions Permalink

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
/

 

0
Comment actions Permalink

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.

0
Comment actions Permalink

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.

0
Comment actions Permalink

Hi Leonid

 

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

 

Thanks

0

Please sign in to leave a comment.