VB.NET and Oracle: Inserting values greater than 4000 Characters into NCLOB. Use bind values?

Posted on 2011-03-14
Last Modified: 2012-05-11
I've been trying to figure out how to run the query below. And have been looking on the internet, where people are saying you cannot enter strings of greater than 4000 characters as SQL treats them as VARCHAR2 values instead of CLOB values and limits them to 4000 characters.

Apparently, in order to do this you need to use bind values. I've no idea about any of this, and made a fruitless attempt to use bind values to achieve this. How do I achieve what I'm trying to do can anyone explain?

Thanks, Aiden
strSQL = strSQL + " (History)"
strSQL = strSQL + " VALUES ('more than 4000 characters')

Open in new window

Question by:AidenA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
LVL 74

Expert Comment

ID: 35129951
you are correct

the problem is you are using a string literal.
a literal is always a varchar2 and sql varchar2 can't be more than 4000 characters
use a bind variable instead

which would look like

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 400 total points
ID: 35129976
What data provider are you using?  I have several examples using CLOBs with Oracle's ODP.Net.

If you aren't using ODP.Net, I would suggest you switch over.  It will save you a lot of headaches down the road.
LVL 74

Expert Comment

ID: 35129977
once you have the ? place holder

it might also be written as :somename  

with the colon (:)

then declare a parameter for your command object and assign the big string to the parameter
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 35130247
Hi thanks,

Data provider is currently Provider=OraOLEDB.Oracle. I was looking for ODP.Net but the reference didn't appear when i went to select it using add reference?

I tried the below piece of code previously with no luck, which is why i asked this question. What's wrong with it? Well i think i already know what's wrong with it... it's just a peice of string which i've added a colon to, so it's no different to before. How exactly should I write it though?
Dim strBind = "long piece of string over 4000 chars"
strSQL = strSQL + " (History)"
strSQL = strSQL + " VALUES (:" & strBind & ")"

Open in new window

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35130313
>>the reference didn't appear when i went to select it using add reference?

You need to download and install the Oracle ODAC.

>>strSQL = strSQL + " VALUES (:" & strBind & ")"

That is not a bind variable.  

Check out the examples in:

That's a 9i reference page but the OLEDB paramters haven't changed.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35130324
I should add that you still might have issues with CLOBs and Microsoft data providers.  You'll just need to see if the oledb driver you are suing supports them.
LVL 74

Assisted Solution

sdstuber earned 100 total points
ID: 35130395
strSQL = strSQL + " VALUES (:" & strBind & ")"

this is simply embedding the value of strBind into the string which creates a sql statement like

insert into csat_table(history) values(:long piece of string over 4000 chars)

which isn't legal syntax

you want a sql statement that looks like this...

insert into csat_table(history) values(:yourvariable)

then create a parameter object to connect your strBind variable to the :yourvariable bind


Author Comment

ID: 35139964
Hi slightwv,

Ok I will have to download and try and use ODP.NET then... can you provide your example of using CLOBS with that provider?

i'll have to look into parameterizing the query then, i guess that's what bind values are doing. will need to check tomorrow though
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35140226
Check out the code below.

It uses the table:
drop table tab1;
create table tab1 (
      col1 number,
      col2 clob

<%@ import namespace = "" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>

<title>CLOB test</title>
<script language="VB" runat="server">

'drop table tab1;
'create table tab1 (
'	col1 number,
'	col2 clob

sub callProc(sender as object,e as eventargs)
		Dim con As New OracleConnection() 

		con = New OracleConnection("User Id=BUD;Password=BUD;Data Source=BUD;")

		Dim cmd as OracleCommand = new OracleCommand()
		cmd.Connection	= con
		cmd.commandText	= " insert into tab1 values(:id, :clobVal) "

		Dim param1 as OracleParameter = cmd.Parameters.Add("id", OracleDbType.varchar2, _
			50, someId.text, ParameterDirection.Input)

		Dim param2 as OracleParameter = cmd.Parameters.Add("clobVal", OracleDbType.clob)



			cmd.commandText	= " select * from tab1 "
			genericDataGrid.DataSource = cmd.executereader()

		catch ex as exception
			ErrMsg.text = ex.tostring()

		end try

end sub

<form id="myForm" runat="server">

	<asp:label id="ErrMsg" runat="server"/>

	ID: <asp:textbox id="someId" textmode="SingleLine" columns=5 maxlength=5 runat="server"/>

	<asp:textbox id="someText" textmode="MultiLine" width="400" rows="5" runat="server"/>

	<asp:Button id="SubmitButton" height="22" width="60" OnClick="callProc" runat="server" Text="Go"/>

	<asp:datagrid id="genericDataGrid" runat="server" />



Open in new window


Author Comment

ID: 35148430
thanks for that. but stuck for the moment though on getting the ODP.NET connection to work. installed it ok but now can't connect now either using ODP or the previous way using OraOLEDB.Oracle. I've put that question into another thread if you have any insights. Otherwise I'll have to solve that before i can try and get this CLOB issue working...
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35149064
I saw the other question.  You are in good hands there.  There really wasn't anything else I could add.

It almost has to be the tnsnames.ora file not being in the network/admin folder for the home of the ODAC install.

Author Comment

ID: 35275122
sorry as with other question i got sidetracked... but will have to start working again on this either fri or monday... so i'll just get back to you on how i go then

thanks for patience...

Author Comment

ID: 35727517
Hi, ok I will close off this question now, decided to go ODP.NET route as suggested by slightwv as this seems to make sense as a dataprovider and will presumably solve this issue although I haven't been able to get it working as of yet.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35728250
>>although I haven't been able to get it working as of yet.

The code I posted in http:#a35140226 isn't working or you?

Author Comment

ID: 35728795
well not yet because i haven't gotten oracle working properly on the server (related to other question), so as soon as i get that working i can connect to the oracle database on the server and then test out the code above...

but you're right, thanks, i might have forgotten about that code... i will hopefully get to use it when I have everything set up

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question