Hi
I have a requirement to refresh a materialized view (mv) only when one table within mv has > 0 records. If count=0 another set of steps need to be done.
To achieve this I decided to call an oracle procedure from a server routine and later branch accordingly. This procedure does a materialized view refresh based on table count and returns a value that should be captured in the routine.
I searched the forum but couldnt exactly find what I am looking for.
I have below lines in a sql file which I am calling from the routine.
DECLARE
v_cnt number;
BEGIN
REFRESH_MV ('t_abc','mv_abc', v_cnt);
dbms_output.put_line(v_cnt);
END;
/
Command = "sqlplus -s ":username:"@":database:"/":password:" @":FileName
Call DSExecute("UNIX", Command, Output, ExitStatus)
When I display Output it shows as below:
Output:
PL/SQL procedure successfully completed.
Error 45 initializing SQL*Plus
Internal error
I am able to execute a function and capture its return value from a routine. I couldnt convert the above procedure into a function because of dbms_mview.refresh in it.
Not sure if I am doing something wrong. All I need is execute procedure and capture its output value (v_cnt). Let me know if there is a different way to approach this.
Execute procedure from server routine
Moderators: chulett, rschirm, roy