Mar 07 2010

Book Review: Oracle SQL Developer 2.1 by Sue Harper

Category: APEX,Books,SQL Developer,Tool — ittichai @ 6:31 pm

Oracle SQL Developer 2.1 by Sue Harper
I must admit that I rarely use the Oracle SQL Developer because I’m using SQL*Plus, Toad from Quest and PL/SQL Developer from Allround Automations as primary tools for database administration and PL/SQL programming. They’re already providing all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it, but I always find lame excuses not to. One of the excuses is that I thought this is just another new tool doing the same things other tools already could do. We’ll find out at the end of this post that I was wrong. Please read on.

Few weeks ago, I’ve received a non-compensated book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this opportunity to explore this tool by reading through this book. No more procrastination! I’m approaching this book as an eager learner who would like to know more the tool and plan for a long-term use.

Let’s get started.

This book is very well organized introducing Oracle SQL Developer from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concepts.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on the installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to connect and make queries to database. It ends with walking you through the navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e.g., data highlighting or filtering are interesting. It goes on explaining some of Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with demonstrating how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in detail with tool’s main interface, SQL Worksheet. It starts with comparing Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features such as drag-and-drop query builder, formatting code, code completion, etc., though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But if you look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries thus saving your time. Samples are as follow:

  • Table with Unindexes Foreign Keys (under Quality Assurance)
  • Search Source Code (under Data Dictionary Reports > PLSQL)
  • ASH and AWR Reports (under Data Dictionary Reports)
  • Grants and Privileges (under Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL code. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working with other tools before. But the book explains this topic very well for those you’ve not experienced working with them before. The feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very cool. The chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard database tuning tools such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler feature which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files
This is one of strengths of Oracle SQL Developer over others because it integrates with open source version control system. I’m really glad that author covers this topic in detail. The version control integration is very nice to have if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work in team development with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer can perform data export and import in various formats including CSV, Fixed, HTML, DML statements, and SQL Loader. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered to be topic-specific. This means that you can choose to read only what interests you.

Chapter 9 -  Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because Oracle SQL Developer can also connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendously beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into detail of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler within SQL Developer.

(More information about Oracle SQL Developer Data Modeler can be found on its site.)

Chapter 11 – Extending SQL Developer
Personally I think this feature sets this tool apart from others. With capability of supporting XML and Java extensions, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 -  Working with Application Express
As an APEX developer, the integration with APEX in Oracle SQL developer is a hidden gem. Now all APEX objects can be easily viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains clearly on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning application’s SQL and PL/SQL codes which are visible through the tool becomes much easier. One of the nice features is an ease of refracting PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing us how to manage applications including importing and exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of this tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for data migration. This includes migration repository preparation and database connection.

Summary
After reading through this book and following most of samples, I’m impressed with varieties of unique features (such as integration with APEX, version control, Data Modeler, etc.) this tool offers. As a matter of fact, I’m already using it in our current APEX development project, and plan to extend its usage more in other areas. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. The book covers some basic concepts filling up the gap for those who are new to accessing Oracle database. But in general, I agree with author that those who are already familiar with Oracle database concepts will get more out of this book.

Thanks to Packt Publishing for book review offer.

References:

Oracle SQL Developer 2.1 by Sue Harper on Packt Publishing site

Oracle SQL Developer page on Oracle site

I must admit that I rarely use the Oracle SQL Developer because the primary tools – Quest Toad and Automation PL/SQL Developer, I’m using for database administration and PL/SQL programming seem to provide all functionalities I need. I have nothing against Oracle SQL Developer. Actually it is quite opposite. I really want to try it out. But I always find lame excuses not to start.

A month ago, I’ve received a book review offer from Packt Publishing on Oracle SQL developer 2.1 by Sue Harper, who is currently product manager for Oracle SQL Developer. I’m really excited about this great opportunity to explore this tool by reading through this book. I’m approaching this book as an eager learner who would like to know the tool better for a long-term use.

Let’s get started.

This book is very well organized introducing the tool from installation to having sufficient information to use the tool right away after only few chapters. Personally I feel you can skip them if you’ve been using other database tools before. The tool is intuitive enough. Having said that if you’re a novice to Oracle database concept, the first four chapters are excellent resources for you to get acquainted with some basic concept.

Chapter 1 – Getting Started with SQL Developer
The first chapter focuses mainly on installation, initial configuration, migration from previous version and updates. Interesting information is with the XML configuration files. The chapter continues with showing how to make the connection and making queries to database. It ends with walking you through navigation of the tool.

Chapter 2 – Browsing and Editing Database Objects and Data
This chapter extends from the first showing how to browse database objects. Many tips on data display, e,g, data highlighting or filtering are interesting. It goes on explaining some Oracle database features such as Flashback and Recycle Bin which may be helpful for non-DBAs. The chapter ends with showing how to create different types of database objects.

Chapter 3 – Working with the SQL Worksheet
This chapter goes in details with tool’s main interface, SQL Worksheet. It starts with compare Worksheet capabilities with more commonly-known SQL tools such as SQL*Plus. I think it is a good idea to show many command-line folks including myself to see that this tool is capable of doing the same things and even more. It guides you through other nifty features, for example, drag-and-drop query builder, formatting code, code completion, etc. even though they are available in other tools as well. One of interesting features is the Code Snippets which are commonly-used codes that can be quickly added.

Chapter 4 – The Power of SQL Reports
Most of the canned/shipped reports may initially look not that useful. But you can look at them closely, some categories/reports are very interesting because they provide very useful information without writing custom queries, thus saving your time. Sample are as follow:

Table with Unindexes Foreign Keys (Quality Assurance)

Search Source Code (from Data Dictionary Reports > PLSQL)

ASH and AWR Reports (from Data Dictionary Reports)

Grants and Privileges (from Data Dictionary Reports > Security)

The user-defined report section is very helpful as well if you plan to write your own custom reports.

Chapters 5 until 8 are intended for developers because they talk about working with database objects or features for development process.

Chapter 5 – Working with PL/SQL
This section explains how to work with PL/SQL codes. It includes working with triggers, functions, procedures and packages. It might not be appealing for those who have been working in other tools before. But the book explains well for those you’ve not experienced working with those objects before. Feature like Refractoring Code, which is to restructure code for performance improvement without impacting its original behavior, is very interesting. This chapter ends with explaining how to use the tool for code debugging.

Chapter 6 – SQL and PL/SQL Tuning Tools
This chapter talks about the ability of the tool to use with the standard tuning tool such as use of EXPLAIN PLAN, Auto Trace and other canned report for Top SQLs. The book also highlights new 11g’s PL/SQL Hierarchical Profiler which records not only the time spent in each sub program, but also number of calls to the subprogram.

Chapter 7 – Managing Files

This is one of strengths of the tools over others because it integrates with open source version control system. I’m really glad the author covers this in details. This version control integration is very nice if your organization does not have a dedicated source control system. This chapter will show you how to check in/out your code and work as a team with code comparison and merging.

Chapter 8 – Importing, Exporting and Working with Data
This chapter may be beneficial to many developers who need to perform data movement. SQL Developer provides many ways to perform data export and import in a various formats including CSV, Fixed, HTML, DML statements, SQL Loader, etc. Using wizard such as database copy allows to move data without using database utilities like imp/exp or data pump.

The remaining sections are considered topic-specific. This means that you can choose to read only what interests you.

Chapter 9 -  Database Connections and JDBC Drivers
Great section hightlights the advantages of this tool because it also can connect to non-Oracle databases including MySQL, Sybase, DB2, Teradata, Microsoft Access via 3rd-party JDBC drivers. This is tremendous beneficial when there is a need to migrate data from the 3rd-party database to Oracle.

Chapter 10 – Introducing SQL Developer Data Modeler
Even though Oracle SQL Developer Data Modeler is a priced independent product (SQL Developer has a SQL Data Model Viewer extension), this book showcases Data Modeler tool by going into details of its features including connecting to database, importing dictionary, working with model, creating ERD, and so on. The chapter ends with showing the integration of the Data Modeler with SQL Developer.

(more information about SQL Developer Data Modeler at http://www.oracle.com/technology/software/products/sql/datamodeler.html)

Chapter 11 – Extending SQL Developer
Personally I feel this feature might set this tool apart from others. With capability of supporting XML and Java extension, you can create custom features to plug-in to the tool and share them with others. This chapter shows some good samples of using XML extensions in the tool.

Chapter 12 -  Working with Application Express
As a APEX developer, the integration with APEX in SQL developer is a gem. Now all APEX objects can be viewed at our finger tips. There is no need to jump into many pages back and forth. This book explains well on how to use SQL Developer to work with APEX objects including LOVs, Pages, Items, etc. Tuning SQL and PL/SQ which are visible through the tool becomes a lot easier. One of the nice features is an ease to refract PL/SQL code (e.g., from anonymous block) to a compiled procedure which is much more efficient. The chapter ends with showing  to manage applications including importing/exporting applications.

Chapter 13 – Working with SQL Developer Migration
This chapter extends its feature of the tool connecting to non-Oracle database for data migration purpose. The Oracle SQL Developer Migration is an integrated feature of Oracle SQL Developer. The book shows technical aspect of using the tool for migration including migration repository preparation and database connection.

After reading through this book and following most of samples, I’m impressed with varieties of features (APEX integration, version control, Data Modeler, etc.) this tool offers. The flow and layout of this book really helps me understand and appreciate SQL Developer tool. Even though some basic concept sections are not brief, those who are new to accessing Oracle database will benefit from those sections. In general, I agree with author that you will get more out of this book if you’re already familiar with Oracle database concepts.

Detail about Oracle SQL Developer 2.1 by Sue Harper

http://www.packtpub.com/oracle-sql-developer-2-1/book

Oracle SQL Developer page on Oracle OTN site http://www.oracle.com/technology/software/products/sql/index.html

Thanks to Packt Publishing for book review offer.

www.PacktPub.com

===========================================================

http://www.oracleapplicationexpress.com/tutorials/65-jquery-datepicker

I remembered not quite a long time ago, in order to find out any technical information, it seems like you can to go through a bunch of technical books.

You must subscribe to

But now many technical blog sites pop

This is to programmatically generate a CSV file when a button is clicked
The custom export to CSV

http://spendolini.blogspot.com/2006/04/custom-export-to-csv.html

Session timeout in release 3.1

http://andrew.tulley.co.uk/?p=50

jQuery Calendar Date Picker

http://jqueryui.com/demos/datepicker/

Good document on its configuration

http://docs.jquery.com/UI/Datepicker

How to replace the default APEX calendar with jQuery calendar…

http://roelhartman.blogspot.com/2008/12/how-to-replace-default-apex-calendar.html

jQuery Masked Input
This is a masked input plugin for the jQuery javascript library. It allows a user to more easily enter fixed width input where you would like them to enter the data in a certain format (dates,phone numbers, etc). I

http://digitalbush.com/projects/masked-input-plugin/

jQuery Impromptu is an extention to help provide a more pleasant way to spontaneously prompt a user for input. More or less this is a great replacement for an alert, prompt, and confirm. Not only does it replace these but it also allows for creating forms within these controls. This is not intended to be a modal replacement, just a quick tool to prompt user input in a fashionable way.

jQuery Impromptu

http://trentrichardson.com/Impromptu/index.php

—————————-

Oracle Universal Installer (OUI) did not start when installing OAS 10.1.3.1 on Windows 2003

Last week we installed the OAS 10.1.3.1 on multiple Windows 2003 servers on the VM farm. All went well except the last one. When double-clicking on the setup.exe file, the system verification window popped up. Next I expected the Oracle Universal Installer (OUI) window, but nothing happened. The same installation files and procedure have been used on different Windows systems and have worked fine before. The installation log doesn’t seem to be much help either. We’ve tried all tricks including using a local administrative account, rebooting, etc., but none worked.

After looking through the Oracle support site (using Internet Explorer because the some contents don’t display properly on Fire Fox 3.6), I found this solution in the document ID 308705.1 – OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 which says that the root cause is because the Application Experience Lookup” Service is not started. Once started, the installation proceeds without any issues. The strange thing is that those servers we’ve previously installed it successfully did not have this service up either.

(

OUI Does Not Start After Running Setup.exe Installing OAS On Windows 2003 [ID 308705.1]
Modified 04-JUN-2009     Type PROBLEM     Status PUBLISHED

In this Document
 Symptoms
Cause
Solution

Applies to:

Oracle Application Server 10g Enterprise Edition – Version: 9.0.4.0.0 to 10.1.2.3.0
Microsoft Windows Server 2003
Microsoft Windows Server 2003 R2 (32-bit)
Checked for relevance on 04-Jun-2009

Symptoms

When attempting to install Oracle Application Server 10g on Windows 2003, the setup.exe appears to quit even before launching the Oracle Universal Installer (OUI).

Cause

The Oracle Universal Installer (OUI) is not able to start because the “Application Experience Lookup” Service is not started.

Solution

Start the “Application Experience Lookup Service” and then relaunch the Oracle Universal Installer (OUI)

<< OLE Object: Picture (Device Independent Bitmap) >> Related

Products

· Middleware > Application Servers > Oracle Application Server > Oracle Fusion Middleware

Keywords

OAS; ORACLE UNIVERSAL INSTALLER; SETUP.EXE; OUI

Tags: , , , , ,

Oct 22 2009

Oracle Application Express (APEX) Certification Exam

Category: APEX — ittichai @ 7:24 am

Oracle is working toward having the first certification exam for Oracle APEX. This “Oracle Application Express Developer Certified Expert” pilot program is intended for intermediate and advanced APEX developers. You will have to indicate the relevant APEX trainings you’ve completed, years of APEX experiences and, of course, the agreement to provide feedback in order to participate. You can see details about this from Joel Kallman’s blog.

I think this is a good thing for APEX community because it shows Oracle’s commitment to this tool.

BTW, I’m very curious about how the tests will be conducted. Personally I think there should be more of actual development hands-on works rather than doing multiple choices.

Tags: , , ,

Oct 10 2009

APEX Listener Released as an Early Adopters

Category: APEX — ittichai @ 8:21 am

Oracle APEX Listener has been released as an early adopters. It is an alternative to Oracle HTTP Server (OHS) and Oracle Embedded PL/SQL Gateway (EPG) as web server to APEX database engine. You may find more information about this at the Oracle’s APEX Listener Download site, or Dimitri’s or John’s blogs.

I just configured the APEX listener with Oracle WebLogic 11g as I would like to compare it with the existing OHS environment. The instructions coming with the download for WebLogic is very straightforward except the last one which asks to recursively copy the apex/images directory to the webserver’s /i. My initial setup shows the logon screen but no images displayed.

I have to admit that I’m a WebLogic novice. My simple workaround to fix this image issue is to make all APEX images into a new application called “i”. This simply can be done by creating a war file of all images under apex/images directory.

F:\DOWNLOAD\apex_3.1.2\apex\images>jar cvf i.war *

Using this i.war file, you just simply install it as a normal application.

If anyone out there have better approaches, please feel free to share. :-)

[Updated: 4/6/2010] Made a correction to the jar syntax. Thanks Boasheng for your comment.

Tags:

Jul 03 2009

Identify Column ID of APEX Tabular Form

Category: APEX — ittichai @ 11:01 am

This post from Patrick is very useful when trying to identify the column ID of the tabular form. But when column order has been moved up/down or some are marked not shown, the column ID might be difficult to determine.

This simple technique of using Javascript event on the Element Attributes of the needed columns can be used to quickly identify the column ID.

onmouseover="alert(this.id);"

You can definitely choose a different event that fits your situation.

See sample here.

Tags: , ,

Mar 15 2009

Pop-up when hovering over tabular column

Category: APEX — ittichai @ 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: APEX — ittichai @ 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: APEX — ittichai @ 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: APEX — ittichai @ 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: APEX — ittichai @ 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 Developer — ittichai @ 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: ,