APEX: Application Builder Defaults Home --> Workspace --> Application Builder --> Application Builder Defaults

I'm not sure which version this was released, but it makes life just a little bit easier...which is ultimately the goal of any technology. From your Application Builder home page in the Tasks section, you should see Application Builder Defaults



In the Application Builder Default section, you can set preferences for Tabs, Authentication, Themes and Globalization. What this means is that each subsequent application you create in a given workspace will default to the values you supply in this section.

Your choices with Tabs are No Tabs, One Level of Tabs or Two Levels of Tabs. I've always used Two Levels of Tabs, just in case I want or need to expand the application.



For Authentication, I like to default to No Authentication, preferring instead to add an Authentication schema at a later time.



Theme 12 used to be my favorite...I had heavily customized it in the past and became very familiar with it. However, I'm starting to like Theme 20 now, so I select that.



As for Globalization, I just accept the defaults. I have yet the opportunity to build an application in anything other than English.



Next time you go to create an application, you should be able to just click through as your favorites are now the default.

Labels: apex, howto

  ¶ 12/05/2009 10:57:00 PM   0 Comments Links to this post

  APEX: Assign Multiple Schemas To A Single Workspace Home --> Administrative Services --> Manage Workspaces --> Assign Multiple Schemas To A Single Workspace

While "teaching" APEX to a group of folks I was asked how to assign multiple schemas to a single workspace. For the life of me, I couldn't remember or figure out how to do it through the web interface. Strangely, I had recently been playing with the APEX_INSTANCE_ADMIN package and I knew it was possible...just couldn't find the right way through the interface.

We'll start with creating a new workspace using the web interface.

First, click on Create Workspace



Next, name the workspace "TEST" and select Next.



Select Yes from the drop down for "Re-use existing schema?" I picked APEX_TEST as the schema to map to. Then select Next.



I left the default Administrator Username, ADMIN. The password is ADMIN and the email is ADMIN@EMAIL.COM. Select Next.



I'm then prompted to confirm the details of the new workspace. Select Create.



My workspace has been created.



You'll be redirected back to the Manage Workspace page.

Now select the link for Manage Workspace to Schema Assignments



You'll be taken to a page that looks like this, select Create



The check "Existing" when prompted for a New or Existing Schema



Select your newly created workspace, TEST, and click on Next



Either enter the schema you want to map to or select it from the popup, click Next



Confirm your settings and click on Add Schema



And voila! You've now mapped your workspace to 2 separate schemas



Manually
You can also do this if you create a workspace via APEX_INSTANCE_ADMIN, but only on creation, there seems to be no facility in that package to add it after creation. The procedure call is ADD_WORKSPACE.

I'll create a new workspace, MANUAL_WORKSPACE and assign the primary schema as APEX_TEST and the secondary (you can add as many as you want with a colon delimited list) will be APEX_USER:

BEGIN
  apex_instance_admin.add_workspace
    ( p_workspace_id => NULL,
      p_workspace => 'TEST_MANUAL',
      p_primary_schema => 'APEX_TEST',
      p_additional_schemas => 'APEX_USER' );
END;
/

PL/SQL procedure successfully completed.

COMMIT;

And you're done



Update 12/05/2009 10:17 PM
I was wrong, you can add a schema after it has been created using the APEX_INSTANCE_ADMIN package...If you want to add a second schema to an already existing workspace, use the ADD_SCHEMA procedure.

CJUSTICE@TESTING>EXEC apex_instance_admin.add_schema( 'TEST', 'APEX_USER' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.52
CJUSTICE@TESTING>COMMIT;

Labels: apex, apex_instance_admin, howto

  ¶ 12/05/2009 03:05:00 AM   0 Comments Links to this post

  APEX: Substitution Strings Home --> Workspace --> Application Builder --> Substitution Strings

I was asked a question the other day about substitution strings. Like many things, I did not know all of them off the top of my head. But I do know where to find them.

A trick I used to use was to create a page with an HTML Region and put all the substitution strings in there...just so I could remember them.

In an HTML Region, the syntax is

&<substitution_string>.

Here's the list of all the substitution strings supplied by APEX in 3.2.1.00.11 (just cut and paste this into your Region Source):

<b>APP_ALIAS</b>: &APP_ALIAS.<br>
<b>APP_ID</b>:  &APP_ID.<br>
<b>APP_IMAGES</b>:  &APP_IMAGES.<br>
<b>APP_PAGE_ID</b>:  &APP_PAGE_ID.<br>
<b>APP_SESSION</b>:  &APP_SESSION.<br>
<b>APP_UNIQUE_PAGE_ID</b>:  &APP_UNIQUE_PAGE_ID.<br>
<b>APP_USER</b>:  &APP_USER.<br>
<b>AUTHENTICATED_URL_PREFIX</b>:  &AUTHENTICATED_URL_PREFIX.<br>
<b>BROWSER_LANGUAGE</b>:  &BROWSER_LANGUAGE.<br>
<b>CURRENT_PARENT_TAB_TEXT</b>:  &CURRENT_PARENT_TAB_TEXT.<br>
<b>DEBUG</b>:  &DEBUG.<br>
<b>HOME_LINK</b>:  &HOME_LINK.<br>
<b>LOGIN_URL</b>:  &LOGIN_URL.<br>
<b>IMAGE_PREFIX</b>:  &IMAGE_PREFIX.<br>
<b>SCHEMA OWNER</b>:  &SCHEMA_OWNER.<br>
<b>PRINTER_FRIENDLY</b>:  &PRINTER_FRIENDLY.<br>
<b>LOGOUT_URL</b>:  &LOGOUT_URL.<br>
<b>PROXY_SERVER</b>:  &PROXY_SERVER.<br>
<b>PUBLIC_URL_PREFIX</b>: &PUBLIC_URL_PREFIX.  <br>
<b>REQUEST</b>:  &REQUEST.<br>
<b>SQLERRM</b>:  &SQLERRM.<br>
<b>SYSDATE_YYYYMMDD</b>:  &SYSDATE_YYYYMMDD.<br>
<b>WORKSPACE_IMAGES</b>:  &WORKSPACE_IMAGES.<br>

And here's what it looks like when you run the page:



You can also create your own custom substitution strings. First, go to your application home and then click on the link shown below:



So you don't have to see the entire page, just click on the "Substitution" filter:



Then add your own, it's a key/value pairing similar to static LOVs



Add that to your Region Source, run the page and voila!

Labels: apex, howto

  ¶ 12/03/2009 11:35:00 PM   2 Comments Links to this post

  Teaching APEX Tomorrow I'll be leaving for Dallas, TX for a 4 day teaching assignment on Application Express.

I'm a bit nervous for multiple reasons.
1. I've never had a problem (well, not in my 30's anyway) getting up and speaking in front of people. My style is light on slides and heavy on winging it. I can do that because I don't often give talks on things I don't know intimately. Don't get me wrong, I have a basic outline to follow, but I have never liked lectures and tried to avoid them at all costs.
2. Leaving the family. The last time I was away from my family for more than a day was 3 years ago, when I moved down to Tampa. Before that, it was 2002 when I went to OOW. I was homesick both times. Homesickness...is a strange thing for me. I grew up as an only child and moved 8 times...so I was always the new kid with no friends. I went to college 2000 miles away from home (CO --> FL). It wasn't until I had a family did I first experience the phenomenon.
3. I have to get on a plane. Yes, it's irrational...I know this. You are far more likely to die on the roads (in the US anyway). Again, this has something to do with the family thing. A feeling of mortality? I don't know. I've probably flown 100 times in my life, I used to love flying...the people watching in the terminal, the feeling of takeoff, landing...

