Two bugs

[Kuba]

There are two another bugs in PL/SQL Developer:

1. Not null, deferrable, initially deferred constraint problem.

Try to this command on any table:

alter table table1 add f1 number(8) not null deferrable initially deferred;

In 'Oracle Enterprise manager console' in table editor this column, as in PL/SQL Developer, is marked as 'nullable', BUT there is new contraint (on 'Constraints' bookmark) named (for example) 'SYS_C004986', type 'CHECK', with content: '"F1" IS NOT NULL' and checked 'DEFERRABLE' and 'INITIALLY DEFERRED' options. In PL/SQL Developer this constraint is not visible on table editor.

2. Explain plan and constants problem.

Try to explain plan of this query:

select * from table WHERE podmiot.act = Constants.T;

where Constants is a package with constant 'T':

CREATE OR REPLACE PACKAGE "CONSTANTS" AS
  T CONSTANT CHAR(1) := 'T';
END;

You should get an error. I think this is not exactly a plsqldev bug, this may be Oracle problem, but plsqldev could change such fields to any acceptable constants with the same type.

What you think about it?

 

[Marco Kalter]

1. We'll fix this.

2. You cannot reference a packaged constant in a select statement. You cannot run it, and you cannot get a query plan. You will need to encapsulate the constant in a function.

 

[Kuba]

1. Thanx!

2. That's not true! I'm referencing constants in select stataments very often - in stored procedures, and it works!

 

[Bo Pedersen]

A package constant can only be referenced by the same PL/SQL enginee and not by a remote enginee. I.e. a constant in a database package can only be referenced in local PL/SQL and not by remote as for example Oracle Forms.

I think it is the same issue here. By starting in SQL you get another PL/SQL enginee when calling the package. The mentioned select cannot be executed as a stand-alone SQL. But if you wrap your select into begin/end, i.e. inside PL/SQL, then you get a local PL/SQL enginee that can refer to the constant. I assume that all the selects you refer to are nested inside PL/SQL

In short: package constants can only be referenced inside PL/SQL running at the database.

And since the explain plan is based on SQL, it is not possible to get explain plans for this directly. You need to rewrite the select statement.

Bo Pedersen

 

[Kuba]

I understand, you're right, but, as I wrote, I'm using constants in select statementens in PL/SQL stored procedure. Very often I need to check explain plan of such query. Then I mark it in stored procedure editor and press 'F5' to explain plan. If in query is any parameter reference plsqldev show error 'invalid identifier', cursor is before this identifier. I have to put ':' then and explain plan works fine. Wore situation is when there is constants reference. I need to replace it with constant ('0' or 'A' for example) - it costs some work. It would be very nice if plsqldev replace that references automaticaly. The same could be done with ':' before parameter reference. This could be an option in 'Preferences' dialog of course.

What you think about it?

 

[Marco Kalter]

Okay, perhaps we can add this.