problem in creating a datawindow through ORACLE stored procedure.

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,
xsysysAsked:
Who is Participating?
 
berXpertCommented:
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
 
tr1l0b1tCommented:
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
 
berXpertCommented:
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
 
xsysysAuthor Commented:
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
 
tr1l0b1tCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.