In fact, when I was at UF, I rode this plane countless times...without fear. (And I swear there was duct tape on the propellors...)



I've just been out of the habit of flying. No more Peter Pan Invincibility syndrome for me I guess.

I know lots of you travel quite frequently and are non-plussed by it. I will be thinking of all the people that I know that travel on a regular basis. It might require a beer or 3 to get on board this "first" time.

4. Almost forgot, I get to meet OracleNude. That could be scary too. :)

I guess this post should have been titled, A Fear of Flying, would have been more appropriate.

Labels: apex

  ¶ 11/29/2009 08:14:00 PM   4 Comments Links to this post

  APEX: Manage Applications Home --> Administrative Services

This is the section where you manage your instance wide application attributes. The only functionality that exists in this section, other than mere reporting, is the ability to change an application from Run and Build Application to Run Application Only.



Application Attributes
In the section you can view the following application attributes:

• Workspace
• Application - the number assigned which will be part of the URL
• Parsing Schema
• Application Name
• Last Updated - The user (workspace admin or developer) who last updated this application.
• Date - Time since the last update
• Pages - Number of pages in the application
• Language

Build Status
From here you can view the Build Status of your applications. It will either be Run and Build Application, which means it can be edited by developers and workspace administrators AND viewed by end users. This is typically the status in a non-production environment; or, it will be Run Application Only which is what the status should be in a production environment. It can not be modified in any way but it can be viewed by the end users.



Edit Build Status
The page where you change the Build Status.



Parsing Schemas
Finally, this tells you what the parsing schema is for the application.

Labels: apex

  ¶ 11/27/2009 12:23:00 AM   0 Comments Links to this post

  APEX: Create Workspace Home --> Administrative Services --> Manage Workspaces --> Create Workspace

Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.

I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.



Of course I had to see if for myself:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf

5 rows selected.

That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:



I give it a name, APEX_TEST:



On Step 2, I set "Re-user existing schema?" to "No"



Step 3:



Step 4, confirming details:



Confirmation page:

CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/testing/system01.dbf
         2 /u01/app/oracle/oradata/testing/sysaux01.dbf
         3 /u01/app/oracle/oradata/testing/undotbs01.dbf
         4 /u01/app/oracle/oradata/testing/users01.dbf
         5 /u01/app/oracle/oradata/testing/example01.dbf
         6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf

6 rows selected.

As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.

This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.

Labels: apex, dba

  ¶ 11/21/2009 07:00:00 PM   3 Comments Links to this post

  APEX: Manage Service > Manage Environment Settings Home --> Administrative Services --> Manage Service

As I mentioned previously, this is probably the most important area for administering your APEX instance.


Messages

There are 2 types of messages you can create, one is the Login message and one is the System Message



The Login message appears at the top of the Workspace Login screen:



The System messages appears after you have logged in.



Feature Configuration

• Application Development
  
  
  
  
  1. Allow PL/SQL Program Unit Editing - This pertains to the SQL Workshop. Will you allow end-users (Developers/Administrators), who have access to SQL Workshop, to edit PL/SQL.
  2. Create demonstration objects in new workspaces - By default, when you create a new workspace, the sample application is automatically installed in the schema that it is mapped to. In my case, CJUSTICE. There are database objects included with this object as well. If you would like to see those database objects and need a script to remove them, read here.
     
     I don't like that it gets created automatically so I typically turn it off.


• SQL Workshop
  
  
  
  
  1. SQL Commands Maximum Inactivity in minutes - "Identify the maximum amount of time a transactional command in the SQL Command Processor waits before timing out."
  2. SQL Scripts Maximum Script Output Size in bytes - "Identify the maximum amount of output a single SQL Script can generate. SQL scripts are run from the SQL Workshop."
  3. SQL Scripts Maximum Workspace Output Size in bytes - "Identify the maximum amount of space all scripts within a workspace may consume. SQL Script results are the output generated when running SQL Scripts from the SQL command processor."
  4. SQL Scripts Maximum Script Size in bytes - "Identify the maximum size of a SQL script used within the SQL Workshop."
  5. Enable Transactional SQL Commands - "Enable Transactional SQL Commands for the entire instance. Enabling this feature permits users of SQL Commands to issue multiple SQL commands within the same physical database transaction."


• Monitoring
  
  
  
  1. Enable Database Monitoring - "Enable or disable monitoring within the SQL Workshop."
  
  
  Honestly, I have no idea what kind of monitoring is done here. I haven't been able to find any related reports...which doesn't mean they don't exist, just that I can't find them.

Security

• Security
  
  


1. Set Workspace Cookie
2. Disable Administrator Login - Defaults to No (otherwise, how would you log in?)
3. Disable Workspace Login - Defaults to No
4. Allow Public File Upload
5. Restrict Access by IP Address


• HTTPS
  
  


1. Require HTTPS - Use a comma delimited list of IP Addresses to restrict, can be either 1 or up to 4 values


• Session Timeout
  
  


1. Maximum Session Length in Seconds
2. Maximum Session Idle Time in Seconds


• Excluded Domains
  
  


1. Domain Must Not Contain - "Enter domains (not including the port), separated by colons, that should not be allowed in regions of type URL or accessed as a Web service. "


• Account Login Control
  
  


1. Require User Account Expiration and Locking - "Select Yes to enable Application Express user account expiration and locking features across all workspaces. This selection prevents the same feature from being disabled at the workspace level.
   
   Select No to allow individual workspaces to enable or disable this feature independently.
   
   Application Express user account expiration and locking features apply to end-user accounts created using the Application Express end-user account management interface."
2. Maximum Login Failures Allowed - "This setting applies to accounts used to access the Application Express administration and development environment only. It does not apply to end-user accounts used by applications developed in Application Express.
   
   However, this setting is used as the default workspace-level "Maximum Login Failures Allowed" preference, which workspace administrators can change. The workspace-level preference applies to the end-user accounts within that workspace."
3. Account Password Lifetime (days) - Self Explanatory


• Workspace Password Policy
  
  


1. Minimum Password Length - Self Explanatory
2. Minimum Password Differences - "Enter the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference.
   
   This setting applies to accounts for workspace administrators, developers, and end use"
3. Must Contain At Least One Alphabetic Character - Self Explanatory
4. Must Contain At Least One Numeric Character - Self Explanatory
5. Must Contain At Least One Punctuation Character - Self Explanatory
6. Must Contain At Least One Upper Case Character - Self Explanatory
7. Must Contain At Least One Lower Case Character - Self Explanatory
8. Must Not Contain Username - Self Explanatory
9. Must Not Contain Workspace Name - Self Explanatory
10. Must Not Contain - colon seperated list of words that cannot be used in the password, default words are: "oracle:hello:welcome:guest:user:database"
11. Alphabetic Characters - default: "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
12. Punctuation Characters - default: "!"#$%&()``*+,-/:;<=>?_"



