Re: Convert code from SQL Server 2000 to SQL Plus for Oracle

You are running into a few issues here:

1. Oracle and SQL Server both use SQL as the data access language
2. SQL does not inherently define a procedural language
3. Oracle uses PL/SQL as it's procedural language, SQL Server uses a vaariant on Sybase's TransactSQL as it's procedural language.

The code you show is primarily procedural and looks like a TransactSQL dialect. Therefore you will need to manually transform it to PL/SQL if you want to remain procedural.

In this case, however, the specific operation (transforming date presentations and apparently showing the first/last days of month) can be done in relatively simple SQL selects in Oracle using the TO_DATE and TO_CHAR functions.

In my experience, many SQL Server code artists do not understand SQL or set theory and therefore have written between 50% and 2000% more code than necessary. This example seems to fit as well.

I strongly advise the following:

1. Ensure current code to be converted has appropriate documentation describing the code intent (not, as usual, "Here we declare variable xyz");
2. Analyze the documentation, either by yourself or with the help of others, including consultants, to determine whether the intent can be accomplished in set theory;
3. Learn, or expand your knowledge of, true set theory and how SQL relates;
4. Learn Oracle specifics - the documentation is all online at http://otn.oracle.com and http://tahiti.oracle.com

In the last, look at the SQL Language Reference manual and the PL/SQL language manuals.

Hope this helps
/Hans Received on Tue Apr 08 2003 - 11:33:16 CDT