10 Replies Latest reply: Mar 31, 2013 3:15 AM by jeneesh RSS

    How to pass changeable actual parameters into a procedure?

    549869
      I am writing a procedure for the front-end. The end-users need to insert multiple rows of data into history tables in the database (11G). My problem is: the multiple actually parameters is not a fix amount, this time, the amount could be 5, next time, it could be 12. I currently used one string and pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8', the procedure was executed scuessfully, but cann't insert any data into history table.

      Please advise, how should I pass these parameters?

      See my procedure below (the base table has clob data, I have to consider insert ... select *), I tried to use to_number (CONTACT_MSG_ID), it doesn't work well:

      PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
      BEGIN
      INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
      SELECT *
      FROM TRC_XREF_CONT_EMAIL_MSGS
      WHERE TO_CHAR(CONTACT_MSG_ID) IN (P_ID);
      DELETE FROM TRC_XREF_CONT_EMAIL_MSGS WHERE TO_CHAR(CONTACT_MSG_ID) IN (P_CONT_MSG_ID);
      COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      RETURN;
      END;

      Please help.

      Thank you very much.

      Gary
        • 1. Re: How to pass changeable actual parameters into a procedure?
          sb92075
          >


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          pass the actual parameter (P_id, number) as '2, 4, 5, 7, 8'
          '2,4,5,7,8' is really a STRING; not a NUMBER & in fact is invalid as a number since numbers do not contain commas or space characters.
          EXCEPTION
          WHEN OTHERS THEN
          RETURN;
          END;
          GREAT Bug lying in wait to bite unsuspecting users!
          • 2. Re: How to pass changeable actual parameters into a procedure?
            549869
            correct, '2, 4, 5, 7, 8' is not numbers, they are string. I just want to pass multiple number, I don't know how to pass, so I tried to use string to pass. Actually my question is: how to pass changeable multipe number ti a procedure.

            Sorry, I didn't read how should I post a question because of urgency.

            Thanks very much,
            • 3. Re: How to pass changeable actual parameters into a procedure?
              SomeoneElse
              Actually my question is: how to pass changeable multipe number ti a procedure.
              PL/SQL does not support varying argument lists. Some of Oracle's own functions do, like decode or greatest. But not PL/SQL.

              Are you sure you need it? Maybe you can use default arguments. It would be a LOT easier than passing a concatenated character string like you've tried.

              You can Google search for "varying in lists" but it's still kind of a pain.

              Perhaps the easiest solution is to store all those numbers in a table instead of arguments. Then you can use WHERE x IN (select ...).
              • 4. Re: How to pass changeable actual parameters into a procedure?
                jeneesh
                user546866 wrote:
                correct, '2, 4, 5, 7, 8' is not numbers, they are string. I just want to pass multiple number, I don't know how to pass, so I tried to use string to pass. Actually my question is: how to pass changeable multipe number ti a procedure.
                {message:id=9360009}

                >
                Sorry, I didn't read how should I post a question because of urgency.
                If you cant spend time to phrase your issue properly, how do you expect OTHERS to spend time for you?
                • 5. Re: How to pass changeable actual parameters into a procedure?
                  dvsoukup
                  You should listen to the other guys on here as I'm no expert....

                  But, I think I understand what you're trying to do. Only way I could think of accomplishing what you're trying to do is to set up a loop that will start looping through your string. This can be done using a clever use of substr and instr, then incrementing a counter by 1 to force your loop index to move to the next "number" in your string of numbers. During each iteration, you would then use the to number function to convert it to a number, and use that variable as an insert.

                  Might be better ways to do it though.

                  Maybe try something like this:

                  procedure myproc(myNumbers in varchar2)
                  is

                  v_int number;
                  i number := 0;
                  v_str varchar2(10);

                  begin

                  --your numbers being passed in is like '2,3,5,7,9' or something...
                  loop
                  v_str := substr(myNumbers, i, instr(myNumbers, ',', i - 1, i + 1));
                  v_int := to_number(v_str);

                  insert into mytable values(v_int);
                  i := i + 1;
                  end loop;

                  So... I am just doing this off the top of my head and I don't have any database tool handy at the moment. But, hopefully you can get the idea with it. Basically you would start walking through your string of numbers with a clever use of substr and instr. The example listed above is absolutely incorrect, but should hopefully provide the basic idea of what needs to be done.

                  I think the last time I did this, I had to utilize some if, elseif, else conditions for different scenarios depenidng if it was the first iteration, an iteration in the middle, and the very last iteration due to the delimiter. If I get time tomorrow at work I will come back and update this example with some actual working code of this loop when I have a tool to test it.

                  Again, I can't speak for if it's the "best" way, or a good practice to do this, but I do know that it can work for a dynamic string of values being passed in!
                  • 6. Re: How to pass changeable actual parameters into a procedure?
                    jeneesh
                    dvsoukup wrote:

                    procedure myproc(myNumbers in varchar2)
                    is

                    v_int number;
                    i number := 0;
                    v_str varchar2(10);

                    begin

                    --your numbers being passed in is like '2,3,5,7,9' or something...
                    loop
                    v_str := substr(myNumbers, i, instr(myNumbers, ',', i - 1, i + 1));
                    v_int := to_number(v_str);

                    insert into mytable values(v_int);
                    i := i + 1;
                    end loop;
                    A note: If your plan is to insert into a table, you dont need a LOOP here. LOOP is row by row - which will be slow.

                    You could just do a INSERT...SELECT..
                    insert into mytable(column_name)
                    select regexp_substr(v_str,'[^,]+',1,level) str
                    from dual
                    connect by regexp_substr(v_str,'[^,]+',1,level) is not null;
                    You may use SUBSTR and INSTR instead of REGEXP
                    • 7. Re: How to pass changeable actual parameters into a procedure?
                      dvsoukup
                      Oh man using connect by from dual is a really good idea! I've never thought to do that... I'm going to try this out myself tomorrow just because I'm curious.

                      I used a custom loop with the substr/instr method as I listed above to do some inserting and emailing to people in the past, but the volume was small so I never noticed any sort of big performance problem. I could see how with more inserts it could really slow it down. But that connect by is a slick method!
                      • 8. Re: How to pass changeable actual parameters into a procedure?
                        Ramin Hashimzadeh
                        check this :
                        PROCEDURE ARCHIVE_XREF_CONT_EMAIL(P_ID IN VARCHAR2) IS
                        BEGIN
                          INSERT INTO TRC_XREF_CONT_EMAIL_MSGS_HIST
                            SELECT *
                              FROM TRC_XREF_CONT_EMAIL_MSGS
                             WHERE TO_CHAR(CONTACT_MSG_ID) IN (
                                                               SELECT REGEXP_SUBSTR(P_ID, '[^,]+', 1, ROWNUM) AS MYPAR
                                                                 FROM DUAL
                                                               CONNECT BY INSTR(P_ID, ',', 1, LEVEL - 1) > 0);
                        --  P_CONT_MSG_ID  undefined
                        --  DELETE FROM TRC_XREF_CONT_EMAIL_MSGS
                        --   WHERE TO_CHAR(CONTACT_MSG_ID) IN (P_CONT_MSG_ID);
                           
                          COMMIT;
                        EXCEPTION
                          WHEN OTHERS THEN
                            RETURN;
                        END;
                        • 9. Re: How to pass changeable actual parameters into a procedure?
                          549869
                          Ramin,

                          Thank you very much for your post and code.

                          I ran it and it executed successfully, but ca't insert anything into the table.

                          I have not found out any problem yet.

                          Thanks,

                          Gary
                          • 10. Re: How to pass changeable actual parameters into a procedure?
                            jeneesh
                            user546866 wrote:
                            Ramin,

                            Thank you very much for your post and code.

                            I ran it and it executed successfully, but ca't insert anything into the table.

                            I have not found out any problem yet.

                            Thanks,

                            Gary
                            Exception part is the problem.. Remove it and see
                            Ramin Hashimzadeh wrote:
                            EXCEPTION
                            WHEN OTHERS THEN
                            RETURN;
                            END;