Oracle 11g - How to call a function with DML inside of it?

Question

I made a function like ...

create or replace function
  pile_data 
  (v_id IN NUMBER, v_area IN VARCHAR2, v_cust_id IN NUMBER)
  return VARCHAR2
AS
  rs VARCHAR2(2);
  cur_id NUMBER;
  -- PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  select if_seq.currVal into cur_id from dual;
  select '00' into rs from dual;   
  insert into IF_WORK
    (id, area, cust_id, rc)
    values 
    (if_tx_seq.nextVal, v_area, v_cust_id, rs);
  update IF_WORK set rc=rs where id = cur_id;
  return rs;  
  exception
    when dup_val_on_index then
      select '01' into rs from dual;
    return rs;
end;

And I got 2 questions

1. How to call this function as test?
2. Is it possible to return value in exception statement?

Follow code is what I want to implement(I made it with java, but have to implement with PLSQL)

public String pile_data(String ... params){
        String rs = "00";
        int cur_id = SEQ.currVal;
        try{
            insert(params);
        }catch(Exception ex){
            //HANDLING ERRORS
            String exceptionName = ex.getClass().getName();
            switch (exceptionName) {
            case "KEY_DUPLICATION":
                rs = "01";
                break;
            case "CONNECTION_TIMEOUT":
                rs = "02";
                break;
            default:
                rollback();
                rs = "99";
                break;
            }
        }finally{
            // UPDATE ORIGINAL TABLE
            update(cur_id, rs);
        }
        return rs;
    }

My Conditions about the code.

• Have to return anyway(SUCCESS OR NOT)
• Have to update, whether insert success or not

I am sorry about java code, but this is what I can explain best. Thanks for answers. b

======== Edit

Sorry I did not told what I have failed. (Error Code from Oracle, message translated by myself)

1. execute pile_data(params ... );. it returns this error ORA-06550 : it is not a procedure or not defined
2. select pile_data(params) from dual;. it returns error ORA-14552: Can not execute DDL, Commit, Rollback which is inside of Query or DML

================= Edit #2 ================

So I am now trying to change this to a procedure with return value. However, since caller need to get result, I wrapped with function.

create or replace procedure
  pile_data 
  (params ... , rs OUT VARCHAR2)
IS 
  cur_id NUMBER;
BEGIN
  select if_seq.currVal into cur_id from dual;
  select '00' into rs from dual;   
  insert into IF_WORK
    (target params ..., rc)
    values 
      (params ..., rs);
  update IF_WORK set rc=rs where tx_id = cur_id;
  -- DBMS_OUTPUT.PUT_LINE(rs); -- it does not work  
  exception
    when dup_val_on_index then
      select '01' into rs from dual;
      -- DBMS_OUTPUT.PUT_LINE(rs);
    when others then 
      rollback;
      select '99' into rs from dual;
      -- DBMS_OUTPUT.PUT_LINE(rs);
end;


create or replace function pile_data_wrapper(params ...)
return varchar2
is
  rs varchar2(2);
begin
  pile_data(params ... , rs);
  return rs;
end;

select pile_data_wrapper(params ... ) from dual;

And I got still ORA-14552.

I got a dilemma, a function cause an error and a procedure can not return. I need better solution for it.

The goal I wanted is below

• A remote DB will call my function, I need to return response code.
• In this function I have to insert and update a table.

Thanks

Answer 1

I can not say I solve this problem, but I found something. ORA-14552 error does not happen when I run this in sqlplus. That error only happens in sql developer.

follow is how I ran this in SQLPlus(terminal)

    SQL> var tmp varchar2(2);                          -- define a variable
    SQL> execute :tmp := PILE_DATA@DB_LINK('a', 1, 2); -- set value to variable
    SQL> print tmp;                                    -- print the result for checking

================================================

Other option is making a wrapper function which handle Exception.

i.e. Make a function without Exception handling like below.

FUNCTION CREATEWITH(v_id IN NUMBER,v_value1 IN VARCHAR2,v_value2 IN NUMBER) 
return Number
Is
BEGIN
    insert into DEVICE_BALJU(id, value1, value2)
    values (v_id, v_value1, v_value2);

return SQL%ROWCOUNT ;
END CREATEWITH; 

And build a wrapper class which handles Exception

i.e.

create or replace FUNCTION "FN_WRAPPER" (v_id IN VARCHAR2,v_value1 IN NUMBER,v_value2 IN NUMBER)
RETURN Varchar2
is
  rs VARCHAR2(2);
  tmp NUMBER;
begin
--    DBMS_OUTPUT.PUT_LINE('Hello!!!');
    rs := '00';
    tmp := createWith(v_id, v_value1, v_value2);
    RETURN rs;

    EXCEPTION
        WHEN OTHERS THEN
          rs := '99';
          RETURN rs; 
END;

This is how I avoid this error on final.

Answer 2

Yes and yes :)

in order to test a function you can 1. save it as a database object by placing it in a package. or you can run it using the execute or exec keyword within your workbench (i usually do this in SQL developer)

(PWLSQL)you can always return a value in an exception block by using the return keyword.

(JAVA)you could write your own class that inherits from the exception class and write your own method that returns a certain value.

I hope this helps.