Konstantin

Register Apex component using DBMS_REGISTRY

2009-07-08T12:22:00.006+03:00

Hello,

As far as you know APEX RTF Templates use a lot of TABLESPACE space. Due to it

comes the need to defragment tablespace where APEX "live". The simples way is to export APEX schema and import it again.
After this actions I found that APEX component was removed from Oracle registry (dba_registry view), and some functionality invalidated.

So, I want to show how to register APEX again.
As a SYS user execute next procedures:

//This procedure emulate APEX v3.2 loading
exec dbms_registry.loading('APEX','Oracle Application Express','validate_apex', 'APEX_030200');

//Save APEX version
exec dbms_registry.loaded('APEX', '3.2.0.00.27');

//Make APEX to be a valid component
exec dbms_registry.valid('APEX');

//Add additional schemes to registry
exec dbms_registry.update_schema_list('APEX', dbms_registry.schema_list_t('FLOWS_FILES'));

That's it!

Undocumented Oracle Application Express Text Messages

2008-06-08T18:34:00.003+03:00

Hi,

A lot of developers create some JavaScript function, for translating messages, or make their own notifications, object names, error handling etc., instead of using regular APEX messages. That’s why I’d like to post some undocumented Oracle Application Express Text Messages.

Name	Message Text	Place
INVALID_CREDENTIALS	Invalid Login Credentials	Login Notification
WWV_FLOW_ITEM_HELP.NO_HELP_EXISTS	No help exists for this item.	Popup window for optional Label with help
LAYOUT.T_ALT_HELP	Help	Popup window for optional Label with help
CALENDAR_TYPE	Calendar	Calendar Icon Hint
WWV_FLOW_UTILITIES.SHUTTLE_RESET	Reset	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_MOVE_ALL	Move All	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_MOVE	Move	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_REMOVE	Remove	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_REMOVE_ALL	Remove All	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_TOP	Top	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_UP	Up	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_DOWN	Down	Shuttle Reset Hint
WWV_FLOW_UTILITIES.SHUTTLE_BOTTOM	Bottom	Shuttle Reset Hint
FLASH_CHART.LOADING_DATA	Loading data...	Flash Chart Load Window
FLASH_CHART.WAITING	Loading data. Please wait.	Flash Chart Load Window

For more information look APEX View:

flows_030100.wwv_flow_messages$

Breaking the 32 character limit using OWA_TEXT

2008-02-18T10:47:00.003+02:00

In this post discussed how to display text, from database into HTML Region, that has more than 32000 characters length.

There were a lot of ways. I think that the decision below is the most simple and clear.
For this purpose I use owa_text.multi_line data type.

This data type is a PL/SQL record that holds large amounts of text. The rows field, of type OWA_TEXT.VC_ARR DATA TYPE, contains the text data in the record.

TYPE multi_line IS RECORD (
rows vc_arr,
num_rows INTEGER,
partial_row BOOLEAN);

The function below, convert clob into owa_text.multi_line data type:

create or replace function clob_to_multi_line(v_clob clob) return owa_text.multi_line is
res owa_text.multi_line;
r_cnt integer;
rem integer;
b_pos integer;
b_siz integer;
begin
r_cnt := trunc(dbms_lob.getlength(v_clob) / 32767);
rem := mod(dbms_lob.getlength(v_clob), 32767);
b_pos := 1;
for idx in 1..r_cnt loop
b_siz := 32767;
dbms_lob.read(v_clob, b_siz, b_pos, res.rows(idx));
b_pos := b_pos + b_siz;
end loop;
res.num_rows := r_cnt;
if (rem > 0) then
dbms_lob.read(v_clob, rem, b_pos, res.rows(r_cnt + 1));
res.num_rows := res.num_rows + 1;
end if;
res.partial_row := false;
return res;
end clob_to_multi_line;

An example source of PL/SQL Dynamic Content Region:

declare
c clob;
begin
select clob_column into c from table_of_clobs
where id=1594;
OWA_TEXT.PRINT_MULTI(clob_to_multi_line(c));
end;

That’s it.

Zapatec AJAX tree in APEX

2008-01-22T11:54:00.000+02:00

In this post, I would like to demonstrate the way of using Zapatec AJAX tree in Apex.
From http://www.zapatec.com/ I downloaded the DHTML tree files.
After this, I applied some of them into my application. I decided to store them in my database, for maintainability.
There is the list of files that I used:

Java Script files

.. \zptree\src\tree.js
.. \utils\zapatec.js

CSS file

.. \zptree\themes\kde1.css
.. \zptree\themes\default.css
.. \zptree\themes\empty.css
.. \website\css\zpcal.css
.. \website\css\template.css

Images

Tree icons

.. \zptree\themes\kde1\folder_open.png
.. \zptree\themes\kde1\folder.png
.. \zptree\themes\kde1\document.png

Tree structure

