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"]

Gotcha: Accessing Oracle 11g with older SQL*Plus

Oracle 11g has several enhancements over the previous versions. Some of these changes are in the client tools like SQL*Plus. To be able to use these you actually need to upgrade the client tools as well. If you are using old clients like me (I use Oracle 10g SQL*Plus), then you won't see those changes in Oracle 11g. For e.g., the EXITCOMMIT option I mentioned in my earlier post cannot be used in my scripts!

(I couldn't upgrade my client because we use Powerbuilder 10.2 with EA Server 5.5 which doesn't support Oracle 11g drivers. So, until we upgrade those tools, we are stuck with 10g client!)

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

Gotcha: PB Deployment - SYSTEM var not supported error

When you work on code deployed to n-tier, you have to be little more careful while defining the interface for the objects. An n-tier application typically runs in an heterogeneous environment, meaning objects in different languages/platforms may talk to each other through a common interface. If you are not careful, your object may end up being passed to the wrong tier that doesn't know how to interpret it. You have to make sure the data types of the interface functions exist on all sides. Otherwise, you will get unexpected errors at compile or run time.

This is true for PB also, if you are deploying your code to EA Server. Mixing system objects and types in the object's interface will cause errors while deploying to EA Server as shown below.

[caption id="attachment_5001" align="aligncenter" width="536"] 1. CORBA IDL Error on PB System objects[/caption]

While deploying this project (p_d_adm_roles_lh) to EA Server, PB throws the following error message:

 ---------- Deploy: Deploy of p_d_its_adm_roles_lh
Doing Incremental Rebuild...
Generating IDL for Selected Components...
Generation Messages:
    Deployment Warning: SYSTEM Variables Not Supported.   The following bypassed for component 'im_adm_roles_impl':    n_txn
    Deployment Warning: SYSTEM Variables Not Supported.   The following bypassed for component 'im_adm_roles_impl':    n_txn
 ---------- Finished Deploy of p_d_its_adm_roles_lh

Here is what Sybase document says:

Deployment error or warning (from PowerBuilder): SYSTEM variables not supported

Public instance variables and arguments to public functions can be any of:      Standard datatypes, Structures, Custom class user objects that have been deployed as EAServer components, ResultSets.

If you are using system datatypes (transaction, data store, and so on) as instance variables, declare them as protected or private. If you are using system datatypes as function arguments, declare the function as protected or private.

The real problem was in the CORBA interface. I declared a public instance variable called n_txn of type Transaction which is known to PB but not to CORBA IDL  (interface definition language). So, this cannot be in the public interface of the object. CORBA IDL only allows types that can be mapped to it's own. User defined objects are allowed, as long as they themselves are composed of simple types that IDL allows. More on these in a separate post.

While deploying a component to EA Server, PB generates IDL and related proxy and stubs/skeletons for the CORBA interface. It checks to make sure generated IDL complies with the rules of IDL definitions. In the above example since PB's Transaction object cannot be mapped to any IDL type, PB prevents deployment and throws the error. Thus avoiding surprises at runtime.

Ideal solution is to avoid non-standard types in the instance/shared variables in objects to be deployed to n-tier. But, if you really have to define instance/shared variables of types that are not translatable to IDL, then make them private or protected. IDL interface is generated only for the public attributes and methods.

References:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.eas_5.2.eastg/html/eastg/CDDDGIFJ.htm

http://www.omg.org/gettingstarted/omg_idl.htm#TypeSystem
http://www.ibm.com/developerworks/webservices/library/co-corbajct3.html
http://documentation.progress.com/output/Iona/orbix/gen3/33/html/orbix33java_pguide/IDL.html
http://www-cdfonline.fnal.gov/daq/CORBAXXX/tutorial.html
http://docs.oracle.com/cd/E13203_01/tuxedo/tux80/cref/member.htm#1120423
http://www-itec.uni-klu.ac.at/~harald/corba/idldatattypes.html
http://www.slideshare.net/arnoldindia/corba-idl

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>

Source Code Control

Background

I always think, we programmers are like writers and artists. We create software that could be powerful and beautiful. There is no lack of abstraction in our work either!

