joi, 27 februarie 2014

Capturing the updated column in a trigger

Sometimes, you need to make a difference between the fact that some column or another one is updated.
Thus, the trigger can fire in a very fine grained fashion.

The trick is very simple, just use the function updating(column_name).

e.g.:

create table test (field1 number, filed2 number);

CREATE OR REPLACE TRIGGER trigo
before update
  ON test 
 REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare 
----
BEGIN
if updating ('field1') and not updating ('field2') then
raise some_exception;
end if;
exception
WHEN some_exception THEN
    Raise_application_error (
      -20300,
      'bad ideea  !!!!' 
    );
END ;
/

In the example above, the trigger prevents updating of the column field1 and leaving the column field2 untouched.

marți, 25 februarie 2014

How to take some session parameters into your variables

There are a few of posibilities. One of them: sys_context().
As you can read at 
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm 
it is a great deal of information within...
Just try this command:

select sys_context('USERENV','SESSION_USER') as username,   sys_context('USERENV','IP_ADDRESS') as ipaddress,   sys_context('USERENV','HOST') as machinename, sys_context('USERENV','module') module, sys_context('USERENV','action') action,
sys_context('USERENV','client_identifier') client_id, sys_context('USERENV','client_info') client_info, sys_context('USERENV','current_schema') schema, sys_context('USERENV','current_schemaid') schemaid,sys_context('USERENV','current_sql') sql from dual;

and maybe you'll notice a lot of parameters that you would wanted them in some audit tables or used by some triggers. For instance, i used the <module> value in order to block a Forms application to change the value of a column in a table.
Enjoy!

joi, 6 februarie 2014

When you relocate Putty and WinSCP

Are you on  a machine that doesn't have putty or winscp, and after their installation, you're frustrated about all the machines in the current  configuration?
It's pretty easy to copy the configurations alog. Take a look:

On the original machine, go to

HKEY_CURRENT_USER\Software\SimonTatham

Exporting Your PuTTy Configuration

Putty stores its settings in the Windows registry. To save a backup of your Putty settings, you'll need to export this registry key to a file. 

HKEY_CURRENT_USER\Software\SimonTatham


click on SimonTatham name, than Click File->Export and save it as a file *.reg

Copy the file on the source machine and right click -> Merge than confirm by clicking <yes> ant this is all.
Your good old configuration is replicated.

Now WinSCP.

Open the login window and click Tools>>Import and you'll see all the machines from the Putty configuration (see above).
Check all, click OK and enjoy!

marți, 4 februarie 2014

Oracle APEX. Problem: ERR-1016 Application "nnn" Page "xxx" not found (requested language="en


During the login process, this bug will prevent the proper authentication, but it will be gone after a second try. Nevertheless, don't let it there or the users will not be very happy.

Solution:
Application Builder > Your Application > Page 101 > Processes (Page Processing) > Login

wwv_flow_custom_auth_std.login(
   P_UNAME => :P101_USERNAME,
   P_PASSWORD => :P101_PASSWORD,
   P_SESSION_ID => v('APP_SESSION'),
   P_FLOW_PAGE => :APP_ID||':1'
   );

Just wipe out the 'about session' row! The result will be:

wwv_flow_custom_auth_std.login(
   P_UNAME => :P101_USERNAME,
   P_PASSWORD => :P101_PASSWORD,
   P_FLOW_PAGE => :APP_ID||':1'
   );

and you'll be glad to note the issue has dissapeared!