Oracle - Packages body not loaded about dbeaver

[serge-rider]

Is there anything in error log?

[steromano87]

Hi Serge,

the error log is empty, but I observed that one of the metadata queries that are called to retrieve data gives 0 rows as result:

I don't know if this information can be helpful...

[serge-rider]

Thanks for info. At least I'll fix source loading text.
Could you please check that this package body is loaded in other Oracle tools?

[steromano87]

Hi Serge,

I confirm that the package body is correctly loaded and parsed by TOAD using the same user.

TOAD retrieves also a slightly different package declaration in header.
TOAD:

CREATE OR REPLACE package SER_MIFID.PKG_CALCOLO_PATRIMONIALE as
    TYPE elencoCLI is varray(8) of char(11);

DBeaver:

package           PKG_CALCOLO_PATRIMONIALE as
    TYPE elencoCLI is varray(8) of char(11);

[serge-rider]

Yet another strange issue.
Are you working as SYSDBA or as a normal user? Could try it as SYSDBA (if you have such permissions)?

There is a difference in package declarations because TOAD makes additional source processing and inserts CREATE OR REPLACE operator. I think DBeaver also should do it..

[serge-rider]

I think that this problem has the same nature as #37. Something about permissions.
But if you are using the same user then the only possible difference is a different connection role (SYSDBA, SYSOPER, Normal). Or maybe some additional initialization queries.
Did you try any other tools besides DBeaver and TOAD?

[steromano87]

Hi Serge,

using TOAD I connect to the same DB using the same user and the same role (normal).
I tried using SqlDbx too, and I can see the package body without issues connecting as normal user.

[serge-rider]

I'm frustrated a bit.
Could you try this trick:
Execute query

SELECT TEXT FROM SYS.ALL_SOURCE WHERE TYPE='PACKAGE BODY' AND OWNER='SER_MIFID' AND NAME='PKG_CALCOLO_PATRIMONIALE'

in DBeaver and in TOAD. Is there any difference?

[steromano87]

Hi Serge,

executing the query on DBeaver and TOAD gives no result on both of them :-( I also checked and TOAD uses the same connection to execute queries and to retrieve package body.

Hoping to be more helpful, if I inpect the current user privileges using the following query:

select * from session_privs

I obtain the following privileges:

• CREATE SESSION
• ALTER SESSION
• UNLIMITED TABLESPACE
• CREATE TABLE
• SELECT ANY TABLE
• INSERT ANY TABLE
• UPDATE ANY TABLE
• DELETE ANY TABLE
• CREATE CLUSTER
• CREATE SEQUENCE
• ALTER ANY SEQUENCE
• SELECT ANY SEQUENCE
• CREATE PROCEDURE
• ALTER ANY PROCEDURE
• EXECUTE ANY PROCEDURE
• CREATE TRIGGER
• CREATE TYPE
• CREATE OPERATOR
• CREATE INDEXTYPE
• SELECT ANY DICTIONARY
• ADVISOR

[steromano87]

Hi Serge,

after some googlin I found the solution of the issue :-)

The correct schema to retrieve the package body if the current user has EXECUTE PACKAGE permissions and the package belongs to another schema is SYS.DBA_SOURCE and not SYS.ALL_SOURCE.
If you execute this query on DBeaver and on TOAD:

SELECT TEXT FROM SYS.DBA_SOURCE
WHERE TYPE='PACKAGE BODY'
    AND OWNER='SER_MIFID'
    AND NAME='PKG_CALCOLO_PATRIMONIALE'
ORDER BY line

I get the package body.

References: TOAD Wiki, Oracle Community

[serge-rider]

Hey,

Thanks a lot for information.
However I doubt that using DBA* views everywhere is the best choice. These views are usually not accessible to users without DBA permissions.
But I think I could try to read info from both DBA and ALL views. If DBA* view is missing/not accessible or contains no data then try to read from ALL* (or even USER*).

Will try to fix this in the next version.

[serge-rider]

Now DBeaver will read sources from DBA_SOURCE view (if it is available for current user).