• Service Administrator Password Policy
  
  
  
  
  1. Policy - You can either define the Workspace Password Policy (above) or just use the default strong password policy. For my sandbox versions, I tend to make it as lax as possible so I do not have to remember complex passwords when putting things like this together.

Instance Settings

For most organizations, the only 3 areas you need to worry about here are Email, Report Printing and Wallet.

• Self Service
  
  


1. Provisioning Status - 3 options here: Manual, Request and Email Verification. For most organizations Manual will do just fine. That will allow your end-users to follow the normal protocols set forth in your organization. For hosted sites, like apex.oracle.com, Email Verification is the way to go.
2. Development Service URL - Used in conjunction with Request or Email Verification above.



• Email Provisioning
  
  



1. Email Provisioning - By default, is is enabled, but without the other settings it is useless. I turn this off...but it doesn't really matter for me since I use a sandbox.


• Schema
  
  



1. Require New Schema - Do you want to require the user to obtain a new schema or reuse an existing one?


• Email
  
  



1. SMTP Host Address - This is where you set up your internal mail server. All APEX mail calls will use this server by default.
2. SMTP Host Port - Self Explanatory
3. Administration Email Address - Self Explanatory
4. Notification Email Address - Self Explanatory


• Wallet
  
  
  
  
  1. Wallet Path - Path to the wallet on the database server.
  2. Wallet Password - Self Explanatory


• Report Printing
  
  
  
  
  1. Print Server - Standard is the Default. To enable pdf printing you need BI Publisher and this is where you enter the server information.
  2. Print Server Protocol
  3. Print Server Host Address
  4. Print Server Port
  5. Print Server Script
  
  For a How To on setting up APEX and BI Publisher, check out the RittmanMead blog entry Oracle ApEx and BI Publisher.


• New Workspace Request Size
  
  
  
  If using the Self Service module, this is where you'll set the sizes (and the default) of the workspaces to choose from. You can enable them all or just a few.


• Workspace Change Request Size
  
  
  
  Just like the New Workspace Request Size section, you can modify the size of workspaces you are offering.

Labels: apex

  ¶ 11/18/2009 12:08:00 AM   1 Comments Links to this post

  APEX: Drop Sample Application Database Objects Here's a simple script to remove the database objects from the default Sample Application from Application Express (APEX). Deleting the application from the interface does not remove the database objects.

There are 30 database objects created by default along with the Sample Application:

OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------
FUNCTION            CUSTOM_AUTH
                    CUSTOM_HASH
INDEX               DEMO_CUSTOMERS_PK
                    DEMO_CUST_NAME_IX
                    DEMO_ORDER_ITEMS_PK
                    DEMO_ORDER_PK
                    DEMO_ORD_CUSTOMER_IX
                    DEMO_PAGE_HIERARCHY_PK
                    DEMO_PRODUCT_INFO_PK
                    DEMO_USERS_PK
LOB                 SYS_LOB0000077614C00007$$
SEQUENCE            DEMO_CUST_SEQ
                    DEMO_ORDER_ITEMS_SEQ
                    DEMO_ORD_SEQ
                    DEMO_PROD_SEQ
                    DEMO_USERS_SEQ
TABLE               DEMO_CUSTOMERS
                    DEMO_ORDERS
                    DEMO_ORDER_ITEMS
                    DEMO_PAGE_HIERARCHY
                    DEMO_PRODUCT_INFO
                    DEMO_STATES
                    DEMO_USERS
TRIGGER             BI_DEMO_USERS
                    DEMO_ORDER_ITEMS_GET_PRICE
                    DEMO_USERS_T1
                    INSERT_DEMO_CUST
                    INSERT_DEMO_ORDER_ITEMS
                    INSERT_DEMO_PROD
                    UPDATE_ORDER_TOTAL

30 rows selected.

If you have nothing else in your schema, you can run this:

DECLARE
  l_sql VARCHAR2(32767);
BEGIN
  FOR i IN ( SELECT object_name, object_type 
             FROM user_objects
             WHERE object_type NOT IN ( 'LOB', 'INDEX', 'TRIGGER' ) 
             ORDER BY object_type )
  LOOP
    CASE 
      WHEN i.object_type = 'TABLE' THEN
        l_sql := 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS PURGE';
      ELSE
        l_sql := 'DROP ' || i.object_type || ' ' || i.object_name;
    END CASE;
    EXECUTE IMMEDIATE l_sql;
  END LOOP;
END;
/

If however, you do have other items in there, use this:

DROP FUNCTION CUSTOM_AUTH;
DROP FUNCTION CUSTOM_HASH;
DROP SEQUENCE DEMO_ORDER_ITEMS_SEQ;
DROP SEQUENCE DEMO_PROD_SEQ;
DROP SEQUENCE DEMO_ORD_SEQ;
DROP SEQUENCE DEMO_USERS_SEQ;
DROP SEQUENCE DEMO_CUST_SEQ;
DROP TABLE DEMO_STATES CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PAGE_HIERARCHY CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_USERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDER_ITEMS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_PRODUCT_INFO CASCADE CONSTRAINTS PURGE;
DROP TABLE DEMO_CUSTOMERS CASCADE CONSTRAINTS PURGE;

Triggers, LOBs and Indexes will be dropped along with their associated objects.

Labels: apex, howto

  ¶ 11/17/2009 03:42:00 PM   2 Comments Links to this post

  APEX: Install 3.2.1 on 11gR2 Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):

/mnt/software/oracle/apex/apex

In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:

[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql

Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:

SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;

There are 4 basic installation scenarios:

1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
4. Installing from the database and configuring the Oracle HTTP server (11g)

I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.

SQL>@apexins.sql SYSAUX SYSAUX temp /i/

The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:



or here if you use INTERNAL as the workspace name (thanks John!):



Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql

SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.

Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

We need to verify the EPG port now:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.

EXEC dbms_epg.sethttpport( 8080 );

Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL 
  INTO ACL_PATH 
  FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' 
    AND LOWER_PORT IS NULL 
    AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) 
  INTO ACL_ID
  FROM 
    XDB.XDB$ACL A, 
    PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) 
    AND EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL 
  THEN 
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
    'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
      ( 'power_users.xml',
        'ACL that lets power users to connect to everywhere',
        'APEX_030200', 
        TRUE, 
        'connect' );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:



You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

Labels: apex, howto, install, oracle

  ¶ 11/16/2009 12:01:00 AM   3 Comments Links to this post

  APEX: Manage Service Home --> Administrative Services

Within the Administrative Services space, you have 4 options:

• Manage Service
• Manage Workspace
• Manage Applications
• Monitor Activity

Of those 4, Manage Service is probably the most important area. This is the location where you will administer your entire APEX instance.



Within the Manage Service space, you have 3 categories:

• Manage Service
• Manage Environment Settings
• Manage Shared Components

Manage Shared Components

This is where you manage your public themes. What are public themes?

Public themes allow you to add to the theme repository. They are available when you create an application or when you create a theme for an existing application.

