Empty package <package name> definition (no public members)

10
votes

I'm facing an issue when trying to run or debug a package where Oracle SQL Developer doesn't display public members (procedures) that don't have input parameters.

There is a couple of procedures, some overloads, but Oracle SQL Developer doesnt display no invoke-able members.

enter image description here

I've tried with following versions of Oracle SQL Developer

  • 4.1.1.19 link to article saying this version solves the issue, but it didn't
  • 4.0.3.16 - same behavior
  • 4.0.0.13 - same behavior
  • 3.2.20.09 - this version actually displayed invokable members, but debugger doesnt work.

This is the Oracle version :

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Has anyone successfully resolved similar situation and how? (Upgrading database is not an option atm)

4
oracleoracle11goracle-sqldeveloper

4 Answers

7
votes

A bit late, but i could help. There is a SqlDeveloper bug. What I have to do is to "Save" it before Compile it...

Hope it helps

3
votes

I found 2 possible cause that may cause the no public member problem

1) SQL Developer hit some kind of bugs itself. This can be resolved by completely close and restarting the SQL developer

2) Some keywords used in your codes (e.g. field name in SQL statements) that works fine in compiled execution , but would ruin the display of function list.

I resolved my problem by applying double quote to the entire packages where a field named “CASE” is used.

i.e. SELECT table1.case FROM table1

change to

SELECT table1."CASE" FROM table1

Alex

1
votes

Definitely late , but came across the same issue & thought of posting what helped me .

The proc was contained in a package that belong to another user & the grants were not provided for the user which was calling the proc .

Works when the grants are done.

-4
votes

I would recommend to save the stored procedure and then compile. In some cases, after saving the procedure, the table will automatically compile. But, you have to save each and every time you make changes.

I hope this helps you out.