Oh yes: http://www.myfoxdetroit.com/story/23118642/bra-sizes-of-female-detroit-cops-mistakenly-emailer-to-officers#ixzz2bs9wF2qy
dinsdag 13 augustus 2013
donderdag 30 mei 2013
Launch clientside executables from webapplication
Some time ago I wrote a post "A method to execute external programs via APEX", here DBMS_SCHEDULER was used to execute commands on the (database)server. Recently we had to find a way to "launch clientside executables via APEX". On the web various solutions are described using ActiveX (via shell.application), but for some reason I wasn't able to get that running. To be clear about our ambition: we want to launch a program, add some parameters, on the clientmachine that is viewing our webpage. We thus want to leave the browser sandbox.
The path we took was inspired by the following hyperlink. In a nutshell; we add an URI handler to the Windows registry so that hyperlinks of the form
The before mentioned hyperlink uses Inno Setup, to build an installation program that makes the registry modifications for us. The beauty of Inno Setup is that de-installation of our registry modification is very easy (via default add/remove). I left the default setting pretty standard and added the following lines to the new setup file:
Myhyperlinktextlaunch the windows notepad program on the client with argument <<text>>. Adding an URI handler requires us to make a modification to the windows registry: we need to tell Windows that hyperlinks of the form "webrep:<<text>>" launch notepad with argument <<text>>.
The before mentioned hyperlink uses Inno Setup, to build an installation program that makes the registry modifications for us. The beauty of Inno Setup is that de-installation of our registry modification is very easy (via default add/remove). I left the default setting pretty standard and added the following lines to the new setup file:
; Script generated by the Inno Setup Script Wizard.
[Files]
Source: "files\launch.cmd"; DestDir: "{app}"
Source: "files\demo.html"; DestDir: "{app}"
[Registry]
Root: HKCR; Subkey: webrep; ValueName: URL Protocol; ValueType: string; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep; ValueType: string; ValueData: URL:webrep Protocol; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep\DefaultIcon; ValueType: string; ValueData: {app}\launch.cmd,0; Flags: uninsdeletekey
Root: HKCR; Subkey: webrep\shell\open\command; ValueType: string; ValueData: "{app}\launch.cmd %1"; Flags: uninsdeletekey
After compilation of the setup file, Inno Setup gives us setup.exe. This executable does 2 thing. It copies the required files (that are included in the installer) to a setup-user-defined directory and makes the required modifications to the registry.
The contents of launch.cmd are as follow:
@ECHO OFF set parameter=%1 ECHO. ECHO Don't close this window. ECHO it will close automatically when notepad is closed. ECHO. cmd /c "c:\Windows\System32\notepad.exe %parameter:~7%"The contents of demo.html are as follow:
<html> <head> <title>D E M O</title> </head> <body> <a href="webrep:mynewfile.txt">Click me!</a> </body> </html>Clicking the hyperlink will cause notepad to lauch with a mynewfile.txt. Making this work in APEX is quite straightforward.
woensdag 13 maart 2013
You can never change who you are, or can you?
In PL/SQL statements are parsed as the current connected schema. One can find out what user is parsing a statement by typing (for instance)
select user from dual;This is usefull in all kinds of debugging, auditing etc. statements. You cannot really change your identity, for security reasons ofcourse. However one can parse a statement using rights and privileges of another user. This is nothing new, Oracle APEX does it all the time, as descibed here. Basically one uses
sys.dbms_sys_sqlpackage to parse the statement using the rights of another user. However, one never becomes that user. This is obvious in APEX. For instance when a trigger writes the USER to an audit column (depending on the Listerner configuration) on sees APEX_PUBLIC_USER. Maybe the trigger is adapted to APEX and uses v('APP_USER'), but that is not what I want to demonstrate. Inspired by this pdf, I wrote some code that allows one to be like Mystique, a chameleon:
create procedure run_statement_as_user(p_statement in clob,
p_user in dba_users.username%type)
authid definer is
l_ret_val number;
l_current_user dba_users.username%type := user;
l_ddl varchar2(128);
--
e_not_allowed exception;
begin
-- here a list of users one can 'change into'
-- be carefull, not to list any users that have DBA like
-- rights or privileges.
--
if p_user not in ('<MYUSER>') then raise e_not_allowed; end if;
--
-- switch user to desired p_user
--
l_ret_val := sys.kupp$proc.disable_multiprocess;
sys.kupp$proc.change_user(upper(p_user));
--
-- switch to the user schema
--
l_ddl := 'alter session set current_schema ='||upper(p_user);
execute immediate l_ddl;
--
-- Next parse the statement using rights of the p_user
--
declare
l_cursor number;
l_ret_val_c number;
uid dba_users.user_id%type;
begin
l_ret_val_c := sys.dbms_sql.open_cursor;
--
select user_id
into uid
from dba_users
where username = user;
--
sys.dbms_sys_sql.parse_as_user( l_ret_val_c
, p_statement
, dbms_sql.native
, uid);
--
l_cursor := sys.dbms_sql.execute(l_ret_val_c);
--
sys.dbms_sql.close_cursor(l_ret_val_c);
end;
--
-- Done, all remains is switching back to the original user.
--
-- switch back to original user
sys.kupp$proc.change_user(l_current_user);
l_ret_val := sys.kupp$proc.enable_multiprocess;
-- switch back to schema
l_ddl := 'alter session set current_schema = '||l_current_user;
execute immediate l_ddl;
--
exception
when e_not_allowed then raise;
when others then
sys.kupp$proc.change_user(l_current_user);
l_ret_val := sys.kupp$proc.enable_multiprocess;
l_ddl := 'alter session set current_schema = '||l_current_user;
execute immediate l_ddl;
raise;
end run_statement_as_user;
When the above procedure is compiled in SYS schema any other user that is given executable grant can execute statemens AS IF p_user . So for instance, when connected to the database as user JOHN (who is given executable rights ofcourse):
declare
l_statement varchar2(128):= ' begin
insert into
mytable ( dbuser )
values ( user );
commit;
end;';
l_user varchar2(64) := 'RONALD';
begin
sys.run_statement_as_user(l_statement, l_user);
end;
results in the insertion of one record in the RONALD.MYTABLE table, with dbuser having the value RONALD instead of JOHN, the latter is not even required to have any grants on RONALD.MYTABLE.
maandag 11 februari 2013
Upgrading APEX in the pre-built Oracle VM Virtualbox
I really like Oracle Virtualbox, especially the pre-built VM's from Oracle. For those not acquainted: it does pretty much the same as VMware. One can download Virtualbox from here, and the pre-built VM's from here.
Personally I like: `Database App Development VM': it delivers a pre-configured Oracle EE database, with APEX and APEX listener pre-installed. Importing a pre-built VM is very easy. If setup with NAT port forwarding, one can use the VM as if it were a true service on the host. To do that: start the VM in headless-mode (left-shift + click on start), so that you don't get a terminal. For a how-to of various network configuration-options click here.
For the following it is convenient to start the VM with a terminal / display. Remember that all passwords on the pre-build VM are `oracle' (without the quotes).
When the pre-built VM is up and running, ofcourse one wants to start APEX. However the version of APEX on the pre-build VM is 4.2, whilst the latest is 4.2.1. So we are in need of a patch. The pre-build VM is configured with APEX listener in standalone mode. Patching APEX is relatively straightforward, however we also need to update the images directory of APEX and this can be a bit of a puzzle. This is how I did it:
1. Start the VM, logon as user oracle/oracle and download the APEX 4.2.1 patch from Oracle (click here). Unzip the file in your favorite folder.
2. Stop the APEX listener using the command:
6. Goto /home/oracle/listener and type the command:
7. When done, press CTRL + c. This will kill the listener, so we have to restart it, this time avoiding hangup when we close the connection:
APEX 4.2.1 should come up.
Personally I like: `Database App Development VM': it delivers a pre-configured Oracle EE database, with APEX and APEX listener pre-installed. Importing a pre-built VM is very easy. If setup with NAT port forwarding, one can use the VM as if it were a true service on the host. To do that: start the VM in headless-mode (left-shift + click on start), so that you don't get a terminal. For a how-to of various network configuration-options click here.
For the following it is convenient to start the VM with a terminal / display. Remember that all passwords on the pre-build VM are `oracle' (without the quotes).
When the pre-built VM is up and running, ofcourse one wants to start APEX. However the version of APEX on the pre-build VM is 4.2, whilst the latest is 4.2.1. So we are in need of a patch. The pre-build VM is configured with APEX listener in standalone mode. Patching APEX is relatively straightforward, however we also need to update the images directory of APEX and this can be a bit of a puzzle. This is how I did it:
1. Start the VM, logon as user oracle/oracle and download the APEX 4.2.1 patch from Oracle (click here). Unzip the file in your favorite folder.
2. Stop the APEX listener using the command:
ps -ef | grep apex | grep -v apexlistener | grep -v grep | cut -c9-15 | xargs kill -93. Patch APEX, from your download folder, on the database using the following command:
sqlplus sys/oracle@orcl as sysdba @apxpatch.sql4. Remove the current configuration-settings of APEX listener using the following command:
rm -r /home/oracle/apex5. Copy the images directory to an oracle accessible folder, for instance: /home/oracle/images
6. Goto /home/oracle/listener and type the command:
java -jar apex.warBecause we removed the previous configuration file, one is required to setup the listener (password for APEX_PUBLIC_USER is `oracle', and SID is `orcl', leave all the rest default) and define where the images folder is located (/home/oracle/images). I kept port 8888 for APEX Listener;
7. When done, press CTRL + c. This will kill the listener, so we have to restart it, this time avoiding hangup when we close the connection:
nohup java -jar apex.war >/dev/null&8. Done. Close the terminal (not the VM) and test the setup by opening a browser and type:
http://localhost:8888/apex
APEX 4.2.1 should come up.
dinsdag 22 januari 2013
Using APEX send-mail, from outside APEX
Some time ago I was asked to write a piece of code that would use APEX send mail functionality, but without the APEX session (from a database job, if I recall correctly)
It turns out to be really simple, just set an APEX session, and make sure that the database-user is connected to an APEX workspace. The code is as follow:
It turns out to be really simple, just set an APEX session, and make sure that the database-user is connected to an APEX workspace. The code is as follow:
create or replace procedure my_send_mail( p_to in varchar2
, p_from in varchar2
, p_subject in varchar2
, p_message in varchar2
, p_apex_workspace in varchar2)
is
l_workspace_id number := null;
begin
--
-- This procedure is possibly called from outside an APEX session.
-- Take note: one is required to set-up an session, before APEX_MAIL.SEND
-- can be used. Make sure that the schema using this procedure is
-- connected to the workpace.
--
-- Ignoring the above will raise
-- ORA-20001: This procedure must be invoked from within an
-- application session
--
l_workspace_id :=
apex_util.find_security_group_id (p_workspace => p_apex_workspace);
--
apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
--
apex_mail.send(
p_to => p_to,
p_from => p_from,
p_subj => p_subject,
p_body => p_message);
--
apex_mail.push_queue;
end my_send_mail;
/
Calling the above procedure will give satisfying results in any PL/SQL session, as long as the schema is connected to an APEX workspace.
dinsdag 18 december 2012
APEX patchset released, fixing bug dataload mechanism
Good news.
"For existing Application Express 4.2.0 installations, please download the Application Express 4.2.1 patch set from My Oracle Support, Patch #: 14732511"
Check http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-421-patch-set-notes-1885751.html
for the relevant patch-set-notes. I am excited to see that bug #14803538 is adressed. I am just in the process of checking! Somebody is going to be happy!
For completeness sake here is the somewhat more elaborate description of the bug:
I'm having this weird issue with the dataload mechanism, first seen in APEX 4.1 and it seems to persist in APEX 4.2. I believe it was resolved in 4.1.1 ref patch set notes 13656397, somehow it is not;
Bear with me: I'm trying to insert / update the record
9-10-2012 32.110 78.249 10.359 V
(that is: 9 oct 2012, and four more columns)
in my table RIS_PAX_VOORL via the data-load-wizard. This table is defined as follow:
column | datatype | constraint
===========================================
dag | date | pk
aank_vertr_code | varchar2(1) | pk
od_pax | number | not null
transfer_pax | number | not null
tot_pax | number | not null
Note that the primary key is composed of 2 columns, one of which is date type: dag and aank_vertr_code. In the data-load wizard I defined that both dag and aank_vertr_code should be used to determine a record uniquely, and for the moment no other transformations / look-ups are used. When using the functionality, the wizard updates the record 10-sep-2012 V ... ? That's not good.
Setting a date format mask (DD-MM-YYYY) during the `Data mapping' step does not seem to have effect. The wizard is stubborn and "want's" to see september instead of october.
To make this a little more interesting I cleared out the table and inserted manually two records :
To eliminate any static i'm now using the wizard to update the first record, with the following input:
Ofcourse we set ';' as seperator character and uncheck the `First row contains...' option. In the data-map we adres the column mapping, not using any format masks. Next step data validation
has detected an UPDATE, so far so good, next step loading the data: updated 1 record, no errors. Excellent! However:
10-SEP-12 V 321 654 987
09-OCT-12 V 123 456 789
No changes! Ok. Trying again with 09-10-2012;V;9999;9999;9999, format mask DD-MM-YYYY, same result. Now, again, let's start of with a clean sheet:
truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This record gets inserted as 10-sep-2012.
again
truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This time we set a format mask: DD-MM-YYYY.
This record gets inserted as 10-oct-2012. Excellent! Now let's update this guy: use 09-10-2012;V;123;456;789 format mask DD-MM-YYYY; Ah but now an INSERT is detected;
Performing the insert triggers a unique constraint violation!
This is not good.
Update 15-02-2013
Still not working as expected, but there is a workaround. After adding a transformation rule in the data loading definition based on the DATE column it is now working. The transformation rule is:
"For existing Application Express 4.2.0 installations, please download the Application Express 4.2.1 patch set from My Oracle Support, Patch #: 14732511"
Check http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-421-patch-set-notes-1885751.html
for the relevant patch-set-notes. I am excited to see that bug #14803538 is adressed. I am just in the process of checking! Somebody is going to be happy!
For completeness sake here is the somewhat more elaborate description of the bug:
I'm having this weird issue with the dataload mechanism, first seen in APEX 4.1 and it seems to persist in APEX 4.2. I believe it was resolved in 4.1.1 ref patch set notes 13656397, somehow it is not;
Bear with me: I'm trying to insert / update the record
9-10-2012 32.110 78.249 10.359 V
(that is: 9 oct 2012, and four more columns)
in my table RIS_PAX_VOORL via the data-load-wizard. This table is defined as follow:
column | datatype | constraint
===========================================
dag | date | pk
aank_vertr_code | varchar2(1) | pk
od_pax | number | not null
transfer_pax | number | not null
tot_pax | number | not null
Note that the primary key is composed of 2 columns, one of which is date type: dag and aank_vertr_code. In the data-load wizard I defined that both dag and aank_vertr_code should be used to determine a record uniquely, and for the moment no other transformations / look-ups are used. When using the functionality, the wizard updates the record 10-sep-2012 V ... ? That's not good.
Setting a date format mask (DD-MM-YYYY) during the `Data mapping' step does not seem to have effect. The wizard is stubborn and "want's" to see september instead of october.
To make this a little more interesting I cleared out the table and inserted manually two records :
truncate table ris_pax_voorl;
--
insert into ris_pax_voorl (dag,
aank_vertr_code, od_pax, transfer_pax, tot_pax)
values (to_date('09-10-2012', 'DD-MM-YYYY'),
'V', 123, 456,789);
--
insert into ris_pax_voorl (dag,
aank_vertr_code, od_pax, transfer_pax, tot_pax)
values (to_date('10-09-2012', 'DD-MM-YYYY'),
'V', 321, 654,987);
To eliminate any static i'm now using the wizard to update the first record, with the following input:
09-OCT-2012;V;9999;9999;9999
Ofcourse we set ';' as seperator character and uncheck the `First row contains...' option. In the data-map we adres the column mapping, not using any format masks. Next step data validation
has detected an UPDATE, so far so good, next step loading the data: updated 1 record, no errors. Excellent! However:
select * from ris_pax_voorl
10-SEP-12 V 321 654 987
09-OCT-12 V 123 456 789
No changes! Ok. Trying again with 09-10-2012;V;9999;9999;9999, format mask DD-MM-YYYY, same result. Now, again, let's start of with a clean sheet:
truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This record gets inserted as 10-sep-2012.
again
truncate table ris_pax_voorl; and use 09-10-2012;V;9999;9999;9999 in the loading wizard. This time we set a format mask: DD-MM-YYYY.
This record gets inserted as 10-oct-2012. Excellent! Now let's update this guy: use 09-10-2012;V;123;456;789 format mask DD-MM-YYYY; Ah but now an INSERT is detected;
Performing the insert triggers a unique constraint violation!
This is not good.
Update 15-02-2013
Still not working as expected, but there is a workaround. After adding a transformation rule in the data loading definition based on the DATE column it is now working. The transformation rule is:
to_date(:DAG,'DD-MM-YYYY')
maandag 3 december 2012
A method to execute external programs via APEX
Some time ago, when I was working on an Oracle Forms project, we used the host command to execute commands on the server. For details check this link.
It is a really cool feature, because it allows you to execute external programs from outside the Oracle Forms environment. So for instance, one can start a script or program controlling some device. The command however is not available in SQL *Plus, giving a hint to availability in Oracle APEX.
That given, it raises the question on how to `access' the OS from within APEX. The answer is somewhat unexpected: DBMS_SCHEDULER. Here the CREATE JOB procedure allows for job type `EXTERNAL'. (note that the CREATE EXTERNAL JOB privilege has to be granted.).
To illustrate the above: here is the code I used on my Oracle XE installation (Windows):
To make the program more interesting we add some parameters. Here and here you will find an explaination for programming Windows batch files, and Unix bash files respectively. Let's add a parameter to the batch script that allows for a different output file.
It is a really cool feature, because it allows you to execute external programs from outside the Oracle Forms environment. So for instance, one can start a script or program controlling some device. The command however is not available in SQL *Plus, giving a hint to availability in Oracle APEX.
That given, it raises the question on how to `access' the OS from within APEX. The answer is somewhat unexpected: DBMS_SCHEDULER. Here the CREATE JOB procedure allows for job type `EXTERNAL'. (note that the CREATE EXTERNAL JOB privilege has to be granted.).
To illustrate the above: here is the code I used on my Oracle XE installation (Windows):
-- Execute as SYS user and replace <<user>> by the correct user.
-- external job privilege is required to make scheduler jobs
-- of type executable.
--
-- grant create external job to <<user>>;
begin
dbms_scheduler.create_job( job_name => 'TST_EXT_001'
, job_type => 'EXECUTABLE'
, job_action => 'c:\script1.bat');
end;
to execute the job (or schedule it, whatever fancies you):
-- execute job
begin
dbms_scheduler.enable('TST_EXT_001');
end;
In case nothing happens: make sure that the OracleJobSchedulerXE service is up. Here script1.bat is a somewhat academical piece of code, writing the output of the windows dir command to a textfile. Here is the listing of the batch file:
dir c:\temp > c:\templist.txt
To make the program more interesting we add some parameters. Here and here you will find an explaination for programming Windows batch files, and Unix bash files respectively. Let's add a parameter to the batch script that allows for a different output file.
@ECHO OFF REM REM This program will list the files in c:\temp and REM write the output to a specified output file. REM REM If no outputfile is specified the program will write to REM default output: c:\output.txt REM IF "%1"=="" ( SET OUTPUTFILE=c:\output.txt ) ELSE ( SET OUTPUTFILE=%1) DIR C:\TEMP > %OUTPUTFILE% REM Clean up, unsetting used variables SET OUTPUTFILE=Using the variable in the job_action variable of create_job, will not give the desired result. Instead, we add the set_job_argument_value procedure from dbms_scheduler, after the statement defining the job:
begin
dbms_scheduler.create_job( job_name => 'TST_EXT_002'
, job_type => 'EXECUTABLE'
, job_action => 'c:\script2.bat'
, number_of_arguments => 1
, enabled => FALSE);
dbms_scheduler.set_job_argument_value('TST_EXT_002',1,'c:\result.txt');
end;
Now when we enable the job, we see that on the filesystem a file is created, result.txt containing the directory listing of c:\temp.
In case of unexpected result: one can check the outcome of the posted job via the following query:
select * from all_scheduler_job_run_details where job_name = 'TST_EXT_002'
Abonneren op:
Posts (Atom)