DBMS_OUTPUT works only for sys and doesnt work for any other schema

Toad for Oracle
1

Hello!

I’ve got a following problem I’ve spend day to find a solution for.

When I’m trying to get dbms_output tab and press a red dot button “Turn output on” as a SYS, everything works just fine. But if I do the same as any other schema user, for instance SM, I get an error instead:

ORA-04067: not executed, package body “SM.DBMS_OUTPUT” does not exist
ORA-06508: PL/SQL: could not find program unit being called: “SM.DBMS_OUTPUT”
ORA-06512: at line 1

I’ve already tried to rerun dbmsotpt.sql script and it didnt help. Whatever info I could find on the internet it’s mostly about “it should work from the box, just press the red dot button”

2

Do you have a schema named “SM” that has a package named DBMS_OUTPUT? That sounds like it might be the problem. If you do and can drop it you should be OK. I’ll look at the Toad source and make sure that all DBMS_OUTPUT references are properly pointing to SYS going forward.

Michael

3

I’ve changed the code that enables/disables/fetches output to make sure that it locates the package owned by SYS. However, we still have a few places that generate DBMS_OUTPUT statements that will fail for you if you use these features. I’m not touching those since this is the first I can recall the issue coming up and many users may not want to see the sys prefix everywhere. I’d sort out whatever conflict you have if that is what is going on.

4

Hello!

Thanks for the answer!

Yea, I’ve got SM schema and it’s got package DBMS_OUTPUT, which I can drop as SM. If I drop it and then press the dbms_output start button, it hangs toad.

Do I need to install an update for toad in that case?

5

wow…I’ve dropped the package once more, then closed Toad, then started it again and connected to the database as SYS, rerun the dmbsotpt.sql, started SM session, pressed red dot button and…it worked!

thanks for the help!

6

That’s great that you got it working, but… how did you drop the package once more? If it was dropped once then you wouldn’t be able to drop it again unless it was recreated. If it’s being recreated you’ll need to track down how that’s happening and nip it in the bud.

7

whenever something bad happens because of someone’s either stupidty or vile intent - it’s more likely to be stupidity.

Originally I’ve dropped that package and rerun the dbmsotpt.sql script as SM. I guess that was my original mistake - running that script on SM schema. Because I’ve got the same problem back right away after that.

Okay, so I’ve dropped that package once more, logged as SYS and rerun the script thus recreating package for SM as well, but this time with SYS privilegies. And that was the deal as it seems.

8

You shouldn’t be creating DBMS_OUTPUT for whatever users need to use it. Just grant it to them. You might need to log in as SYS to do this:

GRANT EXECUTE ON DBMS_OUTPUT to ;

or, if you want every user to be able to use it:

GRANT EXECUTE ON DBMS_OUTPUT to PUBLIC;

I am fairly certain that this package is granted to public by default. Maybe someone revoked that in your database.