Oracle: Commit and Forward Slash

Further to my post earlier about DDL and Forward Slash, I would like to explain Forward slash's role (or lack thereof) in other scenarios. One such scenario people seem to get confused with is COMMIT. They seem to want to find some type of connection between the two. Let me say it upfront, there is no connection between COMMIT and "/".

Forward Slash is simply a SQL Executor and it happens to show up in various situations. This combined with SQL*Plus's own quirks seem to imply certain other functions for this character. Let me repeat: it's just an executor - short for RUN command in SQL*Plus.

One reason it could be construed as connected with COMMIT is that it may often be the last character in a file. When you run SQL*Plus in batch mode (running a script in a file), it typically exits after the last line (on *nix. It doesn't DoS/Windows). And the SQL*Plus's own behavior is to COMMIT a transaction when exiting. (Why? See here for a nice discussion about this.) So, it's the exit that committed not the "/". But. you can see why some newcomer to Oracle who just inherited some scripts, may think that "/" (being the last statement) actually committed the transaction!!!

The other reason could be DDL. DDLs are implicitly committed. (Actually it commits before and after the DDL statement itself, so beware if you are mixing DMLs and DDLs).

Now, typically a DDL could be ended with "/". For e.g.,

INSERT INTO department(100, 'HR');

CREATE TABLE employee(

emp_id NUMBER,

emp_name VARCHAR2(40)

/

In the above case, department  table will have the entry, even if the CREATE TABLE below failed. In generla, any DML (like INSERT/UPDATE/DELETE) before a DDL, DML would have been committed, even if the DDL itself failed. Since "/" is the last statement here, one could think, "/" did it!!! Another strike against "/".

So, trust me folks! "/" doesn't do anything else, except sending a SQL to Oracle in SQL*Plus. It's just a shortcut for Run Command. But, depending on the situation, it may look like it's doing something more. In such cases, analyze the SQL and try to rearrange them. Try to keep DMLs separate from DDLs to avoid surprises. And of course, set some standards for punctuations and stick to it to avoid any undesired effects.

Oracle: DDL and Forward Slash

In this blog,  I write about various technologies I come across. Only a few topics seem to get a lot of attention. One of them happens to be Forward slash in (Oracle) SQL. I've already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding of it's usage. A search through the web quickly reveals that forward slash has been a source of confusion even among some of the experienced Oracle SQL developers ¹. Based on the search terms, I can see the newcomers are often getting confused by the SQLs left for them by their predecessors and are looking for a clear explanation. "/" is often mixed in various statements, such as DDL, COMMIT etc and this leads to confusion about the statement itself. I will try to explain the role of "/" (or lack thereof) in each different scenario in separate posts.

One of the search term that I get a lot is "DDL and Forward slash" or some variation of it. Forward slash has nothing to do with DDL per se. If you saw my other posts, it is just a statement executor (RUN command for both SQL and PL/SQL). Unfortunately, semi-colon doubles as statement terminator and executor for SQLs (poor choice of shortcut) in SQL*Plus. There lies the confusion. Thus, the CREATE TABLE or DROP VIEW statements can be terminated by a semi-colon as well as "/".

For e.g.,

CREATE TABLE employee(
    emp_id NUMBER,
    emp_name VARCHAR2(40));

and

CREATE TABLE employee(
    emp_id NUMBER,
    emp_name VARCHAR2(40))
/

are identical as for SQL*Plus is concerned. But, as a matter of preference, people tend to use "/" in DDL.

But this is not the case in other DDL statements that involve PL/SQL:

CREATE OR REPLACE FUNCTION f_get_employee(a_emp_id NUMBER)
RETURN VARCHAR2
AS
    v_emp_name VARCHAR2(40);
BEGIN
   SELECT emp_name
   INTO v_emp_name
   FROM employee
   WHERE emp_id = a_emp_id;
END;
/

Here "/" is a must, as the above statement is one PL/SQL statement (from CREATE to END;). Remember, PL/SQL block ends with END; and it has to be followed by "/" to be executed ^{2 (see notes below)}.  Thus some DDL statements (those that create programming constructs like packages, procedures, triggers) require "/" at the end. This, I think, led the newcomers to believe there is a link between "/" and DDL!!! There is no connection! You cannot generalize the use of "/" for all DDLs. For e.g., if you tried to do this (semi-colon followed by "/") in a CREATE TABLE statement, you will have unexpected behavior:

SQL> CREATE TABLE employee(
2 emp_id NUMBER,
3 emp_name VARCHAR2(40));
Table created.SQL> /
CREATE TABLE employee(
*
ERROR at line 1:
ORA-00955: name is already used by an existing objectSQL>

In this case, the semi-colon followed by slash is a bad idea.

In a nutshell, use only semi-colon or slash at the end of a SQL (DML or DDL) and semi-colon followed by slash at the end of a PL/SQL. See here for the difference between SQL and PL/SQL).

As a personal choice, I normally use "/" for all DDLs (SQL or PL/SQL) and semi-colon for all DMLs. You can have one DDL per file in which case, the "/" is the last character in the file. You can also have multiple DDLs in a file, each separated by "/".  Try not to mix DDLs and DMLs in the same file! This may lead to more confusion and errors!

References

http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

http://docs.oracle.com/html/A90108_01/sqcmd.htm

Notes:

---

1. See here for e.g., https://forums.oracle.com/thread/1020117

2. When I say "executed", I mean the PL/SQL block that creates the function/procedure is executed - meaning the program is compiled and stored in the database. (Hence the name stored programs.) The actual program execution is done by

SELECT <function name> FROM dual;

OR EXEC <procedure name>

Oracle: Forward Slash in SQL*Plus - Take 2

Take 2?
I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the traffic. Believe it or not, I still get a lot of traffic to this blog site solely based on this one little topic!! So, I thought I would spend a little more time on this.

Wordpress has a nice feature in the Stats page that shows the Search engine terms that drove the user to my page. This is what some enters in say, Google and clicks Search. Here are some sample terms on Slash:

sqlplus forward slash
sql forward slash
forward slash in sql
oracle forward slash
oracle sql forward slash
slash in sql
sql slash
.....
commit vs forward slash
oracle pl/sql semi-colon or forward slash
oracle slash before commit
oracle sql commit slash
forward slash in ddl file

and so many variations of these. One thing that caught my attention (in bold) was the confusion user(s) had with commit and Forward slash. They really don't have anything in common, but I guess Oracle behavior in different scenarios could lead one to that. So, I thought of explaining these one more time.

Then Vendor said: Let there be a Database and a (client) Tool

Let's start from the beginning. A database system is typically run on a remote server. To work with it, the vendors - like Oracle, Sybase etc- provide a client tool. Oracle has always come with SQL*Plus as a client tool. This is a simple command line tool, yet powerful. People use it for both interactive and batch use. Oracle also came up with "better" tools eventually, like iSQL*Plus, SQL Developer etc. Similarly, Sybase has Interactive SQL. Apart from these tools, there are plenty of tools available from outside vendors. Some prominent ones for Oracle are Toad, SQLTools, PLSQL Developer etc. The modern tools are typically GUI based and provide plenty of options for interactive usage. Irrespective of that, SQL*Plus is still going strong. Several DBAs prefer this tool to flashy new GUI tools, so whichever tool you use, you may want to make sure your SQL will run SQL*Plus.

Why those Punctuation?
In a GUI tool, you can type up a SQL and click on a button (or press a key), the tool will get the SQL executed (send it to Oracle server, get the results etc). If there are multiple SQLs, you just highlight one and click and go. Now, SQL*Plus is a command line tool. It's text based and often used in both interactive and batch modes. You couldn't really click a button or press a key to execute SQLs. Especially, if it's running SQLs from a file. Then you need a marker to show the end of each SQL, right? That, my friend, is the semi-colon - the statement terminator. Sort of like the period at the end of an English statement. Semi-colon is mentioned as the statement terminator in the SQL Standards books also.

Unfortunately, SQL Standards came much later. Database vendors already came up with their way of telling their tool to execute the SQLs. For e.g., client tools for Sybase and Microsoft SQL Server use go command. That makes sense. Type in a SQL. Key in go and it goes! (SQL is sent to server to be executed etc). Oracle (SQL*Plus) had a similar command - RUN!! So, if you are running SQLs in a file, you would have a SQL followed by RUN and then the next SQL followed by RUN and so on. This is good. But then Oracle decided to give us the short cuts. Like Forward Slash ("/") for RUN¹. (Those early days, every byte counted. Why type 3 chars for RUN and why store so many of them in a file? Slash will do it for cheaper price!!).

---

1. Actually RUN = Load & Run and the shortcut for it is "R" while "/" is to just "Run" what's already loaded

What does Forward Slash mean?

So, there you have it. Forward Slash executes a SQL before it. If it was left like that, things probably would have been simpler. Oracle also decided to support the statement terminator, semi-colon. (Sybase and other databases did not require this earlier). Now, you have a semi-colon that could get the SQL executed and a Slash that could do the same thing. They are not the same, but served the same purpose of indicating end of a SQL statement in SQL*Plus!! This started the trouble, especially when people started mixing them. Imagine what happens when you have a semi-colon and a forward slash?

Then came PL/SQL. Now, PL/SQL is a programming language in Oracle which can have several embedded SQLs, often separated by you guessed it - semi-colon - the statement terminators. Now you can have a bunch of PL/SQL block in a file, how do you distinguish each block? Don't you need something like a PL/SQL statement terminator? Well, semi-colon is already taken. So, Oracle came up with the ingenious way of reusing a character that already had a purpose - you guessed it, the Forward Slash!!!!! It was meant to be "RUN" the SQL, right? Now, it also has the purpose to terminate PL/SQL. (It could also mean RUN the PL/SQL). That paved the way to all the troubles we face in mixing these up.

Semi-colons and Forward Slashes

That mixture combined with SQL*Plus's own quirks made it worse. In SQL*Plus, you can type a (SQL like) text and end it with semi-colon, it will pass it on to the database. You type a SQL followed by slash ("/"), it will be sent to the server as well. What happens when you have a semi-colon and slash? It gets executed twice. What happens when you have 2 semicolons? You might get an error on the second semi-colon. This is because of the way SQL*Plus works. As soon as you run a SQL, SQL*Plus stores it away in a buffer. Next time you type "/", it re-executes what's already stored in the buffer. Type "/" again, it runs it again. Until you type up a new SQL. You can smell trouble, right? People run into trouble because they don't understand the nuances or simply because they mistyped.

Remember, "/" is a command. So, it has to be on a line by itself. Semi-colon on the other hand, has to be the last character on a line. If there is anything else after that, even a comment (--) or another semi-colon will cause an error!!

DML, DDL etc
When I mentioned SQL above, I meant any type of SQL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE etc. First 4 deal with data in tables. SELECT is a query. INSERT, UPDATE, DELETE make up the DML. There are whole bunch of SQL statements like CREATE TABLE that actually define the structure of a database object (here TABLE). These are called Database Definition Language (DDL). What we saw above applies to both DMLs and DDLs. You can use semi-colon to end DML statements as well as DDL statements. You can use Forward Slash with DML and DDL statements.

To confuse you a bit, general convention in Oracle world is to use Forward Slash to end DDL statements. This would make sense if you look at the syntax for creating Stored Procedures, Functions etc. These are PL/SQL objects. Remember, I said PL/SQL needs to be ended in Forward Slash? Now that we decided to (have to) use Forward Slash for DDL for Stored programs, why not use it for all DDLs? So, there you have it. This is how we arrived at Semi-colon (";") to end DML statements and Forward Slash ("/") for DDLs.

How did COMMIT enter this discussion about Forward Slash?

Phew! We got through that fine. Now, where does commit come into the picture and why confusion about it? Typically, Oracle has Auto-commit option turned off by default. You will have to issue an explicit COMMIT to commit a transaction. (This helps us to have bunch of related DMLs inside a transaction).

Here again there are some short cuts and quirks in the tool that made it more confusing. If you didn't add an explicit COMMIT and simply exit SQL*Plus, the tool will issue the COMMIT for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it's automatically committed and DDLs typically end with Forward Slash ("/"). Now, I can imagine why the user searched for a connection between Commit and Slash. He/She is probably running a script that has "/" at the end. The user probably ran the script and when SQL*Plus exited, it probably committed. Are you seeing the connection now? Really, there is no connection between "/" and COMMIT. They just look to be related because of quirks and twists in the tool and the specs.

What was all the blabbering about?

In summary, Forward slash is like go in SQL Server, but use with caution, as it's not only the RUN command, but could be indirectly construed as a statement terminator as well. Forward Slash ("/") does not commit. SQL*Plus may silently COMMIT your transaction when you exit. But, always add COMMIT and ROLLBACK explicitly.

And Remember, all this is specifically applicable to SQL*Plus. But, since it has become a de facto standard for running SQLs from a file, you make sure to follow these standards to avoid surprises.

And that mumbo-jumbo applies only to Oracle SQL or PL/SQL. Forward slash doesn't have this type of special meaning in other databases!!!!

Windows Automation Tools/Scripts

After the successful run with JMeter earlier to load test our web pages, I have been on a mission to find an open source tool to automate our GUI application. So far, I have not found any good (free) open source tool. But, I stumbled upon several nice tools and scripting languages to automate windows GUI application.

JMeter was very good for the Web. Since each web page can be generated (HTML), the tool could simulate page to page navigation well. If only we can do this for the GUI screens? It's all compiled code and a GUI doesn't look like it can be tampered with (until I found below scripts). So, you essentially need to work on it like a black box, so you can automate a GUI application only by capturing it's external interfaces - mouse clicks, keys types and windows messages to and from the application.

Sikuli

I started with Sikuli. This is a great tool/script. It's Python based and lets you capture screen shots of different parts of the screen and drive your script by looking for these images. They call it Computer Vision. You can build a library of standard images (For e.g., Button with the label OK) and reuse them across the application(s). As long as your application has GUI standards, this will work out great. It's a great concept and it seems to work decently.

[caption id="attachment_4847" align="alignleft" width="1152"] Fig 1. Sikuli IDE. Notice the version # in the title r930 is the latest and stable version.[/caption]

Sikuli IDE lets you capture screenshots from within and use these in functions For e.g., wait function, waits for a screen matching the screenshot to appear. After you create your script in IDE and save it, the underlying script is actually saved as a Python script, with the all the images saved.

[caption id="attachment_4848" align="alignnone" width="588"] Fig 2. Corresponding Python Script[/caption]

Sikuli has a full array of built in (global) functions. See documentation for more.

Note: Above script has UDF - user defined functions. When you record script through AutoIt3 Recorder, it will have one long script. I've divided these into functions.

Sikuli is a great tool. It deserves more research and I reserve it for future. I am more of a scripting guy and I like text based scripts. (It's easier to find and replace globally and you can even write a script to generate another). So, I continued my search for my text based scripting language. Also, I had some issue with the installation. Though it worked initially, the runtime (a batch file running Java jar) kept getting corrupted for some reason. Then I tried their portable version, it seemed to work OK so far. But, if you are having issues with Sikuli installation, please see here, here, here, here. I did not see any recorder for Sikuli, though I saw references to one all over the web.

AutoIt3
Then I landed on AutoIt. It's great tool. I've really grown to like it. It has a BASIC kind of language (actually more like PERL, C etc) and lets you program everything from mouse clicks to taking complete control of a GUI application window. Apparently original AutoIt reached version 2 and stopped. There is a new branch called AutoIt3. This version is bigger and better than previous versions. See here for full history of the product.

AutoIt3 scripts can be edited in integrated Scite Editor. All popular editors like Notepad++ also support Autoscript syntax. AutoIt3 comes with a Recorder as well. I was able to run our application and record it into a script that I could use repeatedly. I am just beginning to explore this tool. I will post more as I explore the tool further.

Fig 3 AutoIt3 Script edited in Notepadd++

When you install AutoIt3, the commands are integrated into Windows Explorer menu. You can edit or Run script from here. Edit opens Scite Editor by default.

[caption id="attachment_4850" align="aligncenter" width="300"] Fig 4. Au3 Commands integrated into Windows Explorer menu[/caption]

Autoit3 Samples

AutoIt site has a very good forum with lots of skilled people supporting it. A section called "Example Scripts" provides lots of good sample scripts. For a real dive into AutoIt3, take a look at a script called Control Viewer. The author has taken the pains to develop it in AutoIt3 itself. It shows you how to create GUI in Autoscript, and using this you can identify controls (widgets) on any windows application currently running. (AutoIt3 comes with AutoInfo which has similar functionality, but CV has it better!). For some reason the author has discontinued it, but you can down the EXE and the scripts from the main page.

[caption id="attachment_4851" align="aligncenter" width="209"] Fig 5. Control Viewer window - GUI built in AutoIt3 script![/caption]

AutoHotKey (AHK)
The third product I looked at, but haven't done much with, is AutoHotKey (AHK). Apparently it combines strengths of 2 different programs, AutoIt (older version) and Hotkey. One immediate observation about AHK scripts was that it didn't look as structured as AutoIt scripts. But, I could be wrong. There are debates about AutoIt vs AHK. See here for a comparison, though somewhat old. AutoIt3 wins hands down for me at the moment.

I will post more as I explore any of these tools for use in test automation. If you are interested, here are some interesting links about the topic:

References

http://www.autoitscript.com/site/

http://www.autoitscript.com/autoit3/docs/
http://www.autoitscript.com/autoit3/docs/intro/dev_history.htm
http://www.autoitscript.com/forum/forum/9-example-scripts/

http://www.sikuli.org/
http://www.javaedge.com/pdf/Sikuli-Gennadi-Zimmerman.pdf
http://tux2323.blogspot.com/2011/06/sikuli-gui-test-automation-with-java.html
http://groups.csail.mit.edu/uid/other-pubs/uist2011-sikuli-guide.pdf
http://code.google.com/p/simplesikuli/
http://stackoverflow.com/questions/2125839/has-anybody-used-sikuli-for-testing-their-gui-based-apps

http://www.autohotkey.com/
http://paperlined.org/apps/autohotkey/autoit_and_autohotkey.html

http://www.scintilla.org/SciTERegEx.html

Software State of Affairs - Tools of Trade

Early man did a lot of things by hand, but soon started inventing tools (Simple machines in physics) to help him do things quicker and easier. Modern tools are way too complex than these early predecessors.

Tools of Trade¹

In the modern world, we depend on tools so much, we don't even realize. See this little Physics activity page to see how many you may be using. Without tools, our lifestyles as we know will cease to exist. Every trade has associated tools, which we will call Tools of trade (In legalese it has a more restrictive meaning. See note 1). When we call a plumber, we expect him to come with the tools necessary, like the wrench etc. The electricians use different set of tools of which, voltage tester may be the most important. If they don't have the right tools, we know what will happen. At the very least, you will be looking up Yellow Pages to call the next technician.








Software Tool Kit

IT field borrows a lot of terminologies from other fields. We are engineers and technicians and architects that "build" software products, just that you won't be able to see the finished products except on your computer screen. While building, running, testing software products, we use a lot of tools. Like the product they help to build, these tools are software themselves. We use Compilers, Editors, Linkers, Runtime etc. to build and execute our software. But, unlike a plumber going to Home Depot to get his tools, we could simply download it. At the same time, while a plumber's tool chest may always look alike, our tool chest varies depending on the field and the software we build. And unlike a plumber or an electrician, we have the luxury of building our own tools, more so now in the age of Open Source.

Tools I use

As a consultant, I am constantly exposed to different tools at different sites and for some tasks, I tend to keep my own time tested tools. Here are some Open Source tools, I currently use. Often times, I use more than one tool for a task and combine the results. These are mostly for Windows environment. I will share other tools and for Linux separately.

Windows Grep I use it to search for text in files. Same like what Notepad++ or Textpad would search, but has a little more flexibility like the grep.

Many years ago, I used Examdiff extensively. Lately, WinMerge is the diff tool I use most frequently. WinMerge helps me compare at directory level and helps to merge files quickly. There are some limitations, but I love it. I also use K-Diff3 and CSDiff as each of these offer something unique. These are all free software. Another great diff tool I found was Beyond Compare. My next software purchase would be this. The above tools don't provide a good report for directory compare (Beyond Compare may). To do this, I found diffUtils and a nice script I found on the internet, to format the output in HTML.

Greenshot This is the open source screen capture tool I use. Pikpik is a great tool as well. Again, each of these have some feature that others lack.

OpenCommandPromptHere There are several names like Command Prompt Here, SendtoToys etc. Essentially, creates a context sensitive menu in Windows Explorer, so you can open command prompt from where ever you are in Explorer. (you don't have to CD into any directory manually).

BareTail I recommended this earlier; I use this to tail Jaguar.log etc. It's really good. You can highlight lines with different phrases.

YEd This I use a lot to do diagramming. It's more a graph editor than a Graphics editor. Works for me. While researching on diagramming, I found UMLet to be interesting. Dot, Dotty and GraphViz help me with automatically generating some flow diagrams.

Apart from these I use Ant, Eclipse, JMeter, Xampp etc as part of the my development environment. I also use Libre Office (Open Office's successor) a lot.

Tools I build

I am always looking for ways to make the computer do the work for me, instead of doing things manually. I feel, if a task has to be done more than once, it deserves a script or a tool. When the tools available are not good enough for a particular task, I try to build some on my own as well. But this is also an excellent opportunity to learn new techniques and keep your skills sharp. I do these during down times and/or as part of the main task.

I am sharing some of my tool building experiences here, hoping it would benefit someone else. If you are a Software developer and somewhat less motivated with the stuff you are working on, give tool building a try next time. The software will benefit from it too. All you need is some imagination and some laziness to resist doing anything manually! Gone are the days, when you had to purchase special software to build such tools. You will be amazed to see how much programming capabilities a standard computer can offer you. For e.g., if you have Word or Excel, you can program build powerful macros (VBA programs). Apart from these, you always have Open Source  scripting languages like PERL, Python, PHP etc.

If you recognize early enough, the tools you build could become integral part of a project as well. At Velos, while working on the task of upgrading the database (Sybase SQL Anywhere) for a newer specification, I decided convert the specification into an Access Database. Once the new Spec. was in a database, I was able to compare existing database elements with the new ones and voilà!, the database upgrade script could be generated right out of the database itself. They had a Powerbuilder application to design and save screens (datawindows) in database (blob columns). After going through problems with the blob, decided to write a tool to view, edit, correct screens outside of the application. Few more times of using this, I ended up in writing a simple scripting language to perform these same tasks in a batch mode. Necessity is the mother of invention! Several of these tools were actually used by the support person to identify and fix issues at customer sites.

Some of the casual tools I build for one time use, end up as permanent additions to the team's tool chest. At Capital group, one developer was collecting batch job stats on a daily basis, and when he went on vacation, he gave a page long instructions on how to get these details in Excel. The task was, to  go to the web pages (someone with tool mentality, pulled the stats from Autosys into the page, using CGI-PERL), copy the job names to a spreadsheet and make adjustments to be presentable. When my turn came, I got tired of copying, pasting and correcting pretty quickly. Before the next time, I added a "Web Query" in Excel to pull up the web pages directly into the spreadsheet and wrote some VBA macros to clean up and present the spreadsheets. Eventually, it became full-fledged product in it's own right, everyone in the team started using it. Page long instructions gone. (Of course, when I left, I left a few pages long document about the macro, but that's another story).

Similarly, in the same company there was another task, developers and/or the analysts had to do. Every now and then company received fund codes from outside (at the time I didn't know much, these are the GICS codes we received from MSCI), that had to be reconciled against codes that existed in the system already. Some codes may be replaced with new ones, some may get new descriptions, some may be discontinued. So, they would enter these "changes" in a spreadsheet or word document and then ask a developer to convert these to SQL (INSERT/UPDATE/DELETE) statements to be put into production. Enter yours truly, the lazy user. I wrote some excel macros that would import the data from the Oracle table (using ODBC), reconcile and generate the SQLs for the developer to run in production. Yes, it did take a little more time to develop and test it, but next time similar request came, it was a breeze.


An Excel macro dubbed "Query Tool", I developed at Capital Group tops it all. It again started as a manual task. Goal was to identify some codes (for e.g., security codes) from another system and see if it existed in our database. This was done manually too. User would extract data from the table into a spreadsheet and look up each code. Sometimes, codes might have been mistyped, so they have to match partially or look at other attributes to identify and correct. At the end of this, they may have gone through a spreadsheet with 1000's of codes in several passes, identifying codes from another list they had. Time consuming, laborious and error prone. If this is not what the computer does best, what else? So, I started coding the excel macros to do the repeated search of the codes they entered. Little by little, it became a full fledged  querying tool (using Database Query - through ODBC - in Excel). The tab containing the actual SQLs helped customized the lists further. What resulted was a nice product. The users loved it.

These one-off solutions may also help you save a project. At FHLB, we were working on a data extraction project. This was part of the data reporting requirements of the bank set by FHFB. I created an Access Database as a prototype to a much bigger data-warehouse built in Oracle by a team of ETL developers. I kept this as a tool to validate the data/report from the ETL tools, before we sent the data to FHFB. All was going well, but suddenly one day, we found a flaw in the Data-warehouse design and the multi-million dollar project simply collapsed. Luckily my boss saw this risk early, supported my efforts to spruce up my Access Database. Believe it or not, my little tool eventually became the official "data reporting tool" for the company, at least the first year.

Journey Continues..

Recently, one developer mentioned about how to check a file in Textpad for a record with odd length. This was the idea behind this PERL script. Another opportunity for such a script was where a batch program creates a large file (XML in this case) that had to be split into smaller chunks. Perfect candidate again for a PERL or a Python script. I did a quick prototype, but it died down for lack of interest. I hear they do it with some tools like access DB and a lot of manual "pulling the hair". And then there are tasks like parsing logs and so forth. A well written script can analyze and report stats from these files, in a jiffy.

The other day, we were discussing testing for a Database upgrade. I brought up the topic of monitoring and my boss said, "you can make yet another tool for it"! I know he was kidding, but I probably will or if I am in luck, I may be able to just download something from the internet. The journey continues!

---

1. Tools of Trade has a different meaning in legalese. It's the tools your livelihood depends on, so in case of bankruptcy, they will not be taken away by creditors.

Quick Tip: How to exit from SQL*Plus on command line

This is about running SQL*Plus in a batch mode. Suppose you have a script (shell or batch file) that calls SQL*Plus to run a SQL in a batch mode. I am talking about running it from Command line, thus:

$ sqlplus <user_id/password>@SID @<sql_file_name)

Chances, you will want to exit SQL*Plus as soon as the script is done (EOF file is reached in SQL file), so we can continue our processing.

Typically, we add an EXIT statement at the end of the SQL file itself and this will force SQL*Plus to quit. What if you forgot or couldn't add EXIT (you use the same script in different scenarios). If you don't have an EXIT or QUIT statement at the end of your SQL file, you will end up seeing the SQL Prompt:

SQL>


Here is a quick tip to exit Sql*Plus after it's done with the script:

exit | sqlplus <user_id/password>@SID @<sql_file_name)

(That's it. Essentially piping exit into sqlplus command! When the End of file is reached, SQL*Plus returns to the shell and your shell script can go on!

This tip works on both DOS (Windows command prompt)_ and *nix systems.

Quick Tip - Oracle on the net (APEX)

While trying to come up with sample SQLs for the previous post, I wanted to test them before posting them. I googled for tips online like always. I found Oracle Application Express. All you have to do is enter a name for the Workspace you would like and an e-mail address. Then they send you an e-mail to validate. When you click on it, they ask you some more details and a reason why you should be given access. Once I completed this, I got another e-mail with temporary password. When I logged in the first time, I had to change the password. After this, it asks me for database name and that's it. I was able to try a few SQLs quickly!

[caption id="attachment_596" align="aligncenter" width="474"] Oracle Application Express[/caption]

So far, I've only tried SQL Workshop menu. This has a nice SQL interface like SQL*Plus, only simpler. And it's free!! I am impressed. Apparently, Application Express (or APEX as it is known) is much bigger than what I saw. See this link for more on this nice tool.

Quick Tip - *nix: Back Quote

While researching on an issue with a Java Stored procedure at work recently, I had to go to oracle udump^† directory to find out if there was any errors. To do this, we do grep like,

grep -i -l "nullpointer" *

to list the names of all log files that may contain NullPointer exceptions^‡. -i = ignore case, -l = list only file names.

This tip is to be able to use that list in another command using back-quote (`). This is the character (also called grave accent) before 1 on PC keyboards, if you haven't used it. Back-quote is used for command substitution in *nix (unix, linux) systems. It's available in

For eg, to list those files with full directory listing:

ls -lt `grep -i -l "nullpointer" *`

Or to open the trace files found by grep, all at once in vi:

vi `grep -i -l "nullpointer" *`

(and then use :n in vi to go through the files)

Below line copies the found files out to another directory,

cp `grep -i -l "nullpointer" *` ~/nullsod

(where nullsod is a directory in my home directory.)

Command Substitution (using Back quote)

This process of executing commands inside back-quote is called command substitution. The command inside the back-quote is executed first and the output is plugged into the commandline that contains it. For e.g.,

echo `date`

returns System Date. Notice a back-quoted command is mostly embedded inside another. If you miss the echo there, it will try to return the date string to the shell which it will try to execute as a command which results in "Command not found" error.

Back-quote is often used in scripts to set the result into a variable. For e.g., we can save the date value in a variable called dt. Then we can use this variable in script.

$> export dt=`date +"%m%d%C%y%H%M%S"`
$> echo "Date is $dt"
$> cp output.log output.log."$dt"

#Where, $> is the shell prompt

In the above list, Date is executed with a specific format (mmddyyyhhmiss). This is then stored in a variable called dt. First we echo dt. Then we use dt to rename a file with date suffix, very handy when you are running a script on a regular basis and want to save the output in timestamped logs.

Incidentally, the statements above, show another kind of substitution, a string or variable substitution using double quotes("). Any expression inside a double quote is often evaluated first and substituted in the command that uses it. Notice the difference: double quote simply evaluates a string/variable and plugs in the value. A back-quote on the other hand, actually executes the string inside as a command and returns the  output to the enclosing command.

You can combine many of these expressions, and that's what makes it all powerful. If you haven't tried back-quote yet, try it. You will be glad you did!

Useful links

1. Command Substitution in Unix


2. Unix Shell Substitutions


3. Advanced Bash Scripting

---

† udump - user dump - contains user trace files in Oracle database; all print statements from stored programs in Oracle (Java, PL/SQL) end up here.
‡ udump directory may have a large # of files depending on activity, in which case, grep may not work with wild card (*). You may have to refine the grep parms.

Quick Tip - *nix: ulimit

Last week one of our DBAs called me. She was trying to install Oracle 11g. She obtained zip files for the installation from another co-worker. This had many zip files, probably one per installation disk. She was trying to unzip the files and got some error.

Like always, I got down to business of checking and googling. When I tried to unzip the first file, it gave an error as to missing zip file headers. It suggested that the file could be part of a multi-part zip file. Good old google suggested how to combine a multi-part zip file. Just cat each file into one big file, then unzip it:

cat file1.zip file1.zip file3.zip > fullfile.zip

Unzip failed on the fullfile.zip. When I checked the file size, it had he same size as the 1st file, the largest file. Strangely, they both had the size of about 1GB. More googling revealed role of ulimit!

Resource limits on *NIX systems (ulimit)

All *nix systems (all flavors of Unix and Linux) have a limits on system resources per user^†. The limits include cpu time per process, number of processes, largest file size, memory size, number of file handles etc (file size is defined in number of blocks.). The root user gets unlimited resources, but all others are assigned a finite settings as defined in /etc/security/limits on AIX. (/etc/security/limits.conf in Ubuntu). Here is a good post, as to why you want to limit resources for users. To check the limits, use ulimit command.

ulimit -a

There are Hard limits (ulimit -Ha) set by the root and Soft limits (ulimit -Sa) a user can set herself.  See this blog for details on using ulimit.

Role of ulimit in our problem

Continuing our story about faile Oracle install above, when we did ulimit -a, one particular entry caught our attention. file size was set to 1GB (approx., remember it's set in blocks?). Bingo, this is why when we try to create the fullfile.zip, it got truncated at 1GB, the limit for the user. Apparently, the DBAs are supposed to have unlimited file size. She got onto calling the Unix admin.

To cut the story short, apparently ulimit played a role in getting the original zip files as well. When she downloaded the files, it truncated the biggest of files to 1GB each. Our first file happened to be one and that's why it looked incomplete. To make matters worse unzip and gzip ended in pointing to a multi-part zip file, which they weren't.

Like I said, this is true for all *nix systems. This is very powerful, in controlling runaway processes sucking system resources away. But there may be a legitimate situation like above, where you need to override the settings. So, next time your file seem corrupted or truncated, remember to check ulimit.

References

1. ulimit in IBM AIX


2. ulimit on HP-UX


3. ulimit on Linux - blog


4. ulimit on Unix - blog

---

†Depending on the *nix you are using, this may be variously mentioned as limits per user, per shell and it's descendants. Some of the settings like cpu time is applied to individual processes user/shell is running.

Quick Tip - Oracle SQL*Plus: HTML output

Recently, I posted about SQL*Plus, a database querying tool from Oracle. Like I mentioned there, the tool is rudimentary and has some basic file handling capabilities. It has a command, SPOOL, to write the console output to a file. Traditionally, you only spooled the output to a text file. Some releases ago (I believe in 8i), Oracle included option to spool the output in HTML format. Useful when you are dumping data from tables (Have you tried dumping table data in plain text file format??) and for generating simple reports.

To generate HTML output from SQL*Plus, all you have to do is add markup option either on command line or inside the script. By the nature of it, it is only useful when you are spooling the output. Just add below to the script before spooling: and the output will be spooled to the file mentioned  in HTML format.

SET MARKUP HTM ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>My TABLE Listing</TITLE> -
<STYLE type='text/css'> -<!-- BODY {background: #FFFFFF} --> -
</STYLE>" -
BODY "TEXT='#000FF'" -
TABLE "WIDTH='90%' BORDER='5'"

...
spool dump_table.html

...

SPOOL off

That's it. Just be aware that the output file will be larger than the usual text file spool. By the way, the hyphen "-" at the end of each line is the line continuation character in SQL*Plus. So, it's really one line command with several options.

Alternatively, you can specify the HTML option on the commandline as shown below:

sqlplus -S -M "HTML ON" <user>/<password>@<SID> @dump_table.sql > dump_table.html

The option -M "HTML ON" is equivalent to MARKUP HTML ON in the script above. The option, -S is for silent, which turns off spurious output from SQL*Plus.

 

See here for more about creating HTML reports using SQL*Plus.

File Checking - Perl to the rescue

One of my coworkers posted about an issue with one of the data files we receive from outside world. We have a batch program that processes these files and posts transactions into our system. The issue was that a file had one record that an extra byte and any amount of checks didn't reveal the "bad" row. After different checks, he decided to download to PC, take it into TextPad and turned on visual spaces to find which row had that extra space.  Very tedious, but it works.

As you may know, Perl is a very good scripting tool for such purposes. I've created a sample perl script that does basic checks from file size to record size. If it finds any discrepancy, it prints the line #s and records. (See sample output below). For those interested, I'm attaching a copy (text version of the script) here for reference.

(If you are uploading to unix, you need to chmod +x to make it executable).

Please let me know, if you want more information. Feel free to change the script as needed (but please send me a copy, so I can keep mine updated).

Sample Usage:

/tmp/chkfile.pl FIN_09022011_131353.txt 259

1st parameter is the file name and the second parm is expected record size.

Sample Output:

$/home/svaradar/dev/perl
$ /tmp/chkfile.pl FIN_999905_BILLPAY_09022011_131353.txt 259
Name of the file              : FIN_09022011_131353.txt
File Size                     : 1554 bytes
Record Size expected          : 259
Total # of lines in file      : 6
File appears to be a DOS file. (contains carriage returns)
All rows match!

After creating a bad record (I just "fixed" one of the record to change it to 260 chars):

$ /tmp/chkfile.pl FIN_09022011_131353.txt 259
Name of the file              : FIN_09022011_131353.txt
File Size                     : 1555 bytes
Record Size expected          : 259
Total # of lines in file      : 6
File appears to be a DOS file. (contains carriage returns)

Following rows were unmatched:
+4 -  Size: 260 - << P0001CHK0000000000000374.4600099999990001400001111              011000015                                                                                     Sample Record                                                                                     <CR>>>

<CR>- The script translates CTRL-M to printable <CR>; otherwise it would have inserted just a blank line in the output!

Below file contains the perl script in PDF file format:

chkfile.pl

Forward Slash in SQL*Plus

Note to the visitor: Thank you for stopping by. I see a lot of people trying to find out about forward slash in SQL*Plus. I want to clarify: This post only applies to Oracle SQL. If you are you are querying about a "/" usage in any other database, you are in the wrong place.

Forward Slash ("/") in Oracle SQL

This post is about using forward slash ("/") in Oracle SQL and PL/SQL. This gets a lot of developers (new and experienced alike) as it's not always needed and sometimes it shouldn't be used.

Have you ever tried typing "/" (without quotes), as soon as you logged into SQL*Plus? Try it. You will see something like below.

SQL> /
SP2-0103: Nothing in SQL buffer to run.
SQL>

The above snippet explains it all pretty well. Forward slash ("/" ) is a shortcut for RUN command in SQL*Plus (like "go" in SqlServer or mysql). Since there is nothing to run yet, SQL*Plus returned an error message (prefixed with SP2-).

The message in the above example also shows there is a SQL buffer in SQL*Plus. Any SQL sent to DB ends up in this (one statement) buffer. Also, if there is a statement in the buffer, "/" would have simply re-executed it.

When you type a line and press ENTER, SQL*Plus checks if it's one of it's own commands, a SQL statement or just bad text. If it's a SQL (identified by keywords like SELECT etc), it starts storing it in the buffer until it sees a "/" or a semi-colon, at which time it executes it (actually sends it to the server to execute it).

Gotcha with Forward Slash (/)

SQL Statements typically end in Semi-colon. Semi-colon is the Statement Terminator in ANSI standard. But, in Oracle SQL, you can use "/" to run a SQL, instead of semicolon, like shown. (This is how SPANISH record got inserted in my attached example at the bottom of this post).

SQL> SELECT dummy FROM dual
 2 /
Dummy
-----
X

But, here is the gotcha: If the statement itself contained a semi-colon, then "/" would have re-executed it!!!!

SQL> select dummy from dual;
Dummy
-----
X
SQL> /
DUMMY
----------
X

Oops. Imagine this was an INSERT statement instead!!! (And this is what happened in my example script attached. If you notice the "/" before commit, this is what caused FRENCH to be inserted twice!!).

So be careful with your usage of "/". For plain SQL, it's a matter of choice. Typically, this is used in DDL statements and semi-colons in DML statements. Be consistent to avoid surprises.

Remember "/" is just a RUN (or push) command in the tool, not part of Oracle SQL itself. If you run my attached example script in SQLTools, you will get an error on "/".

Always, run your scripts in SQL*Plus to make sure it will run fine in production, as this is a tool of choice for several DBAs.

Forward Slash ("/") in PL/SQL

PL/SQL on the other hand is a group of SQL statements with embedded semicolons. In this case, a semicolon alone cannot be used to send to DB. It needs a push with "/"! So, in this case, it acts as a Statement Terminator as well.

SQL> list
 1 DECLARE
 2    x NUMBER;
 3 BEGIN
 4    SELECT 10 as num INTO x FROM dual;
 5    Dbms_Output.put_line('x = ' || x);
 6
 7 END;
 8
 9
 10
 11
 12
 13*
SQL>

In the above example, Notice lines  7 - 13 are blank. This is because I pressed ENTER several times. Since SQL*Plus knows this a PL/SQL (declare, begin...end), it waits for me to signal the real end, which is..... a "/". Until I typed "/" on line 13, it didn't come out of the PL/SQL editing mode. "/" also pushed the PL/SQL block to server to execute it.

Even though this has a bunch of lines, this whole text is a single PL/SQL statement. Type list at the SQL*Plus Prompt. You will see the "single" statement listed in full (minus the "/").

SQL*Plus commands

SQL*Plus commands like SET, SHOW, SPOOL etc are typically one liners and should not be ended with semicolon (and don't need "/" either).

SQL> show serveroutput;
serveroutput OFF
SQL>

These commands will be executed locally in the tool and won't be stored in SQL buffer. So, a "/" after a SQL*Plus command will not re-execute it. In fact, it will re-execute the previous SQL Statement in the buffer!!

(Note: A "/" must be the first non-whitespace character (space, tab etc) on a line by itself. It cannot be at the end of a line, like semi-colon can be).

I've also posted a more detailed description of SQL*Plus here.

Notes

1. There is a subtle difference: RUN actually lists before it runs; / doesn't!


2. This is a local message from SQL*Plus. If this was from Oracle database server, you would see a "ORA" prefix.

Example Script

-- Example data for SQL*Plus related blogs
DROP TABLE greetings;
CREATE TABLE greetings (id NUMBER, lang VARCHAR2(10), msg VARCHAR2(30));
INSERT INTO greetings VALUES (1, 'ENGLISH', 'Hello World');
INSERT INTO greetings VALUES (2, 'FRENCH', 'Bonjour le monde');
/
INSERT INTO greetings VALUES (3, 'GERMAN', 'Hallo Welt');
list
/
INSERT INTO greetings VALUES (4, 'SPANISH', 'Hola Mundo')
/
COMMIT;
-- Here are some extra notes about this script:
-- Just Hello world in some languages. Translations are approx. from Google.
-- Even though we are submitting this script to SQL*Plus entirely,
--   it executes one SQL Statement at a time. List command shows this.

-- Forward Slash after FRENCH, reexecutes previous command. so you will see
-- 2 FRENCH records inserted.
-- Forward Slash after GERMAN was, by mistake, thought to run list, but instead it
-- it ran the previous SQL statement, thus we have 2 GERMANs!!
-- Forward Slash after SPANISH inserts only once - because it's missing semi-colon (;)
-- and / substitutes for it!!
-- Commit is required in Oracle SQL. By default, Autocommit is not turned on in SQL*Plus
-- To see/set default use SHOW/SET AUTOCOMMIT

Further Reading

• See my Take 2 on this topic here.


• If you are looking for any relationship between forward slash and commits (there is none), please see my post here.


• If you are looking to see if Forward Slash somehow affects DDLs, please see here.


•  Please see here for a more detailed description of SQL*Plus in general.

References

SQL BNF Grammars

1. http://savage.net.au/SQL/sql-92.bnf.html


2. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt


3. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

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

What is Powerbuilder?

Powerbuilder is an IDE, an integrated Development Environment, a tool where a programmer do everything within it, including design and develop screens/reports, write command line programs, compile, deploy applications all from within Powerbuilder.

This is in contrast to programming in languages like C or Pascal, where you use a text editor to edit, a compiler to compile and copy files to deploy.

IDE's and case tools were popular in early 90's. There were several such tools that promised "less or no coding" and yet develop and deploy enterprise applications. Powerbuilder was one of many tools available. I remember reviewing PB, Visual Basic (VB), Gupta SQL windows and Oracle CDE (successor to SQL forms in windows). Of all these, I took to PB easily, I liked SQL windows better (I remember it was more colorful and windowy!). Oracle CDE (forms 4.0) didn't measure up. And Visual Basic needed more programming.  I remember the days when VB and PB were constantly compared and contrasted. Each had it's own strength but PB won in some of the corporate contracts. The datawindow, and the quick reporting capability were probably some of the secret.

Contrary to popular belief, it's not a tool for non-programmers - managers, Business Analysts and the like. Yes, several tried, but any serious screens in PB always needed programming. To this day, I hear people brushing it as a child's play in the world of complex Java and .Net applications. But PB is used, surprisingly, in building big corporate applications, even today. And such serious development activities always involve programming. Programming in a language called Powerscript. It's basic with a twist. Long before Java became so popular, it took some tenets of C++ and became Object oriented. (OO pundits may not agree and call it more Object based than object oriented, but that's a topic for another post!). Oh, about PB for managers, Sybase made another tool called Info-maker, a trimmed down version of PB, which didn't require programming (or lacking programming, depending on who looks at it).

PB is definitely easier to use to build screens and rudimentary database applications. I feel, the user interface is definitely easier to use than that of VB, but then I've been using it for many years. The strength of Powerbuilder, as against other tools was and has been the datawindows, a compiled widget readily available to include in any windows. Strictly speaking, a datawindow is a window that may have many controls on it, but the difference is that a datawindow is managed by PB routines as against windows API. For this reason, Sybase always suggests to combine many different controls in a window into a single datawindow, wherever possible, for performance.

A datawindow widget is not much unlike ADO or DAO objects in VB. The strength of the datawindow is that it's built around SQL (what's called a datasource - there are other data sources, more later) and by keeping the SQL in PB itself and database neutral, we could make the same datawindow work with many different databases, like Oracle, Sybase, Informix etc.

PB has changed a lot over the years. People who have seen it in the 90's will definitely realize the transformation. The IDE itself started packing more in version 7.0. Now, in version 10.x and beyond, it's turned into a true development tool. (Rumor has it that PB 12.0 could actually be used as an IDE for .Net development!). The question still remains, in the world of Eclipse and Netbeans, Java and .net, why PB? That too enterprise edition costs a bundle when Java is all free. This blogger aims to ask some of those questions and try to find answers live on this blog.

Currently I am working at the City of LA where we use Powerbuilder heavily in a clustered EA Server environment. We even have extensions on the web using JSP and Coldfusion. The software is a tax software used by thousands of businesses in and around LA. This is no child's play. EA Server is to Powerbuilder like weblogic to Java. It's an application container that can run PB objects and Java objects alike and it uses CORBA (how much it reminds me of Forte UDS!).

From the simple client/server tool, PB has reinvented itself several times to become a n-tier platform here. Earlier PB turned out a non-visual object (NVO) that proved vital to this n-tier platform. An utterly client widget, datawindow, gave birth to (non-visual) datastores that eventually helped them to be n-tier objects as well.

But then again, I feel, Sybase lost several opportunities to build it up and market it as the greatest tool on earth (I don't mean exactly that, but at least the ads should say it, right?).  They are constantly toying with the ideas and drop them like a fly. Most recently, the EA framework, built by Cynergy Systems for Sybase, that can be used to build Enterprise applications in PB/EA Server environment has been discontinued. EA Server itself is dangling. You hear about PBNI, PB ORCA, I've used them too, but their future is always questionable. In the world, where every version of a software gets better with time, some of these tools inside PB appear and disappear. With PB becoming an IDE to .net, their neutrality to languages may be disappearing. And finally, with SAP buying Sybase recently, the whole existence of PB as a tool may be in jeopardy. (You think this is not possible, think what happened to Forte UDS. Sun bought this company only to kill the software, as it was a competition to Java J2EE at the time. How did they do it? They created a lousy tool called Forte for Java to erase the name Forte and eventually pulled the plug on many websites that talked about the old Forte).

We will go on a journey here in  this blog, where I will not only share my experiences with the tool, but also technical articles, how-tos about PB.