Passing string of IDs to Oracle stored procedure
Posted on 2004-08-04
We created a stored procedure to copy all fields of specified records from one table to another:
CREATE OR REPLACE PROCEDURE "XX"."EXPORT_XXXX" ( sIDS VARCHAR2) IS
INSERT INTO "XX"."EXPXXXX" (SELECT * from "XX"."XXXX" WHERE XX.XXXX.INTID IN (sIDS));
If we call it with deXX.procEXPORT_XXXX( "1") then all is fine.
If we call it with deXX.procEXPORT_XXXX( "1,2,3") then Oracle indicates an invalid number.
I think the problem is that we cannot pass numbers in strings to Oracle.
Maybe we have to create an array inside the proc and populate it with the numbers extracted from the sIDS string.
Or maybe there is some other calling type we can use such as VARIANT.
Any solutions to the problem welcome.