Feature Request: Tools

[wbennett]

Request that the tools options support the Explain Plan window type.

e.g.

Our database has different schemas for different countries with the same table structure (don't ask why).

The first thing that is done in the session is changing CURRENT_SCHEMA:

if country_cd = 'US' then v_schema := 'US_USER';
elsif country_cd = 'UK' then v_schema := 'UK_USER';
end if;

Execute immediate 'alter session set current_schema = ' || v_schema;

Now all our queries can be like:

select * from employees;

instead of

select * from us_user.employees;

which would require dynamic sql.

The only problem with this is that it does not work in the explain plan window. Especially since we the developers, cannot log in as the schema owner.

Along the same lines, I have created a descendant of TOracleSession that exposes a SessionParams array (using a TStrings property which allows for future Oracle variables). This allows me to set these at designtime or write code such as:

session.SessionParams['current_schema'] := 'uk_user';

which will then execute the alter session command.

Could you add this to the session component too?

 

[Marco Kalter]

In the Plan Window you can execute an alter session statement, and subsequently display the query plan for a select statement.

Alternatively you can create a "Session tool" (Tools > Configure Tools) to perform the alter session statement for the current session.

Finally, you can modify the AfterConnect.sql script in the PL/SQL Developer installation directory to run the alter session statement for all sessions created in PL/SQL Developer.

I have added your TOracleSession.SessionParams suggestion to the list of enhancement requests for Direct Oracle Access (wrong forum).

 

[wbennett]

Marco,

Your "b" solution is what I'm asking for. Currently it does NOT allow you to select the Explain Plan window as one of the types that this "Tool" is enabled for (on the Options tab). I have my "Session Mode" set to "Multi Session". If I run the command from a SQL window, the Explain Plan window has no knowledge of this.

Solution "a" does not work. You cannot execute SQL or PL/SQL in the Explain Plan window.

Solution "c" is not acceptable. I need to switch schemas based on data that I am testing.

Thanks

 

[Marco Kalter]

Solution "a" does not work. You cannot execute SQL or PL/SQL in the Explain Plan window.

Do you have the latest release? This was added in 6.0.5.