Skip to main content
P
PamG-EApboS
Inspiring
October 16, 2006
Question

<cfinsert> tablename attribute, how to format for Oracle

  • October 16, 2006
  • 1 reply
  • 258 views
I've been using <cfquery> successfully for a long time to do table inserts, but now I'd like to use <cfinsert> for the simple inserts. However, I'm getting error messages. A typical <cfquery> against my Oracle database would look like:

<cfquery name="myQuery" datasource="#application.DataSource#">
INSERT into abc.action_type_def (field1, field2, field3)
VALUES ('value1', value2'', value3'')
</cfquery>

I tried the following <cfinsert> but got the error message that follows the tag:

<cfinsert datasource="#application.DataSource#"
tablename="ABC.ACTION_TYPE_DEF"
formfields="FIELD1, FIELD2, FIELD3">

----------------------
ERROR:

Message: There are no columns found for the given table name "PRFA.ACTION_TYPE_DEF".

Detail: This error may be caused by not specifying the table name in the format that the database server expects. (For example. Oracle Server table name must be upper case or the Server table name must be qualified in the format of "schema.table_name". For the SQL Server, the format is "databasename.username.tablename", such as in customers.dbo.orders)

I've tried upper and lower case on the tablename attribute, tried leaving the "abc." off, but I just can't seem to get it to work. Any idea how to format this tag to go against an Oracle database?

Thanks! Pam

    This topic has been closed for replies.

    1 reply

    P
    paross1
    Participating Frequently
    October 17, 2006
    Word of advice: I would stay away from using cfinsert and cfupdate tags, unless you are prone to masochistic tendencies and enjoy pain and suffering. These tags are notoriously bug-prone, and cause more work than they save. Just my opinion, but I'm sure that it is one shared by many on these forums.

    Having said that, you might take a look at the attributes for this tag, as you seem to have combined the schema (table owner) with the table name, as in ABC.ACTION_TYPE_DEF, which should be listed distinctly using separate attributes, namely tableOwner and tableName respectively. Note: I don’t use these tags so this “fix” is just based on reading the LiveDocs for this tag.

    Since you have so much more control using cfquery, and you said that you have been using it for quite some time, why switch to using cfupdate/cfinsert? If you already know enough SQL to do an insert or update , then you really don't need to use these tags. You will thank me.

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices