Oracle SQL*Plus

Oracle SQL*Plus


I've posted a few SQL tricks earlier. Most of these were in Oracle and often times, we use SQL*Plus to run these.There are several sophisticated SQL clients available for Windows (Toad is a great tool and at work we use SQL Tools++ and Golden), many even free, but SQL*Plus still remains indispensable. In this post, I will explore this tool.

SQL*Plus is an interesting tool. It's an old, text based, SQL Client that has been in Oracle from the beginning. Whatever it's strengths, user interface isn't one of them. With a command line interface (there is a Windows version (SQLPLusw), but not a major improvement) and quirky commands, it is not exactly user friendly, at least not according to today's GUI standards. Would you believe that SQL*Plus is supposed to be an improvement over what Oracle called "User Friendly Interface"?

Oracle did try to come up with several tools to replace it- a browser version called iSQL*Plus, a java based tool called SQL*Developer, but SQL*Plus survived and may actually outlive those. (See here for the bad news for  iSQL*Plus and SQLPlusw in 11g. Among other things, they actually recommend SQL*Plus!!!).  See Lauren's blog for the official links about this announcement. Also don't forget to read up this blog setting up SQL*Plus nicely in windows, so you won't miss those little used tools.

Strengths of SQL*Plus

Here are some of the reasons why SQL*Plus is still used in production environments, in spite of it's limitations:

• For one, it is a very stable product. This combined with it's simplicity and small memory footprint, makes it a tool of choice for production.


• It is available on various platforms, and offers an almost identical user interface across different platforms.


• The tool can be run in batch mode; combining this with spooling capability and some commands, we can automate running scripts.


• The tool conforms to the Oracle SQL standards so closely, so it still remains as tool of choice when you want to ensure your SQL will really pass in the Oracle environment.


• And to it's credit, it has added few features over the years. For e.g., you can actually spool it's output to an HTML file by adding a few directives (see my post here).

SQL*Plus as a Client/Server Tool