You create a public theme by selecting a theme from within a workspace and application. Once created, it cannot be edited directly. To edit, create an application based off the theme, edit the theme within that application and then you can create a new public theme with the updated application theme (and delete the old, if you no longer want it).

Manage Service



In the Manager Service section (of Manager Service, naturally), there are 5 sections:

• Site-Specific Tasks
• Logs
• Session State
• Mail Queue
• Installed Translations

I just now learned what Site-Specific Tasks are (isn't that the whole point of this?). You can either create a task that will display on the Workspace Home or the Workspace Login. I've created 2 tasks, one for each area:



Where do those display?

Workspace Login



Workspace Home



Site-Specific Tasks is helpful to relay messages to your development staff, perhaps a planned outage or a reminder to use constraints. ;)

Next up are Logs. From here you can manually purge SQL Workshop, Page View Acitivity, Developer Activity , External Click, Mail and Login Access logs. The reports on these logs can be viewed from the Workspace Home. Not really a whole lot to see here.

Session State
Not a whole lot to see here either. There is a report to view recent sessions with drill-down capabilities, but that's about it. The other 2 items in this section are "Purge Sessions, by age" and "Session State Statistics."

Mail Queue
Basically just a log of all the pending emails to be sent. You have the option to send them or delete individual emails.

Installed Translations
Finally, not much to see here either. Just a list of, you guessed it, installed translations.



I'm going to take on the final piece of the Manage Service section in a separate post. This is where the guts of your APEX instance are managed. Lots of fun stuff here.

Labels: apex

  ¶ 11/15/2009 11:30:00 PM   0 Comments Links to this post

  Application Express: A Guide Since I'm going through and relearning everything, I'm creating this page as an index page for all future posts. Yes, I could use a label and have blogger do it automatically...I'm just trying to make more work for myself.

What's the abbreviation for Application Express? Joel Kallman answers that here and it's "APEX."

Installation Guide: 11gR2

• Administrative Services
  • Manage Service
    • Manage Environment Settings
  • Manage Workspaces
    • Create Workspace
    • Assign Multiple Schemas To A Single Workspace
  • Manage Applications
  • Monitor Activity
• Workspace
  • Application Builder
    • Substitution Strings
    • Application Builder Defaults
  • SQL Workshop
    • Object Browser
    • SQL Commands
    • SQL Scripts
    • Query Builder
  • Utilities
    • Data Load/Unload
    • Generate DDL
    • Object Reports
    • Recycle Bin
    • Database Monitor
    • APEX Views
    • Schema Comparison
    • About Database

Other
Drop Sample Application Database Objects

Labels: apex, howto

  ¶ 11/06/2009 05:00:00 PM   0 Comments Links to this post

  ApEx: Administrative Services Home

Getting back into the Application Express (ApEx) world again.

Trying to refresh my rusty memory on some key aspects. I figured I'd start with Administrative Services, in other words, controlling/configuring your ApEx installation.

Login
I found this confusing when I first started using ApEx. There are 2 login screens, one for the Administrative Services and one for Workspace Administration.

Administrative Services is in the (URL) form of:

http://<domain>:<port>/apex/apex_admin

For me it looks like this:

http://192.168.1.199:8080/apex/apex_admin

Which quickly resolves to:

http://192.168.1.199:8080/apex/f?p=4550:10

A helpful picture:



For Workspace Administration, the URL looks like this:

http://<domain>:<port>/apex/apex

For me it looks like this:

http://192.168.1.199:8080/apex/apex

Which quickly resolves to:

http://192.168.1.199:8080/apex/f?p=4550:1

A helpful picture:



Services
After you login, you are presented with the following screen:



Your choices are:

• Manage Service
• Manage Workspace
• Manage Applications
• Monitor Activity

Actually, I'll leave it at that for the time being. Each of those sections incorporates a bit of functionality which probably deserve their own post.

Labels: apex

  ¶ 11/06/2009 12:28:00 AM   4 Comments Links to this post

  APEXposed 2009 and "..you down with OPP(2009)?". Sorry, couldn't resist. Was it 1991 that song came out? Were freshmen in college even born yet? You know you're old when you start saying stuff like that...

The event takes place in Atlanta, GA, November 10-11th.

Join PL/SQL and Application Express experts Steven Feuerstein and Scott Spendolini for two days of Seriously Practical training that can immediately be applied to your daily duties at work.

Are you interested in optimization, PL/SQL tools, or writing maintainable code? OPP 2009 is the place for you.

Are you interested in tuning, debugging, or APEX and the Oracle Database? Sign up now for APEXposed 2009.

Want to learn a little of both? Sign up for one conference and attend sessions at either conference. This provides you with a great opportunity for cross-over training.

Admittedly, I've been out of the Application Express community for a couple of years now, but I still use the tool on occasion for smallish (personal) applications and I still follow blogs of those I do know. I still try to sell the tool at every organization I join (maybe APEX is the reason I keep losing (actually, I haven't lost any of them, I know right where they are) my job?). And yes, that list is long...especially recently.

My point is, I wouldn't have remembered this even if I weren't neighbors (so to speak) with Dan McGhan, the Tampa APEX guru. Am I not plugged in to the right communities? Half of this event is PL/SQL...I should know about events related to that. I think they need more press to get the word out...

Of course I want to go and since I have free time now, all I need is a pass. Now...wait for it.



You knew I was going to ask someone didn't you? I still don't qualify as a new media douchebag though.

It's funny, I always thought that this blogging thing would one day pay off in terms of a job or conference attendance. The latter was realized last May when I attended COLLABORATE 09 and almost realized for OOW earlier this month. No job yet though. I've asked John and Dimitri. I've asked Scott too. No takers yet. They've probably read my history. :)

Labels: apex, plsql

  ¶ 10/26/2009 01:33:00 AM   0 Comments Links to this post

  Apex: /apex/wwv_flow_utilities.show_as_popup_calendar was not found Since I hate Blogger's reports, ahem, lack of reports, I decided to try out the Google Bogger API to get what I wanted. Specifically, I want a report of posts by month.

I know this is possible as I've seen this on multiple blogs in the Archive or Categories section:



So instead of manually running an anonymous block to populate a table which I will then query, I decided to throw into Apex to make my life a little bit easier.

I create an application with a blank page and create my first item, P1_FROM_DATE. I decided to use a Date Picker (use item format mask):



I then created an item, P1_DATE_FORMAT, with the YYYY-MM-DDTHH24:MI:SS set as the source. When I created the Data Picker, I was thinking there would be some place to select the item, but there wasn't. So I opted to enter the format in the Source section:



Then I ran the page and clicked on the little calendar icon, only to see this:



Off to the Application Express forum and I read a few posts mentioning bad formats.

So I removed the "weird" YYYY-MM-DDTHH24:MI:SS format and voila!

That'll teach me to try something new when I'm rusty.

As a small aside, the date format, is the RFC 3339 format, according to the Google documentation.

Labels: apex

  ¶ 8/04/2009 10:59:00 PM   4 Comments Links to this post

  How To: Users and Roles (Simple) I'm using Apex again, which is nice. I love the database work; cleaning, refactoring, etc. but there's just something very cool about being able to put a "face" on that database stuff.

