Tag Archives: Oracle

SQL Server: Linked server select query into a new table

It’s always worth remembering SELECT INTO a new table or a temporary table, here’s and example:

SELECT [COL1], [COL2]
INTO [DB name].[Schema name].[Table name]
FROM [Source DB name].[Source Schema name].[Source Table name]

This will create the new table with the columns matching the name and types of the selected columns.
Alternatively you could select into a temporary table like so:

SELECT [COL1], [COL2]
INTO #temporarytable
FROM [Source DB name].[Source Schema name].[Source Table name]

For performance reasons you may want to copy the contents of a linked server into new or temporary tables in SQL Server, then run complex scripts and SQL against your SQL instance. Here’s an example of how I would do this, I am using OPENQUERY because that takes care of type inconsistencies for me:

SELECT [COL1], [COL2]
INTO [DB name].[Schema name].[Table name]
FROM OPENQUERY([Linked Server], N'SELECT * FROM SourceSchema.SourceTable')

Of course here your linked server could be anything, but I’ve used this for Oracle most recently and it was so much better than trying to export the data from Oracle and import it into SQL Server.

Oracle: Trim, replace, substring and indexof #oracle

Further to my post about SQL Server, here’s the differences I need to consider when scripting the same thing in Oracle:
REVERSE AND REPLACE are the same 🙂
LEN => LENGTH
LEFT => SUBSTR
CHARINDEX => INSTR
(String concatenation) + => ||
PATINDEX => REGEXP_INSTR (which is more powerful out of the box, of course you can do regex with SQL Server, but not out of the box)

SQL Server / Oracle: DISTINCT or GROUP BY

Firstly, sometimes sticking in a DISTINCT is quick and easy, but so easily missed when reading the SQL and also conversely the group by can be pretty long if there are a lot of columns selected.

Firstly a basic example:
SQL Server

SELECT
a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b
GROUP BY a

SELECT
DISTINCT a
FROM
(SELECT 1 AS a
UNION ALL SELECT 1 AS a
UNION ALL SELECT 2 AS a) b

Oracle

SELECT
a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b
GROUP BY a;

SELECT
DISTINCT a
FROM
(SELECT 1 AS a FROM DUAL
UNION ALL SELECT 1 AS a FROM DUAL
UNION ALL SELECT 2 AS a FROM DUAL) b;

They both return the same result, simple, now imagine a lot more columns in the select and make your choice of DISTINCT or GROUP BY carefully.

Is COUNT(DISTINCT a) the same as DISTINCT COUNT(a)?
No, the former you select distinct then count them, the later you count then select the distinct counts, so results are 2 and 3.
Also you can’t swap DISTINCT for a GROUP BY in this case, because the GROUP BY would return you two counts, one for each grouped value, however using a nested query you could select the count from the grouped values, like so:

SQL Server

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a
	UNION ALL SELECT 1 AS a
	UNION ALL SELECT 2 AS a) b
	GROUP BY a
) c

Oracle

SELECT COUNT(*)
FROM
(
	SELECT
	a
	FROM
	(SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 1 AS a FROM DUAL
	UNION ALL SELECT 2 AS a FROM DUAL) b
	GROUP BY a
) c;

I always try to remind people that DISTINCT works on what is selected, don’t just go sticking it wherever you feel like as I won’t see it, put it right after the SELECT as a means of helping me see it.

There is also the group by pitfall of grouping on something that you aren’t selecting. Let’s say you originally select two columns grouping on both so you get distinct values, great, then you decide, actually I am only interested in the first column so the second is removed from the select, unless it is also removed from the group by the results will not be the same as distinct! This sort of thing normally leads to the group by being dropped and replaced by distinct.

I hope this helps.

Export SQL Server data and import into Oracle

It’s worth making a note of characterset issues as we come across them.

Let’s say I have some data in SQL Server:

SELECT 'Jörg' AS Name
UNION SELECT 'Jones' AS Name

And I export it using the “Save Results As…” option and save as test.csv.

Great, now we have this unicode file and we need to import it, for the unicode character to be supported by Oracle SQL Developer there is a setting you may need to change.
With the default setting of Cp1252:

when I open the test.csv I see:

Those with a keen eye will spot the Byte Order Mark (BOM), but ouch, look at what has happened to Jörg.

