Problem to open Package BODY Verzion 4.5.1.538

SQL Navigator
1

Hi,

I am facing problem while opening the PACKAGE body of database.

I am able to open the PACKAGE body for some database XYZ and i am not able to open the body part of some database ABC what can be the reason for that can you please assist me so i can try with those options and can able to see the package body in my system.

Thanks waiting for your reply.

2

Hi Daivish,

4.5 is a pretty old version of Nav. Even if it’s an issue, there’s not much we can do. I recommend you to download a free 6.2 beta, it may contain a fix for this issue.

Gwen

Message was edited by: Gwen

3

I use SQL navigator since at least version 4.1 and I never had that problem.

  1. the body its not wrapped?
  2. do you have all the package in your database (not only the spec?)?
  3. what error do you get?
  4. you could try issuing a
    select * from user_source where name=<package_name> and type=‘PACKAGE BODY’
    to check if you really have code for that

Regards
Filipe

4

Hi,

I am getting following Error :

Error when opening a DB object: ORA-00942: table or view does not exist

The thing is My colleague is using Same Version of SQL Navigator and he is able to see all the packages body and only me not able to see the package body for two Specific Database only. While we have total 4 database and i can able to see for other two database package body in my sql Navigator but this is something i am experiencing very strange issue.

My question is it something USER Related ACCESS we can give on database level that is why i am not able to see this package BODY ?

Please let me someways that i can check or let me know the solution to solve this issue.

Thanks,
Daivish.

5

Only the schema user has access to the body code…all others users, if they have being GRANTed EXECUTION on the package, only see the Package Spec.

You didn’t answer all my questions and that makes difficult to guess what is the cause of your problem

Regards
Filipe

6

When you logged in, did you have the option ‘Enable DBA dictionary view’ ON or OFF?

7

Hi Filipe,

Here is your answers.

  1. the body its not wrapped?
    Body is not wrapped actually another team mate can access the body part with same USER ID and PASSWORD for that database in his machine but not with my SQL Navigator he is using same version .

  2. do you have all the package in your database (not only the spec?)?
    I have all packages in Database.

  3. what error do you get?
    Error when opening a DB object: ORA-00942: table or view does not exist

  4. you could try issuing a
    select * from user_source where name=<package_name> and type=‘PACKAGE BODY’
    to check if you really have code for that

I ran this query and it is giving me output for 1561 ROWS with details of NAME,TYPE,LINE,TEXT.

Please let me know…

Thanks,
daivish

8

Hi,

Enable DBA Dictionary view is ON, the check box is clicked.

Thanks,
daivish

9

Hi Daivish,

If another user used the same userid & password and it worked for him and not for you, it’s probably because of the ‘Enable DBA Dictionary view’ Preference. Maybe you have this option ON and he has it turned off. Please try turning it off and see if it works in your environment. Otherwise, I would strongly recommend you upgrade to a newer version of Nav.

Gwen

10

Wowwwwwwwwwwwwwwwwwwwwwwwwwwwwwww !!!

Thanks a lot Gwen…

I worked for me and now i am able to see the body of all packges and it’s working well.

Thanks again.

Regards,
Daivish.

11

Glad it helps.

12

Hi Gwen

Soo, exactly what “Enable DBA Dictionary view” does/allow? I mean, shouldn’t that flag make a check after the start of the connection to check if it makes sense to be “on”?

Thanks

Filipe
Message was edited by: Fsilva

Message was edited by: Fsilva

13

Hi Filipe,

You’re right. It should check first. We’re aware of this but due to the complexity of the case we decide to pospone it till after 6.2.

Gwen

14

Hi Gwen

I think that the check would be easy:

  • check if the user has DBA role, SELECT_CATALOG_ROLE role and SELECT ANY TABLE Privilege, (and SELECT ANY DICTIONARY ACCESSIBILITY if the initialization parameter O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
    (this info is from the user guide :-))
  • or if a select to the SYS.DBA_% fails revert to “Disable using DBA views” giving a warning message

Regards
Filipe

15

Hi Filipe,

Many thanks for your note. We have attempted refactoring this option/preference in 6.2 release but decided to defer it to post 6.2, due to risks associated with the changes…

We will take into account your feedback on how we should ultilize the option in Nav.

It was initially intended to work as an option to set the Enable DBA checkbox in the log in screen, but then it was extended with usage to give our users the option to choose which view to be used for testing purposes or due to performance issues on some environments. We believe the current behaviour is not that clear and it is confusing to our users regarding how this option works…

We are on the same page with your suggestion, we will need to review this option/preference, and will keep you posted with the progress in the next release (post 6.2) Beta, so that you can check it out.

Thanks again and regards,
Bruce

16

I had this problem as well, after enabling DBA views…I can view the body after opening the object but the bodies are still not listed in the DB Explorer Pane.
Any ideas? or is this another item slated for 6.2?

Message was edited by: hendross

17

Hi Patrick,

Which version of Nav are you using? If you’re using 6.2 beta, it should have the fix for DBExplorer.

Gwen