Mar 15 2009

Pop-up when hovering over tabular column

Category: APEXittichai @ 1:44 pm

I just answered APEX forum on the pop-up when hovering over tabular column. It is a quick solution.

1. Create Javascript functions as follows. The description of each function is self-explanatory in its name.

<script language="JavaScript" type="text/javascript">
<!--
function findPosX(obj)  // Get full right offset
{
    var curleft = 0;
    if (obj.offsetParent) {
        while (obj.offsetParent) {
            curleft += obj.offsetLeft;
            obj = obj.offsetParent;
        }
    }
    else if (obj.x)
        curleft += obj.x;

    return curleft;
}

function findPosY(obj)  // Get full top offset
{
    var curtop = 0;
    if (obj.offsetParent) {
        while (obj.offsetParent) {
            curtop += obj.offsetTop;
            obj = obj.offsetParent;
        }
    }
    else if (obj.y)
        curtop += obj.y;

    return curtop;
}

function ShowPopup(hv_item)
{
    var t1;  // First part of Text formatting
    var t2;  // Latter part of Text formatting
    dp = document.getElementById("DisplayPopup");

    t1 = "<table><tr><td width=200 align='center' bgcolor='gray'><font color='white'>";
    t2 = "</font></td></tr>";

    dp.innerHTML = t1 + hv_item.value + t2;

    // Set position of hover-over popup
    dp.style.top = findPosY(hv_item) - 10;
    dp.style.left = findPosX(hv_item) + 20;

    // Set popup to visible
    dp.style.visibility = "Visible";
}

function HidePopup()
{
    dp = document.getElementById("DisplayPopup");
    dp.style.visibility = "Hidden";
}
//-->

</script>

<div ID="DisplayPopup" style="visibility:hidden; position:absolute;"></div>

Note that above includes a DIV tag named DisplayPopup which will be used to store the pop-up text.

You can personalize the pop-up in the ShowPopup function. This includes font’s color, background color, pop-up width as well as X-Y offset position.

2. Call ShowPopup and HidePopup by onMouseOver and onMouseOut Javascript events respectively from the Element Attributes of the column you want to have pop-up shown.

In my sample here, I added the following Javascript events to both Ename and Job columns.

onMouseOver="ShowPopup(this);" onMouseOut="HidePopup();"

Tags: , ,


Feb 28 2009

Read-only fields on APEX tabular form except on a new row

Category: APEXittichai @ 8:11 pm

I was asked by our APEX developer whether or not some fields of the tabular form created by wizard, except on a new row when “Add Row” is clicked, can be made read-only. This is a part of business requirements that the existing parameters should not be modified by end users.

I started searching for the existing solutions and found that most call for using the manually-created tabular form. However, one comment from this forum post suggested Javascript to perform task. Even though there was no code given, I’d like explore this option.

With information from Denes Kubieck’s Demo application and Patrick Wolf’s post on “Which Tabular Form Column is mapped to which Apex_Application.g_fxx array?”, this is the sample of my simple solution which uses Javascript to make fields read-only.

Please note that in this sample application, there are other things I included to show our developers. I added a page-level validation to ensure that there is no duplication in a selected field entry. Even though there is an unique constraint on this column, by using validation, it is more informative to users. The pop-up help reminds users that the field is protected and no duplication is allowed.

Tags: , ,


Oct 18 2008

APEX – Requests to execute DML operations through "Automatic Row Processing (DML)" process

Category: APEXittichai @ 11:59 pm

As a part of the button’s properties, an execution of “Automatic Row Processing (DML)” process either INSERT, UPDATE or DELETE (assuming those operations are allowed) can be controlled by selecting an appropriate Database Action as shown below.

1

Many times, instead through a use of button, page submission is done by calling JavaScript “onSubmit” function. How will we select a DML’s database actions through this approach?

APEX provides a way. The DML process will execute a requested database’s action based on the request value containing one of the listed values here -

2

By default, the request value of a button is the button name itself. But for onSubmit function, the request value is what in the passing value. For example, onSubmit(‘SAVE’) or onSubmit(‘APPLY CHANGES’) will execute database’s UPDATE operation.

The one I found the most useful is APPLY%CHANGES%. The wildcard allows the request value to be flexible.

I could use doSubmit(‘APPLY_CHANGES_D1′) and doSubmit(‘APPLY_CHANGES_U1′) to cause the update operation of all items of the current page. At the same time, it allows me to perform other different operations based on request values from using conditional processing.

The application I’m working on has two created-on-the-fly buttons in the “Display Only” item. The VIEW FILE link and Delete button are visible when file is stored in table. If not, only Upload button is visible.

declare
   v_link  varchar2(300);
begin
   if :P 8_UPLOAD_DOC_ID <> '0' then
	select '<td><a href="#OWNER#.pr_download_file?p_file=' || :P 8_UPLOAD_DOC_ID || '">VIEW FILE</a></td> <td><a href="javascript:confirmDelete(htmldb_delete_message,''APPLY_CHANGES_D1'');"> <img border="0" src="#WORKSPACE_IMAGES#delete.jpg" id="img_delete1"</a/></a></td>'
        into v_link
        from PRODUCT
	   where ID = :P 8_PRODUCT_ID;

   else
        v_link := '<td></td> <td><a href="javascript:doSubmit(''APPLY_CHANGES_U1'')"><img border="0" src="#WORKSPACE_IMAGES#upload.jpg" id="img_upload1"/></a></td>';

   end if;

   return v_link;

