Quick Tip: Oracle Sqlplus - changing prompt

Oracle SQL*Plus has several settings that can be customized using SET command. When you run SQL*Plus, typically the following (boring?) prompt greets you.

SQL>

If you ever want to change this to something more interesting, there is a SET option for that.

SET sqlprompt 'sqlplus:&_user@&_connect_identifier > '

This will be shown as,

sqlplus:<user>@<SID>>

For e.g.,

sqlplus:scott@hr>

Where scott is a user id logged into hr database.

References

http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm#i2675128

Java Stored Procedures

I am sure you are familiar with Stored Procedures. Almost every major database vendor supports it. They are program units that are precompiled and stored inside the database. Since the program is inside the database, it is tightly coupled with the database SQL language constructs. This and the precompiled nature of these programs, make the database access faster. So, even if you are writing external programs that access the database, you will benefit from coding and combining the database operations in stored procedures. This saves a lot of back and forth and thus network traffic. Since version 7, Oracle has support for Stored Procedures. These programs are coded a in native language called PL/SQL.

Since Oracle 8i, Oracle supports coding and running similar procedures (program units) written in Java. Like PL/SQL procedures, these programs are precompiled and stored inside the database and are known as Java Stored Procedures. The JAVASPs(1) are stored as classes (in blob fields) and when invoked they are run inside a JVM that runs within Oracle database. Prior to Oracle 10g, they needed to be wrapped inside a PL/SQL procedure or package. Since Oracle 10g, you can actually invoke Java classes directly from SQL (just the same way you call a PL/SQL function in a SQL).

JavaSPs are different from regular java programs in that they actually run inside a VM within the database. There are a few restrictions while coding a JAVASP.

Building and Deploying Java SP

To write and build the Java stored procedures, you can use your standard Java development environment. I use eclipse to develop and Ant to build it

build_LATAXSP.xml has steps to compile java classes and build jar file.

Deploying Java SP

At this point the jar file is ready to be deployed to Oracle. Typically we pass this step onto the DBA who will then load the jar into Oracle Database instance. But during development, developer can load these themselves using the loadjava utility. This is typically available on the machine where the database is running. (Remember, it is run by DBAs?). In our case we have the Oracle databases running on Unix boxes, so we have loadjava utility available there. I upload the file to Unix and run loadjava. While uploading make sure it’s in binary mode.

Below screenshot shows a sample run of loadjava command on Unix.

In this example, I loaded all the classes in a jar file, to the database. As shown there were 24 classes and 2 resources loaded and there were no errors. If the command failed to load the Java classes, you will see an error message here. The first time around, all the classes files are loaded. Next successive load of the same jar file, will load only classes that have been modified since the last load.

Verifying the load

To view the objects in Oracle, following SQLs can be used:

select * from all_objects where object_type = 'JAVA CLASS' and owner = <owner>;

or

select * from user_objects where object_type = 'JAVA CLASS';

To see a little more detail about the Java stored procs, use

SELECT * FROM user_java_classes; -- this lists java procs for the user.

Earlier I posted about displaying contents of the resources files (text files) loaded above.

Notes:
(1) Oracle actually refers to Java Stored Procedure as JSP. To avoid confusion with Java Server Pages, I prefer JavaSP.

[gallery include="5064,5065"]

Quick Tip: Oracle - Commit on Exit

Oracle by default does not automatically commit a transaction (Autocommit is typically off). Transaction Management (Commit or Rollback) is left to the client as this askTom post explains it nicely. The tool you use may provide this option for you. For e.g., SQL*Plus commits any open transaction upon exit. So, even if you don't have COMMIT at the end of a script, when SQL*Plus will issue a COMMIT when the SQL is exited.

This was the default behavior for a long time and was generally accepted. Some developers wanted to have more control over it, and according to this stackoverflow post there was even a bug (633247) opened for it in 1998! Recently, in Oracle 11g this has finally been changed. Now, as of Oracle 11g R2, the user actually has an option (EXITCOMMIT) to tell SQL*Plus whether to COMMIT or ROLLBACK upon EXIT. Following statement makes SQL*Plus roll back a transaction upon exit.

