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.
EXCEPTIONGREAT Bug lying in wait to bite unsuspecting users!
WHEN OTHERS THEN
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.
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.If you cant spend time to phrase your issue properly, how do you expect OTHERS to spend time for you?
dvsoukup wrote: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.
procedure myproc(myNumbers in varchar2)
i number := 0;
--your numbers being passed in is like '2,3,5,7,9' or something...
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;
You may use SUBSTR and INSTR instead of REGEXP
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;
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;
user546866 wrote:Exception part is the problem.. Remove it and see
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.
Ramin Hashimzadeh wrote:
EXCEPTION WHEN OTHERS THEN RETURN; END;