Home » Infrastructure » Windows » INSERT INTO RETURNING (ADO/OLEDB 11gr2 and up)
INSERT INTO RETURNING [message #672128] Wed, 03 October 2018 10:25 Go to next message
Michel Cadot
Messages: 67444
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does anyone knows and has an example of using INSERT with RETURNING clause in VBA using ADO/OLE DB.
I failed to get any other value than 0 in the returning parameter.

Here's my current code (at least one of those I tried):
        SQL = _
"INSERT INTO gdpr_composants (id, nom, basicat, ocarto, traitement) " & _
"VALUES (gdpr_seq.nextval, TRIM(?), UPPER(?), LPAD(?,5,'0'), TRIM(?))" & _
"RETURNING id INTO ?"
        Set OraCmd2 = New ADODB.Command
        With OraCmd2
          .CommandText = SQL
          .CommandType = adCmdText
          .ActiveConnection = OraCon
          .Parameters.Append .CreateParameter("nom", adVarChar, adParamInput, 80, CompName)
          .Parameters.Append .CreateParameter("basicat", adVarChar, adParamInput, 3, CodeBasicat)
          .Parameters.Append .CreateParameter("ocarto", adVarChar, adParamInput, 5, CodeOC)
          .Parameters.Append .CreateParameter("traitement", adVarChar, adParamInput, 500, TraitName)
          .Parameters.Append .CreateParameter("outid", adInteger, adParamOutput, 10, CompId)
'          .Execute Nb, Options:=adExecuteNoRecords
          Set OraReturn = .Execute
        End With
'        CompId = OraReturn("outid")

The row is actually inserted but the returned value is always 0 when the id is different in the database.

Also posted at https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=91506

[Edit: Specify language and API, and link to DBA-Village]

[Updated on: Thu, 04 October 2018 00:47]

Report message to a moderator

Re: INSERT INTO RETURNING [message #672132 is a reply to message #672128] Wed, 03 October 2018 12:45 Go to previous messageGo to next message
John Watson
Messages: 8390
Registered: January 2010
Location: Global Village
Senior Member
It works for me,
pdby1>
pdby1> var v1 number
pdby1> insert into dept values((select max(deptno)+10 from dept),null,null) returning deptno into :v1;

1 row created.

pdby1> print v1

        V1
----------
        50

pdby1>
or am I missing something?
Re: INSERT INTO RETURNING [message #672133 is a reply to message #672132] Wed, 03 October 2018 13:22 Go to previous message
Michel Cadot
Messages: 67444
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You missed I want to do it with ADO/OLEDB (in VBA) (but I should repeat it in the text in addition to the title).
I know it works in SQL*Plus or PHP or Pro*C or OO4O or..., I often do it. Smile
Actually I am trying to convert an OO4O code (which is no more supported) to an OLE DB one (that I "know" since... only a week). I didn't find any example on the web, finding this seems to exceed my skills on Google search engine.

[Updated on: Wed, 03 October 2018 13:23]

Report message to a moderator

Previous Topic: How do I pass in double quotation argument from dos to rman?
Next Topic: SQL Loader Data Load into table Automation
Goto Forum:
  


Current Time: Wed Oct 21 20:32:39 CDT 2020