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
kgene521Asked:
Who is Participating?
 
Jerry_PangCommented:
Returning clause is not a standard SQL command.
Returning Clause command is external to the SQL, it cannot be understood OUTSIDE of the database.
it can only be run on Oracle Products only.

I could not find any links to gave you any proof, there are no information in oracle's site that tells me that this is possible outside of oracle software.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2073654
0
 
pinaldaveCommented:
Hi kgene521,

No it will not support.

Regards,
---Pinal
0
 
kgene521Author Commented:
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

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
pinaldaveCommented:
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
0
 
mrichmonCommented:
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.
0
 
kgene521Author Commented:
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.
0
 
pinaldaveCommented:
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
0
 
kgene521Author Commented:
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
0
 
Jerry_PangCommented:
similar post
Do oracle sequences get recognized by Coldfusion?
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21146809.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.
0
 
pinaldaveCommented:
Please visit for more info : http://www.macromedia.com/support/
If there is proof they will have it.

Regards,
---Pinal
0
 
pinaldaveCommented:
Also there is phone and email support link : hope you can take their words as proof.
Regards,
---Pinal
0
 
mrichmonCommented:
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.
0
 
pinaldaveCommented:
hello Jerry,
that page requires the username and password just for your referance.
0
 
kgene521Author Commented:
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.
0
 
mrichmonCommented:
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.
0
 
pinaldaveCommented:
Totally Agree with Mrichmon.
0
 
Jerry_PangCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.