Hi,
I need to pass an array of strings larger than 4000 characters to Oracle at once from C# application, so I used CLOB Datatype. String is written like this: "123456 (1); 234567 (2); 765876 (3); 098765 (4);" etc.
Now I need to select numbers from array which are not in brackets into rows and then insert those values into second table, but only If record doesn't allready exist.
For all this I have 2 tables and stored procedure:
CREATE TABLE Table1
(
ID NUMBER NOT NULL,
DATE TIMESTAMP(6),
USER VARCHAR2(50 CHAR)
)
CREATE TABLE Table2
(
SERIAL VARCHAR2(20 CHAR)
)
CREATE OR REPLACE PROCEDURE INSERT_VALUES(
USER_IN Table1.USER%TYPE,
CLOB_VALUES_IN CLOB
)
IS
BEGIN
-- Insert into 1st table
INSERT INTO Table1(ID,DATE,USER)
VALUES (MY_SEQ.NEXTVAL,SYSTIMESTAMP,USER_IN);
-- then I need to loop through CLOB datatype, separate numbers without brackets into rows and insert that into Table2 IF NOT EXISTS
--this is the part where I don't know what to do
END INSERT_VALUES;
/
Can somebody show me how to loop trough array of CLOB Datatype, in order to separate strings in rows and then insert number values ?
For any help thanks in advance.