.. \zptree\themes\img\fetching.gif
.. \zptree\themes\img\lines-b.gif
.. \zptree\themes\img\lines-c.gif
.. \zptree\themes\img\lines-s.gif
.. \zptree\themes\img\lines-t.gif
.. \zptree\themes\img\lines-v.gif
.. \zptree\themes\img\minus.gif
.. \zptree\themes\img\plus.gif
.. \zptree\themes\img\zpempty.gif

I imported it using Shred Documents -> Static Files -> Create
Next step, was adding css and javascript files to HTML Header of my page:
After that I changed all paths, that is used in css and javascripts to APEX ‘paths’, for example:
Original kde1.css

div.tree-item td.customIcon {
background: url("kde1/document.png") no-repeat 0 50%;
}

div.tree-item-expanded td.customIcon {
background: url("kde1/folder_open.png") no-repeat 0 50%;
}

div.tree-item-collapsed td.customIcon {
background: url("kde1/folder.png") no-repeat 0 50%;
}

APEX compatible:

div.tree-item td.customIcon {
background: url("wwv_flow_file_mgr.get_file?p_security_group_id=948527760686885&p_flow_id=100&p_fname=document.png") no-repeat 0 50%;
}

div.tree-item-expanded td.customIcon {
background: url("wwv_flow_file_mgr.get_file?p_security_group_id=948527760686885&p_flow_id=100&p_fname=folder_open.png") no-repeat 0 50%;
}

div.tree-item-collapsed td.customIcon {
background: url("wwv_flow_file_mgr.get_file?p_security_group_id=948527760686885&p_flow_id=100&p_fname=folder.png") no-repeat 0 50%;
}

After that I was ready to create a dynamical content for my tree. There is the needed structure for tree content:

<ul id="tree_defaults">
<li>Group 1
<ul>
<li>Sub Group 1
<ul>
<li>Document 1</li>
<li>Document 2</li>
</ul>
</li>
<li>Sub Group 2
<ul>
<li>Document 3</li>
</ul>
</li>
</ul>
</li>
</ul>

</script>

In my case, I have to create content from two tables. One is the table of ‘groups’, another is the table of ‘documents’.

There is the source code of tree generating procedure:

function enum_documnet_tree (v_session integer) return varchar2
is
res varchar2(32000);

procedure add_str(v_str varchar2) is
begin
res := res || v_str || chr(10);
end;

procedure add_group(v_nid integer) is
v_name varchar2(256);
begin
select cname into v_name from group_table where nid = v_nid;
add_str('<li>'||v_name);
add_str('<ul>');
for rec in (select nid, cname from group_table
where nparent_group = v_nid
order by 2) loop
add_group(rec.nid);
end loop;
for o_rec in (select o.cname, o.nid from documents o where ngroup = v_nid) loop
add_str('<li><a href="f?p=100:19:'||v_session||'::NO::P19_DOCUMENT_ID:'||o_rec.nid||'">'||o_rec.cname||'</a></li>');
end loop;
add_str('</ul>');
add_str('</li>');
end;
begin
res := '<ul id="tree_defaults">';

for m_rec in (select nid from group_table where nparent _group is null) loop
add_group(m_rec.nid);
end loop;
for o_rec in (select o.cname, o.nid from documents o where ngroup is null) loop
add_str('<li><a href="f?p=100:19:'||v_session||'::NO::P19_DOCUMENT_ID:'||o_rec.nid||'">'||o_rec.cname||'</a></li>');
end loop;
add_str('</ul>');
add_str('<script type="text/javascript">
var tree_defaults = new Zapatec.Tree({
tree: "tree_defaults",
defaultIcons: "customIcon",
saveState: true,
saveId: "saveState",
expandOnLabelClick: true,
highlightSelectedNode: true
});
</script>');
return res;
end;

Finaly I created a hidden item :P19_TREE. HTML region with source:
htp.p(:P19_TREE);

And process PL/SQL anonymous block (On Load – Before Header):
:P19_TREE:= enum_documnet_tree(:SESSION);

That’s it!

P.S. If you want to use this AJAX component, you have to read a Zapatec tree license agreement on http://www.zapatec.com/website/main/products/prod3/license.jsp

Changing Scheme in Oracle Application Express (APEX) inside application

2007-10-25T18:23:00.000+03:00

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

2007-07-01T12:31:00.000+03:00

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.

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

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

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

4. Voting

Press on the Vote link, at the main page.
Set your marks.
You can also add a description (optional)
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

2007-06-22T15:54:00.001+03:00

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

2007-06-20T20:31:00.000+03:00

Creating your own self-signed trusted certificate. And creating client certificate

First of all you have to download openssl library (http://www.openssl.org/)
Install it. (I installed on UNIX)

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

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)

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

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

Create Wallet

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

Configure Oracle HTTP Server.

In ssl.conf (ORACLE_HOME\Apache\Apache\conf) enter pass to Wallet.

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

In ssl.conf change port 4443 to 443 (Listener, Port, Virtual Server)
In file opmn.xml (ORACLE_HOME\opmn\conf) change ssl-disabled on ssl-enabled (start-mode)
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]

Configure authentication by client certificate

SSLVerifyClient require

Restart Oracle HTTP Server

SSLVerifyClient require

Transferring certificate to user

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