I've moved...

This blog now has a new home - please update your shortcuts and readers to: www.jeffkemponoracle.com. Sorry for any inconvenience!

Monday, July 02, 2007

Apex 3.0 via Embedded PL/SQL Gateway

I managed to get Apex 3.0 working on Oracle using the embedded PL/SQL gateway (i.e. without installing Apache), contrary to the advice given here. I used apex_epg_config instead of apex_epg_config11. For it to work, however, I had to remove some security on XDB so it's not a recommended option.

[Full Article]


  1. Hi Jeff - great info here. I am trying to allow anonymous login to xmldb using the code posted - am getting an error at XMLType('true') - examples show this should be an xml tag. Do you know what you used to make this modification?

    Thanks -

  2. Hi Robert,

    It worked for me using exactly that syntax. According to the 10.2 doc, the fourth parameter is value_expr, which "is an fragment of XMLType that specifies one or more notes being inserted. It must resolve to a string." [link]

    The purpose of the statement is to simply add a node called "allow-repository-anonymous-access" with the contents "true".

    What error are you getting?


  3. Hi Stephen ..
    ERROR at line 1:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00210: expected '<' instead of 't'
    Error at line 1
    ORA-06512: at "SYS.XMLTYPE", line 301
    ORA-06512: at line 1
    ORA-06512: at line 4

    When I strip the statement down to just the insertchildxml statement it errors on XMLType('true') with the same error.

    DB Version is

  4. Hi Robert,

    Stephen isn't here, sorry :)

    Sorry, I don't know why you're getting that error. Have you tried removing XMLType() and just passing 'true' as that parameter?

    The examples here may help: [link].

    You might need to change it to something like this (I'm just guessing really), but I haven't tested it:

    configxml SYS.XMLType;
    SELECT INSERTCHILDXML(xdburitype('/xdbconfig.xml').getXML(),
    INTO configxml FROM DUAL;


  5. Thanks Jeff -
    This got past the XMLType error. I'll proceed with troubleshooting using the reference you've provided.

    Much appreciated -

  6. Hi Robert. I had the same problems as you. Following code finally did it.

    configxml XMLType;
    configxml2 XMLType;
    -- Get the current configuration
    configxml := DBMS_XDB.cfg_get();

    -- Modify the configuration
    XMLType('<allow-repository-anonymous-access xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd">true</allow-repository-anonymous-access>'),
    INTO configxml2 FROM DUAL;

    -- Update the configuration to use the modified version


  7. Hi Jeff. Thanks for the great info on your site. Can you describe how to create a friendly url to an oracle application? I managed to create a DAD, but now I need to create a procedure or page that links to something like this: f?p=105

    Thanks in advanced.

  8. Hi bas,

    You can create an Oracle PL/SQL procedure to redirect the caller's browser to the URL of your choosing, e.g.:

    PROCEDURE myapp IS

    You may need to modify the url (inside the quotes) to suit your application. You may or may not need "/apex/" in there; or you may need to specify the full URL including server and port. Try it out!

    By the way, if you're using Apache web server instead of Oracle's EPG, you can edit your .conf files to perform the redirection without needing a database procedure. For example, I have lines like the following in my /etc/httpd/conf/httpd.conf file:

    RewriteRule ^/myapp$ http://%{SERVER_NAME}/apex/f?p=105

    The above rule means "match the exact url /myapp and redirect to /apex/f?p=105 on the same server".


  9. Thanks Jeff for the quick reply.

    Unfortunately I get the following error when I try to open the app:

    Not found
    The requested URL http://myapexserver/apex/myapp was not found on this server.

    I created the procedure in the FLOWS_030000 schema. Do I need to give access to the procedure?

    Thanks again.

  10. Hello again Bas,

    1. I would suggest that you don't put your procedures and other objects in the FLOWS_nnnnnn schemas, because when you want to upgrade Apex you'll lose them. Create your own schema for your application (or use the schema that Apex has created for your application).

    2. I don't know that you can use the "apex" DAD to call procedures directly; what I do is create a separate DAD for general-purpose procedures like this. There are three steps to do this. Firstly, create a user via which the DAD will access the database. Secondly, create the DAD and authorize it. Finally, grant execute on any procedure you want anyone to be able to run via the DAD. For example:

    create user abc identified by secretpassword;

    DBMS_EPG.create_dad (
    dad_name => 'ABC',
    path => '/abc/*');
    DBMS_EPG.set_dad_attribute (
    dad_name => 'ABC',
    attr_name => 'database-username',
    attr_value => 'ABC');

    grant execute on flows_030000.myapp to abc;

    create public synonym myapp for flows_030000.myapp;

    (Note 1: if you move the procedure out of the flows_030000 schema, you'll need to modify the above commands)
    (Note 2: the name of the DAD is case-sensitive, so if you see "ABC" above, don't put in "abc", and vice versa)
    (Note 3: don't use "secretpassword" for your password!)

    Once the above is done, a user can run myapp via a url like:


    If you want, you can set a default index page for the DAD using this command:

    DBMS_EPG.set_dad_attribute (
    dad_name => 'ABC',
    attr_name => 'default-page',
    attr_value => 'myapp');

    This way all the user has to type is:


    And they will be redirected to myapp, which I guess will then redirect them to your apex site...

    Hope this helps.


  11. Jeff, following your steps, exactly gave the results I wanted. Thank you very much for you time!

  12. Hi Jeff,

    thanks a lot for the scripts! I noticed that the script for getting all the attributes is using a wrong variable to access the array paths.

    dbms_output.put_line('... path=' || paths(i));

    should be

    dbms_output.put_line('... path=' || paths(j));

    Thanks again

  13. Thanks Patrick for spotting that, I've corrected it.

  14. Thanks Jeff. With apex 3.2 and, I verified that I had all of your settings too, but this is the solution that worked for me.

    SO: After having gone through this problem in three previous apex releases, I think I have this finally figured out (or maybe Oracle does ;)

    In OEM, I changed the images resource administered by the /sys/acls/ro_anonymous_acl.xml ACL file to grant 'dav:read' to ANONYMOUS rather than the default of 'read-contents'.

    This is the sql OEM used:
    UPDATE resource_view r
    SET r.res=UPDATEXML(res, '/a:Resource/a:Contents/b:acl','
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd"
    'xmlns:a="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:b="http://xmlns.oracle.com/xdb/acl.xsd"') WHERE r.any_path = '/sys/acls/ro_anonymous_acl.xml';

    And BAM! "The server %S at XDB requires a username and password" error is obiliterized (I hope).


Note: only a member of this blog may post a comment.