7 Replies Latest reply: Dec 1, 2012 5:00 AM by Billy~Verreynne RSS

    How to Pass Parameters

    user8375449
      DECLARE
      I_USER_ID NUMBER;
      I_PO_ID NUMBER;
      I_FIRM_ID NUMBER;
      I_QUERY_GROUP_ID NUMBER;
      I_QUERY_GROUP_PARAMS AGILE_CT.DX_UTILS_PKG.dx_user_param_list;
      O_QUERY_GROUP_INST_ID NUMBER;
      I_DEBUG_MODE NUMBER;

      BEGIN
      I_USER_ID := NULL;
      I_PO_ID := NULL;
      I_FIRM_ID := NULL;
      I_QUERY_GROUP_ID := NULL;
      -- I_QUERY_GROUP_PARAMS := NULL;  Modify the code to initialize this parameter
      O_QUERY_GROUP_INST_ID := NULL;
      I_DEBUG_MODE := 0;

      AGILE_CT.DX_UTILS_PKG.NEW_QUERY_GROUP_INST ( I_USER_ID, I_PO_ID, I_FIRM_ID, I_QUERY_GROUP_ID, I_QUERY_GROUP_PARAMS, O_QUERY_GROUP_INST_ID, I_DEBUG_MODE );
      COMMIT;
      END;


      ...Above is the code i need to pass parameters into , and I am not sure how to work with the "PARAMS" thanks , how can I also use DBMS OUTPUT to see results especially for testing this scenario. Thanks
        • 1. Re: How to Pass Parameters
          Billy~Verreynne
          What is the definition (user data type) for AGILE_CT.DX_UTILS_PKG.dx_user_param_list ?

          Is it an associative array? String or numeric indexing?

          Or is it a standard array? Of what data type, or which record structure?

          And to initialise it, you need values. Multiple values it seems as the parameter is a type of list. What are these initialisation values and where are you going to get these from?
          • 2. Re: How to Pass Parameters
            user8375449
            not 100% sure the datatype , but is there like a sample or structure I can use , like a loop , and DBMS_OUTPUT to use to test the procedure.thks
            • 3. Re: How to Pass Parameters
              user8375449
              this is what the procedure looks like


              PROCEDURE new_query_group_inst(
              i_user_id NUMBER,
              i_po_id NUMBER,
              i_firm_id NUMBER,
              i_query_group_id NUMBER,
              i_query_group_params    dx_user_param_list,
              o_query_group_inst_id OUT NUMBER,
              i_debug_mode NUMBER := 0
              )
              AS
              c_procedure_name CONSTANT error_log.procedure_name%TYPE := 'new_query_group_inst';
              l_section error_log.section_name%TYPE;
              l_error_code INTEGER;
              l_raise_or_log CHAR (1) := 'R';
              l_entitled BOOLEAN;
              BEGIN
              l_section := 'Starting';
              debug_pkg.start_debug (i_user_id,c_procedure_name,l_section,i_debug_mode);
              debug_pkg.detail_debug (i_user_id, l_section, i_debug_mode);
              l_section := 'Set Pref';
              dsfservices.set_user_locale_preferences (i_user_id);
              l_section := 'Check Entitlements';
              l_entitled := entitlement.validateaccess (i_user_id, c_procedure_name);

              -- Get the new ID and create the associated instance record
              SELECT SEQ_DX_QUERY_GROUP_INST_ID.NEXTVAL INTO o_query_group_inst_id FROM DUAL;
              INSERT INTO DX_QUERY_GROUP_INST(
              DX_QUERY_GROUP_INST_ID,
              DX_QUERY_GROUP_ID,
              DX_QUERY_GROUP_USER_ID,
              DX_QUERY_GROUP_PO_ID,
              DX_QUERY_GROUP_FIRM_ID,
              DX_QUERY_GROUP_START_DT,
              DX_QUERY_GROUP_STATUS,
              AUDIT_USERID
              )
              VALUES (
              o_query_group_inst_id,
              i_query_group_id,
              i_user_id,
              i_po_id,
              i_firm_id,
              CURRENT_TIMESTAMP,
              1, -- Started! (TODO: Use system code values)
              i_user_id
              );

              -- Insert associated user parameters for the group
              INSERT INTO DX_QUERY_GROUP_PARAM_INST(
              DX_QRY_GRP_PARAM_INST_ID,
              DX_QUERY_GROUP_INST_ID,
              DX_QUERY_GROUP_PARAM_ID,
              DX_QUERY_GROUP_PARAM_VALUE,
              AUDIT_USERID
              )
              SELECT SEQ_DX_QRY_GRP_PARAM_INST_ID.NEXTVAL,
              o_query_group_inst_id,
              params.dx_query_group_param_id,
              params.dx_query_param_value,
              i_user_id
              FROM TABLE(CAST(i_query_group_params AS dx_user_param_list)) params;

              EXCEPTION
              WHEN OTHERS THEN
              debug_pkg.end_debug(i_user_id, i_debug_mode);
              error_pkg.raise_error(
              c_procedure_name,
              l_section,
              i_user_id,
              NULL,
              NULL,
              l_raise_or_log
              );
              END;
              • 4. Re: How to Pass Parameters
                user8375449
                I have put in this but I am still getting error:

                DECLARE
                I_USER_ID NUMBER;
                I_PO_ID NUMBER;
                I_FIRM_ID NUMBER;
                I_DXE_QUERY_TPLT VARCHAR2 (32767);
                I_DXQ_VIEW VARCHAR2 (32767);
                I_DXE_VIEW VARCHAR2 (32767);
                I_ORDER_BY VARCHAR2 (32767);
                I_USER_PARAMS dx_user_param_list;
                O_DX_DATA SYS_REFCURSOR;
                O_DX_DATA_row O_DX_DATA%ROWTYPE;
                BEGIN
                I_USER_ID := 1;
                I_PO_ID := 2000;
                I_FIRM_ID := 1;
                I_DXE_QUERY_TPLT := NULL;
                I_DXQ_VIEW := NULL;
                I_DXE_VIEW := NULL;
                I_ORDER_BY := NULL;
                I_USER_PARAMS := dx_user_param_list (NULL, NULL, NULL); --Modify the code to initialize this parameter
                O_DX_DATA := NULL;

                DX_RUN_PKG.DX_EXECUTE_QUERY (I_USER_ID,
                I_PO_ID,
                I_FIRM_ID,
                I_DXE_QUERY_TPLT,
                I_DXQ_VIEW,
                I_DXE_VIEW,
                I_ORDER_BY,
                I_USER_PARAMS,
                O_DX_DATA);

                -- Loop through the resulting data
                IF (O_DX_DATA IS NOT NULL)
                THEN
                LOOP
                FETCH O_DX_DATA INTO O_DX_DATA_row;

                EXIT WHEN O_DX_DATA%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE (O_DX_DATA_row.dx_query_group_param_id);
                END LOOP;

                CLOSE O_DX_DATA;
                END IF;
                END;




                O_DX_DATA_row O_DX_DATA%ROWTYPE;
                *
                ERROR at line 11:
                ORA-06550: line 11, column 17:
                PLS-00320: the declaration of the type of this expression is incomplete or malformed
                ORA-06550: line 11, column 17:
                PL/SQL: Item ignored
                ORA-06550: line 28, column 28:
                PLS-00320: the declaration of the type of this expression is incomplete or malformed
                ORA-06550: line 28, column 7:
                PL/SQL: SQL Statement ignored
                ORA-06550: line 30, column 30:
                PLS-00320: the declaration of the type of this expression is incomplete or malformed
                ORA-06550: line 30, column 8:
                PL/SQL: Statement ignored
                • 5. Re: How to Pass Parameters
                  Billy~Verreynne
                  user8375449 wrote:
                  not 100% sure the datatype , but is there like a sample or structure I can use , like a loop , and DBMS_OUTPUT to use to test the procedure.thks
                  If you do not know what the data type is, then I do not see how you expect being able to use it. An associative array is very different from a normal array in definition and usage. An array of a complex (multiple fields) data structure, is again very different from an array of a single value field.

                  Not knowing what the definition is of the user defined data type, means not being able to use it.

                   
                  The following code is invalid (and written in very silly uppercase and ignoring proper coding standards):
                  DECLARE
                  ..
                  O_DX_DATA SYS_REFCURSOR;
                  O_DX_DATA_row O_DX_DATA%ROWTYPE;
                  The compiler sees that the variable o_dx_data is a ref cursor. The compile has NO idea what the SQL projection will be for the ref cursor. Also, the ref cursor can be used for a select * from emp in the code that follows, closed, and then reopened for a select * from dept.

                  This means the very same ref cursor variable can be used in the same code for different SQL cursors with different SQL projections.

                  Now you are trying to define with variable o_dx_data_row a structure for fetching from the ref cursor. What is the compiler suppose to do? Guess what that structure would be? How is it suppose to deal with that ref cursor variable being used for different SQLs, each returning a different SQL projection?

                  Not possible. The structure (%RowType) of that ref cursor variable is unknown when the compiler attempts to compile variable o_dx_data_row - so it cannot define that variable as the structure to create/compile for that variable is unknown.

                  What bugs me about this code, is the prefixing and uppercase used in this code... THERE ARE NO SUCH MODERN PROGRAMMING STANDARDS. I find looking at such code, never mind having to touch and maintain it, painful.

                  Why are you and your team ignoring modern programming standards as detailed in .Net's Guidelines for Names and Code Conventions for the Java Programming Language?
                  • 6. Re: How to Pass Parameters
                    user8375449
                    Thanks for the response

                    it is An array of a complex (multiple fields) data structure.
                    (sample:= I_USER_PARAMS := dx_user_param_list (NULL, NULL, NULL);

                    Code has been reformatted.


                    Declare
                    I_User_Id Number;
                    I_Po_Id Number;
                    I_Firm_Id Number;
                    I_Dxe_Query_Tplt Varchar2 (32767);
                    I_Dxq_View Varchar2 (32767);
                    I_Dxe_View Varchar2 (32767);
                    I_Order_By Varchar2 (32767);
                    I_User_Params Dx_User_Param_List;
                    O_Dx_Data Sys_Refcursor;
                    O_Dx_Data_Row O_Dx_Data%Rowtype;
                    Begin
                    I_User_Id := 1;
                    I_Po_Id := 2000;
                    I_Firm_Id := 1;
                    I_Dxe_Query_Tplt := Null;
                    I_Dxq_View := Null;
                    I_Dxe_View := Null;
                    I_Order_By := Null;
                    I_User_Params := Dx_User_Param_List (Null, Null, Null); --Modify the code to initialize this parameter
                    O_Dx_Data := Null;

                    Dx_Run_Pkg.Dx_Execute_Query (I_User_Id,
                    I_Po_Id,
                    I_Firm_Id,
                    I_Dxe_Query_Tplt,
                    I_Dxq_View,
                    I_Dxe_View,
                    I_Order_By,
                    I_User_Params,
                    O_Dx_Data);

                    -- Loop through the resulting data
                    If (O_Dx_Data Is Not Null)
                    Then
                    Loop
                    Fetch O_Dx_Data Into O_Dx_Data_Row;

                    Exit When O_Dx_Data%Notfound;
                    Dbms_Output.Put_Line (O_Dx_Data_Row.Dx_Query_Group_Param_Id);
                    End Loop;

                    Close O_Dx_Data;
                    End If;
                    End;
                    • 7. Re: How to Pass Parameters
                      Billy~Verreynne
                      Reformatting the code does not change the fact that you cannot compile a fetch structure (using %RowType) for a ref cursor that, at compilation time, does not have a fixed SQL projection/fetch structure.

                      The compiler does not know what that ref cursor SQL is going to be. So it does not know what +%RowType+ that ref cursor will return.