Thursday, May 04, 2017

APEX and ORDS up and running in....2 steps!

In January 2017, I had a meeting with Dr. Sriram Birudavolu from Hyderabad.  He got my attention when he said he would love to start a 1000-person APEX Meetup group in Hyderabad (gotta love aggressive goals!).  However, he spent much of December and January just trying to figure out how to get APEX installed, configured and running.  He won't profess to be an expert, but he's exactly the type of person we want to enable.  He was correct in saying that if a potential customer struggles to get APEX installed, we've already lost.

Recently, Gerald Venzl asked for some assistance in creating a Docker image for APEX.  His goal was to create an APEX Docker image on top of the base Oracle Database Docker image.  He knows a lot about Docker, but he won't claim to be an expert in APEX.  He wanted something that is scriptable and can result in APEX being installed, configured and up and running, along with ORDS, in as few steps as possible.  A "silent install", if you please. This was the final bit of motivation I needed for this blog post and video.

While the installation documentation is complete and detailed, it's also lengthy and sometimes confusing - especially for the new person.  Thus, I wanted to provide the simplest set of instructions with as few steps as possible to get APEX installed, configured and up and running, along with ORDS configured and up and running.  It can be done in two steps.  That's right, two.  While I explain the individual steps executed from SQL*Plus in detail below, you can combine all of these SQL commands into a single SQL script.  I prefer the name "hookmeup.sql".



  1. Download and unzip APEX http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
  2. cd to apex directory
  3. Start SQL*Plus and ensure you are connecting to your PDB and not to the "root" of the container database (APEX should not be installed at all):
    sqlplus sys/your_password@localhost/your_pdb as sysdba @apexins sysaux sysaux temp /i/
    
  4. Unlock the APEX_PUBLIC_USER account and set the password:
    alter user apex_public_user identified by oracle account unlock;
    
  5. Create the APEX Instance Administration user and set the password:
    begin
        apex_util.set_security_group_id( 10 );
        apex_util.create_user(
            p_user_name => 'ADMIN',
            p_email_address => 'your@emailaddress.com',
            p_web_password => 'oracle',
            p_developer_privs => 'ADMIN' );
        apex_util.set_security_group_id( null );
        commit;
    end;
    /
    
  6. Run APEX REST configuration, and set the passwords of APEX_REST_PUBLIC_USER and APEX_LISTENER:
    @apex_rest_config_core.sql oracle oracle
    
  7. Create a network ACE for APEX (this is used when consuming Web services or sending outbound mail):
    declare
        l_acl_path varchar2(4000);
        l_apex_schema varchar2(100);
    begin
        for c1 in (select schema
                     from sys.dba_registry
                    where comp_id = 'APEX') loop
            l_apex_schema := c1.schema;
        end loop;
        sys.dbms_network_acl_admin.append_host_ace(
            host => '*',
            ace => xs$ace_type(privilege_list => xs$name_list('connect'),
            principal_name => l_apex_schema,
            principal_type => xs_acl.ptype_db));
        commit;
    end;
    /
    
  8. Exit SQL*Plus.  Download and unzip ORDS http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html
  9. cd to the directory where you unzipped ORDS (ensure that ords.war is in your current directory)
  10. Copy the following into the file params/ords_params.properties and replace the contents with the text below (Note:  this is the file ords_params.properties in the "params" subdirectory - a subdirectory of your current working directory):
    db.hostname=localhost
    db.port=1521
    # CUSTOMIZE db.servicename
    db.servicename=your_pdb
    db.username=APEX_PUBLIC_USER
    db.password=oracle
    migrate.apex.rest=false
    plsql.gateway.add=true
    rest.services.apex.add=true
    rest.services.ords.add=true
    schema.tablespace.default=SYSAUX
    schema.tablespace.temp=TEMP
    standalone.mode=TRUE
    standalone.http.port=8080
    standalone.use.https=false
    # CUSTOMIZE standalone.static.images to point to the directory 
    # containing the images directory of your APEX distribution
    standalone.static.images=/home/oracle/apex/images
    user.apex.listener.password=oracle
    user.apex.restpublic.password=oracle
    user.public.password=oracle
    user.tablespace.default=SYSAUX
    user.tablespace.temp=TEMP
    
  11. Configure and start ORDS in stand-alone mode.  You'll be prompted for the SYS username and SYS password:
    java -Dconfig.dir=/your_ords_configuration_directory -jar ords.war install simple --preserveParamFile
    

That's it!!  You should now be able to go to http://localhost:8080/ords/, and login with:

Workspace: internal
Username:  admin
Password:  oracle





IMPORTANT, PLEASE READ:

By no means is this a recommended or secure installation.  These are minimal instructions to get someone from zero to up and running easily and quickly.  In a production instance, I would create different tablespaces for APEX and ORDS, I would use far more complex and distinct passwords, I would use HTTPS and not HTTP, I would deploy ORDS on a physically distinct server, and more.

The above steps were tested with Oracle Application Express 5.1.1.00.08, Oracle REST Data Services 3.0.9, and Oracle Database 12.2.0.1 running on Oracle Linux.


17 comments:

Steven Feuerstein said...

Great reinforcement of how Oracle is making it easier and easier to get stuff done. I especially appreciate the reminder at the end: doing things for test and "trying it out" is very different from setting up for production.

Unknown said...

Hello Mr. Steven Feuerstein,

I am new to Oracle APEX. I want to know that is it possible to make a point of of sales application for a department store with Oracle APEX and it it good idea to do this.

Steven Feuerstein said...