SQL*Plus, like any other SQL Client, is an interactive Client/Server tool. It has a simple editor (don't expect a fancy editor - the one built in is a rudimentary line editor), which can hold 1 statement (however long) in the buffer. You can open a session in SQL*Plus and start typing Statements at the prompt. After you type a line, you can press enter to go to the next line. SQL*Plus will show a line # to indicate it's ready for the next line. You can keep typing and when you are ready, you can press Enter twice, for it to leave edit mode. At this point, the Statement is in memory ready to be executed. When you are done entering/editing the contents, simply issue a “RUN” command (See below) and the contents (SQL) will be pushed to the DB (server) to execute and return results. When the statement is executed successfully, the statement is then put into the 1-statement buffer for reuse.

Below is my sincere effort to document how I see SQL*Plus is executing commands. It's only an educated guess, so use with caution. SQL*Plus by design is a simple tool that executes one command at a time.

[caption id="attachment_471" align="alignnone" width="984"] Fig 1. SQL*Plus Design - an educated guess[/caption]

 

As shown there are 3 different types of statements that are processed by the command processor. It uses the keyword and the end of the statement to decide where to send it. If the statement has a semi-colon (";") or a slash ("/") at the end (See below), it is sent to the Database engine. (Try typing anything with a keyword (like SELECT etc) and end it with a semi-colon, you will get a SQL Error, indicating it went to DB Engine). Rest of them are tried as a local tool command.



 

 

 

 

SQL*Plus as a batch tool

There also seems to be a loop in the command processor, that could read one command at a time from a file and pass it on to the command processor. This combined with few commands to SET and SPOOL makes it a good batch tool. A SQL file is just a combination of different types of statements (SQL, PL/SQL and tools commands).

To run a file with SQL statements you can use START command. The short-cut for this is @. So, run a SQL file, you simply have to type,

@<sqlfile>, where <sqlfile>is any text file with SQL statements.

You can mix tool commands and SQLs in the file. The thing to remember is when we execute a SQL file in SQL*Plus, it processes one statement at a time successively. This is key to understand any problem with your script. You are not passing the SQL file to Oracle, you are giving it to SQL*Plus, which reads and executes one statement at a time depending on the type of statement and terminators used.

Run command and Statement Separator


Oracle uses it's own Run command "(/") and SQL standard for Statement Terminator to execute SQLs.

• Oracle uses Run command as a "statement pusher" to send to DB. This has a short-cut ("/").


• Oracle also allows semicolon (SQL Standard) to terminate SQLs.


• As yet another short-cut, SQL*Plus pushes SQL statements to DB right after a semicolon, so you could use either slash or semicolon there.


• But, with PL/SQL you needed both, as the SQL block will need a semi-colon and to push it to DB, you need a "/".

Potential issues with Run Command

This flexibility in SQL*Plus typically leads to many errors in SQL Scripts. Particularly,

• If you have a PL/SQL in a file and missed the "/", it won't execute the PL/SQL.
  
  
  
  • Imagine, you try to drop a table and recreate it with few changes. If the drop command failed, results of successive commands may become unpredictable
  
  
  
  


• On the other hand, if you added a "/" after a SQL with semicolon, the Statement is run twice!!
  
  
  
  • For e.g., you could end up in inserting a record twice which could make some other Select/into to fail downstream.
  
  
  
  

(Different databases handle this issue differently. tools for SQL Server for e.g., uses "go" as "statement pusher" uniformly for all statements. If you miss a "go" you get an error. MySql handles this slightly differently It uses semicolon for simple SQLs and when it comes to block SQL, it allows us to define a temporary delimiter other than semicolon to mark end of SQL).

See my post here for more details on "/".

How to avoid issues


We can use standards to avoid surprises. I prefer to use semicolons after all SQL (DML and DDL) statements. "/" for PL/SQLs. You may find "/" used for DDLs as well.

If you are planning to use "/" after a SQL statement in a file, I suggest this only for the last SQL statement in the file. This is just a preference for readability.

Few related SQL*Plus gotchas

1. Though both semicolon and "/" could push a SQL statement to Oracle, there is a subtle difference. you can type multiple statements in 1 line separated by semicolon. "/" cannot be used thus.

2. The "/" must be the only character on the line by itself. No other character except spaces may be allowed, not even comments.

3. Sometimes inline comments that start with /* could be construed as "RUN" and may cause syntax errors.

4. Typically, you can include blank lines inside PL/SQL block. But SQL*Plus may frown if you had blank lines inside simple SQL statement. There is SQL*Plus setting (sqlblanklines) to avoid this, but I suggest not to use it to stick to standards.

Reference

As mentioned, SQL*Plus can execute SQL and PL/SQL statements and several of it's own commands. Oracle supports Standard SQL and has several extensions as well. Oracle has very good documentation on this topic and several others. See here for Oracle SQL reference and here for good reference on PL/SQL. See here for a complete reference on SQL*Plus from Oracle. refer to this site for the SQLPlus tool commands.

Links

http://www.williamrobertson.net/documents/sqlplus-setup.html

http://thomas.eibner.dk/oracle/sqlplus/

http://www.adp-gmbh.ch/ora/sqlplus/

http://mti.ugm.ac.id/~adji/courses/resources/CICC/Short%20Cicc/text/or8_text/ch2.htm

http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch2.htm

http://ugweb.cs.ualberta.ca/~c391/manual/chapt4.html

http://laurentschneider.com/wordpress/2006/03/isqlplus-and-sqlplusw-desupport.html

Ubuntu - Thunderbird on Linux

I've used Mozilla Thunderbird as my mail client on Windows, for a while now. This worked out better for me than Eudora, Outlook express. Ever since I lost my account at Yahoo some years back, I store important messages on PC. I switched Gmail for the fact that they allowed POP client. (On Yahoo, I had to pay for that service!)

Since I moved to Linux last year, I've tried various software on it. I had Evolution as my mail client as it comes as default on Ubuntu. It was very similar to Thunderbird, but there were some issues: For some reason the address lookup never worked for me there. Also, it had some file permission problems constantly, so my sent mail will sit in outbox forever. Worse yet, it used to silently crash and restart itself every now and then. I am sure, I could have googled for a solution, but I decided to go back to Thunderbird. I love it.

(Googling for "ThunderBird vs Evolution" brings backs hundreds of threads like the one below. They also talk about Kontact, Claws etc. Must explore in the future.
http://ubuntuforums.org/archive/index.php/t-256684.html. )

When I got Thunderbird, I set it up POP settings for Google. Since, I have 2 PCs connecting POP to Google mail, messages started disappearing on either, depending on which one got it first. (Nature of POP, once you downloaded a message, it's no longer available to download. See below link). Then I discovered IMAP. It's always been there, but suddenly it occurred to me that I could use that for a multi-client setup. I did that, and it's been working OK. One thing with IMAP is that it's a direct access to the Internet Mailbox. So, nothing is stored on your PC. But, IMAP has an option to store locally as well. So, on one PC I set it up to download and the other just connect directly. Below link has a nice article about IMAP vs POP mail.

http://www1.umn.edu/adcs/guides/email/imapvspop.html

Java & I - My Java Development experience

Over the years I worked with various versions of Java through 1.6. But, Java was never my main bread & butter. Irrespective of the environment there was always some flavor of Java used in the development environment.

Java Swing, Applet etc
I started working with Java 1.0, many years ago. All those books and web sites with applet code with flashy animations made it look really interesting. (Real applet experience was totally different though!). Since then, I've worked in Java on and off. For a few years, I worked in a Java swing building Java applet (2MB jar!!) against a Forte Server. This included working with Visual Cafe, Visigenic CORBA, IDL files etc. Then I had chance to upgrade the application Java 1.2.

J2EE?!
Later, I got a chance to work on a J2EE application against Oracle. This ran in Apache and JBOSS. Java bean, EJB, Servlets, JSP and the whole nine-yards of typical Java developer's field. Then I got contracts to work on PB and VB. I kept in touch with Java though.

Java as a reporting tool
Couple of years ago, I got back into Java when I worked on a Java reporting package based around BIRT Java API for working with Excel files. (I think it's owned by Actuate now). The Java program was part of the batch environment on UNIX. We used it to generate Excel reports (with multiple worksheets) to be sent to users via e-mail. This was a challenging project.

Java Stored Procedures
Recently I got a change to work on yet another Java flavor. As mentioned elsewhere, where  I work currently, the environment is mainly in Powerbuilder and Oracle. Every now and then we find some surprises (in the last 18 months I've learned to expect there). There are a lot of Java packages/programs built around the PB application. These run on EA Server's Java container and use CORBA layer to talk to the PB application. The one I worked recently was slightly different. It was not the usual JDBC application, but a Java Stored procedure(s) that runs inside Oracle. I ended up in cleaning up the code, setting up a development environment around Eclipse and Ant and setting standards for other developers.

Java JSP, Coldfusion
We also have a lot of JSP pages that use CORBA to talk to our PB application through IIOP/CORBA. Recently we started building Coldfusion pages. While trying to help the developers to interface with our PB application, I realized that Coldfusion has been completely rewritten in Java. Coldfusion server was running on a Linux box. To get this to work with our Powerbuilder (EA) server, we needed a Visigenic product. (I eventually wrote a Java class that interfaces with the CORBA thus isolating Coldfusion from CORBA thus saving money for the company).

So many places, so many flavors. I've been having fun with Java. I hope to share some of these experiences here. In the coming posts, I will go into more detail about these Java experiences, hoping it would benefit some newcomers.

Sybase to Oracle conversion

Background: Few years ago, I worked on a project where I had to convert close to 100 stored procedures from Sybase to Oracle. There are several differences between the 2 databases.

I was hired to be the Oracle expert in a team full of Sybase developers. I constantly worked with them to convert the databases and the code to Oracle. I worked on it from start to finish. I was asked to set up the environment, set the standards for Oracle coding and convert a lot of stored procedures. I participated in their migration project and eventually even supported their batch environment that ran in both Sybase and in the new Oracle environment. I even got a chance to work as a Oracle development DBA within the team. This gave me a lot of opportunity to learn about both the databases. I got to know their similarities (they both are SQLbased relational databases, aren't they?) and their differences (Did you know, Sybase database is the same as Oracle Schema and Oracle Database is the same as Sybase Server??).

There are a lot of sites out there discussing the differences and even migration from one to another. I benefited from a lot of those and I made a lot of notes at the time. When I was working on the project, I was so busy I couldn't post all those notes online. Now, that I have some time, I am dusting off my old notes and posting here. I hope it benefits someone doing similar kind of migration.

Sybase to Oracle Database conversion

This is a much bigger topic than I intend to cover in this post. My take on the conversion is mainly from a developer point of view. The DBAs will have a lot more to add about the database setup and architecture. Though, you will see references to database level differences, as I did perform the duties of a development DBA within the team. Below presentation lists out some of the differences while coding a stored procedure in the 2 databases. I took one of the procedures we migrated and annotated it with comments. I made this for the team at the time. I've changed the names of the tables and procedures to be more generic.

Note about the convention in the attached presentation: I wrote a complete Oracle Coding standards manual for the Sybase developers. I am following this convention in the attachment below. In general keywords are upper cased, other identifiers lower cased. The names of identifiers follow certain naming convention. I will post about this separately.

Sybase to oracle_conversion[slideshare id=8735258&w=668&h=714&sc=no]

View more documents from svaradar

PB 101 - Contd... Project Workspace

As mentioned earlier, PB is geared up for developing applications for various platforms. We saw how the IDE is built around the Project Workspace, and the tools it provides to access every in the workspace. First let's define the various parts of a Powerbuilder based software development project.

PB 101 - Contd... Powerbuilder IDE

Powerbuilder IDE

PB IDE, just like any other windows application is an MDI application. It is made up of several windows and menus and toolbars. When you open the IDE, it opens up with no Workspace. You need to create/open a Workspace (using options in File menu) to work on a specific project. A  Workspace wraps around one or more Targets which in turn includes one or more Libraries. The libraries contain the objects. The objects themselves have properties and methods like in any Object oriented environment. The objects also contain events as Powerbuilder is an event driven programming environment.

Navigation through the project hierarchy is available through a tool called System Tree. The IDE also contains a full menu toolbar called Powerbar that lets you perform several tasks including creating various components in the project hierarchy. The IDE is made up of several individual tools and wizards, which are called Painters. There is a painter for each type of object and each task in PB.

System Tree & Powerbar

The System Tree is a project explorer. This let's you see the project hierarchy mentioned above, in a tree like fashion. The TreeView object fits this hierarchical structure perfectly. The topmost parent in the tree is the Project Workspace. You can drill down targets and libraries to object properties and methods here. You can expand or collapse at any level in the tree. You can open any existing objects in the System Tree (by double-clicking on them).

Powerbar is the Powerbuilder menu Toolbar containing buttons for important menu items.  You can create new objects by Clicking on the "New" button in Powerbar (or File->New option in the menu).

Clicking on New opens up a pop-up window that has several options (buttons) to open various wizards and tools in PB. You can create workspace, target, PB objects including windows, menus, datawindows from here. You can also open up several tools and wizards, such as Library explorer, Database wizard, File Editor, PB Profiler etc, by creating "New" instances of these. Every such wizard or tool in PB is called a Painter.

[caption id="attachment_139" align="aligncenter" width="1024" caption="Powerbuilder System Tree and Powerbar"][/caption]

Fig 1 - Powerbuilder System Tree & Powerbar

PB New Options - Here showing several types of PB Objects

Fig 2- New Option in Powerbar leads to "New" options Pop-up

Painters

As mentioned above Powerbuilder IDE is essentially made of several Painters. Each specific component type has it's own Painter (Object Editor) and several tasks have associated Painters (Tools or Wizards). So you will see Window Painter, Datawindow Painter, Database Painter, Library Painter etc. Each Painter is self-contained unit and has it's own menu (and toolbar), a control(s) list and properties sheet. As you can see, Powerbuilder IDE itself is designed in an object oriented way.

You open the individual component painters when you open an object in System Tree View or when Click on New on he menu bar.  Several task and tool painters can be opened in Powerbar.

Here are some of the Painters are available in PB:

• Application Painter


• Window Painter


• Menu Painter


• DataWindow Painter


• Function Painter


• User Object Painter


• Database Painter


• Library Painter


• Project Painter (this is used to build applications - sort of compiler/linker options window; more about this later).

PB IDE - Other parts

Apart from these, the IDE UI has the following components:

An Output (child) Window which shows all the messages from the PB IDE environment, such as compiler outout, error messages etc.

A To-Do List Popup - Here you can enter a "todo" list. You can enter your own laundry list of things to be done in a project. Some of the wizards in PB also create To-Do entries automatically.

PB IDE includes a Browser window, which lists out all the objects in the project. This tool is very useful in locating objects in a large project with lot of libraries. As of version 10.5, it does not have a search capability yet, but you can navigate through libraries and types of objects to easily locate an object. Once found, you can "Edit" the object.

There are also several tools and wizards available in PB. Application Profiler, Debug Window, Database Painter, EA Server Profile etc. Each of these have special Toolbars attached to them.

See below link for more information (The link is for PB 11.0, but still applicable to any version > 8.x in general).

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc37772_1100/html/pbgs/CBBCHDJG.htm

PB 101 - Starting from scratch

the last few posts, we talked about PB a lot. My last post on Hello World gave an introduction to PB as a programming language. Now, it's about time we introduce Powerbuilder, the programming environment itself and how to use it to successfully develop a simple application.

PB as a n-tier tool

PB started as a simple client/server tool. We and PB are now in n-tier world. This means an application could be divided into multi-tiers,  such as a client and one or more application servers connecting to the database in the backend. Even the client can be multiple, as in you may have a PB application client in the office, a web page accessing the same servers etc. Dividing the application logic into logical blocks of client and server(s) is called application partitioning. Java J2EE is a great multi-tier development environment in 3GL. PB has grown into a n-tier tool in the 4GL world.  Sybase has come up with an Application Server called EA Server (originally called Jaguar in version 7). You can now use it to build not only windows applications, but middle tier applications (Application Servers) and Web Applications as well. To handle this new world, PB has completely changed it's own GUI as of version 7.

PB IDE

When you open PB (versions > 8.x), the first thing you will see is the workspace. A workspace comprises of several "Targets" each target representing a piece (partition) of the application that can be independently deployed. A target can be for different type of applications - a windows client application, a Jaguar component, a DLL, a JSP program, a .Net program etc.

To the target, you add the PB libraries, by adding it to the library list. A library list is not only for housekeeping of libraries in a target, but at run-time this serves as the search path for the application to find objects.

And to the libraries, you add objects such as Applications, windows, datawindows etc. When you create a windows client application, an application object will be automatically added to the library just created. Below images show the various parts of a workspace. We will start looking into each of these in detail in the coming posts.

[gallery]