Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem in creating a datawindow through ORACLE stored procedure.

Posted on 2006-05-10
5
Medium Priority
?
1,559 Views
Last Modified: 2013-12-26
Hi everybody,

I need some help in creating a datawindow through ORACLE stored procedure.
 
I am using ORACLE 10g as back end   and     PowerBuilder 8 as Front end for my application.

I have a stored procedure which returns result set through an INOUT cursor populated from a Temporary table.    This procedure is returning result set when I execute the stored procedure in ORACLE ISQL* Plus(SQL Plus).      

But when iam creating a grid datawindow with this stored procedure, PowerBuilder is throwing the following error message.

 “Cannot create DataWindow.”

“‘Arguments for select are invalid or incomplete’ ”

Can any one suggest the solution for this problem?

Thanks,
0
Comment
Question by:xsysys
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16656323
Who or what creates the temporary table ?
It should be avaliable when you create the dw so
Datawindow Painter can retrieve the procedure resultset
information.
0
 
LVL 5

Expert Comment

by:berXpert
ID: 16668742
Hi,

Can you post the header of your stored procedure?  I think maybe there is a parameter missing.

If temporary table is the problem, try to re-create what you have in ORACLE ISQL* Plus(SQL Plus) on database painter

1. Create your temporary table:

create global temporary table your table ( field1 int, field2 int);

2. execute your stored procedure:
exec sp_your_sp:

3. Post here your results


Regards,

BerXpert
0
 

Author Comment

by:xsysys
ID: 16687742
Hi Everybody,

I thought temporary table was giving problem in my stored procedure.

But, i came to know that assigning value to a variable through select statement in the stored procedure is giving problem.

CHECK BELOW,

EXAMPLE 1:
CREATE OR REPLACE procedure sp_testing( IN_EMPL_ID IN VARCHAR2, IN_OUT_CURSOR IN OUT testing_package.cursor_Type )
as
BEGIN
INSERT INTO TT_EMPL
SELECT empl_name FROM employee WHERE EMPL_ID =  IN_EMPL_ID;

OPEN IN_OUT_CURSOR
FOR
SELECT * FROM TT_EMPL;
end;


When i created datawindow through this stored procedure, datawindow is creating fine.

But, When similar stored procedure through variable assignment process is giving problem in creating DATAWINDOW.
EXAMPLE 2:

CREATE OR REPLACE procedure sp_testing( IN_EMPL_ID IN VARCHAR2, IN_OUT_CURSOR IN OUT testing_package.cursor_Type )
as
ls_value VARCHAR2(100);
BEGIN

SELECT empl_name INTO ls_value FROM employee WHERE EMPL_ID =  IN_EMPL_ID;

INSERT INTO TT_EMPL VALUES ( ls_value );


OPEN IN_OUT_CURSOR
FOR
SELECT * FROM TT_EMPL;

end;

This(EXAMPLE 2) stored procedure is creating fine and executing fine.
But while creating the datawindow it is giving problem like ORA-01403 no data found.

can any body help me how can i use variable assignment through select statement in the stored procedure which will not create problem in creating datawindow through stored procedure ?

0
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16688872
Hi,

I think that the problem is that you have an input parameter (IN_EMPL_ID) that in
dw creation time has no value and that's because the ORA-01403 raises.

If I were you, I'll try :

1 .- Create the procedure including in the body only the cursor select (comment the remaining):

CREATE OR REPLACE procedure sp_testing( IN_EMPL_ID IN VARCHAR2, IN_OUT_CURSOR IN OUT testing_package.cursor_Type )
as
ls_value VARCHAR2(100);
BEGIN
-- SELECT empl_name INTO ls_value FROM employee WHERE EMPL_ID =  IN_EMPL_ID;
-- INSERT INTO TT_EMPL VALUES ( ls_value );
OPEN IN_OUT_CURSOR
FOR
SELECT * FROM TT_EMPL;
end;

2.- Create the DW.
3.- Create the procedure again, but with original syntax uncommented.

See what happens.
Regards
0
 
LVL 5

Accepted Solution

by:
berXpert earned 750 total points
ID: 16691133
Hi,

If you think you can solve your problem taking out that variable assignment, then I suggest you to split your sp in 2, and in your window before making your DW retrive first call to assigner sp.

//----------------
// Window.Open
//----------------

// This sp insert into tt_empl values ( ... )
string ls_my_sp = "sp_who_fill_tt_emp"
execute immediate :ls_mysp

// And this DW is filled with only select * from tt_empl
dw_1.Retrieve( )

//------ end.

Regards,

BerX
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

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.
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.
Suggested Courses

564 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