Solved

Calling Oracle Store Proc Thru PB

Posted on 2004-04-16
5
7,273 Views
Last Modified: 2013-12-26
Hi all,

1. I want to call Oracle Stored Procedure thru my PB application. Can you please tell me how i can do this?

I am using PB 8.0.1 with Oracle 8.1.7. I want to call a store proc that will either return a single value or a result set. What is the way to call both kind of store procs from PB. I tried using the RPCFUN method but i coulden't get the result.

Can i directly attach the store proc returning resultset to the datawindow? If yes then how can i change the output of a datawindow dynamically based on the input parameter?

What is the purpose of DataWindow.Modify() method?
Can i attach the store proc returning single value to a  textbox or other control?

What are different ways for all this?

2. Also tell me how to access a value returned thru an OUT parameter from a store proc.
Ex: if i have a store proc in oracle as:
procedure showname(empno IN number, ename OUT varchar2) i.e. i am getting the name of the employee from emp table by entering the empno.
Now when i call this store proc from PB, i want to display this name into a textbox when i click on a button. How can i get the returned name value from the procedure?

Thanks
0
Comment
Question by:mayuri25
  • 3
5 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
0
 

Author Comment

by:mayuri25
Comment Utility

hi,

Thanks for the solution. I have gone thru the examples given in the link...

In the example for Stored Procedure returning Resultset, the store proc is defined in a package by first declaring a cursor. And it is returning the resultset using a cursor declared.

Is it necessary to define a store proc returning resultset , in a package only? can't it be defined separately?

I tried defining the separate store proc returning a resultset using a cursor. But it is giving error for the cursor declaration. Does this mean that the cursor must be declared first?

waiting for the solution...

Thanks
0
 

Author Comment

by:mayuri25
Comment Utility

Hi,

I am getting one problem in PowerBuilder.

I have created one store proc in Oracle 8.1.7. This procedure returns a resultset using the PBDBMS.Put_Line procedure. The code for my procedure is:

CREATE OR REPLACE PROCEDURE show_emp_details(deptno IN NUMBER)
IS
    test_eno number(2);
BEGIN
   test_eno := NVL(deptno,0);
   PBDBMS.Put_Line('SELECT empno, ename, job, sal  ');
   PBDBMS.Put_Line(' FROM scott.emp  ');
   PBDBMS.Put_Line(' WHERE deptno =  ' || test_eno);

END show_emp_details;

This procedure gets created and executed successfully in oracle. Now i want to attach this proc to a datawindow in PB. But when i tried to create a datawindow using this store proc as a datasource it gave me an oracle error as:
   Can not create datawindow
  ORA 24333:  zero iteration count
  1 execute scott.show_emp_details;0

What does this mean? Is the store proc code incorrect? what are the exact steps for using a store proc returning resultset in PB. How to attach the store proc to the datawindow and how we can change the datawindow output dynamically based on the input given to the store proc?

Pls reply ASAP.

Thanks

0
 
LVL 8

Accepted Solution

by:
gajender_99 earned 20 total points
Comment Utility
hi

when you are declaring your function you have to declare it as a subroutine where i have added the last argument as REF DOUBLE return_value"

"SUBROUTINE ABS_S_ADMIN_RECALC(DATETIME STATEMENT_DATE, REF DOUBLE RETURN_VALUE ) RPCFUNC ALIAS FOR "DBO.ABS_S_ADMIN_RECALC" "
AS A LOCAL EXTERNAL FUNCTION

double ll_rc
the variable ll_rc should be the last argument int you subroutine which can hold back the return value.

SQLCA.abs_s_admin_recalc(DateTime(idt_end_dt),ll_rc)

If SQLCA.SQLCode < 0 then
    generate error
    SQLCA.AutoCommit = FALSE
    return -1
end if

if ll_rc = -56 then
  // your condition
Else
//your condition
End if

one more thing to rembember
the PROCEDURE which you are exceuting should have the last argument defined as
IN OUT INTEGER
//Example
ABS_S_ADMIN_RECALC(a_date in datetime, b in integer, v_status IN OUT INTEGER)

so you can always return a value in V_STATUS  and get that value in ll_RC

I am working on this type of procedures for around three years.
if you have any problem let me know. i can give you a detail example.

thanks
Gajender

0
 

Author Comment

by:mayuri25
Comment Utility

Hi,

Thanks for the solution, but i am a little bit confused abt it. It will be better if you could give the detail example for this.

today I tried another method for this. I have created one procedure in oracle. The code for the proc is:

CREATE OR REPLACE PROCEDURE show_emp_details(deptno IN NUMBER,
empcur IN OUT pk_demo_cur.cur_pb_demo)
AS
   
BEGIN
    OPEN empcur FOR
    SELECT empno, ename,
    job, sal  FROM emp
    WHERE deptno = deptno;

END show_emp_details;

Where cur_pb_demo is defined in package pk_demo_cur. This procedure returns the employee info for the deptno given. Now i want to pass this deptno to the proc from my PB application. I have created a datawindow using this store proc. I have placed the datawindow on my application window and i am passing the argument to the datawindow using the Retrieve() method as:

CONNECT USING SQLCA;
dw_1.SetTransObject(SQLCA)
dw_1.Retrieve(30)        // 30 is argument as deptno

Now when i run my application it is giving error as:

select error: ORA-06550: line 1, column 16:
PLS-00306: wrong number or type of arguments in call to 'SHOW_EMP_DETAILS'

It is not displaying the records in the datawindow. Please tell me what i have to do for this. How we can display data in datawindow by passing argument from PB?

Pls reply ASAP

Thanks

 
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
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.

772 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

10 Experts available now in Live!

Get 1:1 Help Now