set exitcommit off

See this Oracle Post for a good example.

Notes:

• One poster in the Stackoverflow post above, actually mentions about Autocommit option in SQL*Plus. I just want to clarify:

SQL*Plus does have an option to set Autocommit on or off. This is actually meant for every statement that you issue, not just the last statement before exit. Prior to Oracle 11gR2, this did not have an impact on the Commit on Exit!

• Also, this change is actually in client tool (SQL*Plus) not in the database. So, if you are using an older client (like I am), you will be disappointed not to find this option!

References

http://stackoverflow.com/questions/1368092/why-does-sqlplus-commit-on-exit

http://www.oracle.com/technetwork/articles/sql/11g-misc-091388.html

http://www.acehints.com/2011/07/oracle-11g-r2-sqlplus-set-exitcommit.html

http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:314816776423

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!!!!

Top N subcategories

Top N subcategories.

Oracle SQL - UNION ALL

Oracle SQL - UNION ALL

Here is another TechTarget article about Oracle SQL. This time about using UNION ALL to combine results of 2 SQLs to sort numbers.

Link: Confusion around PL/SQL to_date and to_char functions

See the TechTarget post below:

Confusion around PL/SQL to_date and to_char functions.

Quick Tip: tnsping

Tnsping is a nice little utility that comes with Oracle. Lot of developers don't know about this. If you have SQL*Plus available on your machine, chances are you also have tnsping. You can use this tool to troubleshoot Oracle connectivity issues, sort of like ping for TCP/IP. To use it, just type

tnsping <Service Alias> [count]

Where Service Alias is defined in tnsnames.ora.

Typically, if tnsping returns an error, chances are the alias doesn't exist in tnsnames.ora file or a typo. Just add/correct that in the file, you will be able to ping the Oracle instance.

On a Windows system, this is where an introduction to tnsnames.ora would end. But not on Unix.

Gotcha on Unix

When we had connectivity issue with Oracle 11g database this morning, we kept getting errors while trying to tnsping.I logged in with the oracle id and checked the contents of tnsping.ora file. It looked OK. Also, with this id, I was able to login to Oracle without any problem. This was a puzzle.

Then I remembered the file level permission on *nix systems. I looked at the permissions attribute of the file, tnsnames.ora (If you do a ls -l on Unix, it shows you that). Bingo! The file had didn't have "read" access for Other (public).

-rw-rw-r-- tnsping.ora

In this case, even though  the file was there, it wasn't "visible" to other id's, because of missing read permission. When I added that (r in bold), the other user was able to tnsping and connect to Oracle finally.

Note on connectivity

If tnsping is successful, it merely tells us that the SQL*Net listener is running correctly on the server side. This doesn't guarantee that the database itself is running. You need to login to find that out.

References

1. http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/


2. http://www.orafaq.com/wiki/Tnsnames.ora


3. http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TDT2/Default.aspx


4. http://docs.oracle.com/cd/B19306_01/network.102/b14212/connect.htm#sthref1535


5. http://www.dartmouth.edu/~rc/help/faq/permissions.html

Gotcha: Oracle11g Password Case Sensitivity

We are in the process of upgrading to Oracle 11g. Today, a friend at work asked about an issue he had with connecting to Oracle 11g from Unix. He was able to connect fine in SQL*Tools, SQL*Plus etc, but not when connecting from a script. The script kept failing with invalid user name or password error. The difference was that the script actually used an encrypted password and decrypted it using ccrypt utility.

We tried the decrypt option on command line, and it turns out the password was all upper case. Same script used to work in 10g. Next, we tried SQL*Plus with upper case password. It failed while the same thing worked in Oracle 10g. There we realized that Oracle 11g passwords may be case sensitive.

A beautiful post about the same, confirmed this. Apparently, passwords are case sensitive in Oracle 11g by default. This can be overridden as mentioned in the above link. Just to be complete, I am including that here:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

You can also look at Oracle page about authentication for more details. AskTom talks about this change.

It was a real gotcha for us today.

References
http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG3225
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217

