Solved

Need help in executing dynamic stored procedure in PB

Posted on 2006-06-21
5
758 Views
Last Modified: 2013-12-26
Hi Everybody,
 
I am using ORACLE( 10g Release 2 ) for my Power Builder( PB8 ) application.
 
I have created following stored procedure
 
CREATE OR REPLACE PROCEDURE SP_out_parm_demo( OUT_param_value OUT varchar2(10))
as
Begin
 OUT_param_value := '100';
END;
 

and i want to execute this stored procedure dynamically at runtime,
as any stored procedure can be executed at runtime( the Stored procedure name will be decided at the runtime ) which is having only one varchar output parameter( the above procedure is just an example of the Stored procedure ).
 

In this case how can i execute the stored procedure in PB script and return the value of the OUTPUT PARAMETER.
 

can any body help me, how can i execute this dynamic stored procedure( syntax ) ?

Thanks,
0
Comment
Question by:xsysys
5 Comments
 
LVL 8

Accepted Solution

by:
gajender_99 earned 68 total points
ID: 16958310
CREATE OR REPLACE PROCEDURE SP_out_parm_demo( OUT_param_value OUT varchar2(10))
as
Begin
 OUT_param_value := '100';
END;

To declare that procedure for processing within PowerBuilder, you code:
string dept
dept=space(10)
DECLARE dept_proc PROCEDURE FOR
      SP_out_parm_demo(:dept);

Note that this declaration is a non-executable statement, just like a cursor declaration. Where cursors have an OPEN statement, procedures have an EXECUTE statement.

When the EXECUTE statement executes, the procedure is invoked. The EXECUTE refers to the logical procedure name.

EXECUTE dept_proc;

hope this will help you

Gajender
0
 
LVL 6

Assisted Solution

by:tr1l0b1t
tr1l0b1t earned 66 total points
ID: 16990871
Example :

CREATE OR REPLACE PROCEDURE spm1
(dept varchar2, mgr_name OUT varchar2)
      IS lutype varchar2(10);
      BEGIN
      SELECT manager INTO mgr_name FROM mgr_table
      WHERE dept_name = dept;
      END;


Processing within PowerBuilder ---------------------

We create a global function which receives dept code
and returns manager name :

String f_getmgrname(string as_dept)


      String ls_retval

      DECLARE dept_proc PROCEDURE FOR spm1(:as_dept) USING SQLCA ;

      If SQLCA.SqlCode < 0 Then
            MessageBox("Error SQLCODE:" + String(SQLCA.SqlCode), SQLCA.sqlerrtext)
            Return "-"
      End If

      EXECUTE dept_proc;
      If SQLCA.SqlCode < 0 Then
            MessageBox("Error SQLCODE:" + String(SQLCA.SqlCode), SQLCA.sqlerrtext)
            Return "-"
      End If

      FETCH dept_proc INTO :ls_retval ;
      If SQLCA.SqlCode < 0 Then
            MessageBox("Error SQLCODE:" + String(SQLCA.SqlCode), SQLCA.sqlerrtext)
            Return "-"
      Else
            CLOSE dept_proc;
            Return ls_retval
      End If


Hope it helps
Regards
0
 
LVL 4

Assisted Solution

by:Lordain
Lordain earned 66 total points
ID: 17053255
Personally I would rather use the Transaction object to make my procedure calls.

Create a user object and map it to SQLCA.  I call mine U_RPCFUNC.

In the local external function section of of the U_RPCFUNC you make your declarations

Example:
Use Function key word when you want to return a value such as you indicated
Use Subroutine key word when no return is desired

FUNCTION <return datatype> output_demo(<datatype> <parm1>, <datatype> <parm2>) RPCFUNC ALIAS FOR "<locational structure>.<locational structure>.SP_out_parm_demo"

You then call it as follows:

      RETURN = SQLCA.output_demo(<parm1>, <parm2>)
      
      CHOOSE CASE SQLCA.SQLCODE
                  
            CASE 0
                  //SETMICROHELP("RPC CALL SUCCESSFULL")
                  
            CASE ELSE
                  MESSAGEBOX("REMOTE PROCEDURE CALL ERROR", SQLCA.sqlerrtext)
                  
      END CHOOSE
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now