So let’s change the Encoding setting to UTF-8:

Then reopen and voila:

The Oracle SQL Developer Data Import Wizard can trick you too, if Encoding setting is set to default then it won’t be the UTF-8 we’ve set, you will need to manually change this to UTF-8 each time you do an import:

Oracle: Commit; just in case you forget

I’ve been writing a few Oracle scripts recently and it dawned on me that I should really stick a COMMIT; at the end of every script rather than forgetting and not actually applying the update. I know it is a minor thing, but when switching between SQL Server and Oracle it is often forgot.

Oracle: Ampersand (&) substitution in SQL Developer

Try this:
SELECT 'HELLO & GOODBYE' FROM DUAL;
and you will get a popup:

Ampersand is used to denote a substitution variable, great, but in this case I just want the & character in my string. A workaround is to put the ampersand at the end of a string, so the following will work:
SELECT 'HELLO &' || ' GOODBYE' FROM DUAL;
Where || is the Oracle string concatenation function.

Oracle: ROWNUM doesn’t equals

I often forget ROWNUM and the equals quirk, also not forgetting the ORDER BY/WHERE and ROWNUM combination issue too.
-- Works - result is two rows (bye, hello)
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM < 3;

-- Doesn't
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM = 2;

This is apparently because (I quote) “the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row” from Oracle ROWNUM Documentation.
So it is calculated on the select, this means you have to be careful with ORDER BYs and WHEREs (etc) and explains why it doesn’t work as we hoped.

-- So this works; returns one row (hello)
SELECT "Greeting" FROM
(
SELECT "Greeting", ROWNUM AS "N" FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
)
) WHERE N = 2;

There are other ways, but I am only focusing on ROWNUM in this post.

Oracle: ODP.NET is the devil

Well, those were my words yesterday.
The errors Oracle was throwing up were:
ORA-06550: line X, column X: PLS-00224: object 'X' must be of type function or array to be used this way
and
ORA-06550: line X, column X: PLS-00382: expression is of wrong type
followed by
ORA-06550: line X, column X: PL/SQL: ORA-00904: : invalid identifier
No matter what I did I had these problems, these errors were just annoyingly vague and did not help at all, Oracle might as well have just said, “ERROR”.

I noticed if I had one too many parameters I got the error:
ORA-06550: line X, column X: PLS-00382: expression is of wrong type
which made no sense as they were all Varchar2 fields and the parameters were too (well, one was a PLSQLAssociativeArray).

Today I found out why I was getting such odd behaviour that I couldn’t for the life of me figure it out.
First port of call is to check your command is correct and you have the parameter names correct.
Secondly, are sure of that are you binding parameters in order or by name? The default is to bind by order (BindByName = false). This was my mistake and I needed to set:
cmd.BindByName = true;
where cmd is my OracleCommand object.

Best of luck and I hope I remember to read this post next time.

Oracle: Bulk insert using an array

You really don’t want to generate an insert for each row you want to insert, this will be slow to create, slow to transmit, slow to parse and slow to process. I also don’t like creating CSVs and importing these via SQL Plus. I’m an application developer, so I want to do bulk operations via ODP.NET.

Instead I use arrays, they truly are wonderful and often overlooked.
A syntax like so is really useful to remember:
DECLARE iVal INT;
BEGIN
FOR iVal IN 1..200 LOOP
INSERT INTO [TABLE] (column1, column2) VALUES (:array1[iVal], :array2[iVal]);
END LOOP;
END;

where array1 and array2 are PLSQLAssociativeArray arrays.

See also Oracle: Using PLSQLAssociativeArray with ODP.NET

Oracle: PLSQLAssociativeArray TimeStamp workaround

Oracle (at time of writing) doesn’t support an PLSQLAssociativeArray of type OracleDbType.TimeStamp.
So what you need is to use a standard string instead then convert it to a timestamp. For this you will need to set the timestamp format via:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD/MM/YYYY HH24:MI:SS'

Then in your OracleCommand CommandText you can use:

TO_TIMESTAMP(:t(i))

where t is an input OracleParameter of type OracleDbType.Varchar2 with CollectionType of OracleCollectionType.PLSQLAssociativeArray and the values are formatted as per the NLS_TIMESTAMP_FORMAT.