Update a table with a stored procedure

[ced62]

Hi, is it possible to update with a stored procedure and how ??

ex :
=============================================
PROCEDURE UPDATE_VIEW_PARAMETER_BO
(I_date_deb in DATE,
I_date_fin in DATE/
)
is
BEGIN
update dba.view_parameter
set
date_fin = I_date_fin,
date_debut = I_date_deb
where
mandant = 'RTF' and
queue_name = 'RTF_INDIC_GEN_HEB' and
username = 'GUIFFROYMA'
;
commit;
END;
================================================


but because there is data found, it doesn't work ...
Thanks

 

[Sridharan]

Ced,

To your first question yes it is very much possible to update tables using Stored Procedures. BTB which DB are you on??? I don't know if some DB doesn't support that feature.

Why is your SP working if the Data is found???? What error are you getting or it is not giving error and also not updating the table???

Since this finds a place in the BO forum are you calling you SP thru BO???

Sri

 

[ced62]

Sorry, I wanted to write :
but because there is no data found, it doesn't work ...

To resume, the table is updated but a message "This request send no data" appears. And I stay on the same step : I have to define my varibles again even if the table is updated.
I would like to use this procedure with WebI and it is not great for users !!

My goal is to update this table in first before executing my request to refresh the report. Do you know how to do ??

Thanks

 

[Sridharan]

Ced,

If you want to get rid of message then you can first check if row does exists for the data you are trying to update. If not then don't update the table.

You want to execute the report only when Refreshing the report or your report use Universe or SP as the Data Source??? How are you passing the parameters to the SP presently while refreshing the report.

Sri

 

[ced62]

Sri,
The request is a big factory :-( ...
With BO, I just do a select all on a view with SQL hand(easy). But, the view takes informations from many tables, functions and views !! There is a table for parameters and I have to update this table (my problem), values are needed in functions.
So, the easest solution would be to :
1/execute a stored procedure for the parameters table
2/refresh the view with BO

In my mind, when the user will refresh the report, the system will ask him to enter parameters (so the table will be updated) and after the view will be refreshed.

What do you thing about this ??

How just programmed a updated procude with BO ??

Thanks

 

[Sridharan]

Ced,

If you using Free Hand SQL then I suggest to create a SP that will first update the table and return the values from the View for you. Then use this SP as the Source for your report. You are done.

Sri