Nov 09 2011

Customize/Personalize Oracle APEX Workspace Environment

Category: 11g,APEX,Browser,Tool,URL — ittichai @ 4:06 pm

When you have multiple Oracle APEX environments, e.g., development, test, UAT and production, and for multiple groups, one of the confusing things is that sometimes developers are lost on which environment they’re working on. Most of technically proficient developers can make a distinction easily from the URLs they’re currently using. However, most of less technically proficient (e.g., business users) sometimes cannot.

A quick and simple solution here is to make a visual distinction on the APEX workspace pages especially on the logo area. Instead of using plain vanilla logo images, they are replaced with more distinct and informative images.

This is the default APEX workspace logon page.

Default Workspace Logon Image

This is a sample of the personalized workspace logon page with a company logo and it is showing that this is the development environment.

Customized APEX workspace logon page

This is the main workspace page after logon showing the default logo image.

Default Workspace Page

You can change it to be like this.

Customized APEX workspace development page

With just this minor change, our users feel more comfortable knowing that they’re working the right environment.

How to change it?

With a quick investigation, you can easily find the paths of those image files as follows:

Workspace logon page:

Image path: /i/apex/builder/apex-logo-white.gif
Image size: 300px x 30px
Transparent background

Image path:/i/apex/builder/apex-db-apps.png
Image size: 240px x 200px
Transparent background

Workspace main page after logon:
Image path: /i/htmldb/apex_logo.gif
Image size: 300px x 30px
White background

  • Use Photoshop or any image editing tools to recreate those image files.
  • Copy and replace those image files on the web server. Please make the backup of those files before replacing them.

Note that this approach will NOT work if you’re using a shared web server for multiple APEX database environments because they’re using the same image files.

Update: Using ApexLib script in the login message (under Manage Instance) as mentioned by Peter in the comment section is another solution for customization. I like this approach as not only you can customize the logo, but you can also change the description text on the workspace logon page. In addition, since the change is on the APEX instance itself, it can definitely be used on a shared web server.

Oct 24 2011

Book Review: Android Application Testing Guide (Deigo Torres Milano) by PACKT Publishing

Category: Android,Book Review,Books,Programming,Tool — ittichai @ 10:39 am

Android Application Testing Guide book (Diego Torres Milano) by Packt PublishingWhen I was offered to review this book, I was so excited. The topic is probably one of the least popular topics in the Android development circle based on the number of books written on Android. Most of the books in the market tend to cover solely on the area of actual development because it gives readers an instant gratification when creating something. However, it is unfortunate (but true) that the application testing might be something most developers least think about, or if they do, they not do it systematically. (I’m guilty of this as well.) I would like to expand my horizon by learning from the pros. That’s why I’m so excited for a chance to review this book.

The “Android Application Testing Guide” is a very practical book introducing available frameworks and most widely used tools & techniques to improve the qualify of the application by engaging in the quality control and testing throughout the development cycle (not just start testing at the end). This agile development concept called the Test Driven Development (TDD) relies on repeatable short cycle to trying to catch and take care of potential issues (i.e., bugs) as early as possible.

The first chapter explains what involves in the different stages of testing in the Android development including unit test, integration test, functional or acceptance test, system test, and performance test. It starts introducing the Android testing framework extending JUnit which provides the complete testing framework suitable for the end-to-end testing strategies.

The chapter 2 starts working on the actual testing using JUnit which is the default framework for Android testing project and is supported by Eclipse which it the most widely-used IDE for Android development. The chapter jumps right into the step-by-step on how to create the Android test project which is a separate from its development project being tested. The test project will have independent structure and a set of its own components. Having a separate project is the best practice because from the production build’s standpoint, testing codes will not be included in the actual build, thus it will be not be in the APK.

Chapter 3 dives into individual building block in the Android SDK tool.  This covers Assertions, TouchUtils class (to simulate the touch events), Mock objects (to simulate mock objects in order to isolate the tests), TestCase class, and Instrumentation. There is an extensive explanation of individual component accompanying by code samples.

