APEX: Create and Parse Arrays It's been awhile since I've been able to work with APEX extensively, so I am rusty.

A question came up today whether we could get multiple values into a single variable (Item in APEX).

Yes we can!

APEX_UTILSNeed some data first:

CREATE TABLE t ( some_text VARCHAR2(10) );

INSERT INTO t ( some_text )
SELECT dbms_random.string( 'a', 10 ) some_text
FROM dual
  CONNECT BY LEVEL <= 5;

CJUSTICE@TESTING>SELECT * FROM t;

SOME_TEXT
----------
thrFXviVWJ
kpfGRRwctv
EVxNrcmBHC
gcBlHaKrLa
irYduOZfkS

I want that table data to be in a single item. TABLE_TO_STRING is your function.

VAR C VARCHAR2(100);

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  SELECT some_text
  BULK COLLECT INTO l_table
  FROM t;

  :c := apex_util.table_to_string( p_table => l_table );
END;
/

PL/SQL procedure successfully completed.


C
-----------------------------------------------------------
thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS

Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer.

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_table := apex_util.string_to_table( p_string => :c );
  
  FOR i IN 1..l_table.COUNT LOOP
    d( 'value ' || i || ':  ' || l_table(i) );
  END LOOP;
END;
/

value 1:  thrFXviVWJ
value 2:  kpfGRRwctv
value 3:  EVxNrcmBHC
value 4:  gcBlHaKrLa
value 5:  irYduOZfkS

PL/SQL procedure successfully completed.

Done.

Labels: apex, apex_util, howto

  ¶ 3/10/2010 01:23:00 AM   0 Comments Links to this post

  APEX: LDAP Authentication I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected.

I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide.

Anyway, it was remarkably easy.

Setup
APEX: 3.2.1
Web Server: Apache (OHS)
Database:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

First I fired up the web server:

C:\oracle\http\opmn\bin>opmnctl start
opmnctl: opmn started

C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication."

After I had created the application, I went into Shared Components --> Authentication Schemes --> Create

Select the default and click Next



Select "Show Login Page and Use LDAP Directory Credentials" and click Next



I've already done this so I'm selecting my current Login page, 11, click Next



Enter your LDAP Host and your DN:



Your DN String should look something like this (from article above):

cn=%LDAP_USER%,l=amer,dc=oracle,dc=com

Make sure you use the %LDAP_USER% after the cn= portion of the string.

Name it ldap_test, click Create Scheme:



You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current



To test it just run your application and login using your LDAP (AD) credentials



Success!

Labels: apex, howto

  ¶ 3/09/2010 01:09:00 AM   0 Comments Links to this post

  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: 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