Quick Tip: Java Stored Procedures in Oracle - Reading Content

This post is about how to get the contents of a resource file that is loaded into Oracle as part of Java Stored Procedures package.

While working on enhancing some Java procedures, I added a simple Java logger class, fashioned after Log4J. To be able to dynamically configure the logging properties (log levels, threshold), I added in a properties file, bundled into the Jar file itself¹. After loading the properties file into the Database (using LoadJava command; loaded as a Java Resource), I wanted a way to "see" what's inside the file. That's when I posted a question on StackOverflow, but eventually googled and found the answer. See my question and my own answer here. And here is the script, I came up with:

SET SERVEROUTPUT ON
EXEC DBMS_JAVA.SET_OUTPUT (1000000);
DECLARE
   bText CLOB; 
   len PLS_INTEGER;
   offset PLS_INTEGER;
   text VARCHAR2(2000);
BEGIN
   DBMS_LOB.CreateTemporary(bText, FALSE );
   DBMS_JAVA.Export_Resource('LAJavaSP.properties', bText);
   len := 1000; -- length of the lob to read.
   offset := 1;
   DBMS_LOB.Read(bText, len, offset, text); 
   DBMS_OUTPUT.Put_Line(text);
END;
/

This printed the following:

logLevel=ERROR
dbLogLevel=ERROR

For now, that's all I have in the properties file. Hoping to add more later.

---

1 Adding the properties file into the jar file makes it easy to read it using getSystemResourceAsStream. Otherwise, we will have to worry about Directory objects, Permissions etc.

(Project) Withdrawal Symptoms

By cartoonsv | View this Toon at ToonDoo | Create your own Toon

I must admit. I think, I am going through withdrawal symptoms. No, not that kind. I am not an alcoholic, and I don't do drugs. I do drink coffee, but definitely no withdrawal from there! This is a different kind of withdrawal. I think, I just OD'd on work and now I am feeling the project withdrawal symptoms. I did a regular day's work today, it feels like I am not doing any real work. I get out of the building, while there is some light, it feels like I am leaving too early!

I just finished a project of refactoring one of our websites for performance. It took a lot of long days and nights, incessant reading, writing, thinking. Lot of headaches, anxieties, uncertainties. At times, it looked impossible, insurmountable and the team members expressed concerns, doubts. Even the week we were getting ready, there were issues, bugs, failed tests, network issues. After all, we are in the tax (peak) season already, is this a good time to deploy such a big change? Is it going to happen? Is it worth it? It was like writing/reading a mystery novel at the same time. What is the end? I am writing it, but I am also hoping to read it ahead. Couldn't stand the suspense no more.

The number of tasks grew and grew, as I picked up slack for team members that went on vacation. Finally, it's over!! poof! gone! We installed the system Thursday night. No issues!!!!! What? No way! I was looking for issues. But none. It was a good thing, lot of planning and work went into it, yet it felt like a very bland ending of the mystery novel. Phew! No issues! It feels good. But, why does it feel like nothing happened? The rest of the team members haven't said anything yet. Did the implementation really happen? I look at the beautiful log file created by Log4J smiling at me. The changes actually helped us catch an issue, we otherwise wouldn't. Yes, we did it!!

Now, I am catching up with the rest of life. I was here, wasn't I? Calls to be made, photos to share, trips to make, people to catch up with. In the past few months, I would come home late from work, eat and jump right back on the computer, pouring over the source code, popping in and out of forums, go round and round the world wide web, eyes glued to pages of the books and the mountain of pages I printed. Now, it feels like I don't have anything to do. But, it's a nice withdrawal! No hangovers, no issues! Work, life balance? My wife thinks, I am back to life!!! Good to be back!

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.

Gotcha - Oracle: Null

NULL essentially points to an "undefined" value in the database. It helps to conserve space in database columns. It's useful when we want to store "nothing" in a column. NULL is defined in ANSI, so all databases including ORACLE implement them. But, ORACLE differs from ANSI standards in dealing with NULLs. Some of these could be real gotchas for developers coming from other databases.

Gotcha 1 - Empty String is the same as NULL in Oracle SQL

