-
Visit my blog
read all about bochgoch
- What is systems thinking?
- When is a system a system
- Complexity
- Beliefs
- Information Gathering
- Methodology Method Tool
- Sustainable Development
- Reflection
- Success
I like to take a holistic approach to problem solving, systems thinking helps...
What shall we say then?
Sybase SQL to Oracle SQL conversion
For successful conversion of Sybase SQL to Oracle SQL syntax a number of amendments must be made to the SQL.
These guidelines should aid you in this process.
These notes are written from the perspective of the migration of legacy Sybase databases to Oracle 9i / 10G databases and therefore may not be accurate in comparision with current version Sybase databases.
Functions
| Sybase | Oracle | Description |
|---|---|---|
| PATINDEX | INSTR | PATINDEX and INSTR both return the starting position of a character or string of characters within another string. SYBASE SYNTAX: INSTR (expression , '%pattern%') ORACLE SYNTAX: PATINDEX ( '%pattern%' , expression ) |
For PATINDEX when used to apply a condition within a SELECT statement, as in:
sum(Order_Value * convert(bit,patindex
('666',substring(Order_Code,1,3))))
...wrap INSTR within a CASE statement.
sum( CASE WHEN
INSTR(Order_Code,'666') = 1
THEN Order ELSE NULL END )
|
||
| CASE | CASE |
ORACLE SYNTAX:
CASE WHEN condition THEN value
WHEN condition THEN value
...
ELSE value
END
|
| SUBSTRING | SUBSTR |
Returns the portion of a string expression from the start character for the number of characters specified by the length. SYBASE SYNTAX: SUBSTRING(expression,start,length) ORACLE SYNTAX: SUBSTR(expression,start,length) |
| CHAR_LENGTH | LENGTH |
Returns number of characters in a a string expression. SYBASE SYNTAX: CHAR_LENGTH(expression) ORACLE SYNTAX: LENGTH(expression) |
| n/a | LAST_DAY |
Returns the last date in the month (i.e. 31-Jan-2005) of the date expression. ORACLE SYNTAX: LAST_DAY(expression) |
| n/a | EXTRACT |
Extracts a portion of a date as an integer. The following types can be extracted: DAY MONTH YEAR HOUR MINUTE SECOND TIMEZONE_HOUR TIMEZONE_MINUTE TIMEZONE_REGION TIMEZONE_ABBR ORACLE SYNTAX: EXTRACT(type FROM date_expression)
SELECT EXTRACT(YEAR FROM SYSDATE) * 100 +
EXTRACT(MONTH FROM SYSDATE) FROM DUAL
|
Conversion
| Sybase | Oracle | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CONVERT( INTEGER(etc) ,expression) | TO_NUMBER( value ,[format]) | When converting strings to numbers, i.e. TO_NUMBER('1768.01', '$9999.999')...the following options may be included to build [format]:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| CONVERT( varchar, expression) | TO_CHAR( value, [format]) | When converting dates to strings, i.e. TO_CHAR(sysdate, 'YYYY/MM/DD') ...the following options may be included to build [format]:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| CONVERT( date, expression) | TO_DATE( value, [format], [nls_language]) | See TO_CHAR for format mask options & syntax. |
Temporary Tables
| Sybase | Oracle | Description |
|---|---|---|
| # tables | GLOBAL TEMPORARY TABLE | Oracle differs significantly from Sybase in this respect. A Sybase (SQL Server) # (hash / temporary table) is created in memory and only exists for the duration of the transaction it is created in. A Oracle GLOBAL TEMPORARY TABLE (GTT) is created in the same way as a TABLE, but the data is TEMPORARY and only exists within the GTT for the duration of the transaction that it is inserted into the GTT in. |
NULL's
| Sybase | Oracle | Description |
|---|---|---|
|
The handling of NULL values differs between Sybase and Oracle. In short, in Oracle Null is neither EQUAL to Null nor is it NOT EQUAL (greater or less than) to Null! -- See Example 1 below. In Sybase, Null is EQUAL to Null -- See Example 2 below. Example 1 -- Null is neither = Null nor <> Null in Oracle
SQL> select * from dual
D
-
X
SQL> select * from dual where null=null
no rows selected
SQL> select * from dual where null<>null
no rows selected
Example 2 -- Null = Null in Sybase
declare @x integer
select 'Hello'
where @x=null
-----
Hello
(1 row affected)
This difference necessitates careful handling of comparisons that may include Null's within the Oracle environment.
As can be seen in Example 3 below, unhandled comparison of null values will generate errors in otherwise valid code.
The solution is to code defensively, most obviously through use of the NVL function and through ensuring that variables are assigned non-null values on definition.
Example 3 -- Null comparison in Oracle
set serveroutput on;
declare
x integer;
y varchar2(5);
begin
x:=null;
select 'Hello' into y from dual
where x=x;
dbms_output.put_line(y);
end;
ORA-01403: no data found
ORA-06512: at line 6
set serveroutput on;
declare
x integer;
y varchar2(5);
begin
x:=1;
select 'Hello' into y from dual
where x=x;
dbms_output.put_line(y);
end;
Hello
PL/SQL procedure successfully completed.
set serveroutput on;
declare
x integer;
y varchar2(5);
begin
x:=null;
select 'Hello' into y from dual
where nvl(x,1)=nvl(x,1);
dbms_output.put_line(y);
end;
Hello
PL/SQL procedure successfully completed.
|
