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

8
23 comments

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

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

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
Avatar
Permanently deleted user

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

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

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
Avatar
Permanently deleted user

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
Avatar
Permanently deleted user

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
Avatar
Permanently deleted user

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

0
Avatar
Permanently deleted user

Any updates here? 
Having the same issue.

0

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

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
Avatar
Yuriy Vinogradov

Brandonmills could you please share DataGrip and Oracle versions?

0

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

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
/

 

0
Avatar
Permanently deleted user

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

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
Avatar
Permanently deleted user

Hi Leonid

 

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

 

Thanks

0

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.

0

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? 

0

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.

0
Avatar
Yuriy Vinogradov

Please take  a look at screenshot:

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

-1

Please sign in to leave a comment.