In case you didn't know, empty String (also known as null string)('') and NULL are considered the same in Oracle. This is not ANSI standard and is a real gotcha for developers coming from other databases, where they are different.

Here is a SQL I found in one of the programs I am supporting:

SELECT * FROM employee WHERE NOT((employee.dept_nbr IS NULL) AND (employee.dept_nbr = '') );

In Oracle, the above Where condition is *not* really checking for 2 different things. Seems to me, the developer wanted to make sure it was not empty and it was not null either. This is because, he/she didn't realize Null String is essentially same as NULL in Oracle.

When in doubt, I always try a simpler query than original. Oracle system tables and the built in table DUAL come in handy for those queries. Here is a sample query that shows empty string is Null.

SELECT 'empty string is null' FROM dual WHERE '' IS NULL;

Another Gotcha - a bug in the SQL above


The above example had another bug. if the NULL was not the same as '', the above query would have failed completely!! (How can same field be NULL AND something else (here null string ('')) at the same time?). The above condition must have been

SELECT * from employee WHERE NOT((employee.dept_nbr IS NULL) OR (employee.dept_nbr = '') );

-- OR even below SQL:

SELECT * from employee WHERE (NOT(employee.dept_nbr IS NULL) AND NOT(employee.dept_nbr = '') );

Gotcha 2 - Checking for NULL

Did you notice "IS NULL" there? When you check for a value being NULL, you cannot use equal to (=). You have to use IS or IS NOT. This is yet another gotcha for many new developers to Oracle. Strange thing is Oracle will not complain if you entered '' = NULL in the above SQL. It simply won't find any rows!! Who better to explain this than Ask Tom?

[caption id="attachment_585" align="alignleft" width="329"] Oracle Select "IS NULL"[/caption]

[caption id="attachment_587" align="aligncenter" width="412"] Select equal to NULL results in "no rows found"[/caption]

If you are coming other databases, this may be a surprise to you (SQL Server for e.g., allowed = NULL check). It's a real gotcha in Oracle, because Oracle won't complain if you said "= NULL", but the query may not work as expected. A query may return less number of rows than expected, because of NULLs in some fields in the where condition. For e.g., below SQL will return only 2 rows (I expected the row with NULL to show as well).

SELECT emp_nbr, dept_nbr, first_name || ' ' || last_name || '(' || dept_nbr || ')' AS employee
FROM employee WHERE employee.dept_nbr IN (NULL, 100. 200);

The above WHERE condition must be written as,

WHERE employee.dept_nbr IS NULL OR employee.dept_nbr IN (100, 200);

Another way of doing this is to use the NVL function in Oracle. NVL translates NULL value to the value passed in.

WHERE NVL(employee.dept_nbr, 0) IN (0, 100, 200);

Here NULL is translated to 0 and this we can check in equality or IN condition.

Gotcha-3 Concatenating NULL (Empty String)


When you concatenate text fields together, if any of the value is NULL, the result will *NOT* be NULL in Oracle. In other databases (for e.g., SQL Server, mysql), String + NULL results in NULL. In Oracle, only NULL + NULL results in a NULL.

SELECT emp_nbr, dept_nbr, first_name || ' ' || last_name || '(' || dept_nbr || ')' AS employee
FROM employee WHERE (employee.dept_nbr IS NULL);

In this case, if you expected NULL, you are in for surprise. The concatenation actually works. Only the NULL values will be missing. (For e.g., this will return Sam Varadar() based on the sample data in the screenshots above). Though, Oracle is not promising this in the future versions. See here.

Gotcha-4 Nulls and Indexes


Another hidden gotcha in this is that when we a query uses an index, we are implicitly applying Equality checks. If a column that is in an index has NULL values in some rows, then those rows will not be indexed and thus won't be picked up!

This also means a primary key column cannot have NULL values. This is because a primary key column value identifies each row uniquely and thus they have to be in the primary index on the table.

Other NULLs


We saw above, concatenation of strings works even if one of the values is NULL. This is because, a NULL is also considered an Empty String. Other operations may not be as kind:

For e.g., Adding NULL to a Number will result in NULL.

