EasyCFM.COM ColdFusion Forums / Oracle Related Issues / Oracle Error with ColdFusion script

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Oracle Error with ColdFusion script

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

tryston02
11-10-2004 @ 11:38 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 1
Joined: Nov 2004

Hey guys, here's the situation. We have a rather large Oracle database and have a requirement to change the field in one table from a LONG to a CLOB, however just doing so will result in us losing the data that was stored there. So I created another table that was exactly the same as the one before just this time the field is a CLOB. Then I wrote a ColdFusion script to query the first table and then output its results into the new table. But, I keep getting this Oracle error: ORA ERROR - 000936 Missing expression. This sounds like a simple error, but form some reason I cannot find the cause. Here is the code I used:




<cfquery name="getLongData" datasource="#application.maindsn#">
select block_id, section_id, order_x, order_y, block_text, block_style
from block
</cfquery>

<cfoutput query="getLongData">

<cfquery name="insertClobData" datasource="#application.maindsn#">
insert into block2
(block_id, section_id, order_x, order_y, block_text, block_style)
values (seq_block2.NEXTVAL, #section_id#, #order_x#, #order_y#, '#block_text#', '#block_style#')
</cfquery>

</cfoutput>

<center>
Data conversion complete
</center>



Thanks guys, any help would be apprecited.

-Chris

Webmaster
11-10-2004 @ 7:14 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Why NOT insert based ona select?

INSERT INTO block2(block_id, section_id, order_x, order_y, block_text, block_style)
SELECT block_id, section_id, order_x, order_y, block_text, block_style
FROM block

Also, what are the field types? Can you give us a quick break down?

Thanks,
Pablo Varando
Team Macromedia Member
=====================================================

This message was edited by Webmaster on 11-10-04 @ 7:14 PM


Website Designed and Developed by Pablo Varando.