Solved

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

Posted on 2011-03-14
15
944 Views
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 = "INSERT INTO CSAT_Data"
strSQL = strSQL + " (History)"
strSQL = strSQL + " VALUES ('more than 4000 characters')

Open in new window

0
Comment
Question by:AidenA
  • 6
  • 6
  • 3
15 Comments
 
LVL 73

Expert Comment

by:sdstuber
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

values(?)
0
 
LVL 76

Accepted Solution

by:
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.
0
 
LVL 73

Expert Comment

by:sdstuber
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
0
 

Author Comment

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

Open in new window

0
 
LVL 76

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.
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.
0
 
LVL 76

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.
0
 
LVL 73

Assisted Solution

by:sdstuber
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




0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AidenA
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
0
 
LVL 76

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

0
 

Author Comment

by:AidenA
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...

http://www.experts-exchange.com/Database/Oracle/Q_26890514.html#a35148343
0
 
LVL 76

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

Author Comment

by:AidenA
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...
0
 

Author Comment

by:AidenA
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.
0
 
LVL 76

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

Author Comment

by:AidenA
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now