?
Solved

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

Posted on 2004-10-04
17
Medium Priority
?
636 Views
Last Modified: 2013-12-20
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
0
Comment
Question by:kgene521
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 12221359
Hi kgene521,

No it will not support.

Regards,
---Pinal
0
 

Author Comment

by:kgene521
ID: 12221734
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
 
LVL 21

Expert Comment

by:pinaldave
ID: 12221756
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
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
LVL 35

Expert Comment

by:mrichmon
ID: 12222009
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
 

Author Comment

by:kgene521
ID: 12222887
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
 
LVL 21

Expert Comment

by:pinaldave
ID: 12222895
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
 

Author Comment

by:kgene521
ID: 12222979
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
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12223157
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
 
LVL 21

Expert Comment

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

Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12223312
Also there is phone and email support link : hope you can take their words as proof.
Regards,
---Pinal
0
 
LVL 9

Accepted Solution

by:
Jerry_Pang earned 1500 total points
ID: 12223615
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12227431
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
 
LVL 21

Expert Comment

by:pinaldave
ID: 12228225
hello Jerry,
that page requires the username and password just for your referance.
0
 

Author Comment

by:kgene521
ID: 12228329
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12228463
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
 
LVL 21

Expert Comment

by:pinaldave
ID: 12228487
Totally Agree with Mrichmon.
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12235636
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question