Toad World® Forums

Cannot check out code


#1

Hello,

With SQL Navigator 6.2 Beta Build 1250, I tried to check out code (functions, procedures) on an Oracle 10.2.0.4 database. I did get the Code Editor to display the code, but the status bar keeps displaying “Loading object source…” and I get an ORA-00942 error in the output window (see attached screenshot).
The toolbar does not display the “Save to Database” icon (just the normal Code Editor icons, I think), and I cannot save my changes in the code to database using CTRL-S, instead I’m asked where to save the Code Editor’s content to disk.

BTW: SQLNav 5.5.4 has no problems.


#2

Hi Dominique,

Please clarify these information for me. Was the user you were using got granted ‘select_catalog_role’? I assume you right clicked on an object in DBX/DBnav tree and selected check out? Which tree was you using?

Gwen


#3

Gwen,

Indeed, this role was missing, however, even after granting this role, the ORA-00942 error remains present.

How I proceeded:

  • logged in
  • in the the DB Explorer window, double click a function
  • Confirmation: Do you want to check out this object? (Yes)
  • Team Coding: Check Out […] - function/owner - Comments (left empty)
  • Function code loads in new Code Editor window, ORA-00942 appears in the output window, “Loading object source…” stays on the status bar

Things that got fixed through the role grant: CTRL-S now works as it should (save to database), the icon bar now also displays the “Save to Database” icon (i.e. it is the familiar icon bar when editing database code, not the default Code Editor one).

Anyway, this enables me to edit/save code: me happy, thanks!

BTW: I’ve checked the 9.2.0.7 database from which the current 10.2.0.4 originates. The same user there has only “connect” and “resource” (as (s)he had on the 10), yet I can check out code there without encountering the problems I had/have on 10g. What more am I missing?


#4

Hi Dominique,

The reason I asked if you user has that role is because there’s a bug where a customer got this oracle error when his user has this role. I just wanted to check if you has the same issue. Hence, there’s no point in adding this role to your user.

Just to be clear, your user only has connect and resource role. No other privileges?
Does this function belong to your current schema or a different one?

Do you have the “user DBA view” selected when you log in?

Gwen


#5

Gwen,

The code being checked out/edited belongs to the current schema and the “Enable using DBA views” check box is flagged.

The user at first only had connect and resource.
This prevented me from saving any changes made to the checked-out code (along with the ORA-00942 error and the “Loading object source…” message).

With select_catalog_role granted, I can save changes (the ORA-00942 error and the “Loading object source…” message remain, but it appears as though I get “further” in the checkout process - the icon bar is now the familiar one when editing code).

Update: the above only applies to functions. The behavio(u)r for packages remains the same whether I have select_catalog_role or not: it is not possible to save edited code to the database.
And when I right-click (in DB Explorer) on “Functions like…” or “Procedures like…” and select “Create” in the pop-up menu, I only get an ORA-00942 error in the output window - nothing else happens.
(I tried to create a procedure to see if select_catalog_role also allowed to edit/save procedures, that’s when I found this out)


#6

Hi Dominique,

I couldn’t reproduce your issue. Mine let me save and check out without any error. Which client were you using? Can you post a couple of screenshots please?

Gwen


#7

Gwen,

The screen shots are packed in a zip, attached.

What I do:

  1. Open SQL Navigator, connect to the database with the user (B48_001).
  2. In DB Explorer window, open the functions for the current user (B48_002).
  3. Select a function, double click or press Enter - I get the message “Do you want to check out this object?” (B48_003).
  4. Select “Yes” - I get the “Team Coding: Checkout” message and enter some comment to describe the reason for checking out (B48_004).
  5. Select “OK”. Now, the code loads in the Code Editor window (B48_005, blanked out actual code, sorry). Notice the ORA-00942 messages (the first one seems to be generated when logging on, the second when checking out the code) in the output window and the “Loading object source…” message in the task bar beneath the code editor and db explorer.

If I do CTRL-S now, I get the “Save As” dialog (I’d expect this shortcut to compile/save the code to DB). The icon bar above the code editor/db explorer is also not the one I expect to see when editing DB code (it’s the same one a in a new code editor window).
Essentially, I can edit the code, but I cannot compile/save it…

Edit: Could it be that SQL Navigator is looking for the plan table? This user does not have one…
B48.zip (139 KB)


#8

Hi Dominique,

Thanks for the screenshots. I found a couple of strange things in there.

Firstly, the code editor toolbar changes depending on if the current content is a script or an object. Some buttons that are available for script will be hidden when the object is open such as: execute to end, execute step… Somehow, even after you check out the object, the layout of the code editor doesn’t change. That’s why when you save, it gives the Save as dialog.
Secondly, I noticed that your layout is a bit different than mine. At the top, you have the Project drop down list. This one has been removed in 6.2. It shouldn’t be there.
I suspected there’s something wrong with your installation process. It might be possible that you install 6.2 beta on top of 6.1 (e.g in the same folder).

I would suggest you uninstall the current version. Make sure it’s uninstalled correctly (shortcut is removed, link in Start>Program>Quest Software is removed) and install 6.2 again.

Let me know if it helps

Gwen


#9

Gwen,

I always uninstall the previous version before installing (except for the 5.5.4 version, which I keep as a fallback option).

Anyway, I uninstalled SQL Navigator 6.2.0.1250 (check: all links were gone) and reinstalled it.

The project drop down list is still there, it is part of the “functions” toolbar - see screenshot (right clicked the mouse just to the right of the toolbar).

UPDATE: If I grant “DBA” to the user, everything is OK (no ORA-00942 errors, no problems when checking out code). Thus it would seem that a “default” user - only being granted “connect” and “resource” - cannot access a table (or view) which SQL Navigator 6.2.0.1250 parses just after logging on and when checking out code…


#10

Hi Dominique,

If you right click on the toolbar, select Customize and choose Reset, will that list disappear?
I tested using users that only have resource + connect --> no issue whatsoever.
I even tested with users that only have connect role --> couldn’t reproduce either.

Gwen


#11

Gwen,

Indeed, if I select “Functions Toolbar” in the “Customize…” dialog, press “Reset…” and confirm the changes to be made, the list disappears.

As for the ORA-00942’s and code checkout problems: maybe it has something to do with the DB being 10.2.0.4 (standard edition)?
Oh, and 5.5.4 does not have any problems (no ORA-00942’s, no code checkout problems). I’d think SQLNav 6.2 tries to access a new table or view - if it would be of help, I could try to install 6.1 (or whatever other version) to see if it happens there, too.

BTW: it’s a bit awkward to live a significantly different timezone - would there be a way to have the forum display “forum local time” (i.e. the time at your place, which would be Melbourne, Australia)? Thx.


#12

Hi Dominique,

You may be right. Could you please capture the queries fired by Nav 5.5 and 6.2 using SQL Monitor when the object is checked out?
I will have a look and see if there’s anything suspicious.

Thanks
Gwen


#13

Gwen,

Found it! There’s a query against sys.all_arguments, which has a public synonym, but public is not granted select (see attached tkprof output).

As sys, I then did “GRANT SELECT ON all_arguments TO PUBLIC;” …and all is well.

I wonder if this is a “feature” of Oracle 10.2.0.4 or was caused by the upgrade from 9.2.0.7…
output.txt (56.4 KB)


#14

I’m not sure why your user doesn’t have privilege to select from that view. Mine just has connect & resource role and the query works fine.

Gwen


#15

Gwen,

I guess (and assume) something went wrong upgrading the database.
Sorry to bother you with this problem.


#16

Sorry I couldn’t help much. Please let me know if anyone else have the same problem.