problem in creating a datawindow through ORACLE stored procedure.

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.

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

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 ?

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