Execute procedure from server routine

[sspreethi]

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.

[kduke]

You probably need the Oracle environment variables like ORA_HOME. You can put these in dsenv and restart DataStage.

[sspreethi]

Thank you Duke. I will try if this works.

Meanwhile I decided to write the count to a temp table from within the procedure which I then read from the routine. This solved my issue.

[swarnkar]

Meanwhile I decided to write the count to a temp table from within the procedure which I then read from the routine. .

If you could read table from routine then you could execute the stored procedures also.

Thanks,
Nitin Swarnkar