This discussion is archived
10 Replies Latest reply: Mar 31, 2013 1:15 AM by jeneesh RSS

How to pass changeable actual parameters into a procedure?

549869 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >


    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?
    880597 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?
    880597 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points