I'm mostly done with the application and I'm going through adding in security. It's not an after thought, I'm just not going to add it first as it will make my life very difficult.

I would love to use Database Authentication, but it only checks to see if you have an account. Your database roles do not carry over into Apex (yet). I don't want to use Apex Authentication mostly because I've never used it.

I could create my own table based security, but not yet. I'm trying to keep it as simple as possible for now.

I decided on a mix of Database Authentication and table based users and roles. I will not be storing passwords nor writing any custom authentication. I'll just rely on Oracle to do that. I will however capture the username and put it in an Application Item. In the Administrator's interface, the users will be able to create users...but only if they already exist in the database. I will be creating the following roles:

--ADMIN - this role can do anything. full access to the application.
--SECTION_DEVELOPER - as it stands, we can not completely hand this off to the business. There will be some intervention required by IT. This role should be able to do all that the business person can do and a couple of extra actions.
--SECTION_ADMIN - this will be the role assigned to the business person.
--TAB_DEVELOPER - similar to SECTION_DEVELOPER, just another section of the application
--TAB_ADMIN - same as SECTION_ADMIN, assigned to business user

Perhaps an easier way to visualize it:
ADMIN
--SECTION_DEVELOPER
----SECTION_ADMIN
--TAB_DEVELOPER
----TAB_ADMIN

Here's the table to hold the roles:

CREATE TABLE t_roles
(
  role_name VARCHAR2(30)
    CONSTRAINT pk_rolename PRIMARY KEY,
  parent_role_name
    CONSTRAINT fk_rolename_troles REFERENCES t_roles( role_name )
);

INSERT INTO t_roles( role_name )
VALUES ( 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'SECTION_DEVELOPER', 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'TAB_DEVELOPER', 'ADMIN' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'SECTION_ADMIN', 'SECTION_DEVELOPER' );
INSERT INTO t_roles( role_name, parent_role_name )
VALUES( 'TAB_ADMIN', 'TAB_DEVELOPER' );

I'm creating a view on top of this table because I want to use some of the hierarchical capabilities.

CREATE OR REPLACE
VIEW v_roles
AS
SELECT
  role_name,
  parent_role_name,
  SYS_CONNECT_BY_PATH( role_name, '/' ) role_path,
  level role_level,
  CONNECT_BY_ISLEAF is_leaf,
  RPAD( '-', level * 2, '-' ) || role_name display_role_name
FROM t_roles
  START WITH parent_role_name IS NULL
  CONNECT BY PRIOR role_name = parent_role_name;

I created a procedure, is_authorized which took in the username (Application Item) and a literal; ADMIN, SECTION_DEVELOPER, SECTION_ADMIN, TAB_DEVELOPER, and TAB_ADMIN. It returned either TRUE or FALSE (boolean).

Here's the table definitions for the user and user/role intersection tables:

CREATE TABLE t_users
(
  username VARCHAR2(30)
    CONSTRAINT pk_username PRIMARY KEY
);

INSERT INTO t_users( username ) VALUES ( 'CJUSTICE' );

CREATE TABLE t_user_roles
(
  username
    CONSTRAINT fk_username_userroles REFERENCES t_users( username )
    CONSTRAINT nn_username_userroles NOT NULL,
  role_name
    CONSTRAINT fk_rolename_userroles REFERENCES t_roles( role_name )
    CONSTRAINT nn_rolename_userroles NOT NULL
);

INSERT INTO t_user_roles( username, role_name )
VALUES( 'CJUSTICE', 'ADMIN' );

CREATE OR REPLACE
VIEW v_active_user_roles
AS
SELECT
  ur.username,
  r.role_name,
  r.parent_role_name,
  r.role_path,
  r.role_level,
  r.is_leaf,
  r.display_role_name
FROM
  v_roles r,
  t_user_roles ur
WHERE r.role_name = ur.role_name;

Now I need a FUNCTION that will tell me whether this person is authorized or not.

I struggled with this a little bit (hence the post).

DECLARE
  l_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = :p_username
    AND role_name = :p_role_name;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

OK, so if the code from Apex is passing in CJUSTICE for the username and ADMIN for the role, it works. But ADMIN is the all powerful user. So I need another check in there:

DECLARE
  l_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = :p_username
    AND role_name = :p_role_name;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    SELECT COUNT(*) 
    INTO l_count
    FROM v_active_user_roles
    WHERE ...
  END IF;
END;

That's where I kind of got stuck. The extra SELECT COUNT(*) would have needed at least one more additional IF THEN control statement. Time to step away.

Thinking about it, it might be easier if ADMIN were the bottom-most...leaf? In other words:
SECTION_ADMIN
--SECTION_DEVELOPER
----ADMIN

Using SYS_CONNECT_BY_PATH it would have been easy to check. I think.

If ADMIN is at the bottom though, you'll have to jump through all kinds of code hoops to keep it at the bottom. That's the indicator to me that I'm overthinking it.

What about a loop, using ROLE_PATH (SYS_CONNECT_BY_PATH)? Something like this:

    FOR i IN ( SELECT role_path
               FROM v_roles
               WHERE INSTR( role_path, l_rolename ) > 0 )
    LOOP
      IF INSTR( i.role_path, l_rolename ) < INSTR( i.role_path, p_rolename ) THEN
        RETURN TRUE;
      END IF;
    END LOOP;

If the user's role (ADMIN) is at a place closer to the front of the string and the passed in role (TAB_ADMIN) is further back, the user will be authorized to see/view/execute whatever the authorization is applied to. Here's the code all put together:

CREATE OR REPLACE
FUNCTION is_authorized
  ( p_username IN VARCHAR2,
    p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
  l_count INTEGER;
  l_rolename VARCHAR2(30);
BEGIN
--1.  see if the user/role exists
--1a.  if true, return true
--1b.  if false
--1b1.  find the role the user is assigned to
--1b2.  see what roles inherit from the user's role
  SELECT COUNT(*)
  INTO l_count
  FROM v_active_user_roles
  WHERE username = p_username
    AND role_name = p_rolename;

  IF l_count = 1 THEN
    RETURN TRUE;
  ELSE
    BEGIN
      SELECT role_name
      INTO l_rolename
      FROM v_active_user_roles
      WHERE username = p_username;
    EXCEPTION
      WHEN no_data_found THEN
        RETURN FALSE;
    END;

    FOR i IN ( SELECT role_path
               FROM v_roles
               WHERE INSTR( role_path, l_rolename ) > 0 )
    LOOP
      IF INSTR( i.role_path, l_rolename ) < INSTR( i.role_path, p_rolename ) THEN
        RETURN TRUE;
      END IF;
    END LOOP;
      
    RETURN FALSE;
  END IF;
END is_authorized;
/
show errors

It's certainly not the best solution in the world. When I have a bit more time, I'll certainly revisit it.

Integration with Apex
Since I haven't worked with Apex regularly since 2.2, I started to use the Conditions section to store the code. What that would mean is possibly storing

RETURN is_authorized( :F_USERNAME, 'ADMIN' /*(or others)*/ );

in about 60 locations throughout the application. Then I saw this:


I had completely forgotten about that! Authorization Schemes allow you to define as many...schemes as you want. I created 5, one for each role.

Added bonus, when I need to change these, I only have to do so in one place.

You can add Authorization Schemes to almost everything. Pages, HTML Regions, Buttons, Items, Columns in reports, etc. As fine a granularity as you would ever need.

Update: 08/07/2009
This function does not work! Sue me. There is a later post addressing the issues I found, Users and Roles - Revisited.

Labels: apex, development, howto, security

  ¶ 7/20/2009 04:25:00 PM   4 Comments Links to this post

  APEX: URL Syntax This is more for my own edification than anything. I always forget which place the ClearCache inhabits.

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:PrinterFriendly

With numbers so I don't have to count every time:

f?p=App1:Page2:Sess3:Requ4:Debug5:Cache6:items7:printer8

f?p=1:2:3:4:5:6:7:8

Link to the documentation is here.

Labels: apex

  ¶ 6/28/2009 10:59:00 PM   2 Comments Links to this post

  Virtual Box, OEL, 11g, ApEx Finally.

After many months and many attempts, I have managed to install a database on a non-windows platform. I think I deserve a little credit. ;)