Hello Unknown! Well I feel most uncomfortable answering an APEX question on Joel Kallman's blog post. But I will start and then I invite Joel to "get real."

Generally, sure, I don't see why you couldn't create a point of sales application in APEX. And I say without seeing any of your requirements, any sense of the volume of activity, number of users...none of that! :-)

Raymond said...

Hello Unknown
Yes you can make a point of sales application with APEX. I have done it including barcode scanner and works great.
I have also a networked receipt printer (EPSON) and print the receipts from APEX (I use PL/PDF).
It will get a bit tricky if you want to control a cash drawer and printer directly. But this is not an issue of APEX, it is an issue of the security model of the browser. The printer is not a real problem anymore as both Firefox and Chrome has add-ons to print silently.
If you want to control a cash drawer so it opens on taking payment you will to resort to Java plugins or some other clever stuff send a signal to a cash drawer connected on the serial port. There are other options when you use a Mac.

Farzad Soltani said...

Hi Raymond,
Can you point me in the direction of those Firefox and Chrome add-ons? Much appreciated.

விம்மும் குமுறல்கள் said...

Hi Raymond,
I was try to build the POS application long back(some apex4.0 lack?),it was issue in barcode scanner(after the scan to auto move next item) and cash drawer connectivity.can you referred to any sample test page?

Unknown said...

Dear Sir,



is it possible to auto-authenticate without asking for username and password from one session logged in apex application to another apex server application?



for e.g. As end-user i am currently logged on apex application server A and one of the page from server a links to another server b apex application, when i have linked in this way using url - it asks for authentication username and password of server b application.

i dont want to make public on server b.



is there any way to pass cookies/username from server a to server b automatically?

Unknown said...

Dear Mr Joel,

Any suggestions?

Joel R. Kallman said...

Dear "Unknown",

What are you using to maintain your user credentials?

Joel

Melody Sager said...

Thank you so much for the tip! I really appreciate it. Last year I took a Training On Demand class from Wayne and now I want to roll up my sleeves and test it out.

I need help with getting my PDB to see the APEX tablespace in the container...(if possible).

At home, I created a two node RAC (12.1.0.2.0)/ASM with one pluggable (PDBWIC) on Oracle Linux.

I successfully installed APEX 5.1.1.00.08 into the container

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> SELECT comp_name, version, status FROM dba_registry WHERE comp_id='APEX';

COMP_NAME VERSION STATUS
------------ ----------- -------
Oracle Application Express 5.1.1.00.08 VALID

The APEX tablespace exists in the container

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
UNDOTBS2
APEX

6 rows selected.

I attempted to install APEX into PDBWIC but I suspected it would fail because I did not create the APEX tablespace there.

SQL> show con_name

CON_NAME
------------------------------
PDBWIC

SQL> @apexins.sql APEX APEX TEMP /i/

PL/SQL procedure successfully completed.

------ omitted irrelevent information

Error: The tablespace APEX does not exist.
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 22
ORA-06512: at line 27

Is there a way to have the PDB use the APEX tablespace in CDB$ROOT?

Thank you in advance!



Joel R. Kallman said...

Hi Melody,

I definitely do *not* recommend installing APEX as common (that is, in CDB$ROOT). I outlined our reasons here: http://joelkallman.blogspot.com/2016/03/an-important-change-coming-for-oracle.html

The tablespaces would need to be created in each PDB. From a PDB, you cannot access a tablespace in CDB$ROOT.

Before you go a step further, I recommend *removing* APEX from your CDB and all PDBs, and installing APEX into each PDB separately.

I hope this helps.

Joel

Joel R. Kallman said...

Hi Melody,

Just to be clear, if APEX 5.1 is installed "common", connect to CDB$ROOT and run @apxremov.sql. This is documented at:

http://docs.oracle.com/database/apex-5.1/HTMIG/cleaningup-after-failed-installation.htm#GUID-A16032EF-3440-4D7A-B8A2-193A49D0BB30

I hope this helps.

Joel

Motohari Varma said...

Thanks Joel for this post. I was able to launch the url successfully and could access the apex pages.

I installed apex and ords on a windows server and for some reason, the server is refusing to connect when I closed the command prompt from where I ran the .war command to install ORDS. If I rerun java -jar ords.war, my apex installation is up and running again. Am I missing any configuration or the command should be left open always?

Please help!

Taylor Meiklejohn said...

Joel--thanks for this write up. HUGE relief to see this easy walkthrough. A coworker and I are attempting a basic proof of concept to get some APEX buy-in, but we're having a difficult time just getting APEX installed. This post is a God send.

I'm able to get everything working well until I get to the last step and configure and start ORDS. This fails miserably, as I usually never get prompted for anything and the following is generally the first error/indication in the massive stack:

WARNING: Failed to connect to user ORDS_PUBLIC_USER jdbc:oracle:thin:@//pmad-linora02:1530/pdblarch
Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor


I can provide more info if need be. Any advice?

Joel R. Kallman said...

Hi Motohari,

This is really more of a question of "how do you run a program in the background on Windows" - it's not really specific to ORDS. On Linux it's easy, on Windows - not so trivial.

My opinion - you'd be best served to follow the instructions and deploy ORDS in Apache Tomcat. It's straightforward.

Joel

Joel R. Kallman said...

Hi Taylor,

Your problem is that ORDS cannot connect to your PDB. The service name you provided in your ORDS configuration is "pdblarch". From the command line where your database server and database listener are running, run the command "lsnrctl status". Ensure that there is a service name of "pdblarch".

Joel

Motohari Varma said...

Thanks Joel. I will try using Apache Tomcat.