Init oracle sessions

[datadeconline]

IS possible to initialize all oracle sessions that opens the PL/SQL Developer?. We want to call a database procedure or function that init session variables (VPD, DBMS_RLS), like trigger PRE-FORM on Forms.

Regards
P.Gutierrez

 

[Marco Kalter]

Yes, you can edit the AfterConnect.sql file in the PL/SQL Developer directory and enter any SQL or PL/SQL block here. SQL statements must be terminated by a semi-colon. PL/SQL Blocks must be terminated by a slash.

 

[datadeconline]

Hi Marco,
I test with your notes and modify AfterConnect.sql but I don't see changes. It's like don't execute this file, I put this:
--------------------------------------
BEGIN
--
ASP_ENTORNO_ASP.INIT (7640);
--
END;
/
--------------------------------------
What can I do ?

Regards
P.Gutierrez

 

[Marco Kalter]

Two questions:

1. Which PL/SQL Developer version are you using?
2. How do you determine that asp_entorno_asp.init was not called?

 

[datadeconline]

1. PL/SQL Developer Version 6.0.6.947 01.29667
2. We insert in one table and execute commit with asp_entorno_asp.init, it only inserts one record in a table. When I open a command window or a explain plan window (another oracle session) it doesn't insert into table. Only one record but I have many sessions

Regards
P.Gutierrez

 

[Marco Kalter]

This works fine for me.

Is your "Session Mode" preference set to "Multi-Session"? See Tools > Preferences > Oracle/Connection.

Also note that a session is only created when you actually execute something in a window.

 

[datadeconline]

Hi Marco,
It's ok, this works fine.
How can stop connection from AfterConnect.sql?
With this file in "AfterConnect.sql"
DECLARE
--
--
BEGIN
--
RAISE_APPLICATION_ERROR (-20001, 'No entry allowed');
--
END;
/

clients can connect.

Regards
P.Gutierrez

 

[Marco Kalter]

You can't prevent a connection through AfterConnect.sql. If it raises an exception, it is ignored. If you want to prevent users from connecting to specific databases with PL/SQL Developer, you can use the PL/SQL Developer Authorization functionality instead.

 

[datadeconline]

Can display a message from AfterConnect.sql ?

Thx
P.Gutierrez

 

[Marco Kalter]

No. What exactly are you trying to accomplish? Maybe there is an alternative besides AfterConnect.sql.

 

[datadeconline]

We want control all oracle sessions that connect from PLSQL/Developer classified by osuser, client machine (IP) and oracle user. With this three keys we want allow connect or not and display a message to user with cause.

 

[ScottMattes]

Isn't there a DB logon trigger where this more properly belongs?

 

[CTzen]

Well you can raise exception in logon trigger and prevent user from connecting, but it still doesn't help with 'displaying a message', imho.

 

[datadeconline]

ok, thanks a lot.
With this options we can work fine.

P.Gutierrez
Datadec Online