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: ,

Jan 22 2009

SQL and PL/SQL reserved words

Category: SQLPlus — ittichai @ 10:27 am

I found this tip from forum discussing about Oracle’s reserved words. You can use it to quickly view reserved words for both SQL and PL/SQL.

SQL> help reserved

RESERVED WORDS (PL/SQL)
-----------------------

PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
for identifier names (unless enclosed in "quotes").

An asterisk (*) indicates words are also SQL Reserved Words.

ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
AND*            DO              LIKE*           PCTFREE*        SUM
ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
AS*             ELSIF           LONG*           POSITIVEN       TABLE*
ASC*            END             LOOP            PRAGMA          THEN*
AT              EXCEPTION       MAX             PRIOR*          TIME
AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
BODY            EXTRACT         MODE*           RAW*            TO*
BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
BULK            FETCH           NATURAL         RECORD          TRUE
BY*             FLOAT*          NATURALN        REF             TYPE
CHAR*           FOR*            NEW             RELEASE         UI
CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
CLUSTER*        GOTO            NOWAIT*         ROW*            USE
COALESCE        GROUP*          NULL*           ROWID*          USER*
COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
CONSTANT        IN*             ON*             SEPERATE        VIEW*
CREATE*         INDEX*          OPAQUE          SET*            WHEN
CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
CURSOR          INTEGER*        OPTION*         SPACE           WHILE
DATE*           INTERFACE       OR*             SQL             WITH*
DAY             INTERSECT*      ORDER*          SQLCODE         WORK
DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
DECLARE         INTO*           OTHERS          START*          YEAR
DEFAULT*        IS*             OUT             STDDEV          ZONE
DELETE*         ISOLATION

RESERVED WORDS (SQL)
--------------------

SQL Reserved Words have special meaning in SQL, and may not be used for
identifier names unless enclosed in "quotes".

An asterisk (*) indicates words are also ANSI Reserved Words.

Oracle prefixes implicitly generated schema object and subobject names
with "SYS_". To avoid name resolution conflict, Oracle discourages you
from prefixing your schema object and subobject names with "SYS_".

ACCESS          DEFAULT*         INTEGER*        ONLINE          START
ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
ALL*            DESC*            INTO*           OR*             SYNONYM
ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
AND*            DROP*            LEVEL*          PCTFREE         TABLE*
ANY*            ELSE*            LIKE*           PRIOR*          THEN*
AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
AUDIT           FILE             MAXEXTENTS      RAW             UID
BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
CHAR*           FROM*            MODE            REVOKE*         UPDATE*
CHECK*          GRANT*           MODIFY          ROW             USER*
CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
CONNECT*        IN*              NULL*           SESSION*        VIEW*
CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
CURRENT*        INDEX            OF*             SHARE           WHERE
DATE*           INITIAL          OFFLINE         SIZE*           WITH*
DECIMAL*        INSERT*          ON*             SMALLINT*

Tags: , ,

Dec 24 2008

Generating HTML reports from SQL Plus

Category: SQLPlus — ittichai @ 3:13 am

Last week I was asked by one of IT managers on how to generate reports from database. He’d like to remind his direct reports on ticket’s backlog status. His requirement is just simply to run a simple SQL statement and email the result out weekly. I quickly told him that you can just cron or schedule the script. “Hold on! I’m not finished yet,” he said. If possible, he’d like to have some report data color-coded so everyone can be visually reminded of meeting or missing goals. “And lastly, if possible, report should be formatted nicely – not just in plain text,” he added.

Um…

This is the sample of report I tried to assist him. The required features would include a company logo, color-coded report data based on thresholds, and URL link on ID.

sample

To have the query result in the html format, fortunately, the SQL Plus provides the “markup html” feature since version 8.1.6. There have been a quite number of discussions about this already. But the best place for reference is at Oracle SQL Plus User’s Guide and Reference web site.

I started out with the current configuration of the markup html which by default is OFF.

SQL> show markup
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF

This looks like it is a lot, but actually it is just a bunch of CSS syntaxes which control how the data content to be formatted. If you re-arrange it, it should look like this -