SELECT 1 + null FROM dual;

In PL/SQL NULL is a NULL statement - a statement that does nothing. It is often needed in control blocks, like if-then-else where we want to leave the if or else part empty, but PL/SQL syntax doesn't allow this.

IF (dept_nbr = 100) THEN
    dbms_output.put_line('Inside IF block');
ELSE
    NULL; -- Do nothing
END IF;

When sorting a query result using ORDER BY, you can sort rows with NULLs to come FIRST or LAST (default).

SELECT * FROM employee ORDER BY dept_nbr DESC NULLS FIRST;

Functions to deal with NULLs

Because of the unpredictable  behavior of queries around NULL values, it's often better to translate NULL into something meaningful, so equality (or inequality) tests can be done without worrying about NULLs.

We showed the use of NVL above. It translates NULL into a more meaningful value, 0. NVL2 is a similar function that returns one value for NULL and another for Non-NULL values. COALESCE is another function that could be passed in a series of comma separated values and it will return the first non-NULL value in the list.

These functions can also be used while creating index on a column that may have NULLs and thus help with performance also. See here for more on this.

Other functions include NULLIF, DECODE etc. See here for a nice discussion of the functions related to NULL.

References

1. http://psoug.org/reference/null.html


2. http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null


3. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17320984423926


4. http://www.sqlines.com/oracle/string_concat


5. http://www.oracle-base.com/articles/misc/null-related-functions.php


6. http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

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.

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 Gotcha - Commit in DDL

This happened to one of my fellow developers recently. She had an ALTER statement in the middle of a SQL script that produced an unexpected results when the SQL failed. The problem was mixing DMLs with DDLs in the same script. Read on!

As you probably know, Commits work differently in DMLs and DDLs in Oracle.

• Typically* DML statements (like INSERT, UPDATE, MERGE, DELETE...) needs an explicit COMMIT statement to commit changes to DB.

• DDL statements (like CREATE, CREATE TABLE AS (CTAS), DROP, TRUNCATE etc) don't require a COMMIT statement. DDL actually issues an implicit COMMIT before and after the statement is executed.  This also means, any COMMIT statement after a DDL is not required and has no effect.

So, if you are mixing DMLs and DDLs, be mindful of this. If you have a DML (insert/update...) statement, followed by a DDL statement, you might have inadvertently committed earlier DML. Any later rollbacks won't have any effect.

Here is an example. Test 1 doesn't mix DMLs and DDLs. (There is no DDL immediately after DML). Test 2 on the other hand has a bug in that it has a DDL after the DML statement which results in unpredictable results.

The gotcha here is that, since the commit is done implicitly before even the DDL is executed, this happens even if the DDL failed!! 

Test 1 -- to show the effect of rollback after a DML

-- 1. DDLs 
DROP TABLE test_commit; 
CREATE TABLE test_commit (id int, name VARCHAR2(30));

-- 2. DML
INSERT INTO test_commit VALUES(1, 'Test 1');
-- this shows 1 row
SELECT * FROM test_commit;

-- 3. Rollback below reverses the inserts (no DDLs since last DML)
ROLLBACK;

-- 4. This shows no rows
SELECT * FROM test_commit;

Test 2 - show the effect of an interspersed DDL on commit!

-- 1. DDLs
 DROP TABLE test_commit;
 CREATE TABLE test_commit (id int, name VARCHAR2(30));

-- 2. DML
INSERT INTO test_commit VALUES(1, 'Test 1');
-- This shows 1 row
SELECT * FROM test_commit;

-- 2a. This DDL implicitly commits, so above insert is now committed.
DROP TABLE temp_table2;

-- temp_table2 doesn't really exist - I just used it to show it really doesn't matter, 
-- commit happens implicitly in the DDL

-- 3. This rollback is same as in Example 1, but this has no effect 
ROLLBACK;

-- 4. his shows 1 row; if the rollback above worked, we wouldn't see any rows here. 
SELECT * FROM test_commit; 

-- End of Test; cleanup
DROP TABLE test_commit; 

Notes:
* When Autocommit is off, which is the default in Oracle

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