end;

When either button is clicked, all item information on the form will be saved (through DML’s update process). If Upload button is pressed, then page will be re-directed to the upload page. If Delete button is pressed, after confirmation, it will remove the actual file which is stored in a different table.

Tags: ,


Aug 16 2008

Oracle APEX displays report’s column heading 90 degree rotation

Category: APEXittichai @ 4:22 am

One of the cool things about Oracle APEX is ability to embed HTML tags into everything you could display. This includes simple HTML tags like

Add a break

Employee<br>Name

Add color

<font color="#FF0000">Employee Name</font>

1

Or more complex one like -

Rotate 90 degree vertically (work with IE only)

<div style="writing-mode: tb-rl">Employee Name</div>

Tags:


Nov 30 2007

APEX to upload a text file and write into a table

Category: APEXittichai @ 9:32 pm

One of the web development projects using Oracle APEX I’m working on is to upload the content of a text file in csv format into database. By default, all files using the “File Browse..” element will be uploaded to the “wwv_flow_files” table which has structure as follows:

SQL> desc wwv_flow_files
Name         Type           Nullable Default Comments
------------ -------------- -------- ------- --------
ID           NUMBER
FLOW_ID      NUMBER
NAME         VARCHAR2(90)
FILENAME     VARCHAR2(400)  Y
TITLE        VARCHAR2(255)  Y
MIME_TYPE    VARCHAR2(48)   Y
DOC_SIZE     NUMBER         Y
DAD_CHARSET  VARCHAR2(128)  Y
CREATED_BY   VARCHAR2(255)  Y
CREATED_ON   DATE           Y
UPDATED_BY   VARCHAR2(255)  Y
UPDATED_ON   DATE           Y
LAST_UPDATED DATE           Y
CONTENT_TYPE VARCHAR2(128)  Y
BLOB_CONTENT BLOB           Y
LANGUAGE     VARCHAR2(30)   Y
DESCRIPTION  VARCHAR2(4000) Y
FILE_TYPE    VARCHAR2(255)  Y
FILE_CHARSET VARCHAR2(128)  Y

Even though the text file contains character data, the uploaded file content will be stored in the “binary format” in a BLOB column named BLOB_CONTENT. Due to nature of BLOB, to read character out, the conversion using “chr” function is required. Please see a good article about this concept and conversion by Natalka Roshak on the orafaq at http://www.orafaq.com/node/895.

I modified the sample script provided in above article to suite my need – reading each line of the text file and insert into a target table.

DECLARE
v_blob_data       BLOB;
v_blob_len        NUMBER;
v_position        NUMBER;
v_raw_chunk       RAW(10000);
v_char      CHAR(1);
c_chunk_len   number       := 1;
v_line        VARCHAR2 (32767)        := NULL;
v_data_array      wwv_flow_global.vc_arr2;
BEGIN
-- Read data from wwv_flow_files
select blob_content into v_blob_data
from wwv_flow_files where name = 'F29800/Data_CSV.csv';

v_blob_len := dbms_lob.getlength(v_blob_data);
v_position := 1;

-- Read and convert binary to char
WHILE ( v_position <= v_blob_len ) LOOP
 v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);
 v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));
 v_line := v_line || v_char;
 v_position := v_position + c_chunk_len;
-- When a whole line is retrieved
 IF v_char = CHR(10) THEN
-- Convert comma to : to use wwv_flow_utilities
   v_line := REPLACE (v_line, ',', ':');
-- Convert each column separated by : into array of data
   v_data_array := wwv_flow_utilities.string_to_table (v_line);
-- Insert data into target table
   EXECUTE IMMEDIATE 'insert into TABLE_X (v1, v2, v3, v4)
    values (:1,:2,:3,:4)'
    USING
      v_data_array(1),
      v_data_array(2),
      v_data_array(3),
      v_data_array(4);
-- Clear out
   v_line := NULL;
  END IF;
 END LOOP;
END;
/

Tags:


Nov 14 2007

Oracle APEX in 11g Installation

Category: 11g,APEX,SQL Developerittichai @ 8:55 pm

Today I installed Oracle 11g (11.1.0.6) on my machine. I did not realize that Oracle APEX is a part of the standard database components.

So after the 11g installation, I just follow simple steps (shown later below) for the post-installation. In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.

To configure the embedded PL/SQL gateway:

1. Go to the $ORACLE_HOME/apex directory.

2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.

SYS AS SYSDBA@db11r1> @apxconf

PORT
----------8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.

Enter a password for the ADMIN user              []admin_password
Enter a port for the XDB HTTP listener [      8080]
...changing HTTP Port

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

3. Unlock the ANONYMOUS account.

SYS AS SYSDBA@db11r1> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

User altered.

4. Enable Oracle XML DB HTTP server

SYS AS SYSDBA@db11r1> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SYS AS SYSDBA@db11r1> COMMIT;

Commit complete.

5. We’re now ready to access APEX.

http://host:port/apex

http://host:port/apex/apex_admin — for admin page

Port in this case is 8080 which is the default.

Note that the format of URL is a little bit different from when using HTTP server with mod_plsql -

http://host:port/pls/apex

http://host:port/pls/apex/apex_admin — for admin page

Also the SQL Developer 1.1.3 is included under “sqldeveloper” directory of ORACLE HOME. So just double-click at sqldeveloper.exe to launch application.


Tags: ,


« Previous Page