There is one difference, though. When an Author writes a book, she could revise her work so many times at her desk, but only the final outcome becomes public. Not so with our work! A programmer comes up with several revisions of his/her program before it goes to production. Even so, there may be a bug or something was missed that he/she has to revise it again.

A software could also be "backed out" to a prior version. (Even software giant like Microsoft does this once in a while. Remember when people rejected Windows Vista, their solution was to go back to XP, the prior version of Windows! I myself went back to Gnome desktop on my Ubuntu 12.04 desktop, because of some issues with Unity desktop). And of course, someone might point out that something worked in an earlier version, that you will have to dig up the sources for that version, find out what changed and try to re-incorporate that code back into the new version!! I can't imagine an artist having to go through this type of revising of his/her painting! (I agree a book may have editions, but it's always move forward for them.)

So, if you are a software developer you need to worry about keeping track of the history of the program. Not just the history, but all the files that made up a particular version! And to do this we resort to so many ingenious(?) ways. How many times, have you seen files with .bak, .sav, .org etc extensions on a developer's machines? (guilty!). Some may even organize their files in dated directories, so they can go back to a specific date (guilty as well). But when your program is made up of so many files (gone are the days when you wrote all your code in one file!), such rudimentary methods of keeping track of files won't work! Even more so, if you are working (collaborating) with other developers.

Source Code Control or Version Control

Wouldn't it be better if a Software can track these information at the source level, so it's easy to pull up any version of the software sources and rebuild it? Such a software tool exists for over 40+ years and is known variously as Source Code Control Software (SCCS), Version Control System (VCS), Revision Control System (RCS) and even Software Control Management (SCM). Dozens of tools have been developed with these acronyms in their names. Some of the most popular ones over the years are SCCS, RCS, PVCS, Perforce, Visual Source Safe (VSS), Mercury etc. And in the open source arena, we have had CVS, SVN and Git. Please refer to the wiki site for comparison of many more tools available on the market. The difference is not only in the names, but in techniques and technology involved. Newer tools support concurrency better.

The least a VCS can do is to keep track of history of files and thus offers reversibility. With various versions saved in the repository, it is only natural that we require the VCS to provide a good compare (diff) utility or at least the capability of using external diff tools.  Any type of files can be saved in the repository including binary files. Though, the diff utility is typically geared for text files.

More traditional VCS offers a centralized repository. SCCS, PVCS etc belong to this category. More modern ones offer more distributed approach. With developers from around the world working on open source projects, distributed repositories more sense. And the other characteristics that go with the territory is the concurrency. Again, modern VCS have more and more concurrent support built in. For e.g., CVS refers to Concurrent Version System.

Further Reading

Please refer to the Eric Sink's website (his book is available in PDF format on his site) for detailed discussion on the SCCS tools. This blogger recaps the history of Source Code control in a funny way! And the wiki page like always delivers a nice introduction to this topic. Eric Raymond's paper explains various generations of Version Control Systems nicely.

http://www.ericsink.com/vcbe/index.html
http://www.catb.org/esr/writings/version-control/version-control.html#why_vcs
https://code.google.com/p/pysync/wiki/VCSHistory

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

Quick Tip - Oracle 11g Instant Client

Oracle 11g has 2 versions of clients, Instant Client which has minimum required files for connecting to Oracle and the full blown (thick?) client which has many drivers and tools like SQL*Plus etc. The full blown version seems to be more for the developer and the instant client seems to be suitable for end users.

Recently, we upgraded to Oracle 11g database. When our desktop admin made changes to a user's PC to connect to our 11g database, one of the applications (in VB) stopped working. Incidentally, he had only installed Oracle instant client for end users, as it uses less space.

The VB application in question actually used OleDB to connect to Oracle. Apparently, the instant client we installed for this user did not include OleDB related files. This is why the connection failed. To make it work, we did the following:

1. Installed Instant Client for Oracle 11g (32bit) (option 1 during installation)

2. After the install we copied the OraOLEDB.dll files into the %ORACLE_HOME%\bin directory and registered it. (using regsvr32).

