?
Solved

Calling Oracle Store Proc Thru PB

Posted on 2004-04-16
5
Medium Priority
?
7,294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10841510
0
 

Author Comment

by:mayuri25
ID: 10848216

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
ID: 10849014

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 60 total points
ID: 10857470
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
ID: 10859114

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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
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.
Suggested Courses

771 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