Chapter 4 talks about the concept of Test Driven Development. Again, it is the strategy of performing tests along the development process – not at the end as in the traditional approach. This even includes writing test cases (and test codes) first right after studying the requirements, and then writing the actual codes to satisfy (having the “pass” results) the test cases. Author claims that this approach of creating test cases this early will ensure that tests will be performed instead of, if left until the end, it is highly possible that they would have been forgotten or ignored. I agree with the concept. But in reality this may not work with all types of the projects, and this is confirmed by author in a few paragraphs later to use your judgement and expertise in applying this approach to wherever suitable. The latter part of the chapter shows samples on how to apply the TDD in the step-by-step sample application and tests, which I found this makes the case very compelling.

Chapter 5 introduces the Android Virtual Device (AVD) which is the next best thing to the real devices. The obvious advantage of using AVD is ability to create different Android configurations to run tests. The Headless Emulator (no window displayed) is also mentioned here allowing the automated tests via command line with no windows. I really enjoy many samples of what you can do with command-lines especially when you can simulate different device’s conditions including network bandwidth throttling, or different locales, etc. Later in the chapter, it talks about the support of the Monkey application (you can read more about Monkey Theorem here) which allows random event generation. It also demos the server-client setup and test scripting with Monkneyrunner.

Chapter 6 talks about the Behavior Driven Development which, according to author, is considered the evolution of the Test Driven Development and a need of Acceptance Testing. In summary, the approach emphasizes not only to include the business or end users in testing, but also to use non-technical terms in the test cases that business or end users would understand. Personally I think the concept is too abstract than practical. However, the introduction of Fitnesse and Slim tools makes the point to see its usefulness especially the part of “Wiki”-style test cases and test result documentation.

Chapter 7 contains all practical real-world samples of the disciplines and techniques you can use in the different situations. This includes testings on activities and applications, databases and Content Providers, UIs, exceptions, parsers and memory leaks. Couple tools are introduced here including EasyMock which provides mock objects for interfaces, and Hamcrest which is a library of matcher objects for comparison.

Chapter 8 continues the introduction of Agile technique with Continuous integration. Similar to continuous testing, it is suggested that integration should be done frequently early in the process and in small steps. The most common practice, as stated by author, is to trigger the build process after every commit to the source code repository. The chapter goes in details how to setup the automate building process using ant, the version control repository using Git, the continuous integration with Hudson, and test result analysis using nbandroid-utils.

Chapter 9 discusses one of the most critical components in designing Android application which is performance. As we all know, with a device which has limited computing power and battery life, balancing between performance and effective use of resources is something developers should keep in mind when designing an Android application. The chapter again introduces us with a series of tools and techniques for performance testing. This includes using traditional LogCatlogging, extending Instrumentation, Traceview, dmtracedump, and Caliper microbenchmarks.

Chapter 10 – alternative testing tactic is not for the most Android developers as it involves building Android from source code. Again, more tools are introduced. Even though it is very technical, it is still a good read.

Summary:

I have to say that for a novice Android developer like I am, I learn a lot from reading the Android Application Testing Guide. Best-in-class testing techniques are covered in this book which are commonly practiced in the agile development. These include Test Driven Development, Behavior Driven Development, Continuous Integration, etc. They in generally oppose to the traditional approach of doing test and integration at the end of development cycle. However, as author states in the book, there is no silver bullets in software development – the suggested testing approaches might not apply to certain needs or projects. But I can see that for a large complex project, applying these methodologies and techniques will likely increase in the productivity and quality.

Numerous tools are introduced in this book. This may be one of the big issues for those who are less-advanced in Android development. Since there is no common repository of these tools, extended knowledge of a varieties of open-source tools, and ability to incorporate them with the development process are required. I wish that there would be consolidated repository for all testing tools or even better all required testing functionality would be built-in in the SDK. Well, before when that would happen, this Android Application Testing Guide book is probably one of a few reference books that has the complete collection of Android testing topics.

Disclosure: I received the Android Application Testing Guide e-book from Pubkt publishing with no charge. I’m not compensated for this book review. The content expressed in this post is personal opinion.

 

Tags: , , , , , , , ,

Sep 21 2011

Oracle APEX (Application Express) Built-in LDAP Test Tool Stand-alone Application