In January, I began installing Ubuntu Intrepid Ibex on all of my home computers. What this meant for me is that I was often without one because I screwed something up. I even got so far as to install Oracle XE on Ubuntu which took 2 blog posts.

Just before COLLABORATE 09, I installed Jaunty Jackalope on my laptop. Then I got the crazy idea to install Oracle on Ubuntu. I ended up having to wipe the hard drive and start fresh. I have no doubt that it (Oracle) can be installed on Ubuntu, it just takes someone much more experienced than I.

Armed with a fresh install, I was going the virtual route. Inspired, I believe, by this comment (Aman Sharma).
1.  Install Sun's Oracle's VirtualBox
2.  Download the latest version of Oracle Enterprise Linux (v5)

I think that's the point I realized my first installation of Jaunty was hosed, and by hosed I mean unrecoverable by me. I couldn't see the 2nd partition, the one with all the room.

3.  Create OEL virtual machine
4.  Follow this guide to get it ready to install the Oracle database (seriously? Why does OEL not come ready for the database server?)


4a.    There's no mention of opening up the firewall in that article. It's probably obvious to most...
5.  Connect to Oracle from the host machine
6.  ApEx? Which version am I running? 3.0? That won't do. Download ApEx 3.2
7.  Install ApEx
8.  Configure Embedded PL/SQL Gateway for port 80
8a.    Ports below 1024 are reserved for privileged processes...Configure Embedded PL/SQL Gateway for port 8080
9.  Connect from host machine.


9a.    Login to ApEx.


10.  WIN!

Labels: apex, dba, oel, virtualbox

  ¶ 6/23/2009 10:37:00 PM   6 Comments Links to this post

  COLLABORATE 09: Convert Forms to ApEx David Peake
Oracle Application Express 3.2 Forms Conversion

Stupid laptop battery. This would have been done during.

Anyway, it was a tad depressing to see this presentation. All ApEx all the time. The first 20 minutes were an overview of ApEx. Yeah, I know, it's cool. Thanks for reminding me that I can't sell it to anyone. I had Miladin interested in it, but I left before we could really do anything.

I attended because my new place has Oracle Forms and there doesn't seem to be a lot of experience with them. The Forms Converter is part of the latest release, 3.2.

First, you convert your OLB or menu files using the forms2xml utility.
Next, create a workspace and associate it to your Forms application schema.
Create a conversion project.
Analyze the Oracle Forms application.
Generate the Oracle ApEx application.

Voila! You're done. Now all you have to do is customize to suit your needs. Very nice stuff indeed.

You can find a tutorial on Oracle-by-Example (OBE) here.

Labels: apex, collaborate_09

  ¶ 5/04/2009 02:26:00 PM   0 Comments Links to this post

  Oracle As a Platform I'm always hearing about this platform and that platform and all the wonderful things you can do with them.

What about Oracle as a platform?

Before you get all crazy eyes on me, I'm not talking about eBay, Google or the like. I'm talking about the other 99% of the applications out there.

Database
Oracle is the market leader in database technology. There is a very good reason for that and I'll let you decide on what that reason might be.

Web Server
Either Oracle's Application Server (Apache), which came on the Companion CD on 10g (not sure about 11g), or Oracle's built in XDB HTTP Server (pre-configured in 11g). MOD_PLSQL allows the web server to communicate with the database.

MOD_PLSQL is an Apache (Web Server) extension module that allows one to create dynamic web pages from PL/SQL packages and stored procedures. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).

Front End
You have 2 choices (that I am aware of) for creating web based applications, Application Express and the HTP/HTF packages. The former is built atop the latter...and I'd definitely use ApEx before the packages.

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure.

ApEx is, in my experience, one of the most under-used tools out there.

Procedural Language
PL/SQL is a very powerful language. It's loosely based on Ada and is a procedural extension to SQL. PL/SQL has been around since 1992, version 6. It supports arrays, collections, user defined types, and many other data structures. Best of all though, Oracle provides a vast library of PL/SQL code. Sending email? Got it. Lower level stuff? UTL_TCP should do the trick. Queueing? Done.

I could go on and on, but you get the point.

If you want to build powerful, robust applications that are low on complexity and high on performance, Oracle's the way to go.

What's that? Oracle licensing is too expensive? How about Oracle Standard Edition (SE) for $5,000? There's not a whole lot you don't get with SE.

Of course you could disagree, but I'd say you're pretty crazy.

Labels: apex, database, development, oracle, plsql

  ¶ 3/09/2009 11:49:00 PM   6 Comments Links to this post

  Debugging ApEx with FOP I've been helping a friend out, Daniel McGhan, with an ApEx application he's created. Now by "friend" I mean he's the annoying heckler from my very first ApEx presentation. We also co-presented in September. Now when I say "co-presented" I mean he did everything. I was completely unprepared (and unemployed at the time).

Anyway, he's off galavanting in Europe right now and I offered to help support his application is his absence (slacker).

Of course he leaves me with a whopper of a problem...the PDFs won't generate correctly.

I've been out of the daily ApEx world for going on 2 years now. I try to keep up by reading blogs and such, but there's no replacement for doing it every day.

So we've got the latest version of ApEx (3.1.2) running on Oracle XE (dev environment) to be deployed on a 10.2.0.3 Enterprise Edition Oracle Server running on Windows. At least I understand Windows.

Dan is using FOP to render the PDFs. I have no idea what it means, only that it is Java based. Do I put it in the database? No. It runs from the OC4J server. OK, I have a little experience with that...but it was mostly command line, at some point they (being Oracle) finally made a nice and pretty front end (where the hell was that before?).

