Home » SQL & PL/SQL » SQL & PL/SQL » Get Return Value from Function without SELECT
Get Return Value from Function without SELECT [message #662521] Thu, 04 May 2017 16:48 Go to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
Maybe a bonehead question ....

Normally, when I need a return value from a function, I do
SELECT {function call} FROM DUAL;

My challenge is that I need to run a function that inserts a new record to another table and returns the PK of the newly created record.

Unfortunately, if I run this using the above SELECT syntax, Oracle fails and says that it cannot do an INSERT or UPDATE as part of a SELECT statement.

How then would I accomplish this without a SELECT?

I can do this with a BEGIN/END block, but then how do I get at the return value?

Thanks.

Wayne
Re: Get Return Value from Function without SELECT [message #662523 is a reply to message #662521] Thu, 04 May 2017 18:27 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

A_VARIABLE := <a_function>; -- valid syntax

SYSDATE is an Oracle supplied function
INSERT INTO SALES_ORDER (SALES_ID, SALES_DT) VALUES (1,SYSDATE);

Learn something new today by Reading The Fine Manual

http://docs.oracle.com/database/122/SQLRF/INSERT.htm#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423__I2122356
Re: Get Return Value from Function without SELECT [message #662524 is a reply to message #662521] Fri, 05 May 2017 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I can do this with a BEGIN/END block, but then how do I get at the return value?

With SQL*Plus:
SQL>  create or replace function f return varchar2 is begin return 'The result'; end;
  2  /

Function created.

SQL> var r varchar2(50)
SQL> begin :r := f(); end;
  2  /

PL/SQL procedure successfully completed.

SQL> print r
R
--------------------------------------------------------------------------------------
The result
Same thing with your programming language.
Re: Get Return Value from Function without SELECT [message #662525 is a reply to message #662521] Fri, 05 May 2017 01:11 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Can you use a RETURNING clause?
orclz>
orclz> var n number
orclz> insert into dept values(50,'new','uk') returning deptno into :n;

1 row created.

orclz> print n

         N
----------
        50

orclz>
Re: Get Return Value from Function without SELECT [message #662561 is a reply to message #662525] Fri, 05 May 2017 08:36 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
My problem is that I need to return the function's return value back to MS Access via ODBC. Currently, I have an Access function that will go out and get the next sequence from Oracle, and I can then insert that as a FK into a secondary table. But I'd like to be able to get the sequence and insert the record on the Oracle side and just send back the sequence for insertion into the referencing table.

Not sure how to do that without using SELECT {function that inserts record and returns PK}.

Wayne
Re: Get Return Value from Function without SELECT [message #662564 is a reply to message #662561] Fri, 05 May 2017 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
How does Oracle obtain all the column values that need to be contained by INSERTED row?
Is PK populated by SEQUENCE? Which SEQUENCE? By Before INSERT TRIGGER?
Re: Get Return Value from Function without SELECT [message #662565 is a reply to message #662564] Fri, 05 May 2017 09:45 Go to previous messageGo to next message
whdyck
Messages: 17
Registered: May 2017
Junior Member
Oracle will get the column values for inserted row from an Oracle function that gets invoked by Access. So Access invokes an Oracle function, which computes some of the inserted values (and grabs the next sequence), then does the insert. I want this function to return the new PK (from sequence) so I can store it in the referencing record in Access.
Re: Get Return Value from Function without SELECT [message #662566 is a reply to message #662565] Fri, 05 May 2017 10:23 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+function+doing+dml+operation
Re: Get Return Value from Function without SELECT [message #662567 is a reply to message #662565] Fri, 05 May 2017 12:00 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So what is exactly your problem?
You don't know how to call a PL/SQL function from Access?

Previous Topic: Using Virtual Column
Next Topic: Adding missing values in external table
Goto Forum:
  


Current Time: Sat Feb 24 17:56:48 CST 2018

Total time taken to generate the page: 0.06735 seconds