markup HTML ON HEAD "<style type='text/css'> -
body { -
font:10pt Arial,Helvetica,sans-serif; -
color:black; background:white; } -
p { -
font:8pt Arial,sans-serif; -
color:grey; background:white; } -
table,tr,td { -
font:10pt Arial,Helvetica,sans-serif; -
text-align:right; -
color:black; background:white; -
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -
th { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:0px 0px 0px 0px;} -
h1 { -
font:16pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
border-bottom:1px solid #cccc99; -
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 { -
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
margin-top:4pt; margin-bottom:0pt;} -
a { -
font:9pt Arial,Helvetica,sans-serif; -
color:#663300; -
background:#ffffff; -
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style> -
<title>SQL*Plus Report</title>" -
BODY "" -
TABLE "border='1' width='90%' align='center' -
summary='Script output'" -
SPOOL OFF ENTMAP ON PREFORMAT OFF

Note that I added “-” (hyphen) so the tool will treat the next line as the continued line.

To make the query result to be in the html format, the “markup html” must be enabled – MARKUP HTML ON. Also, I disabled the ENTMAP to be OFF, so the SQL Plus won’t replace special characters <,>, ” and & with HTML entitles &lt;, &gt;, &quot; and &amp; respectively. This is needed so I can use the HTML tags in SQL statement. This could be done at the column level as well.

So this is the updated version with a “set” syntax to be ran at the SQL prompt -

$ cat /scripts/set_markup.sql
set markup HTML ON HEAD "<style type='text/css'> -
body { -
font:10pt Arial,Helvetica,sans-serif; -
color:blue; background:white; } -
p { -
font:8pt Arial,sans-serif; -
color:grey; background:white; } -
table,tr,td { -
font:10pt Arial,Helvetica,sans-serif; -
text-align:right; -
color:Black; background:white; -
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } -
th { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:0px 0px 0px 0px;} -
h1 { -
font:16pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
border-bottom:1px solid #cccc99; -
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 { -
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; -
color:#336699; -
background-color:White; -
margin-top:4pt; margin-bottom:0pt;} -
a { -
font:9pt Arial,Helvetica,sans-serif; -
color:#663300; -
background:#ffffff; -
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
.threshold-critical { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:red; } -
.threshold-warning { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:orange; } -
.threshold-ok { -
font:bold 10pt Arial,Helvetica,sans-serif; -
color:green; } -
</style> -
<title>SQL*Plus Report</title>" -
BODY "<img src=http://www.acme.com/company_logo..gif/>" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP OFF SPOOL ON

For ease of execution, I placed above syntax in a SQL file named /scripts/set_markup.sql which will be called when report runs.

I added new classes (“.threshold-XXX”) so different texts can be displayed into different colors based on defined thresholds. I also added the company logo’s image on report under BODY section.

Please note that you can remove unneeded CSS formats, i.e., a{} or margins, to make script cleaner. However, for a demonstration purpose, I leave everything as is.

The report part /scripts/run_weekly_backlog_report.sql is something like this -

$ cat /scripts/run_weekly_backlog_report.sql

connect user/password

@/scripts/set_markup.sql

set pages 100
TTITLE LEFT _DATE CENTER '<h1>Weekly Top Backlog Report : Database Team NA Region</h1>' -
RIGHT 'Page:' FORMAT 999 SQL.PNO SKIP CENTER '<h2>Red = Backlog > 30 days, Orange > 10 days</h2>'

spool d:\temp\backlog_report.htm

select '<a href="http://www.acme-intra.com/pls/apex/f?p=100:3:::::PID:'||ID||'">'||ID||'</a>' ID,
DESCRIPTION, ASSIGNED_TO, OPEN_DATE,
CASE
when SYSDATE-OPEN_DATE > 30 then
'<span class="threshold-critical">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
when SYSDATE-OPEN_DATE > 10 then
'<span class="threshold-warning">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
ELSE
'<span class="threshold-ok">'||to_char(trunc(SYSDATE-OPEN_DATE))||'</span>'
END BACKLOG
from TBL_TICKETS
where STATUS='Open'
order by SYSDATE-OPEN_DATE DESC;

spool off

exit

This will execute “set_markup.sql” first to enable markup html. For the rest, there is nothing unusual. The ID column is embedded with an URL, so users can view the content of the ticket if needed. Also please note the use of the <h1>, <h2> and <span class> tags to format data based on defined thresholds.

This script can be called just by using

sqlplus /nolog @/scripts/run_weekly_backlog_report.sql

The last thing is just put above syntax in a batch file and add sending email (using mail or blat for Windows) with the content of the spooled html file.

Tags: , ,

Jun 15 2008

Oracle SQL Developer Migration Workbench

Category: Migration,Tool,Uncategorized — ittichai @ 10:59 pm

The Oracle SQL developer is not only a great tool for SQL development, with its migration workbench, it is an excellent tool for database migration. Did I also mention it is free?

To get it started, you will need to download the latest version of the tool which is version 1.5.1. Also it is recommended to install the latest updates. The current release of update as of 6/15 is 1.5.1.54.40.

Next, you will need to create a repository. An database account, which has CREATE SESSION, RESOURCE, and CREATE VIEW must be created first. Then logon into SQL Developer as that account. From the tool, create a repository from Migration menu > Repository Management > Create Repository. This process creates a bunch of MD* and MIGR* tables and packages.

To connect to other non-Oracle databases, you will need to create connections through provided JDBC drivers. If somehow, your SQL Developer does not show these 3-rd party connection tabs, you will have to configure it. Instruction is clearly documented on Oracle site here.

image

In my case, I migrate one of our legacy applications on MS SQL to Oracle. Unfortunately this application contains column name named “rowid”, which is Oracle’s reserved word, in most of tables. The tool has a mechanism to append a suffix “_” (with no quotes) if it encounters these reserved words. So “rowid” will be converted to “rowid_”. To see the list of all reserved words, you can just query “KEYWORD” column in MIGRATION_RESERVEDW_WORDS table under the repository schema. Unfortunately, the “rowid” is not really in this table, I have to insert this manually. I added a keyword “privilege” as well because, again, there is a column named “privilege” used in application.

insert into migration_reserved_words values ('rowid');
insert into migration_reserved_words values ('privilege');

I encountered a bug (which Turloch O’Tierney pointed out – thanks) when using version 1.5.1 without the latest patch. In the function “first_char_check” under MIGRATION_TRANSFER package, a number “4” is missing. The impact of this is that If the column name is numeric prefix, for example, 3Q2008, a letter “A” will be prefixed into it to be “A3Q2008”. But due to this bug, it won’t do it with, for example, 4Q2008, resulting in a failure when trying to create table. With the latest patch, it seems like it was fixed already because I now see number 4.

v_allowed := C_DISALLOWED_CHARS || '012356789_$';

The process of migration is straight-forward. You will start with capture then convert. You can capture and convert the whole database at once. But I would not recommend that because if error is encountered, it might be difficult to troubleshoot.

You can just capture only the table first, this will create a converted script. Before running it, please inspect it first and comment un-needed sections especially CREATE default USER syntax and CONNECT using default user. By running the converted script, it will create the definitions of tables, sequences, triggers and constraints. If there is no error, then you can run to move data.

Once data is there, repeat the steps with stored procedures and views. Obviously, you will just need to run the converted script – there is no need for data movement.

Tags:

Mar 16 2008

11g SQL*Plus

Category: SQLPlus — ittichai @ 2:08 am

In 11g SQL*Plus, when session is terminated, somehow it displays the process ID, SID and Serial#. Not quite sure what is really for? I guess it is for debugging purpose.

10g Client

user @db10w1> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on

11g Client

user @db10w1> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 135 Serial number: 29158

Tags: ,

Mar 16 2008

show spparameters

Category: SQLPlus — ittichai @ 1:25 am

I found one of the interesting features in SQL*Plus 11g for Oracle11g database when doing “help show” from the tool.

SHOW  SPPARAMETERS [parameter_name]

Show specific parameters -

SYSTEM @db11r1> show spparameters sga
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------
*        lock_sga                      boolean
*        pre_page_sga                  boolean
*        sga_max_size                  big integer
*        sga_target                    big integer

Show all parameters -

SYSTEM @db11r1> show spparameters
SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------------------
*        O7_DICTIONARY_ACCESSIBILITY   boolean
*        active_instance_count         integer
*        aq_tm_processes               integer     2
*        archive_lag_target            integer
*        asm_diskgroups                string
*        asm_diskstring                string
*        asm_power_limit               integer
*        asm_preferred_read_failure_gr string
:         :         :
*        use_indirect_data_buffers     boolean
*        user_dump_dest                string
*        utl_file_dir                  string
*        workarea_size_policy          string
*        xml_db_events                 string

Originally I thought it is a feature of SQL*Plus 11g, but if I connect to the 10g database, it does not display anything. Note that error indicates that Server version too low for this feature.

SYSTEM @db10w1> show spparameters
SP2-0614: Server version too low for this feature
SP2-0735: unknown SHOW option beginning "lege'@'_co..."
SP2-0158: unknown SHOW option """

If database is started up with pfile, all values of “show spparameters” will be empty.

Tags:

Dec 04 2007

SQL*Plus Copy

Category: SQLPlus — ittichai @ 5:17 pm

The SQL*Plus COPY command can copy data between two databases via SQL*Net.

Syntax:

USER1 @srcdb> copy
usage: COPY FROM [db] TO [db] [opt] [table] { ([cols]) } USING [sel]
[db]   : database string, e.g., hr/your_password@d:chicago-mktg
[opt]  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
[table]: name of the destination table
[cols] : a comma-separated list of destination column aliases
[sel]  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

Sample:
@Target database, the table initially does not exist.

USER1 @targetdb> select * from rep_tab;
select * from rep_tab
   *
ERROR at line 1:ORA-00942: table or view does not exist

@Source database, run copy command within SQL Plus.

USER1 @srcdb> copy from user1/user1@srcdb -
> to user1/user1@target_host:1521/target_sid -
> create rep_tab -
> using -
> select * from rep_tab;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table REP_TAB created.

1 rows selected from user1@SRCDB.
1 rows inserted into REP_TAB.
1 rows committed into REP_TAB at user1@host1:1521/targetsid.

@Target database, now table is copied here.

USER1 @targetdb> select * from rep_tab;

A
----------
1

This is a quick and easy way to copy data between two databases especially on where the usage of database link is restricted. It only requires SQL*Net service name (or host/port/service) and proper privileges.

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: ,

Next Page »