Back to ApEx. Interactive Reports. Check the box for PDFs (I'm skipping the whole setting it up through the administrative interface on purpose, but rest-assured, it was set up). I open the page, click on the Interactive Reports thingy, select PDF and Open. Invalid File Type. What? Tried saving it to file, same error.

Maybe this latest greatest Adobe Reader (9 something) can't read...but their latest greatest should be able to read any pdf created by any version right? I'll just open up the file in Notepad. Big clue...right at the top:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1186
ORA-12570: TNS:packet reader failure

Awesome! It's an Oracle error...I can fix that (hopefully).

I went through the forums which let me here. Didn't really help me much. But I did realize the OC4J server wasn't running. Barnacles.

Started it through the provided .bat file. Go through the steps again, still invalid. But I have a new error in the file:

500 Internal Server Error
OracleJSP:
An error occurred. Consult your application/system administrator for support.
Programmers should consider setting the init-param debug_mode to "true" to see the 
complete exception message.

Closer.

This is where my old command line skills came in handy. .bat file looks like this:

set ORACLE_HOME=C:\OAS\oc4j_101340
set JAVA_HOME=C:\Program Files\Java\jre1.6.0_07\
cd C:\OAS\oc4j_101340\bin
oc4j -start

I know where the oc4j file is now, let's start it from the command line:

c:\java -jar c:\oas\oc4j_101340\j2ee\home\oc4j.jar

08/11/19 21:57:01 Oracle Containers for J2EE 10g (10.1.3.4.0)  initialized

Run the PDF again, check console to see what happened. An error! Some sort of jsp exception which of course I don't have and can't repeat now.

So I add -Dbuild.debug=true to the command. Right in the middle of it I see

!!!!!cannot find javac compiler!!!!!!

If ever a clue there was.

Which java is it using? The JRE? Of course there's no compiler, that's a runtime environment. Where's JDeveloper? It has a compiler. c:\jdeveloper\jdk Plug it in a voila! It works.

So apparently it has to be compiled on first use (it's been awhile since I've used J2EE), and that's the reason I can't (or won't) recreate the problem.

A small lesson learned in...something.

Labels: apex, debug, fop, java

  ¶ 11/19/2008 10:26:00 PM   0 Comments Links to this post

  Lookup Tables Also known as reference, crosswalk and a few other names.

I love 'em.

I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.

I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.

I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.

Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.

To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.

Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!

Just don't ask Duke Ganote whether type is a good name or not!

Labels: apex, constraints, database, design, development

  ¶ 7/01/2008 09:52:00 PM   3 Comments Links to this post

  ApEx: Oracle Marketing WTF? At the time of writing, the score is 68 have not used ApEx leaving only 8 who have. Obviously this poll isn't scientific, but it does have to represent a small bit of the community. Perhaps Oracle Mix would be a better place to ask the question?

I guess my followup poll would be why? Why haven't you used it?

Did you not know about it?

If that's the case, then it's definitely an Oracle Marketing WTF.

Did you know about it but just never got around to trying it?

I'm completely dumb(quit snickering)-founded.

I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.

Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).

As someone pointed out in the comment section, it's one of the busiest forums on OTN.

I wonder if John and Dmitri run into this? Probably makes cold calling difficult.

As a [ApEx] community, what can we do to help promote it?

Labels: apex, database, oracle, wtf

  ¶ 5/13/2008 10:01:00 PM   4 Comments Links to this post

  ApEx: What is it? Surprisingly it seems, very few Oracle professionals know about ApEx. At WellCare, no one knew about it. Many others I have talked to have no idea what it is.

How can this be?

I'm hard-pressed to believe I am an early adopter. I like to think of myself as such, but perception and reality are two totally different things.

In a nutshell, ApEx (or Application Express, aka HTMLDB) is a rapid application tool that allows you to quickly build web based applications on top of an Oracle database. If I remember correctly, it was initially billed as the Microsoft Access killer.

It is also the front end for Oracle XE.

I've personally built 3 (professional, i.e. paid for) ApEx applications. One for my former day job with 350 pages (1.5 years to build and maintain), one for my fellow baseball alumni at the University of Florida, and one for my now defunct business, CABEZE.

I believe the question I get most often is: "Can you format it?"

Which I take to mean can you make it pretty or design it anyway you want?

The answer to that is a resounding "Yes!"

So, what follows is a list of ApEx applications for you to evaluate on style and design (borrowed from the unofficial ApEx wiki housed at shellprompt):

• Dance Tunes - arguably the best designed ApEx application out there.


• AskTom - We all know, or should know, this site. 40,000 page hits a day on a 4 CPU box.


• Metalink - In my opinion not the best implementation of ApEx, but it's a busy site.


• Shellprompt - host your applications here for a very reasonable price.


• View other examples here

In summary, you can do with ApEx what you can do with any other web tool/environment. Want AJAX? Done. CSS? Done.

You're only limited by your imagination.

(Polls are fun...I'll stop apologizing for it soon)

Labels: apex, development, oracle

  ¶ 5/12/2008 07:34:00 PM   13 Comments Links to this post

  ApEx Presenting = FUN! Wow...that's all I can say.

At lunch today, I put together my power point presentation. I meant to do it sooner of course, but it's been another busy week. In fact, I hadn't done a thing to prepare other than walking through it in my head.

I did not want this to be a presentation so much as a demo. Six slides is all they got.

I suddenly got very nervous around 4 today. I just want to get over there and start. I ended up leaving work just before 5.

The meeting was at the PriceWaterhouseCoopers building in Tampa, just across the street from the Tampa Bay Buccaneers headquarters. Very nice building.

The Presentation

I was introduced by the SOUG president promptly at 6:30. Roughly 40 people showed up (filled the room).

I had an hour and a half to complete my presentation...ummm...WHAT? I've got like 10 minutes worth of material! How the hell am I going to manage this??

Anyway, I walked through some of the features of APEX: Load/Unload data, SQL Commands, that kind of stuff, just trying to get to the application builder.

A couple of nights ago I began walking through and building a basic little reporting application tailored to the DBAs. Reports on roles and privileges basically. I never even got to that.

I created a 2 line csv (yes, I should have done it before hand), uploaded it to demonstrate how easy it was and then off the creating a report on that table.

"Can you create a form to update that record?"

Sure, here's how you do it. One minute later the form was done and I had updated the record. True to form...it's just so damn easy.

I answered a few other questions and then I got stuck. A member of the audience started answering the questions that I couldn't. We (the member in the audience) had spoken before the meeting and I believe he's just as passionate about APEX as I am). It worked rather well, so well in fact, that we're going to try and work out a dual presentation at the upcoming Technology Day SOUG puts on.

Someone would ask a question, I would answer them by showing them how to do it. Have I mentioned how much I like APEX? Rocks.

Next thing I know, it's a little past 8 and I can see the President trying to end the meeting. No way dude, too many questions to answer!

I got out of there around 9:30. I answered questions to the best of my ability...if I didn't know, pointed them to resources that could.

Exhilarating!

Can't wait to do it again...but next time I'll practice!

update
BTW, great crowd tonight. I couldn't have asked for a better group. Thanks to everyone for their support, and thanks to Tom and LewisC for asking me to do it.

Did I mention how cool it was? ;)

Labels: apex, database, oracle, presentation

  ¶ 4/24/2008 10:49:00 PM   3 Comments Links to this post

  ApEx Presentation I'll be doing my first professional IT presentation this Thursday for the Suncoast Oracle Users Group.

I'm a bit nervous, but excited at the same time. My goal is to make it as interactive as possible and just let it take me where it takes me.

I will have a canned demo, but I would rather it be more fluid. Can't always plan for those things...I could be a total bust!

