asked on

CF and Oracle 9i: INSERT INTO table_a ... RETURNING ID INTO variable_a

The question of getting the new ID after INSERT keeps popping up in forums but my question is very specific. I don't need any workarounds like MAX(ID), sequences, triggers, timestamps, etc.

Here is my question:

Does ColdFusion(6.1)/JRun4/JDBC support this specific Oracle 9i extension to SQL and, if it does, what is the CF syntax of doing it:

Table: table_a
ID number(10)
name varchar2(50)
address varchar2(50)

Trigger and sequence are used to generate a new ID value

Oracle 9i SQL:

INSERT INTO table_a (name, address) VALUES ('Bob', '100 Main st') RETURNING ID INTO v_variable_id

Thanks

pinaldave

Hi kgene521,

No it will not support.

Regards,
---Pinal

kgene521

ASKER

Any more details besides stating the fact? Where did you get this information from? Macromedia documentation? What is the source? You use future tense in your statement. Do you have any information of the plans by Macromedia not to support it in the future? Where did you get this information from?
I need specific proof one way or another.

Thanks

pinaldave

Hi kgene521,
I do not have any proof... this is what I happen to know. Maybe someother expert will post the proof.
I have tried to use before the oracle special sequence and never got sucess...
may be you can use triggers but I am not aware how to code them.

Regards,
---Pinal

mrichmon

I would think that you could use a stored procedure that is triggered from CF and that basically executes the above code and then returns the variable back to CF.

So it may be a wrapper, but should work.

kgene521

ASKER

Stored procedure is not going to work for several reasons. I need either to get it work directly from CF or find out if it is not possible with CF.

Again, I don't need workarounds.

Thanks for your responses, but they have not answered my question yet.

pinaldave

Hi kgene521,
It is not possible directly through CF. That is answer.
It is from experience so there is no documentation to support the claim.

Regards,
---Pinal

kgene521

ASKER

I need proof. I cannot just take your word for it. Prove it with documentation and code. This is not a specific programming problem, this is fundamental issue of Macromedia's support of an Oracle 9i SQL extension. So just stating the fact is not going to be enough. This is one of those tricky, seemingly easy questions that buffle you big time.

Thanks

Jerry_Pang

similar post
Do oracle sequences get recognized by Coldfusion?
https://www.experts-exchange.com/questions/21146809/Sequence-question.html

i have no proof. i run into some sql functions in sybase before that where not supported in coldfusion, i had to look for proof but i could not find any on the web, the only proof i gave to my boss is the documentation found in the help files.

Like - Export function
like
select * from statement
export to file

it turns out that the export function only works on ISQL (Interactive sql). so it wont work on other external programs like coldfusion or any third party tools.

its probably same in your case.

pinaldave

Please visit for more info : http://www.macromedia.com/support/
If there is proof they will have it.

Regards,
---Pinal

pinaldave

Also there is phone and email support link : hope you can take their words as proof.
Regards,
---Pinal

ASKER CERTIFIED SOLUTION

Jerry_Pang

membership

This solution is only available to members.

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

Start Free Trial

mrichmon

If you need proof this is not the place to get it.

We can give you guidance, suggestions, comments from experience, but the only people who can give you a definitive answer is Macromedia.

Here is the phone number to call for support for Cold Fusion: 1-415-252-9080

That is the only way to get what you want.

pinaldave

hello Jerry,
that page requires the username and password just for your referance.

kgene521

ASKER

Thanks to everybody, if I insisted on getting the impossible done no apologies. The programming is always "show me the code" thing, and not "I'm the expert, take my word for it". Been there, heard that.

Take care, people, you are all great help.

mrichmon

Really Jerry's answer deserved m0ore.

I think you should be more polite to the people trying to help if you expect anyone to continue to try and answer your questions.

pinaldave

Totally Agree with Mrichmon.

Jerry_Pang

thanx.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2073654
returning_clause
The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE) statement. You can specify this clause for tables and materialized views, and for views with a single base table.

When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.

expr
Each item in the expr list must be a valid expression syntax. All forms are valid except scalar subquery expressions.

INTO
The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.

data_item
Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.

Restrictions on the RETURNING Clause
You cannot:

Specify the returning_clause for a multitable insert.
Use this clause with parallel DML or with remote objects.
Retrieve LONG types with this clause.
Specify this clause for a view on which an INSTEAD OF trigger has been defined.