3. Created the directory C:\Oracle11g\Network\Admin and move over the tnsname.ora. (By default tnsnames.ora is left in Oracle11g folder. If you don't move the file, you need to add/change TNS_ADMIN environment variable to point to the right directory.)

Once we did this, the VB application was able to connect to Oracle 11g through OleDB.

Note:

1. This is the minimum required client installation for Oracle. We didn't install the full blown client, as it comes with SQL*Plus etc which the end user doesn't need.

2. All the data access drivers are bundled in an optional package called ODAC. We didn't want to install all the drivers for the user. So, copied only OleDB driver from here.

3. Apparently, SQL*Plus can be added on to instant client as well. See here for more information about Oracle instant Client. Question about connection strings has a hint for connection problems similar to ours.

Reference:
http://www.oracle.com/technetwork/database/features/instant-client/index.html

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html

http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

http://support.dundas.com/Dashboard4.Installing_the_Oracle_Data_Provider_for_dot_NET.ashx

Tools: Notepad++ - Syntax Highlighting

I've posted about Notepad++ (Npp) tool briefly in a different context. I've been using it for a while and the more I use, the more I discover about the tool. This post is about the syntax highlighting in the tool and how you can customize it.

Npp is simply a text editor. It does the same thing Windows' Notepad does and then some. Hence the name Notepad++. With it, you can open almost any text file. Have you ever tried to open a text file from Unix (or Linux) in Windows' Notepad? All lines will be collapsed into a big jumble? This won't happen in Notepad++. It also allows you to adapt various encoding in file (ASCII/ANSI, UNICODE etc) and thus you won't see box characters (well, most of the time). So, it's a great text editor. But that's not all. What makes it a great tool are it's easy-to-use user interface, it's search capabilities (includes Regex out of the box), plugins etc. If you haven't already tried it, download it from here.

As developers we tend to expect a lot more from any tool. I myself have used several editors, starting DOS's EDIT, Unix Vi etc. (I still remember Brief Editor of the early 90's made by a company called Underware!) Each had unique capabilities and weaknesses. One thing they were all missing was the Syntax highlighting. For a long time, this was the feature of IDEs/Editors that came with the language. But in the era of extensible editors like Eclipse, we've come to expect Syntax highlighting as a minimum for any text editor.

I have always wondered how the programming tools color their syntax. I can understand a dedicated tool like Powerbuilder. They know their language, so they can just hard code it in their programs. How does a simple editor like Notepad++ do that without having knowledge about each language it supports? These tools provide some kind of mechanism (proprietary or standard) to define various aspects of the syntax and how to highlight them. But, they just don't know anything about the language itself. That is the syntax highlighting in such tools is based on pure syntax and not the semantics of the language. This is the key to understanding some anomalies in highlighting in these tools.

Notepad++ (Npp) is one of the editors based around a standard editing component called Scintilla. According to their home page, "Scintilla is a free source code editing component. It comes with complete source code and a license that permits use in any free project or commercial product.". More on this later. Npp comes with syntax definitions for several languages. But, if your language of choice is not in the list, no worries; you can simply add another "User Defined Language" (UDL). With the latest version (6.2.x), it's improved and called UDL2.

It's the UDL that I am going to talk about in this post. If you open Npp and click on Language menu, you will see a list of languages available. You can then click "Define Your language" option and get going with adding the definition for your language. I used this define the syntax highlighting for Powerbuilder code.

User Defined Language (UDL)



Fig 1. Npp UDL dialog

Using this dialog, you can create new syntax definitions, import, export and copy from another language definition. There are several tabs here. We will come to Folder & Default in a minute. Keywords Lists is where you put in the keywords in your language. You can separate the keywords in your language into several groups. For e.g., in my example below, I've 2 lists - one for all the reserved words in Powerbuilder and the second list contains all the types. Separating it thus allows you style your keywords distinctly.



Fig 2. Setting up Keyword Lists

When you define an entity - here Keyword list, you can also attach a styler to it. The styler is where you define the fonts and colors for the text for the particular type of entity. For e.g., I've colored PB reserved words in blue as shown in Fig3.



Fig 3. Styler Dialog

Next Tab in the UDL dialog is the "Comment & Number". This is where you can define the commenting symbols in your language. For comments they have 2 styles. Line Style which is a single comments. In PB (C, C++, Java etc) single line comments are identified // at the beginning of the text. Such comments can be at the beginning or end of the line or by themselves.

The second style is C style languages is the multi-line comments, anything in between /* and */. Here is a screenshot of the comment section. As you can see, there is a lot more you can do with it. In the styler for the comments, you can also specify about nested comments.



Fig 4. Setting up Syntax highlighting for comments

Similarly, you can highlight numbers.

The last tab is the Operators & Delimiters. This is where you list out the operators and any delimiters in the language you are defining. Operators are the symbols like +, -, /, * etc. Comma (,), semi-colon(;), colon(:), pipe (|) are examples of delimiters. Again, use the stylers to format them.

I will now get back the first tab - Folders & Default. Default is simple - This the normal text that is not defined in any of the tabs above - i.e., text that is not a keyword, comment, operator or a number. To define this, just click on the Default Style-> Styler button.

The folders section is a bit more interesting. Have you ever seen code folding? Eclipse has it. I believe, Visual Studio does. Powerbuilder itself does not offer it. It's the feature in some editors that let you selectively hide sections of code, so you can see the "big picture". For e.g., if you are working with a multi-level nested IF statements, you can hide inner ones to see what the outer most if/else does. You typically see code folding available for any control structures (let's call it block definers) in the programming language - like IF, CASE, FOR, DO, {, } etc. These typically have an opening and closing marker text or symbol. The code folding also helps in such cases, to see if any block is missing closures.

