CODEWITHSUNDEEP
oracle-databasefunctionplsqlcallpowerbuilder
XLD_a
XLD_a

Reputation: 195

Execute Oracle function from PowerBuilder

I have an Oracle function with one argument. I need to call it from PowerBuilder. Is this possible? If so, how? Thanks!

My function:

CREATE OR REPLACE FUNCTION OPEN.F_VALIDATION (f_date DATE)
   RETURN NUMBER
IS
v_an number;
v_debug_line varchar2(20);
BEGIN
/*some relevant code - delete, selects, updates, inserts*/

DBMS_OUTPUT.put_line ('OK');
   COMMIT;
   RETURN v_an;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('v_debug_line: ' || v_debug_line);
      DBMS_OUTPUT.put_line (
         'ERROR: ' || SQLERRM);
      ROLLBACK;

      DBMS_OUTPUT.put_line ('ERROR');
      RETURN v_an;
END F_VALIDATION ;

Upvotes: 0

Views: 2804

Answers (3)

semmons123
semmons123

Reputation: 16

Another way to call an Oracle function in PowerBuilder, assuming your app is already connected to the database:

long l_rc
date d_date = today()

SELECT open.f_validation( :d_date )
INTO :l_rc
FROM dual;

if sqlca.sqlcode <> 0 then
    messagebox( "Exception", "Unable to validate:~r~n" + sqlca.sqlerrtext )
end if

Upvotes: 0

Eric Glenn
Eric Glenn

Reputation: 399

In this example, the Oracle function is declared in the same schema as the application connects. 

CREATE OR REPLACE FUNCTION F_VALIDATION(f_date DATE) RETURN NUMBER IS
  v_an number;
begin

  /* If f_date is today then 1 else 0 */
  select decode(to_date(sysdate), f_date, 1, 0) into v_an from dual;
  RETURN v_an;

EXCEPTION
  WHEN OTHERS THEN
    /* This will never happen */
    v_an := -1;
    RETURN v_an;
END F_VALIDATION;

To declare stored procedures as external functions for the user object:

  • In the Script view in the User Object painter, select [Declare] from the first list and Local External Functions from the second list.

  • Place your cursor in the Declare Local External Functions view. From the pop-up menu or the Edit menu, select Paste Special>SQL>Remote Stored Procedures.

  • PowerBuilder loads the stored procedures from your database and displays the Remote Stored Procedures dialog box.

  • Select the names of one or more stored procedures that you want to declare as functions for the user object, and click OK.

  • PowerBuilder retrieves the stored procedure declarations from the database and pastes each declaration into the view.

  • For example, here is the declaration that displays on one line when you select F_VALIDATION:

    function double F_VALIDATION(datetime F_DATE) RPCFUNC ALIAS FOR "F_VALIDATION"

Assumptions:

  • Your SQLCA is connected to the same schema where F_VALIDATION was compiled.

  • The PowerBuilder code exists from the object where you declared F_VALIDATION.

Here is an example of how to call the function.

double ld_result
ad_date = datetime( today(), now() )

ld_result = F_VALIDATION( ad_date )

if sqlca.sqlcode = -1 then
    //Some error handling
    //messagebox("Unexpected error", "Error calling F_VALIDATION. database error message=" + sqlca.sqlerrtext )
    ld_result = -1
end if

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142753

I don't know how to call an Oracle function from PowerBuilder - Google knows, for example on PowerBuilder: Calling Oracle Stored Procedures and Functions, saying that

... a good approach is to first declare it as an external function and then invoke it based on that declaration.

I don't feel like copy/pasting that contents over here because it'll most probably be wrong (as I said, I don't use that software product).

However, I have some objections on code you posted. I apologize if it goes off-topic.

"Some relevant code" - this won't work just like that; what DML (and why) do you perform in that function? 

SQL> create or replace function f_test
  2    return number
  3  is
  4  begin
  5    insert into dept (deptno, dname) values (99, 'test');
  6    return 1;
  7  end;
  8  /

Function created.

SQL> select f_test from dual;
select f_test from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.F_TEST", line 5

If you want to perform DML in a function, you miss the PRAGMA AUTONOMOUS_TRANSACTION and COMMIT the DML (though, COMMIT is there already so maybe there's the pragma ass well), but that's most probably NOT a good idea.

SQL> create or replace function f_test
  2    return number
  3  is
  4    pragma autonomous_transaction;
  5  begin
  6    insert into dept (deptno, dname) values (99, 'test');
  7    commit;
  8    return 1;
  9  end;
 10  /

Function created.

SQL> select f_test from dual;

    F_TEST
----------
         1

SQL>

AskTom says that such things are evil, so - consider using a procedure with an OUT parameter instead.

Also, you do realize that such an EXCEPTION handler is of no use for end users? They can't ever see DBMS_OUTPUT.PUT_LINE result. On the other hand, seeing "v_debug_line" suggests that you use it for debugging purposes - in that case, I have no objections on that.

But really, try not to use DML in a function.

Once again, sorry if it is irrelevant & you insist on keeping it the way it is.

Upvotes: 1

Related Questions