Solved

Calling Oracle Store Proc Thru PB

Posted on 2004-04-16
5
7,288 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 20 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
netbeans not showing web project 3 94
PHPStorm, PHPUnit not found in include path 2 591
Nest Related files not working in Visual Studio 4 82
CKEditor - will not function 7 77
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
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 columnThat will then direct you to their download page.From that page s…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

726 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