In this tab, Npp UDL offers 2 styles - one that doesn't require separators and the other that does. This is a bit confusing and I had to try both to understand. The one that doesn't require separator are symbols like {, }, (, ) etc. Even if you have text touching them, they are identifiable. You put them in the first style. The second style, that needs separators (typically space), will contain all the other block definers. For e.g., in PB we have IF...ELSE...END IF to define a IF block. The opener is the IF, END IF is the closer. ELSE happens to be the middle (Earlier versions of UDL only supported open and close. So, upgrade to the latest version of Npp, if you haven't already done so). Notice END IF is actually 2 words; in such cases surround them in Double quotes.

Folding in comments allows you to put specific comments in your code, that you can use for folding the section. We often comment out whole block of codes to test portions of code. Just add marker texts (open, middle, close) defined earlier to such comments, you will be able to just hide the whole block.



Fig 5. Code Folding in Npp

Of course, you can add styles in the stylers.

Here is a sample PB code in Npp, after I created the UDL "Powerbuilder" and applied to the file opened.



Fig 6. Editing PB code in Npp

Note: When I added code folding the syntax highlighting for the keywords in Code folding (IF, END IF, CHOOSE CASE etc) seem to disappear. It may be a bug in the current version of Npp. I will check and post back here.

[gallery include="4872,4873,4874,4875,4876,4877"]

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.

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

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

Ubuntu in action... - NDAS

Last week, my Windows PC stopped working. (I have to look into it later). At the moment, I am working with my taxes and I needed some files from the old machine, desperately. Luckily, I've been backing up. For this, I use IOCell Networks (also Ximeta)¹, NetDisk. NetDisk is a Network Direct Attached Storage (NDAS) device. This seems to be a combination of Network Attached Storage (NAS) and Direct Attached Storage (DAS). As a device, NetDisk is really an external hard disk enclosure that is network enabled and you add hard disk (I added a 1TB drive to it). The difference from other external drive enclosures is that this is network enabled, so once plugged into Ethernet, it can be accessed from any PC on the network. And Direct Access because, you can just plug in the device directly to Ethernet, not through any computer or server.  To add security, they have added a software layer (IOCell/Ximeta NDAS) that you need to install on each PC to access the drive. You will have to register the software with the specific serial # of the NetDisk. Thus, only specific PCs with the right software and serial # register can access the drive. I had installed this software on my Vista machine and thus worked like a charm. (This post relates only to the Linux version of this software. If you need help with Windows version, please refer to their user manual).

Now, at this critical moment, when I needed to access the back up, I couldn't. My main PC is dead. My wife's laptop runs Windows 7 and is not capable of running Ximeta NDAS software (It freezes - even the version 2.72 they suggest to fix the issue). Then my other PC, where I am typing this from, is running Ubuntu 12.04 for which Ximeta does not have a driver or programs for NDisk. At least, not any installation binaries!

But there was hope. Luckily, Ximeta provides (open source) source code at https://github.com/iocellnetworks/ndas4linux/tree/master/ for Linux. Downloaded the source code (If you go to zip tab in the above page, it will download a file named ndas4linux-master.zip. The source directory has several versions. Which version to use depends on your kernel version. (To find your kernel's version, you can use uname -r). For Ubuntu 12.04, I used version 3.2.0. Though I work with large systems at work, I never venture to compile big programs at home. This time, it's different. Desperate situation require desperate measures. I ventured on compiling the driver for Linux myself. I was amazed. Huge set of files got compiled while I watched without major issues. Once the source code is compiled, we need to install and load it. Here is a list of things to do from their documents:

1. Download a source tarball.


2. Unpack


3. CD into the right folder and run make


4. Change to root or sudo make install


5. Start ndas as root


6. As root, or by sudo, register and enable ndas devices with ndasadmin

 Apart from the documents provided, each directory inside the main folder has a README. Read these for instructions on how and where to compile. Here is a little better instructions on how to build it.

Building the software

This is really confusing, as there are many directories and many make files. To do this correctly, you need to be in the right directory. Change to ndas4linux-master/3.2.0/doc (I use 3.2.0; change to the right version) and read

how_to_export.txt first. Here is the summary of how to compile:

cd ndas4linux-master/3.2.0
make linux-rel

This creates a new directory, ndas4linux-master/3.2.0/build_x86_linux. linux-rel is one of the options. This is the final version. But, you can also build dev or debug versions. Take a look at the doc files.

Installing the software

Once you compiled, you need to install the driver and start it. Below commands do that.

cd ndas4linux-master/3.2.0/build_x86_linux/ndas-3.2.0.x86
make

The make command compiles so many files and actually builds ndasadmin and "installs" in /usr/sbin. This is the command we will use to mount NetDisk.

ndasadmin has several options. Please take a look here. To run this command, you need to be root or use sudo. If you just type sudo ndasadmin on command prompt, it will display various options available.

Starting ndas Driver

After building and installing the driver software, you need to start the driver before you can use the device.

sudo ndasadmin start

 

Mounting the Device

You also need to register the device serial #, before you can mount the device volumes. The readme file, how_to_use_ndasadmin.txt inside the version folder (3.2.0 in my case) lists the steps to install and run NDAS software. To register the device, you need to find the serial number (and write key) of the NetDisk device. These are available at the bottom of the box.

sudo ndasadmin register <SERIAL_NUMBER> -n NetDisk1
sudo ndasadmin enable -s 1 -o r

brings up the NetDisk volumes. NetDisk1 is just name I gave to the device. This can be anything. Also, register option have a slight variation with Serial # and Write key. See the readme file mentioned above.

First, I didn't see them come up. Then I found them listed in "Places" option in Gnome desktop menu. To see the device listing, you can use the following command:

cat /proc/ndas/devs

To disable/unmount the device, use the following commands:

sudo ndasadmin unregister  -n NetDisk1
sudo ndasadmin disable -s 1 -o r

Further Help

Like I said earlier, the doc folder inside the version you are working with (ndas4linux-master/3.2.0/doc) actually contains all the instructions. Here are some of the files in that directory.

how_to_build.txt

how_to_export.txt

how_to_use_ndasadmin.txt

Note that the software seems to be constantly evolving, so it may not support all the functionalities, the actual device can support. And if you need more help, here are some links if you want to learn more about Iocell Ximeta on Linux.

1. http://ndas4linux.iocellnetworks.com/


2. https://github.com/iocellnetworks/ndas4linux/wiki/How-to-export-NDAS-source-for-different-architectures


3. http://ndas4linux.iocellnetworks.com/trac/index.cgi/browser/ndas4linux/release


4. http://ndas4linux.iocellnetworks.com/kermit/index.cgi/wiki/Usage


5. http://ndas4linux.iocellnetworks.com/kermit/index.cgi/wiki/HowToBuildDEB

---

^{1 IOCell acquired Ximeta's NDAS; so you will see the names used interchangeably here and the web. See here}

SQLite Database

I've been working with different types of Databases over the years from dBase to B-Trieve to Oracle enterprise database. Along the way, I looked at or picked up quite a few - Integra SQL, Informix, MS Access, MySQL, SQL Server(s), Watcom SQL/Sybase SQL Anywhere, DB2, DB2/400 etc. While SQL databases are good, I miss the simplicity of dBase or B-Trieve files. After many years now, I think I found the lost love now!! SQLite!

In my intro I talk about "beautiful software". This is how I felt about this software. Installation is simple, you just download and unzip 1 EXE - sqlite3.exe. (or install in Linux using apt-get or yum). Done! When you are ready to create a database, you type sqlite3 <database name>. That's it!!! You want to work with the same database again, just type,

sqlite3 <database name>

The file size is what amazes me - it's a mere 50k. They've really packed a lot inside it. For one, it supports almost full (basic) SQL. I was able to actually run SQLs meant for Oracle in Sqlite. The command line utility achieves what other big databases didn't. SQL*Plus can learn a lot from this tool. And it's highly portable - in fact, I created a database in windows at work and brought home to Linux. It works like a charm.

For one, they differentiated the tool commands by .prefixing with period (.). So, you want help, just type .help. You want to see the tables in the database you just opened, just type .tables.

What impressed me with the tool, other than the simplicity is, that it provides table export imports naturally. Commands?

.import <filename> <table>

What about exporting table or query output? Just use,

.dump [<tablename>]

.output <filename>

Don't you want to export to CSV or HTML etc.? Sure, just use .mode. Then, you want a different separator? Use .separator. (Though HTML mode only dumps the tags for table rows, as it's output is often embedded in CGI outputs, but you can always add the missing tags, right?). Have you tried exporting a table from Oracle SQL*Plus? It's like extracting teeth, so we end up writing programs/procedures to do it.

True, Oracle (and other databases) is much larger and has a lot more bells and whistles, but for a simple (personal) database task, SQLite is the natural choice. Granted, this is just file based database, but smaller tasks this is a great tool to have in your toolbox. My specific task this time, is to import the log files into a database table, so I can query and procedure usage reports and charts for our recently re-factored web app. In the past, I used MS Access for this type of activity, but with a slow machine I have, Access doesn't fair well with hundreds of thousands of log records.

I've only scratched the surface with SQLite. It seems to be used in embedded applications. There is an API available to work with it C/CPP. Python (Perl, PHP etc) come with native support for SQLite. It's a pleasure to work with SQLite in Python. More on this later. Apart from these, SQLite offers extensibility by allowing to load (.load) external modules. You can also use Virtual Tables (like Oracle External table) to access a CSV file directly.

I can go on and on, but I will stop ranting and raving about the greatness of this software. If this post made you want to look up more on SQLite, please try the links below.

• http://www.sqlite.org


• http://en.wikipedia.org/wiki/SQLite


• http://www.sqlite.org/whentouse.html


• http://www.sqlite.org/selfcontained.html


• http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/


• http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles


• http://code.btbytes.com/2010/09/16/sqlite-csv-loading-using-virtual-table-extension.html


• http://docs.python.org/2/library/sqlite3.html


• http://www.sqlite.org/vtab.html


• http://www.drdobbs.com/article/print?articleId=202802959&siteSectionName=database


• http://www.yolinux.com/TUTORIALS/SQLite.html


• http://robert.accettura.com/blog/2008/02/26/and-the-winner-for-most-embedded-db-is-sqlite/

 

Stumbled Upon: New Human Species Discovered

This is from 2010, but interesting read

http://www.sciencedaily.com/releases/2010/03/100325100848.htm

2012 in review - Auto generated Wordpress Report

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.



Here's an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 4,900 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 8 years to get that many views.

Click here to see the complete report.

SamV: Wasn't exactly a good year, but this is the year, this blog is turning around. I am finally seeing some visitors. I am still in the process of improving this. If you happened to stop by here and have some ideas as to how to improve the user experience, please feel free add as comments. I appreciate it. Have a wonderful 2013!!!

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!

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.