select workspace
, application_id
, application_name
, page_id
, region_name
, download_formats
from apex_application_page_ir
where application_id=20120618
order by page_id;
This will show all interactive reports which are used in my application:
select workspace
, application_id
, application_name
, page_id
, region_name
, download_formats
from apex_application_page_ir
where application_id=20120618
and instr(download_formats, 'EMAIL') > 0
order by page_id;
These data dictionary views contain a wealth of information about your application. This is a real strength of Oracle Application Express, due to its nature being driven by a metadata repository.select workspace
, application_id
, application_name
, page_id
, region_name
, pagination_display_position
, show_rows_per_page
, show_flashback
from apex_application_page_ir
where application_id=20120618
and ( pagination_display_position <> 'Top and Bottom - Left'
or show_rows_per_page = 'No'
or show_flashback = 'Yes' )
order by page_id;
Here is the result:
select * from apex_dictionary where apex_view_name like '%IR%'This will show us all view including their respective columns:
select * from apex_dictionary where apex_view_name like '%IR%' and column_id=0;Now, we get only the view names including their description:
The following post is targeted towards a German audience, thus it is in German:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Wir (Denes Kubicek und Dietmar Aust) werden am 10 Februar 2009 um 19:00 noch einmal den APEX Stammtisch in einer lockeren Variante durchführen, einfach nur ein leckeres Bier, gutes Essen, interessante Gespräche rund um APEX und interessante Leute.
Weitere Details und die kostenlose Anmeldung findet Ihr hier ...
Danach werden wir dem Stammtisch ein wenig mehr Struktur geben. Bei den Treffen werden wir uns dann auf einige wenige Themen konzentrieren, die im Vorfeld bekannt gegeben werden. Die Teilnehmer können auch gerne ihre eigenen APEX Applikationen vorstellen, ein Beamer wird vorhanden sein.
Wir freuen uns auf ein lockeres Kennenlernen bei einem Bier und evtl. leckerem Flammkuchen.
Leider ist seit dem letzten Stammtisch viel zu viel Zeit vergangen. Dennoch wollen wir es noch einmal versuchen, die Veranstaltung in regelmäßigen Abständen durchzuführen.
Viele Grüße und bis zum Stammtisch,
~Dietmar.
We (Denes Kubicek, Patrick Wolf and Dietmar Aust) are happy to announce the next APEX Training in Bensheim/Germany. Thus the rest of the post is in German.
Das nächste APEX Training findet am 23.03.2009 bis zum 25.03.2009 erneut in Bensheim bei Frankfurt statt, im dortigen Aleehotel., um Best Practice Vorgehensweisen aus vielen erfolgreichen APEX Projekten zu lernen.
Neben einer Fülle an Informationen, die wir in vielen erfolgreichen APEX Projekten erarbeitet haben, sind insbesondere die abendlichen Sessions (von 19:00-21:00) als Highlight zu nennen.Dort stehen wir Ihnen für zusätzliche Hands-On Sessions zur Verfügung, um die Beispiele des Tages durchzuarbeiten oder auch, um konkrete Fragestellungen in Ihren aktuellen Projekten zu diskutieren.
Insgesamt haben wir das Kursprogramm noch etwas aufgeräumt und einige Hands-on Übungen integriert.Weitere Details zum Kurs sowie die Anmeldung finden Sie hier.
~Dietmar Aust.
ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
can be reset with a simple
ALTER SYSTEM SET fixed_date = NONE;
HR@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
NOW
-------------------
26.01.2007 22:18:56
HR@o102> create table products(id number, product varchar2(50), valid_from date, valid_to date);
Table created.
HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (1, 'product1', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.12.2007','dd.mm.yyyy'));
1 row created.
HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (2, 'product2', to_date('01.01.2006','dd.mm.yyyy') , to_date('25.01.2007','dd.mm.yyyy'));
1 row created.
HR@o102> insert into products(id,product,valid_from,valid_to)
2 values (3, 'product3_new', to_date('31.01.2007','dd.mm.yyyy') , to_date('31.12.2007','dd.mm.yyyy'));
1 row created.
HR@o102> commit;
Commit complete.
HR@o102> create or replace view products_current_v as
2 select * from products where trunc(sysdate) between valid_from and valid_to;
View created.
HR@o102> select id, product from products_current_v;
ID PRODUCT
---------- --------------------------------------------------
1 product1
HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2006-12-31-10:00:00';
System altered.
SYS@o102> conn / as sysdba
Connected.
SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
NOW
-------------------
31.12.2006 10:00:00
SYS@o102> select id, product from hr.products_current_v;
ID PRODUCT
---------- --------------------------------------------------
1 product1
2 product2
SYS@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date='2007-02-01-10:00:00';
System altered.
SYS@o102> conn hr/hr
Connected.
HR@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
NOW
-------------------
01.02.2007 10:00:00
HR@o102> select id, product from products_current_v;
ID PRODUCT
---------- --------------------------------------------------
1 product1
3 product3_new
HR@o102> conn / as sysdba
Connected.
SYS@o102> alter system set fixed_date=none;
System altered.
SYS@o102> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
NOW
-------------------
26.01.2007 22:18:56
SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';
System altered.
SQL> ALTER SYSTEM SET fixed_date = NONE;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00065: initialization of FIXED_DATE failed
SQL>
SQL> create pfile from spfile;A new pfile was created in the $ORACLE_HOME/dbs directory, in my case:
File created.
SQL> startup pfile=/opt/oracle/product/9205/dbs/inito920.oraThen restart the database to have it started using the spfile.
ORACLE instance started.
Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;Now do it again, this time the proper way:
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> ALTER SYSTEM SET fixed_date = '2007-04-01-10:00:00';
System altered.
SQL> ALTER SYSTEM SET fixed_date = NONE;
System altered.
SQL> alter system reset FIXED_DATE scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 571545196 bytes
Fixed Size 452204 bytes
Variable Size 402653184 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
On the tab System-DSN add another data source:
import java.sql.*;
import oracle.jdbc.*;
public class PasswordValidator
{
public PasswordValidator()
{
}
public static int isValidPassword(String pServerName, String pListenerPort, String pSID, String pUserName, String pPassword) throws SQLException
{
boolean bIsValid = false;
try{
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + pServerName + ":" + pListenerPort + ":" + pSID, pUserName, pPassword);
bIsValid = true;
} catch (SQLException e)
{
//e.printStackTrace();
//System.out.println("ErrorCode=" + e.getErrorCode());
if (e.getErrorCode() == 1017)
{
// ORA-01017: invalid username/password
bIsValid = false;
} else {
//result = e.getMessage();
// re-raise exception
throw(e);
}
}
return bIsValid == true ? 1 : 0;
}
}
loadjava -user APEX_TEST/apex_test@o920 PasswordValidator.class
begin
dbms_java.grant_permission('APEX_TEST','SYS:java.net.SocketPermission', 'localhost:1521', 'connect,resolve' );
end;
/
c_server_name CONSTANT VARCHAR2 (50) := 'localhost';
c_listener_port CONSTANT VARCHAR2 (50) := '1521';
c_sid CONSTANT VARCHAR2 (50) := 'o920';
CREATE OR REPLACE PACKAGE apex_sec_pck IS
/******************************************************************************
NAME: APEX_SEC_PCK
PURPOSE: Security Package for database authentication
(using jdbc thin connections)
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- -----------------------------------
1.0 14.06.2006 D. Aust Initiale Erstellung
******************************************************************************/
/**************************************************************************
Constants and Types
**************************************************************************/
/*======================================================
FUNCTION is_valid_password_fc
Tests, wether this username/password is a valid combination
for this instance.
Parameter : p_username
Parameter : p_password
Return : boolean
Exception :
=======================================================*/
FUNCTION is_valid_password_fc (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN boolean;
END;
/
CREATE OR REPLACE PACKAGE BODY apex_sec_pck IS
c_server_name CONSTANT VARCHAR2 (50) := 'localhost';
c_listener_port CONSTANT VARCHAR2 (50) := '1521';
c_sid CONSTANT VARCHAR2 (50) := 'o920';
FUNCTION is_valid_password_java_wrp_fc (p_server_name IN VARCHAR2, p_listener_port IN VARCHAR2, p_sid IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN NUMBER AS
LANGUAGE JAVA
NAME 'PasswordValidator.isValidPassword(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
-----------------------------------------------------------------------------
--
--
--
FUNCTION is_valid_password_fc (p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN IS
l_user VARCHAR2 (30);
l_retval NUMBER;
BEGIN
l_retval := is_valid_password_java_wrp_fc (p_server_name => c_server_name, p_listener_port => c_listener_port, p_sid => c_sid, p_username => p_username, p_password => p_password);
IF l_retval = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN FALSE;
END;
END;
/
SET serveroutput on
BEGIN
DBMS_OUTPUT.put ('Password test 1 (correct password): ');
IF apex_sec_pck.is_valid_password_fc ('apex_test', 'apex_test')
THEN
DBMS_OUTPUT.put_line ('password is valid');
ELSE
DBMS_OUTPUT.put_line ('password is *not* valid');
END IF;
DBMS_OUTPUT.put ('Password test 2 (incorrect password): ');
IF apex_sec_pck.is_valid_password_fc ('apex_test', 'incorrect_pwd')
THEN
DBMS_OUTPUT.put_line ('password is valid');
ELSE
DBMS_OUTPUT.put_line ('password is *not* valid');
END IF;
END;
/
http://<server>:<port>/<dad>/<schema>.<procedure>
conn HR/<pwd>@xe
create or replace procedure hello_world as
begin
htp.p('hello world');
end;
/
grant execute on hello_world to anonymous
/
conn SYSTEM/<pwd>@xe
alter session set current_schema=FLOWS_020100;
CREATE OR REPLACE function wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
-- procedure
-- schema.procedure
-- package.procedure
-- schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in (
'HR.HELLO_WORLD') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
/
SELECT link, label, value FROM...For example:
selectA simple chart might look like this:
'f?p=141:6:'||:app_session||'::::F141_P6_EMPNO:'||empno link,
ename,
sal
from emp
While this works well in Internet Explorer 6, Firefox 1.0.7 and Mozilla, what you see in Firefox 1.5 is:
<a xlink:href="newdoc.html" target="_top">
xlink:href="javascript:window.top.location.href='newdoc.html'"
SELECT *
FROM (SELECT 'f?p=&APP_ID.:14:' || :app_session || '::::P14_EMPNO:' || empno || ':' LINK
, ename label, sal VALUE
FROM HTMLDB_SAMPLES.EMP
WHERE sal IS NOT NULL
ORDER BY ename)
SELECT *
FROM (SELECT 'javascript:window.top.location.href=''f?p=&APP_ID.:14:' || :app_session
|| '::::P14_EMPNO:' || empno || ':''' LINK
, ename label, sal VALUE
FROM HTMLDB_SAMPLES.EMP
WHERE sal IS NOT NULL
ORDER BY ename)