asked on

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 = "INSERT INTO CSAT_Data"
strSQL = strSQL + " (History)"
strSQL = strSQL + " VALUES ('more than 4000 characters')

Open in new window

Sean Stuber

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

values(?)

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

AidenA

ASKER

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 = "INSERT INTO CSAT_Data"
strSQL = strSQL + " (History)"
strSQL = strSQL + " VALUES (:" & strBind & ")"

Open in new window

slightwv (䄆 Netminder)

>>the reference didn't appear when i went to select it using add reference?

You need to download and install the Oracle ODAC.
http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

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

That is not a bind variable.

Check out the examples in:
http://download.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm

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

slightwv (䄆 Netminder)

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.

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AidenA

ASKER

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)

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" %>


<html>
<title>CLOB test</title>
<body>
<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)

		param2.value=someText.text

		try
			con.open()
			cmd.ExecuteNonQuery()


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


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

		finally
			cmd.Dispose()
			con.Close()
			con.Dispose()
		end try


end sub
</script>

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

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

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

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

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



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

</form>

</body>
</html>

Open in new window

AidenA

ASKER

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...

https://www.experts-exchange.com/questions/26890514/ODP-NET-installed-but-now-I-think-there-might-be-two-oracle-homes-cannot-connect-anyway.html?anchorAnswerId=35148343#a35148343

slightwv (䄆 Netminder)

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.

AidenA

ASKER

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...

AidenA

ASKER

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)

>>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?

AidenA

ASKER

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