Category: APEX,Tool,URL — ittichai @ 6:56 pm

Oracle APEX provides multiple ways for authentication. The most commonly used one is the LDAP authentication. Configuring it is very straight-forward in most cases – just providing the LDAP host, port, whether or not to use SSL, and finally the DN string. See here and here for posts about APEX LDAP configuration.

Sometime you want to test the LDAP configuration before deploying it. You can either use the 3rd-party tools to validate the configurations, or create an APEX application to test the logon, or use the built-in LDAP test tool in APEX.

The built-in APEX LDAP test tool can be launched when the authentication is being configured as seen below.

LDAP Test Tool

It will pop-up a new window as shown below.

LDAP Test Tool Pop-up Screen

By accident, I found out that you can even launch the APEX LDAP Test Tool as the stand-alone application using Application ID 4000 and Page Number 3890.

For example,

http://localhost:8888/apex/f?p=4000:3890

It will obviously prompt you to log on to the workspace first, then the LDAP test tool will just show just like an application, not the pop-up window.

Additional resources:

Jul 05 2011

SQL Developer with “I/O Error: SSO Failed: Native SSPI library not loaded” when connecting to MS SQL using Windows Authentication

Category: SQL Developer — ittichai @ 5:57 pm

I encountered this error “I/O Error: SSO Failed: Native SSPI library not loaded” when using Oracle SQL Developer connecting to a MS SQL database using Windows Authentication. Fortunately, couple web sites (here and here) already mentioned about this.

Error when using SQL Developer to connect to MS SQL using Windows Authentication

Basically, from the  jTDS distribution downloaded files, I have to copy a DLL file named ntlmauth.dll (which is for NT authentication) under the jtds-x.x.x-dist\x86\SSO\ or jtds-x.x.x-dist\x64\SSO\, to any directories in the PATH environment. I copied it to the bin directory of the JDK (which I already have it in the PATH). Then after restarting the SQL Developer, I can now connect to MS SQL database using Windows Authentication.

 

Mar 20 2011

Oracle 11g SQL Error Logging

Category: 11g,Database,SQLPlus,Tool — ittichai @ 8:00 am

We’re working on the installation scripts for an internal PL/SQL applications. Multiple scripts are called from the main one. To capture errors from script execution, normally the spool syntax will be used in the scripts to pipe out all executions into log files, and then later the deployment team members will examine them using find/search ORA- for any errors. This would work fine if there are only a few scripts but it becomes cumbersome when multiple scripts are involved. In addition we’d like to be able to run the scripts on either Windows and Unix platforms, handling OS file paths for multiple platforms using spool adds unnecessary layer.

The SQL Error Logging is a new feature in 11g. This simplifies the way we capture and locate error messages as information now will be stored in database table instead of OS files.

To check whether or not the Error Logging is enabled.

SQL> show errorlogging

errorlogging is OFF

To enable the Error Logging. The default table SPERRORLOG is created.

SQL> set errorlogging on

By default, the SPERRORLOG will be created under current user. In this sample, the current schema is TEST1_USER.

SQL> show errorlogging

errorlogging is ON TABLE TEST1_USER.SPERRORLOG

Here is the structure of this table SPERRORLOG.

SQL> desc SPERRORLOG
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(256)
TIMESTAMP                                          TIMESTAMP(6)
SCRIPT                                             VARCHAR2(1024)
IDENTIFIER                                         VARCHAR2(256)
MESSAGE                                            CLOB
STATEMENT                                          CLOB

To enable the Error Logging to an user defined table instead of the default SPERRORLOG table.

SQL> set errorlogging on table [schema].[table]

If non-default, the table has to be created in advance, otherwise you will get an error.

SQL> set errorlogging on table my_sperrorlog;

SP2-1507: Errorlogging table, role or privilege is missing or not accessible

Here is the syntax to create an user-defined table. If this table is created on a different schema, an insert grant to is needed for the current user.

SQL> CREATE TABLE my_sperrorlog (
username     VARCHAR(256),
timestamp    TIMESTAMP,
script       VARCHAR(1024),
identifier   VARCHAR(256),
message      CLOB,
statement    CLOB
);
SQL> set errorlogging on table my_sperrorlog

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.my_sperrorlog

