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.

Oracle: Using PLSQLAssociativeArray with ODP.NET

In Oracle and ODP.NET you can create arrays (of certain types, not timestamps, see Oracle: PLSQLAssociativeArray TimeStamp workaround about that) and use them as parameters to any kind of SQL using the standard syntax.

Here’s an example for a string array:

OracleParameter p = new OracleParameter("p", OracleDbType.Int32) { Direction = ParameterDirection.Input, CollectionType = OracleCollectionType.PLSQLAssociativeArray, Value = myArray, Size = myArray.Length };

where myArray is a normal C# String[].
For an example of really making use of this for bulk inserts see Oracle: Bulk insert using an array.

See Oracle Documentation for more information

Copied for reference if link is dead:

OracleCommand cmd = new OracleCommand(
        "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);
 
      OracleParameter Param1 = cmd.Parameters.Add(...);
      OracleParameter Param2 = cmd.Parameters.Add(...);
      OracleParameter Param3 = cmd.Parameters.Add(...);
 
      Param1.Direction = ParameterDirection.Input;
      Param2.Direction = ParameterDirection.InputOutput;
      Param3.Direction = ParameterDirection.Output;
 
      // Specify that we are binding PL/SQL Associative Array
      Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
 
      // Setup the values for PL/SQL Associative Array
      Param1.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param2.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param3.Value = null;
 
      // Specify the maximum number of elements in the PL/SQL Associative Array
      Param1.Size = 3;
      Param2.Size = 3;
      Param3.Size = 3;
 
      // Setup the ArrayBindSize for Param1
      Param1.ArrayBindSize = new int[3]{13, 14, 13};  
 
      // Setup the ArrayBindStatus for Param1 
      Param1.ArrayBindStatus = new OracleParameterStatus[3]{
        OracleParameterStatus.Success,
        OracleParameterStatus.Success,
        OracleParameterStatus.Success};
 
      // Setup the ArrayBindSize for Param2
      Param2.ArrayBindSize = new int[3]{20, 20, 20};
 
      // Setup the ArrayBindSize for Param3
      Param3.ArrayBindSize = new int[3]{20, 20, 20};
 
      // execute the cmd
      cmd.ExecuteNonQuery();