Understanding APEX URL Syntax

A typical APEX URL looks as below:

http://apex.somewhere.com/pls/apex/f?p=52035:1:15424011031777:::::

It means:
"Open Application 52035 on Page 1 using Session 15424011031777"

Explanation :-

• http:// –> the protocol, can be http or https
• apex.somewhere.com -> URL of the server. Can also be localhost.
• /pls -> pls is the indicator to use the mod_plsql cartridge e.i. it indicates that you are using Oracle HTTP Server with mod_plsql. If you are using APEX Listener or Embedded PL/SQL Gateway this part is obsolete/missing.
• /apex -> apex is the database access descriptor (DAD) name. The DAD describes how HTTP Server connects to the database server so that it can fulfill an HTTP request. The default value is apex.
• /f?p= -> It is a prefix used by Oracle Application Express.
• 52035 is the application being called.
• 1 is the page within the application to be displayed.
• 15424011031777 is the session number.

However, the general syntax of APEX URL is as :

http://apex.oracle.com/pls/apex/f?p=AppId:PageId:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

We can create links between pages in our application using the above syntax.

The following describes the arguments you can pass when using f?p syntax :

	Syntax	Description
1	AppId	Indicates an application ID or the Alias of the Application.
2	PageId	Indicates a page number or the Alias of the Page.
3	Session	Identifies a session ID. You can reference a session ID to create hypertext links to other pages that maintain the same session state by passing the session number. You can reference the session ID using the syntax: Short substitution string: &SESSION. PL/SQL: V('SESSION') Bind variable: :APP_SESSION
4	Request	Sets the value of REQUEST. Each application button sets the value of REQUEST to the name of the button. This enables accept processing to reference the name of the button when a user clicks it. You can reference REQUEST using the syntax: Substitution string: &REQUEST. PL/SQL: V('REQUEST') Bind variable: :REQUEST
5	Debug	Displays application processing details. Valid values for the DEBUG flag are YES or NO. Setting this flag to YES displays details about application processing. You can reference the Debug flag using the following syntax: Short substitution string: &DEBUG. PL/SQL: V('DEBUG') Bind variable: :DEBUG
6	ClearCache	Clears the cache. This sets the value of items to null. To clear cached items on a single page, specify the numeric page number. To clear cached items on multiple pages, use a comma-separated list of page numbers. Clearing a page's cache also resets any stateful processes on the page. Individual or comma-separated values can also include collection names to be reset or the keyword RP, which resets region pagination on the requested page. The keyword APP clears cache for all pages and all application-level items in the current application and removes sort preferences for the current user. The keyword SESSION achieves the same result as the APP keyword, but clears items associated with all applications that have been used in the current session.
7	itemNames	Comma-delimited list of item names used to set session state with a URL.
8	itemValues	List of item values used to set session state within a URL. Item values cannot include colons, but can contain commas if enclosed with backslashes. To pass a comma in an item value, enclose the characters with backslashes. For example: \123,45\
9	PrinterFriendly	Determines if the page is being rendered in printer friendly mode. If PrinterFriendly is set to Yes, then the page is rendered in printer friendly mode. The value of PrinterFriendly can be used in rendering conditions to remove elements such as regions from the page to optimize printed output. You can reference the printer friendly preference by using the following syntax: V('PRINTER_FRIENDLY') When referenced, the Application Express engine will not display tabs or navigation bars, and all items will be displayed as text and not as form elements.

Wrap Text In Report Column Using CSS & HTML in Oracle APEX

Suppose we have a classic report on Oracle APEX.

Now, we needed to modify the report so that the "Responsibility" column would be wrapped text rather than truncated.

Click on 'Edit Report Columns -> Report Attribute Tab -> Edit RESPONSIBILITY column -> Column Formatting -> HTML Expression : 

We are to add below script in HTML Expression:  (Adjust the pixels for your own needs.) :
<html>

<head>
<style> 
p.test
{ 
width:500px; display:block; white-space:normal; word-wrap:break-word;
}
</style>
</head>
<body>
<p class="test"> <span> #RESPONSIBILITY#</span> </p>
</body>
</html>

(If image is not displayed properly, please click on the that image to view.)

Restrictions On Trigger

Trigger :

Triggers in Oracle are blocks of code which Oracle automatically executes based on some action or event on a table.

They are mainly used for following tasks -

1. Applying complex-integrity-constraints which is not possible through complex approach of applying constraint.
2. Performing audit-trail i.e. recording changes made by other users on a table.
3. Generating Primary-key values automatically.

 Restrictions On Trigger :

Although Triggers are very powerful objects but still they suffers from few restrictions -

1. A Trigger can't issue any TCL-command (commit, rollback, savepoint). This is because trigger is never executed on its own rather it is always fired due to execution of a DML-statement (Insert, Update, Delete). Now at the server-end both, the trigger & the triggering-event are merged as a single block i.e. Server sees both of them as a single unit of transaction.Thus trigger alone can't use COMMIT or ROLLBACK. So when at the SQL prompt, the triggering statement is committed or rolled-back automatically.
2. If the trigger from its body is calling any stored procedure or function then that function or procedure also can't use commit or rollback.
3. There are restrictions on the table which a trigger can access. But to understand these restrictions, we will have to understand two terms, called Mutating Table and Constraining Table.

A "Mutating table" in Oracle is one which is currently undergoing any kind of change. For a trigger, it is simply the table on which trigger has been created.

A "Constraining table" on the other hand is one which is serving as 'parent-table' for the 'mutating table'.

Considering the above tables, the restrictions are -

• A trigger can't access any column of the mutating-table from its body. In other words, 

1. A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
2. A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete. (A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.)

• The statements of a trigger cannot change the PRIMARY, FOREIGN, or UNIQUE KEY columns of a constraining table of the triggering statement. However, staring with Oracle 8i, there is no such restriction.

 Example :

Whenever we try to access the column of the mutating-table form trigger's body, Oracle have encountered :
ORA-04091 (table xxx is mutating. Trigger/function might not see it)

Also, please keep in mind following points :

• The trigger body cannot declare any LONG or LONG RAW variables. Also, :new and :old cannot refer to a LONG or LONG RAW column in the table for which the trigger is defined.
• Code in a trigger-body may reference and use LOB (Large Object) columns, but it may not modify the values of the columns.

Question: How many triggers can be applied to a table?

Answer: A maximum of 12 triggers can be applied to one table.

Question: Explain the uses of database trigger.

Answer: A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :

1)Audit data modifications.

2)Log events transparently.

3)Enforce complex business rules.

4)Maintain replica tables

5)Derive column values

6)Implement Complex security authorizations