Changing Scheme in Oracle Application Express (APEX) inside application

There is some application when you need to change your current scheme to another one. For example you would like to create your own SQL Editor in HTML. It would be much easier to change the current scheme, then work with "scheme." prefix. So, there is a solution.

You have to update three Oracle Application Express tables (wwv_flows, wwv_flow_companies, wwv_flow_company_schemas) in scheme flows_030000 (in my case I use APEX 3, if you use older version check your flows_ name).

There is an example:
You have to know your application and workspace identifier.

update flows_030000.wwv_flows
set owner=&NEW_SCHEME_NAME
where id=&APP_NAME;

update flows_030000.wwv_flow_companies
set FIRST_SCHEMA_PROVISIONED=&NEW_SCHEME_NAME
where PROVISIONING_COMPANY_ID=&WORKSPACE_ID;

update flows_030000.wwv_flow_company_schemas
set schema=&NEW_SCHEME_NAME
where id=&WORKSPACE_ID;


Best Regards!

Best HTMLDB Websites

Recently, I found very interesting post in Oracle Application Express Forum, BEST HTMLDB WESITE COMPETITION
I think that it is nice idea and decide to create an application where people can add and vote for websites.
This is the link of my application:
http://best-htmldb.et.ua/
Now I would like to present a short user guide.

1. Adding the Website.

1. For adding a Website you have to push "Add" button on the main page.
2. Then you have to enter "Website name", "URL", "Site author"(optional), "Screen shot" (optional) and "Short description"
3. At the end you have to push "Add" button.

2. For going to the website push "Go" button.
3. Looking the description

1. Click on the website name.
2. At new page you can find the information about website, statistic, screen shot and users comments.

4. Voting

1. Press on the Vote link, at the main page.
2. Set your marks.
3. You can also add a description (optional)
4. And finally press "Vote" button
   

P.S. You can vote only one time.

If you have any ideas and propositions about this application, please write to me.

Two Level Tabs

Using my experience, Apex beginners always has a trouble with creating Two Level Tabs. That’s why I decided to post this blog.
First of all some words about the structure of Two Level Tabs.
This is a schema of Apex structure of Tabs


For example:
“Parent Tab Set” is – “Main”
“Tab Set + Parent Tab” is – “Parent 1” and “Parent 2”
“Standard Tab” is – “Page 1” and “Page 2”

Now let’s create a sample two level tabs:
1. Go to Shared components -> Tabs
2. Click on “Create New Standard Tab”
3. Creating Standard Tab Set
a. Check “Create a new tab set and a new tab within the tab set.”
b. Enter name for your tab set, for example “TS1”
c. Enter the name of parent tab set, for example “PTS1”
d. Enter tab label, for example “page 1”
e. Enter page number, for example “1”
f. ………..
g. Push “Create Tab” button.
4. Now you have only One Level Tab
If you include this tab to your page you will have the next view:
5. Lets add Parent Tab, by clicking on “Add” link (add new parent tab)
a. Enter parent tab label, for example “Parent 1”
b. ……….
c. Push button “Create Parent Tab”

6. Go to page “Display Attributes“, Select page template to “Two Level Tabs” and Standard Tab Set, in our example it is “TS1(page 1)”
7. If you run your application you can see the next tabs:
8. Now lets add one more standard tab.
a. Click on “Add” link (add new standard tab)
b. Enter tab label, for example “page 2”
c. ……..
d. Click on “Create Tab” button
e. Go to page 2 and do the same thing as in point 6.

9. At this step you have one parent tab and two standard tabs. The next step is adding another parent tab. It was the main reason for writing this report because each novice has a problem with it. You will see it later.
a. Reproduce point 5 (example Parent 2)
b. Click on created parent tab (Parent 2) and, and reproduce point 8
c. Go to the page which you have just associated with standard tab. And reproduce point 6 (standard Tab Set -> T_PARENT2(Page 3))
d. Lets run our application
If you go to “Parent 2” the page 3 will be opened, but there would be one level tab:
This is because your Standard Tabs should be in one Parent Tab Set

10. Lets add Tab Set with page 3 to Parent Tab Set of page 1 and 2.
a. Open Shared Components -> Tabs
b. Go to “Edit Standard Tabs” link
c. Click on Edit icon of new tab set. (note that the value of Parent Tab Set is (null) )
d. Go to Parent Tab Set
e. Change it to your Parent Tab Set (TS1)
f. Click Apply Change button.

11. Now everything works perfectly
12. At the end of my post I would like to show you how do I drop Parent Tabs and Parent Tab Sets
a. Click on Edit icon on parent tab:
b. Press “Delete” button
c. You can find that tab set is still exist
d. Go to Edit Standard Tabs and delete it in the same way.

