CODEWITHSUNDEEP
sqloracle-databasefunction
generous enq
generous enq

Reputation: 1

How to update a column in a table based on the result of a external function?

I'm running into a situation where I received a external function from 3rd party, FUNCT(a1,a2,a3, ZZZ). This function accepts values for a1, a2, a3 and returns a number ZZZ.

I also have similar table structure, column1, column2, column3, Amount. I would like to match the table value of column1 to a1, column2 to a2, column3 to a3 and read the output of ZZZ and update into the Amount column in the table. I see go about cursor. Please help how it can be updated.

Thanks in advance.

EDIT

(Copied from OP's comment on answer below)

I received a function not procedure. I was told to call the function and update. here is the code I tried running into the issue.

DECLARE
  pass varchar2(100);
  func_amt number;
BEGIN
  update table yt
    set yt.amount = func_amt
    where XYXFUNC(yt.a1,yt.a2, yt.a3,yt.a4,yt.5, pass , func_amt );
END;

Upvotes: 0

Views: 109

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50047

I'm guessing that what you've got is defined as

CREATE OR REPLACE PROCEDURE FUNCT(a1  IN  VARCHAR2,
                                  a2  IN  VARCHAR2,
                                  a3  IN  VARCHAR2,
                                  ZZZ OUT NUMBER)
AS
  -- ...whatever...
END FUNCT;

A PROCEDURE, such as the one you've been given, cannot be called directly from SQL. Only FUNCTIONS, which return a value via the RETURN statement, can be called from SQL.

However, all is not lost. What you need is a wrapper function which calls your third party procedure, passing the appropriate arguments, captures the returned value, and then uses the RETURN statement to return the value to the caller. If you define the following function:

CREATE OR REPLACE FUNCTION WRAPPER_FUNC(a1  IN  VARCHAR2,
                                        a2  IN  VARCHAR2,
                                        a3  IN  VARCHAR2)
  RETURN NUMBER
AS
  ZZZ  NUMBER;
BEGIN
  FUNCT(a1, a2, a3, ZZZ);

  RETURN ZZZ;
END WRAPPER_FUNC;

you can then use WRAPPER_FUNC from an SQL statement as:

UPDATE YOUR_TABLE yt
  SET yt.AMOUNT = WRAPPER_FUNC(yt.COLUMN1, yt.COLUMN2, yt.COLUMN3)

Add any WHERE clause you need to the UPDATE statement and you should be good to go.

Upvotes: 1

Related Questions