Can I call a procedure from a SQL Script calculation view

Hi Patrick,

SQL script calc view is also implemented as a procedure. For each Calc view a corresponding Procedure is created in SYS_BIC schema and a table type is defined for the VAR_OUT structure. The created procedure has READ only access on the data which means any data manipulation commands are not allowed.

So now theoretically it should be possible to call another procedure from one procedure and pass on the output. It is an interesting concept and I would like to try it tomorrow (no access to system on Sunday) and confirm you back.

Regards,

Ravi

The only restrictions I remember are regarding input parameters (as far as I understand, you cannot stack passing parameters from the outer calc view to the inner proc) and the fact that your inner proc needs to be read only.

@ Jody Hesch

There's some decent stuff to be found on SCN huh 😉. Above tip came in handy for me today, cheers.

Script calc view:

/********* Begin Procedure Script ************/

BEGIN

call "MISSIONCONTROL"."hpl.missioncontrol.procedures::get_mission_time_idle" (:IP_MISSIONID,IP_VEHICLEID,IP_PILOTID, lvSecondsIdle);

call "MISSIONCONTROL"."hpl.missioncontrol.procedures::get_mission_time_motion" (:IP_MISSIONID,IP_VEHICLEID,IP_PILOTID, lvSecondsMotion);

var_out = select label, seconds from :lvSecondsIdle

union all

select label, seconds from :lvSecondsMotion

union all

select 'PCTIDLE' as "LABEL", ABS(A.seconds / (A.seconds + B.seconds) * 100) from :lvSecondsIdle A

inner join :lvSecondsMotion B on A.IDX = B.IDX

union all

select 'PCTMOTION' as "LABEL", ABS(B.seconds / (A.seconds + B.seconds) * 100) from :lvSecondsIdle A

inner join :lvSecondsMotion B on A.IDX = B.IDX;

END /********* End Procedure Script ************/

where "var_out" for the two called procedures is:

type tt_seconds {

IDX : idxT;

LABEL : VSStringT;

SECONDS : secondsT;

};

And example from the "idle" procedure output

ex_idle_seconds = select 1 as "IDX", 'IDLE' AS "LABEL", lvTotalSecondsBetween as "SECONDS" from dummy;

Hi Patrick,

So far, I could successfully execute a procedure and could get the result from the result view to another table variable and could use it in CE function.

Unfortunately I haven't been able to use the OUT parameter value yet. Will have to look into the code for details.

I am not very positive about the performance impact of this, as the CE operators and the SQL code in procedure are executed by different engines and there could be high data transfer between the engines which would deteriorate the performance.

Regards,

Ravi

This message was moderated.