That’s all

P.S. One more reason that the parent tabs were not being displayed was because, the target page for the parent tab was not set as a target (set current) for any of the standard tabs that belonged to that parent tab .
SO what happened was whenever a parent tab was clicked the app loaded the target page with the standard tabs properly, but since none of the standard tabs itself was set to be current for that target page, the parent tab itself was not current and hence not displayed. (user581875)

Authentication using client SSL certificates

1. Creating your own self-signed trusted certificate. And creating client certificate
   
1. First of all you have to download openssl library (http://www.openssl.org/)
2. Install it. (I installed on UNIX)
   

$ ./config
$ make
$ make test
$ make install

3. Create self-signed certificate

 # openssl req -new -newkey rsa:1024 -nodes -keyout ca.key -x509 -days 500 \
 -subj /C=RU/ST=Msk/L=Msk/O=My\ Inc/OU=Sale/CN=bla/emailAddress=usr@dom.ru \
 -out ca.crt

 You can use next commands for previewing key and certificate:

 # openssl rsa -noout -text -in ca.key              (key)
 # openssl x509 -noout -text -in ca.crt             (certificate)

4. Creating clients certificate

Creating clients certificates configuration file ca.config

 [ ca ]
 default_ca             = CA_CLIENT       # When signing certificate
         # use section CA_CLIENT

 [ CA_CLIENT ]
 dir                    = ./db            # Catalog for service files
 certs                  = $dir/certs      # Catalog for certificates
 new_certs_dir          = $dir/newcerts   # Catalog for new certificates
 database               = $dir/index.txt  # File with db of signed certificates
 serial                 = $dir/serial     # File with serial number of certificate (HEX)
 certificate            = ./ca.crt        # File of trusted CA
 private_key            = ./ca.key        # CA key
 default_days           = 365             # Expired date of signed certificates
 default_crl_days       = 7               # CRL expired date
 default_md             = md5             # Algorithm
 policy                 = policy_anything # The name of policy description section
 
 [ policy_anything ]
 countryName            = optional        # Country - not required
 stateOrProvinceName    = optional        # ......
 localityName           = optional        # ......
 organizationName       = optional        # ......
 organizationalUnitName = optional        # ......
 commonName             = supplied        # ...... - required
 emailAddress           = optional        # ......

 Create directories that fit to config file

 # mkdir db
 # mkdir db/certs
 # mkdir db/newcerts
 # touch db/index.txt
 # echo "01" > db/serial

Create client key and certificate request (CSR)

 # openssl req -new -newkey rsa:1024 -nodes -keyout client01.key \
 -subj /C=RU/ST=Msk/L=Msk/O=Inc/OU=Web/CN=usr/emailAddress=usr@dm.ru \
 -out client01.csr

5. Sign client certificate request using trusted certificate (CA)
   

 # openssl ca -config ca.config -in client01.csr -out client01.crt –batch

  Preparing client certificate for transferring it to client

 # openssl pkcs12 -export -in client01.crt -inkey client01.key \
 -certfile ca.crt -out client01.p12 -passout pass:q1w2e3

2. Create Wallet
1. Open Oracle Wallet Manager
2. Press create new wallet (Wallet - > New)
3. Enter password. (standard type)
4. Create Certificate Request. (Enter CN – domain name - required) or export certificate that was created in 1.с
5. Export Certificate Request to file. Sign it in CA
6. Import Trusted Certificate
7. Import signed certificate
   
8. Check Auto_login (Wallet - > Auto-Login)
9. Save Wallet

3. Configure Oracle HTTP Server.
1. In ssl.conf (ORACLE_HOME\Apache\Apache\conf) enter pass to Wallet.

SSLWallet file:c:\oracle\product\10.2.0\db_2\MyWallet

2. In ssl.conf change port 4443 to 443 (Listener, Port, Virtual Server)
3. In file opmn.xml (ORACLE_HOME\opmn\conf) change ssl-disabled on ssl-enabled (start-mode)
4. In the end of httpd.conf (ORACLE_HOME\Apache\Apache\conf) set rule for ssl. For example:

RewriteEngine On

RewriteRule ^/pls/apex/(.*)$ https://support.gtsupport.ru:443/pls/apex/$1 [L,R]

5. Configure authentication by client certificate

SSLVerifyClient require

6. Restart Oracle HTTP Server

SSLVerifyClient require

4. Transferring certificate to user

1. You have to transfer file that was created in part 1.f
2. If user use Mozilla, than he have to press Import (Tools –> Options -> Advanced -> Encryption -> View certificates -> Import) and choose file.