So, if you're in the neighborhood and you want to check it out (or just laugh and heckle me), come on by. Details can be found here.

Labels: apex, oracle, presentation

  ¶ 4/21/2008 08:54:00 PM   3 Comments Links to this post

  Vacation? I've been on "vacation" since April 4th. Of course I was sick and slept most of the day. On Sunday, I took the family to Disney World. Last year my parents bought us annual passes to the Magic Kingdom so that we could all go together to get away.

I am an only child. My parents and I never had vacations, we just moved somewhere (8 times). If we did go somewhere, it was to my parents home town town for a week or two to visit Grandma and Grandpa.

Now, here's what I consider a vacation, sitting on a beach drinking Corona's with my beautiful wife (no kids) staring at the ocean. Yes, this is the Corona ads you've probably seen. Quiet. No work. Perfect.

I love the kids, but they are harder "work" than work. On "vacation," I am the pack mule. I carry everything and everyone. A friend at work has shared this story about Man's Stages of Life, I'm in the donkey phase...

I'll say it again because I don't believe my sense of humor always comes out correctly (I'm rarely serious), I love my family, my kids, but a break it is not.

Disney H5N1?

We did have a great time despite all of us coming down with the Disney version of the bird flu. Imagine a virus made up of viri (sp?) from 100 countries...that's what we all have. I'm hacking right now. I took a half day on Thursday and spent the following day with Kate at home. She slept most of the day. Kris and Little Chet spent the day at Disney's Hollywood Studios. Saturday we came home because Little Chet finally caught it. Now we're resting.

Upcoming

• Little Chet gets a tooth pulled tomorrow


• Pick up new Dell XPS laptop tomorrow (woohoo!)


• Go back to work (woohoo?)


• I'll be doing my first Application Express presentation for the Suncoast Oracle Users Group, SOUG. Actually, it's my first IT related presentation. If you can make it, please come by and laugh. More information about the presentation can be found here.

Labels: apex, kate, vacation, work

  ¶ 4/13/2008 12:41:00 PM   2 Comments Links to this post

  The Return to ApEx It's been almost a year, but I've finally gotten a chance to dive back into ApEx!

I've been working primarily on our financial reconciliation for our Medicaid business. That's now very stable as we have everything in our fancy new star schema.

One of the support type activities we've been doing for the past 6 months is maintaining their rate tables...manually. They send (and resend) a csv file and we then match and insert those new records into their rate tables. I don't get to recreate the entire thing unfortunately, it's horribly designed, but I do get to do something.

So instead of doing these manually I finally convinced my boss that this could be done relatively easy with ApEx. I've demo'd it for him in the past, so he's aware of it's capabilities (my evangelism of it doesn't hurt though). Of course our VP steps in and says we have to go through the technical review board. Fair enough, I'm all for standards.

Thankfully my manager convinced the architects that we don't have the Java or Ruby resources to do this, plus, it would take weeks!

So, here's to ApEx, and the further infiltration of it at WellCare!

Labels: apex, oracle, work

  ¶ 3/19/2008 12:35:00 AM   2 Comments Links to this post

  Keeping it Simple One of my all time favorite articles is The Complicator's Gloves on Worse Than Failure (formerly the DailyWTF). It identifies the tendency of software developers in particular to come up with overly complex solutions, usually when there is a much simpler one available.

This was the context of my latest rant to my CIO. Actually, this theme seems to play out in all my rants. Funny how that works.

While web services and the like have their place, many times they are used just because they are the cool new thing, not because of a pressing need. I know I am not the first to mention that nor will I be the last.

Whether it was years of reading asktom (for pleasure no less) or the influence of my first boss, I have striven (sp?) to build applications that are scalable yet easy to maintain.

One of my proudest accomplishments as a developer was at my previous job. A small state-contracted agency where I was the lone developer. I, thanks to a very trusting boss, was allowed to install Oracle and soon after found Application Express (APEX). In 18 months I was able to create some 350 pages of forms and reports for the organization. One person, 350 pages. I once found a job ad for a web developer to help maintain a 100 page website on a team of six. What? Six people? Really? Must be java or something. ;)

I continued to work for them on a contract basis for about six months after I left. Mostly until the new guy got comfortable. Unfortunately for me, they didn't require my services a whole lot. Yes, I could be deluding myself, I realize that...but I just don't believe it. They WOULD tell me.

Back to my point. At our organization we seem to have quite a few architects. They talk of Ruby on Rails, Java, JBoss, etc. MySQL gets a brief mention on occasion.

We have a hard enough time writing good SQL or PL/SQL, so now we're going to introduce new languages and a new database platform?

If we were a company that made software, I will probably be [mostly] on board, but we are not. We store and manage data for the business to do their job.

I do hope I am wrong about them and that they do talk about the importance of data in our organization. I just haven't see it yet.

So, put it in the database, use APEX when appropriate (95% of the time) and keep it simple.

Labels: apex, database, development, work

  ¶ 11/29/2007 01:00:00 AM   1 Comments Links to this post

  Looking for an Oracle Developer? So I am on vacation (well, I wouldn't necessarily call being a pack mule vacation) at Disney World this week.

Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.

FBI raid shutters Medicare insurer

FBI Raids Tampa WellCare

I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.

I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...

So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.

Labels: apex, development, oracle, work

  ¶ 10/25/2007 11:41:00 AM   0 Comments Links to this post

  APEX Evangelism I started using APEX, Application Express, formerly HTMLDB, more than two years ago.

Prior to APEX, anything web related I used Java (J2EE).

When I started at my previous job, they had virtually no internal systems. I was hired as a Reports Analyst/Data Analyst because they had decided their 3rd party vendor wasn't working out.

I somehow managed to talk them into purchasing an Oracle database (SE); since they wanted it quick, and that's what I knew, it was a no-brainer for my boss.

Relatively quickly I managed to nail down my first report after downloading all the data into our shiny new Oracle database. I wanted to impress so I began the tedious task of webifying it, and boy were they impressed.

Up to that point, I had been using a modified version of the Struts framework. It was definitely overkill. I created a bean (class) for the report data and a page (jsp) to display that data. It was a time consuming process at best.

I had read about HTMLDB through AskTom at the job before that one and it was intriguing. So I downloaded it at the new job and took it for a spin. Within a month I had a couple of pages up and running (complete with user login and our cool new graphs). It was difficult initially to wrap my head around the terminology, but once I did it was full speed ahead.

I found the forums on OTN for APEX and began researching problems and asking questions. Soon after that I was contributing. I was hooked.

I was now an APEX evangelist. I would extoll the virtues of APEX to anyone that would listen (mostly my wife, but she was just humoring me). By the time I left that job, 18 months later, our internal web site consisted of almost 350 pages.

At one point I read an job description something along the lines of "Developer wanted to help maintain 100 page web site with a team of 6." Six people? What? Why does it take so many people? What the hell are they using, Java?

I still consider myself an APEX evangelist, but I no longer get to work with it on a daily business. I did manage to convince my superiors though that it would be a good basic reporting tool for our data warehouse environment. We've built 2 applications so far that utilize APEX and hopefully there's more to come.

Labels: apex, database, oracle

  ¶ 8/27/2007 11:40:00 PM   0 Comments Links to this post