CharlesSHill
asked on
Passing string of IDs to Oracle stored procedure
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
BEGIN
INSERT INTO "XX"."EXPXXXX" (SELECT * from "XX"."XXXX" WHERE XX.XXXX.INTID IN (sIDS));
END;
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.
CREATE OR REPLACE PROCEDURE "XX"."EXPORT_XXXX" ( sIDS VARCHAR2) IS
BEGIN
INSERT INTO "XX"."EXPXXXX" (SELECT * from "XX"."XXXX" WHERE XX.XXXX.INTID IN (sIDS));
END;
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.
why not create the sql string dynamically and pass it to the stored procedure for execution.
ASKER
Good Idea, what would my Stored Procedure then look like? Sorry, have never worked with SPs
i'm not familiar with Oracle SQL syntax. there is a system stored procedure for SQL Server called sp_executesql where you just pass the sql statement as a parameter. Oracle might have a similar system stored procedure that's equivalent to sp_executesql.
in my case, i created my own stored procedure using this system stored procedure.
CREATE PROCEDURE unsp_EXECUTE_SQL
(
@sql nvarchar(4000)
)
AS
EXEC sp_executesql @sql
GO
i then call this stored procedure and pass my dynamically created stored procedure.
hope this helps
in my case, i created my own stored procedure using this system stored procedure.
CREATE PROCEDURE unsp_EXECUTE_SQL
(
@sql nvarchar(4000)
)
AS
EXEC sp_executesql @sql
GO
i then call this stored procedure and pass my dynamically created stored procedure.
hope this helps
btw, is your application written in VB6? just curious
ooops
i mean i call the stored procedure passing my dynamically created sql statement
i mean i call the stored procedure passing my dynamically created sql statement
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.