Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1198
  • Last Modified:

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

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

  • 6
  • 6
  • 3
2 Solutions
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

slightwv (䄆 Netminder) Commented:
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.
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

AidenAAuthor Commented:
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

slightwv (䄆 Netminder) Commented:
>>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.
slightwv (䄆 Netminder) Commented:
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.
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

AidenAAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Check out the code below.

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

<%@ import namespace = "system.data" %>
<%@ 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

AidenAAuthor Commented:
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...

slightwv (䄆 Netminder) Commented:
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.
AidenAAuthor Commented:
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...
AidenAAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>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?
AidenAAuthor Commented:
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

Technology Partners: 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!

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now