Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1873
  • Last Modified:

ORA-03113 error while executing an Oracle function from PowerBuilder

Hi,


My Stored Procedure is
------------------------------------------------------------------------
create or replace procedure concatname(a varchar2,
                              b varchar2,
                              c out varchar2)
  as
  begin
    c := a || b;
  end;
 


My Function is
--------------------------------------------------------------------
create or replace function ex(sp varchar2)
  return varchar2 as
   v varchar2(100);
  begin
    execute immediate 'begin '||sp||'(''test'','' procedure'',:o); end;'
    using out v;
    return v;
  end;
 


Iam using PowerBuilder 8 as a FRONTEND Tool( connecting to ORACLE db through ODBC driver) for executing the above function where i will pass the stored procedure name ( CONCATNAME ) as an argument.


While executing this function from the frontend tool iam getting the following error.

SQLCA.SqlErrText = ''SQLSTATE - 08S01||[ORACLE][ODBC][ORA]ORA-03113: end-of-file on communication channel.

I dont know, why this error is coming ?  As the function is executing fine through ISQL ?

Can anybody help me ? How to avoid this error ( ORA-03113 ) ?
0
xsysys
Asked:
xsysys
  • 2
2 Solutions
 
michaelstoffelCommented:
Execute immediate won't work with stored procedures where you are expecting results back.  It shouldn't have disconnected you from the database, but it still wouldn't have worked.

Check out Remote Procedure Calls,declaring in the help.  Basically, create a custom transaction object and in the local external functions, declare your procedure as:
SUBROUTINE concatname(string a, string b, ref string c) RPCFUNC;

Then simply call as SQLCA.concatename(a,b,c).

If you want dynamic invocation as indicated by your function, you really need to check out Dynamic SQL Syntax 4 in the help, which is not trivial.
0
 
LordainCommented:
I agree with Michael as I find this to be a better practice.  My only comment is since you want a return you declare it as a function.  Use Subroutine when you don't want a return.
0
 
michaelstoffelCommented:
I agree, the current Oracle procedure should be a function returning a VARCHAR2 instead of an OUT parameter and then change the corresponding PowerBuilder declaration to match.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now