Highlighted

Oracle 11g: Issue with INSERT ALL

LEGEND ,
Apr 19, 2018

Copy link to clipboard

Copied

Hello, all,

I'm trying to migrate data from an old schema to a new schema via ColdFusion, and I'm getting an error message.

<cfquery name="old_data" datasource="#application.dsn_old#">

SELECT colA, colB, colC

FROM old_table

</cfquery>

<cfquery name="new_data" datasource="#application.dsn_new#">

INSERT ALL

     <cfoutput query="old_data">

          into new_table(aCol, bCol, cCol)

               values(colA, colB, colC)

     </cfoutput>

SELECT * from dual;

</cfquery>

904: ORA-00904: "colC": Invalid identifier

If I CFDUMP the first query, all the data is there.  Why does Oracle think that colC is invalid?

V/r,

^ _ ^

TOPICS
Database access

Views

677

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Oracle 11g: Issue with INSERT ALL

LEGEND ,
Apr 19, 2018

Copy link to clipboard

Copied

Hello, all,

I'm trying to migrate data from an old schema to a new schema via ColdFusion, and I'm getting an error message.

<cfquery name="old_data" datasource="#application.dsn_old#">

SELECT colA, colB, colC

FROM old_table

</cfquery>

<cfquery name="new_data" datasource="#application.dsn_new#">

INSERT ALL

     <cfoutput query="old_data">

          into new_table(aCol, bCol, cCol)

               values(colA, colB, colC)

     </cfoutput>

SELECT * from dual;

</cfquery>

904: ORA-00904: "colC": Invalid identifier

If I CFDUMP the first query, all the data is there.  Why does Oracle think that colC is invalid?

V/r,

^ _ ^

TOPICS
Database access

Views

678

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 19, 2018 0
LEGEND ,
Apr 19, 2018

Copy link to clipboard

Copied

Never mind.  Gave up the ghost.  Too much time spent on it, and I need to get stuff done.  I just bit the bullet and did individual CFQUERY within the CFOUTPUT, cringing all the way.  It worked.


V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 19, 2018 0
Advocate ,
Apr 24, 2018

Copy link to clipboard

Copied

Sorry I'm late to the party, but surely line 9 should be:

values(#colA#, #colB#, #colC#)

If any of them are not numeric, remember to include quotes.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 24, 2018 0
LEGEND ,
Apr 24, 2018

Copy link to clipboard

Copied

I actually revisited this during a break I was taking, and the solution turned out to be putting the values in CFQUERYPARAM tags.

V/r,

^ _ ^

PS:  Late or no, always good to hear your input.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 24, 2018 0
Advocate ,
Apr 25, 2018

Copy link to clipboard

Copied

cfqueryparam is definitely the more robust option. Glad you got it sorted out.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 25, 2018 0