(YABAOAE) Yet Another Blog About Oracle Application Express: Oracle Database Resource Manager and Oracle Application Express

(YABAOAE) Yet Another Blog About Oracle Application Express: Oracle Database Resource Manager and Oracle Application Express:  dbms_resource_manager.create_plan_directive

Change color of report cell based on cell value

This is from the following post: http://forums.oracle.com/forums/thread.jsp?forum=137&thread=230789&message=651450&q=666f726d61742063656c6c#651450


select serial,
'<div style="width:100%;height:100%;background:'||
case when purchase_price > 5000 then 'red' else 'green' end
|| '">'||purchase_price||'</div>' purchase_price,
brand
from hardware

Create users programmatically

See: Htmldb forum


In a nutshell:
select :WORKSPACE_ID from dual;

...in the SQL Workshop's SQL Command Processor to determine your workspaces workspace_id (also known as a security_group_id internally). assuming that query came back with a value of 12345, you could then run a block like...


BEGIN
wwv_flow_api.set_security_group_id(p_security_group_id=>12345);

wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'regular_user',
p_email_address => 'myemail@mydomain.com',
p_web_password => 'regular1') ;

wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'developer_user',
p_email_address => 'myemail@mydo.com',
p_web_password => 'dev1',
p_developer_privs => 'ADMIN') ;

end;
/

Highlight rows in report based on column values.

You will have to create a report template for this one.
Let's say you have a report based on "SELECT * FROM emp" and you want to highlight the rows where the salary is greater than 2000.
Create a report template (i usually just copy an existing one and modify it.)
For Row Template 1, specify the look for your highlighted row: <td bgcolor="yellow" #ALIGNMENT#><b>#COLUMN_VALUE#</b></td>
Here i made the background yellow and also made the text bold.
In Row Template 1 Expression enter the PL/SQL expression that determines the condition for using row template 1:
#SAL# > 2000
(Note that column substitution variables are enclosed by '#' -- see side bar on the right side of report template wizard.)
Now, in Row Template 2 specify the look for your un-highlighted rows (i just left out the bgcolor and bold tags):
<td #ALIGNMENT#>#COLUMN_VALUE#</td>
Now enter the condition for displaying the second template:
#SAL# <= 2000.

In your report page, navigate to the Report Attributes tab and select your newly created report template in the 'Layout and Pagination' section.

That's it.

Dynamically highlight rows in report.

To dynamically highlight rows in a htmldb report you need to use javascript. I found a nice script to do this at www.dhtmlshock.com (thanks Shivaji.)



1.
Create or alter a report template. In the 'Before Each Row' section paste:

<tr onMouseOver="cOn(this);" onMouseOut="cOut(this);">

In the 'After Each Row' section paste:

</tr>

2.
In the report page definition click on the page attributes and navigate to the HTML Header section. Paste the following script into this section:

-----------------------------------

<script language="JavaScript1.2">

/*
To comply with copyright please download the script from here: www.dhtmlshock.com
*/

</script>

-----------------------------------

3.
In the report region navigate to the 'Layout and Pagination' section and select the report template you altered above.

4.
Now the report row will get highlighted when you mouse over. If you want to change colors, edit the color attributes in the javascript.

Update primary key with sequence number

To dynamically set a primary key column to a sequence number, add a page process that fires 'On Submit - After Computations and Validations' before the 'Process Row of [table]' process.
Use a PL/SQL statement that selects the sequence.nextval into the primary key column:

if :P3_EMPNO is null then
select ct_id_seq.nextval into :P3_EMPNO from dual;
end if;

Make sure to use the if statement. If you don't then you will encounter No-data-found errors.

Pre-Update trigger

A trigger that functions similar to the pre-update trigger in Oracle forms is created by creating a page process that fires On Submit-Before validations and computations:

-- This example populates the country_code field from a database table
-- based on the value passed in from the state_code field.
BEGIN
IF :P1036_STATE_CODE IS NOT NULL AND :P1036_COUNTRY_CODE IS NULL THEN
select country_code
into :P1036_COUNTRY_CODE
from ct_state_code
where state_code = :P1036_STATE_CODE;
END IF;
END;

Change the case of a field to upper when a page is submitted:

Change the case of a field to upper when a page is submitted:
Create a page process: Processing > Processes
On Submit and Before Computation
Process: :P1036_STATE_CODE := UPPER(:P1036_STATE_CODE)

Create a hyperlink

Create a hyperlink to another page and pass values:

Create a region item as ‘Display as Text (does not save state)’
In the source put:
a href="f?p=&APP_ID.:1036:&SESSION.::::P1036_CT_ID:&P1032_CT_ID

Note that variables are preceded by an ampersand (&) and post ceded by a period (.).

HTMLDB Blog

I have created this blog for people to help with Oracle's (TM) HTMLDB database development tool.
I encourage people to submit ideas on how to solve development issues in order to share them with others.