Demo
Generate error #1.

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

Generate error #2.

SQL> alter table EMP add new_column VARCHR2(1);
alter table EMP add new_column VARCHR2(1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

Check the SQL Error Logging.

SQL> column username format A10
SQL> column message format A30 wrap
SQL> column statement format A30 wrap

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

TEST1_USER alter table EMP add new_column ORA-01735: invalid ALTER TABLE
VARCHR2(1)                     option

Without commit, other sessions won’t see this information.

SQL> commit;

Commit complete.

Set an unique identifier to make it easier to identify the logging record. In sample here, the identifier is set to REL1.

SQL> set errorlogging on identifier 'REL1'

Generate error #3.

SQL> alter table EMP modify ABC NOT NULL;
alter table EMP modify ABC NOT NULL
*
ERROR at line 1:
ORA-00904: "ABC": invalid identifier

Check the SQL Error Logging with the identifier=’REL1′.

SQL> SELECT username, statement, message, IDENTIFIER
FROM sperrorlog where IDENTIFIER='REL1';

USERNAME   STATEMENT                      MESSAGE                        IDENT
---------- ------------------------------ ------------------------------ -----
TEST1_USER alter table EMP modify ABC NOT ORA-00904: "ABC": invalid iden REL1
NULL                          tifier

Truncate the Error Logging to clear all existing rows in the error log table. This will clear out all records regardless of who creates them.

SQL> set errorlogging on truncate

SQL> SELECT * FROM sperrorlog;
No rows selected

There is no set errorlogging truncate only a specified identifier. Doing below is the same set errorlogging on truncate. So basically the identifier is ignored.

SQL> set errorlogging on truncate identifier 'REL1'

But you can just delete records as the regular table.

SQL> delete sperrorlog where IDENTIFIER='REL1';

SQL> commit;

Disable Error Logging. (Log off will automatically disable it.)

SQL> set errorlogging OFF

SQL> show errorlogging
errorlogging is OFF

Will it work with previous versions of Oracle database?

Since the SQL Error Logging is the feature on client, I have no reason to believe that it would not work with pre-11g databases.

The 11g SQL Plus Client to 10.2.0.1 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 10.1.0.4 Database

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Solaris: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> show errorlogging
errorlogging is OFF

SQL> set errorlogging ON

SQL> show errorlogging
errorlogging is ON TABLE TEST1_USER.SPERRORLOG

SQL> select username, TIMESTAMP from  sperrorlog;

no rows selected

SQL> SELECT 1/0 FROM DUAL;
SELECT 1/0 FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> SELECT username, STATEMENT, message FROM sperrorlog;

USERNAME   STATEMENT                      MESSAGE
---------- ------------------------------ ------------------------------
TEST1_USER SELECT 1/0 FROM DUAL           ORA-01476: divisor is equal to
zero

The 11g SQL Plus Client to 9i Database
Unfortunately I don’t have any Oracle 9i databases.  :-)

The 11g SQL Plus Client to 8i Database

Surprisingly, I still have one 8i database left. But, well, the 11g client no longer supports the 8i database.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Related topics:

Tags: , , , , , ,

Mar 07 2011

Mumbai – Free Performance Analysis Tool for Oracle Database

Category: Database,Tool — ittichai @ 8:18 am

I found this tool via Karl Arao’s TiddlyWiki under the Performance Database Tools.  This free tool written by Marcus Mönnig called Mumbai has many nifty features focusing on data visualization including session profiling, alert & trace file viewing, 10046 tracing/viewing within the tool, etc. It also integrates with snapper and orasrp.

Here are some introductory webcasts of this tool.

Please check it out.

See also: http://ittichaicham.com/wiki/Oracle_Database_Tools

Tags:

Feb 14 2011

Easy Connect (EZCONNECT) Naming Method with Instance Name

Category: 11g,Network,SQLPlus — ittichai @ 7:11 pm

Oftentimes I use the Easy Connect (EZCONNECT) to connect to a new database just to quickly verify database connectivity. The syntax I usually use is as follows:

