7 Replies Latest reply on Feb 10, 2016 2:02 PM by Hank Ratzesberger

    Procedure Examples Would Really Help

    Hank Ratzesberger Newbie

      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;

       

        • 1. Re: Procedure Examples Would Really Help
          Steven Hawkins Master

          > 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")

           

          It's best to capture anything you want to see as an issue.

           

          > 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.

           

          Do you mean getting the out parameter from calling the exec?  The standard way with JDBC is to use a CallableStatement with an out parameter ?.  Calling from a virtual procedure you would assign the out parameter to a variable of the same type:

           

          declare bigdecimal var;

          exec get_lam_awarded_amt_vdb( system_type=>'RBA', system_id=>'NP9700127061', item_id=>'128262', item_num=>'0001', awarded_amt=>var);

           

          The non-standard way is to rely on the implicit projection of the out parameter when there is no resultset, so you can use the subquery syntax:

           

          select * from (exec get_lam_awarded_amt_vdb( system_type=>'RBA', system_id=>'NP9700127061', item_id=>'128262', item_num=>'0001')) as v

           

          Or are you meaning something else?

          • 2. Re: Procedure Examples Would Really Help
            Hank Ratzesberger Newbie

            Steven, et al., thanks, I am able to execute the procedure now..

             

            Apologies -- I'm no expert at PL/SQL either, but I don't know how to assign an OUT value. I have tried local vars, "set ... into ..." and other strategies, but the code below, when done as a view, works, but as a procedure, I get null.

             

            Not sure if this qualifies as an issue, but how do I assign OUT parameters in the following?

             

            create virtual procedure test2_amt (

              out awarded_amt            bigdecimal

            ) as

              begin

             

            select 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) amounts

              where amounts.front_end_order_id = 'ID09140052'

                             and amounts.task_item_num = '0001';

             

            end;

             

            --Hank

            • 3. Re: Procedure Examples Would Really Help
              Steven Hawkins Master

              > Not sure if this qualifies as an issue, but how do I assign OUT parameters in the following?

               

              Just use an assignment:


              awarded_amt = expression;

              • 4. Re: Procedure Examples Would Really Help
                Steven Hawkins Master

                A full example of that will be added to the 9.0 community docs.

                • 5. Re: Procedure Examples Would Really Help
                  Hank Ratzesberger Newbie

                  Thank you Steven. It would really help me, look forward to the examples.

                   

                  Now I am trying  multiple out parameters.  In MS SQL it would look something like :

                   

                  select @var1 = col1, @var2 = col2, @var3 = col3 from ....

                   

                  Is there anything similar?

                   

                  I've tried, a little, to put this into a loop, even though there will be only result set.

                   

                  Best regards,

                  Hank

                  • 6. Re: Procedure Examples Would Really Help
                    Steven Hawkins Master

                    Each variable assignment would be it's own statement.  If this is coming from effectively a scalar subquery, then you can use a temp table or an array value (depending upon the designer version).  With a temp table:

                     

                    select a, b into #temp from ...;

                    var1 = (select a from #temp);

                    var2 = (select b from #temp);

                    • 7. Re: Procedure Examples Would Really Help
                      Hank Ratzesberger Newbie

                      That's it. Thank you so much. So many questions, many simply because of my inexperience with this kind of programming.

                       

                      Well, this is great, because these queries may need to route to different databases, but this will let me consolidate all that logic into one procedure.

                       

                      I Should be good for a while ...

                       

                      Best,

                      Hank