Procedure Examples Would Really Help
I appreciate that you have provided BNF syntax for Virtualization, it is well organized and the hyperlinks are helpful.
However, a few examples would really help, and I think this is a strategy for a lot of programmers (yes, I've seen the "programming to be formally renamed Google Stackoverflow")
I am attempting to use pl/sql code, which I am also not an expert, so perhaps my experience is what's really lacking. Even so....
A complete example, including the declaration and invocation of a few types of procedures / functions that could demonstrate a few key aspects would help.
o Procedure with multiple in/out/inout parameters.
o Function with parameters
o Demonstrate in/out parameter passing and result set
o Examples that demonstrate executing the above passing/receiving results.
That said, if you could comment on or point to an example of using OUT parameters, that would be great.
The following compiles and execution succeeds, but I don't know how to get the out parameter. select * from (exec) , gives me various Parsing errors.
Thank you, Hank
exec get_lam_awarded_amt_vdb( system_type=>'RBA', system_id=>'NP9700127061', item_id=>'128262', item_num=>'0001');
create virtual procedure get_lam_awarded_amt_vdb (
in system_type varchar(4),
in system_id varchar(20),
in item_id varchar(20),
in item_num varchar(2),
out awarded_amt bigdecimal
) as
begin
if (system_type = 'RBA')
begin
SELECT lam_awarded_amt.awarded_amt FROM
(SELECT OSD.FRONT_END_ORDER_ID, OSD.ORDER_ID, OSD.COI_STATUS, OSD.ORDER_TYPE, OSD.ACT_NUM_ID, OSD.FUNDING_TYPE, OFT.TASK_ITEM_NUM, TID.TASK_ITEM_TYPE,
NVL(SUM(OFT.TRANSACTION_AMT - OFT.TRANSACTION_FEE_AMT),0) AWARDED_AMT
FROM TABLE_MASTER.ORDER_FUND_TRANSACTIONS OFT
INNER JOIN TABLE_MASTER.ORDER_STATIC_DATA OSD
ON OSD.FRONT_END_ORDER_ID = OFT.FRONT_END_ORDER_ID
INNER JOIN TABLE_MASTER.ORDER_STATUS OS
ON OS.ORDER_MOD_ID = OFT.ORDER_MOD_ID
LEFT JOIN TABLE_MASTER.TASK_ITEM_DATA TID
ON TID.TASK_ITEM_DATA_ID = OFT.TASK_ITEM_DATA_ID
AND tid.TASK_ITEM_TYPE != 'FEE'
WHERE OFT.TRANSACTION_TYPE = 'OB' AND OS.FUNDING_STATUS = 'TO NEAR' AND UPPER(OS.MOD_STATUS) = 'AWARDED'
GROUP BY OSD.FRONT_END_ORDER_ID, OSD.ORDER_ID, OSD.COI_STATUS, OSD.ORDER_TYPE, OSD.ACT_NUM_ID, OFT.TASK_ITEM_NUM, OSD.FUNDING_TYPE, TID.TASK_ITEM_TYPE) as lam_awarded_amt
WHERE lam_awarded_amt.front_end_order_id = system_id
and lam_awarded_amt.task_item_num = item_num;
end
else
begin
-- soon...
end
end;