CONNECT username/password@[//]host[:port][/service_name]
CONNECT scott/tiger@host1-vip:1522/RACDB

In RAC, this will connect to whatever instance based on the service’s load balancing goal.

I just learned that with the 11g net services client software, you can also specify the instance name to connect to. This may be useful especially for RAC setup if you have the need to connect to a specific instance.

CONNECT username@[//]host[:port][/[service_name][:server]][/instance_name]]

-- Connect to the instance name RACDB1
CONNECT scott/tiger@host1-vip:1522/RACDB/RACDB1

-- Connect to the instance name RACDB2
CONNECT scott/tiger@host1-vip:1522/RACDB/RACDB2

Also, the optional [:server] is either dedicated or shared.

CONNECT scott/tiger@host1-vip:1522/RACDB:dedicated/RACDB1

Reference: http://download.oracle.com/docs/cd/B28359_01/network.111/b28316/naming.htm#NETAG255

Tags: , , , , , , , , ,

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 05 2009

Online SAN Storage Migration for Oracle 11g RAC database with ASM

Category: 11g,Database,Migration — ittichai @ 7:30 am

It has been awhile since my last post. My pathetic excuses are all pretty much mentioned here. :-)

Last month we’ve worked with the storage team to migrate the SAN storage of our Oracle 11gR1 database to a new one. The drive of migration is mainly for SAN consolidation which is, of course, ultimately for cost saving. In addition to migrating the ASM disk groups storing database’s data files, all clusterware files (OCR and voting disk) must be migrated too. The rebalance feature in Oracle ASM makes data migration very easy and seamless. And since the clusterware files have redundancy, they can be seamlessly migrated as well. With 11gR1, all migration tasks can be performed online.

Prerequisites/Assumptions:

- New SAN LUNs/disks are already visible to all RAC nodes. In case of the disks for ASM diskgroups, they are already discovered by ASM. The minimum numbers and permissions of the OCR and voting disks must be met.

- It is recommended to perform the migration tasks during off-peak hours or even better if during planned maintenance window period.

Note that the sample shown here is specific to my environment (11.1.0.7 on Solaris 10 with dual-pathing to Hitachi SAN, and OCR and voting disks are on raw devices).

SAN Migration of the ASM diskgroups

If you’re more comfortable with GUI, all tasks here can be accomplished using the Enterprise Manager.

1. Add new disks to ASM diskgroups.

ALTER DISKGROUP PMDW_DG1 ADD DISK
  '/dev/rdsk/c4t60060E80056FB30000006FB300000823d0s6' NAME PMDW_DG1_0003,
  '/dev/rdsk/c4t60060E80056FB30000006FB300000826d0s6' NAME PMDW_DG1_0004,
  '/dev/rdsk/c4t60060E80056FB30000006FB300000829d0s6' NAME PMDW_DG1_0005
REBALANCE POWER 11;

We go with the rebalance power of 11 which is full throttle because it is planned maintenance.

2. Check rebalance status from Enterprise Manager or v$ASM_OPERATION.

3. When rebalance completes, drop the old disks.

ALTER DISKGROUP PMDW_DG1 DROP DISK
   PMDW_DG1_0000,
   PMDW_DG1_0001,
   PMDW_DG1_0002
REBALANCE POWER 11;

When adding or removing several disks, it is recommend to add or remove all disks at once. This is to reduce the number of the rebalance operations that are needed for storage changes.

SAN Migration of the OCR Files

1. Backup all OCR-related files.

# {CRS_HOME}/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     921332
         Used space (kbytes)      :       4548
         Available space (kbytes) :     916784
         ID                       :  776278942
         Device/File Name         : /dev/rdsk/c4t50060E800000000000002892000003F8d0s6
                      Device/File integrity check succeeded
         Device/File Name         : /dev/rdsk/c4t50060E800000000000002892000003F9d0s6
                       Device/File integrity check succeeded

Backup the /var/opt/oracle/ocr.loc file:

# cp ocr.loc ocr.loc.old

Manually backup OCR:

# {CRS_HOME}/bin/ocrconfig -manualbackup

2. As root, run the following commands to replace OCR files. This change can be performed on-line, and will be reflected across the entire cluster.

# {CRS_HOME}/bin/ocrconfig -replace ocr /dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6

# {CRS_HOME}/bin/ocrconfig -replace ocrmirror /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6

3. Verify the new configuration.

Check new ocr.loc file updated:

# cat /var/opt/oracle/ocr.loc
#Device/file /dev/rdsk/c4t50060E800000000000002892000003F9d0s6 getting replaced by device /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6
ocrconfig_loc=/dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6
ocrmirrorconfig_loc=/dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6

Check OCR:

# {CRS_HOME}/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version                  :          2
Total space (kbytes)     :     921332
Used space (kbytes)      :       4548
Available space (kbytes) :     916784
ID                       :  776278942
Device/File Name         : /dev/rdsk/c4t60060E80056FB30000006FB300001014d0s6
Device/File integrity check succeeded
Device/File Name         : /dev/rdsk/c4t60060E80056FB30000006FB300001015d0s6
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Logical corruption check succeeded

SAN Migration of the voting disks

1. Backup the voting disks.

Query the original locations:

# /opt/oracrs/bin/crsctl query css votedisk
0.     0    /dev/rdsk/c4t50060E800000000000002892000003FBd0s6
1.     0    /dev/rdsk/c4t50060E800000000000002892000003FCd0s6
2.     0    /dev/rdsk/c4t50060E800000000000002892000003FFd0s6

Backup voting disks using dd:

dd if={voting_disk_name} of={backup_file_name}

Example,
dd if=/dev/rdsk/c4t50060E800000000000002892000003FBd0s6 of=/tmp/voting1

2. Move voting disks.

Starting with 11.1 onwards, the voting disk migration can be performed on-line.

# /opt/oracrs/bin/crsctl delete css votedisk /dev/rdsk/c4t50060E800000000000002892000003FBd0s6
# /opt/oracrs/bin/crsctl add  css votedisk /dev/rdsk/c4t60060E80056FB30000006FB300001017d0s6

# /opt/oracrs/bin/crsctl delete css votedisk  /dev/rdsk/c4t50060E800000000000002892000003FCd0s6
# /opt/oracrs/bin/crsctl add css votedisk  /dev/rdsk/c4t60060E80056FB30000006FB300001018d0s6

# /opt/oracrs/bin/crsctl delete css votedisk  /dev/rdsk/c4t50060E800000000000002892000003FFd0s6
# /opt/oracrs/bin/crsctl add css votedisk  /dev/rdsk/c4t60060E80056FB30000006FB300001019d0s6

3. Verify the new configuration.

# /opt/oracrs/bin/crsctl query css votedisk
0.     0    /dev/rdsk/c4t60060E80056FB30000006FB300001017d0s6
1.     0    /dev/rdsk/c4t60060E80056FB30000006FB300001018d0s6
2.     0    /dev/rdsk/c4t60060E80056FB30000006FB300001019d0s6

Reference:
Metalink #428681.1:  OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE), including moving from RAW Devices to Block Devices

Tags: , , , , , ,

May 04 2009

Informatica 8.6.1 Hot Fix 2 – Object Check-In is Slow

Category: Database,ETL — ittichai @ 7:51 am

If you’re using Informatica as ETL tool as we do, this may be useful if you’re working on upgrading it to the latest version 8.6.1 hot fix 2.

During the testing, our ETL developers complained that the object check-in took a long time (sometime up to 2 minutes – compared to only a few seconds in the existing environment). There are no loads or waits as far as we could see on the repository database. My gut feeling was that something was missing here. We then opened a support case with Informatica, and, sure enough, the support team asked us to try adding new indexes as follows:

CREATE INDEX OPB_VALIDAT_IDX_SR
ON OPB_VALIDATE (
OBJECT_ID, OBJECT_TYPE,
VERSION_NUMBER
);

CREATE INDEX OPB_COMPONE_IDX_SR
ON OPB_COMPONENT (
REF_OBJ_ID, OBJECT_TYPE
);

With these new indexes, the check-in is now instant!

Tags: ,

Next Page »