Programmatically load CSV file into oracle table using VB.Net

[MR1]

I am trying to find a VB.Net code sample that loads a CSV file into an Oracle table.

Thank you,
M.R.

 

[HarleyQuinn]

What have you tried so far?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jimirvine]

VB is a completely innappropriate tool for doing this. Use External Tables.

 

[HarleyQuinn]

VB is a completely innappropriate tool for doing this. Use External Tables.

That's quite a blanket statement Jim (and assumes that the OP has the ability to create/get created the necessary directory/directories to allow them to use External Tables). It could be quite feasible that the application could be reading a CSV file (from anywhere on the computer or from a drive inaccessible to the Oracle instance) and then needing to import the data into Oracle from the application.

I could also be wrong, but I can't see a reason why (once the External Tables have been set up and permissions are OK) you can't use External Tables through VB anyway?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jimirvine]

You are entitled to your opinion of course.

 

[jdemmi]

Jim -- you used the word inappropriate. Is that really what you meant? I could see an argument that it isn't the BEST tool for the job but inappropriate? No.

While MR1 didn't exactly provide a whole lot of (ok any) details into his goal you didn't answer his question. You really didn't offer a solution to replace vb.net with -- you simply stated "use external tables". For example, what is he needs to read/write to files on clients machines?

It is my understaning that external tables allow you to query a flat file as if it were an Oracle table. So, why not provide suggestions, even if only rough, on how to do so programatically? Had you done so I think your opinion would have been better received. Or better yet, how about first clarifying MR1's true goal/need and then offering an explanation that not only solves the problem but includes an explanation of reasoning for any alternate methods used.

So, MR1 -- what are you up to anyway?

Is this a one time load?
Will data only be loaded into Oracle?
Or will it also be extracted?
Will the location of the CSV file be dynamic?
Will the file name be constant or dynamic?
Will this be a user executed process? Or scheduled?



-- Jason
"It's Just Ones and Zeros

 

[MR1]

Most of these questions are irrelevant. I just need to find a piece of code that reads the contents of a CSV file and writes this data into an Oracle table.
If you are familiar to SQL Server, there are VB.NET techniques like 'bulk insert ' or using a 'Microsoft.Jet.OLEDB.4.0' connection that are extremely efficient for this purpose.
I am looking for some similar method that applies to Oracle.

M.R.

 

[jdemmi]

They're not irrelevant. Especially if you're concerened with efficiency -- which you failed to mention in your initial post.

Here's a good link (and site) for tips on fast data loading.

http://www.dba-oracle.com/oracle_tips_data_load.htm

You can then make use of a these, as you see fit, with the Oracle Data Access components for .Net


With that I will step out of this post.

-- Jason
"It's Just Ones and Zeros

 

[MR1]

Thank you for your time and effort jdemmi, the link you provided is very useful!

Regards,
M.R.

 

[prh47]

MR1, did you find your solution?

 

[MR1]

Yes prh47, I created an Oracle stored procedure that uses external tables and I call it from my VB.Net application.

Regards,
M.R.

 

[prh47]

A quick question, MR1. What does VB.Net use to run a stored procedure? Is there an API of some sort?

 

[dstxaix]

You could use ADO.NET with VB.NET

 

[MR1]

I am using Vb.Net 2008 that is very well integrated with Oracle.
You can easily define Oracle data access components.

here is a code sample:

Dim cmd As OracleCommand
Dim ORConn As OracleConnection

ORConn = New OracleConnection("Data Source=" & sDataSource & ";User Id=" & vsDatabaseName & ";Password=" & sPWD & ";")

cmd = New OracleCommand(YourStoredProcedureName, ORConn)
With cmd
.CommandType = Data.CommandType.StoredProcedure
.Parameters.Add("CSV_name", OracleDbType.Varchar2).Value = sFileName
.Parameters.Item("CSV_name").Direction = ParameterDirection.Input
.Parameters.Add("dir_path", OracleDbType.Varchar2).Value = Replace(sPath, "\", "/")
.Parameters.Item("dir_path").Direction = ParameterDirection.Input
.Parameters.Add("s_table", OracleDbType.Varchar2).Value = sExternalTableName
.Parameters.Item("s_table").Direction =
'output parameter
.Parameters.Add("rec_count", OracleDbType.Varchar2).Direction = ParameterDirection.Output
.Parameters.Item("rec_count").Size = 10
.ExecuteNonQuery()
End With

This executes your Oracle stored procedure. It passses some parameters and also returning the number of records affected.

Regards,
M.R.