How to convert oracle VARRAY to SQL SERVER

Question

0.00/5 (No votes)

See more:

Hi,

I am working in an oracle to sql server migration task, and I am stuck at a place where i need to convert a type in oracle which looks like:

create or replace type Q_ARRAY
is varray(2000) of VARCHAR2(11);

in Oracle.

Any help on this is appreciated..
thanks,
Dave

What I have tried:

I didnt get an exact conversion of varray. I am new to sql. sorry

Posted 11-Jan-17 5:26am

daviseban

Updated 13-Jan-17 1:13am

Add a Solution

Comments

Richard Deeming 11-Jan-17 12:15pm

I don't think there's a direct equivalent. Maybe a table variable[^] would work?

Table Variables In T-SQL[^]

3 solutions

Solution 2

I tried a lot of things and got a solution at last..

Instead of VARRAY in Oracle a I created TABLE TYPE in SQL server like this:

CREATE TYPE dbo.Q_ARRAY
AS TABLE
(
  Q_ID VARCHAR(11)
);
GO

This is used in a stored proc as an argument and I need to pass the string array to this stored proc from java.
I was able to do this with the below java code:

SQLServerDataTable qDT = new SQLServerDataTable(); 
				vehDT.addColumnMetadata("Q_ID", java.sql.Types.VARCHAR);
				vehDT.addRow("11122369");
				vehDT.addRow("45523264");
				
				
				SQLServerPreparedStatement spStat = (SQLServerPreparedStatement)con.prepareStatement("{call [dbo].[process_qRequest](?)}");
				spStat.setStructured(1, "dbo.Q_ARRAY", qDT);

This is working like a champ!!
Here I am using sqljdbc41 jar.
Please note that the old sql jdbc jars doesnt provide many of these functionalities.

thanks,
Dave

Posted 13-Jan-17 1:13am

daviseban

Solution 3

can any one help with the download link for sqljdbc4.jar?

Posted 15-Nov-17 15:58pm

Member 13523765

Updated 15-Nov-17 16:02pm

Comments

CHill60 16-Nov-17 4:12am

If you have a question of your own then use the red "Ask a Question" link at the top of the page. Be sure to read the guidelines for posting questions which will be shown when you click the link...Especially the one about doing your own research.
Do not post questions or comments as "Solutions" to other posts

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2017-01-11T09:39:00

As far as I know an exact match for VARRAY does not exist in SQL Server, at least not with all the collection functions.

So the decision what to use depends highly on what kind of operations you're going to make with the array. In most scenarios I believe that using a table variable would be sufficient. Have a look at the following example

SQL

-- Define the type, valid only inside the batch
DECLARE @SomeList TABLE ( col1 int );

-- Add some data
INSERT INTO @SomeList (col1) values
(1), (2), (3), (4)

--Set based operation
BEGIN
   SELECT * FROM @SomeList
END;

-- The result is 
--   col1
--   1
--   2
--   3
--   4

--Cursor based operation to handle individual rows
BEGIN
   DECLARE @col1value int;
   DECLARE ListCursor CURSOR FOR SELECT col1 FROM @SomeList;
   OPEN ListCursor;
   FETCH NEXT FROM ListCursor INTO @col1value;
   WHILE @@FETCH_STATUS = 0  BEGIN 
      PRINT 'Col1 value is '+ CAST(@col1value AS varchar(100))
      FETCH NEXT FROM ListCursor INTO @col1value;
   END;
END ;
-- the result is 
--    Col1 value is 1
--    Col1 value is 2
--    Col1 value is 3
--    Col1 value is 4
GO

The type can also be persistent just like in Oracle. In such case you need create the type separately. Have a look for an example at How to pass multiple records to a Stored Procedure[^]

Also remember that SQL Server does not have the concept of packages so you cannot define public types and the code in the same module.