0

I got an error message when I tried to add a clob of data longer than 4000 characters.

CREATE OR REPLACE PROCEDURE aaa(p CLOB) AS
BEGIN
  FOR all_rec IN (
                  SELECT *
                    FROM JSON_TABLE( p,
                                    '$' COLUMNS(
                                                x NUMBER PATH '$.x',
                                                y CLOB   PATH '$.y'
                                               )
                                    ) jtM
                  ) 
  LOOP
    INSERT INTO demo1(x, y) VALUES(seq_demo1.nextval, p);
  END LOOP;
END;
/

I need to insert more than 4000 characters using CLOB data type using parameterized procedure.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Monica Augustine-Plsql Newbie
  • 11
  • 1
  • Does this answer your question? [Oracle CLOB can't insert beyond 4000 characters?](https://stackoverflow.com/questions/18394691/oracle-clob-cant-insert-beyond-4000-characters) – Pirate Feb 24 '23 at 05:56
  • this link concerns CLOB as STRING literal, here the CLOB comes from a query, but the first step for the poster should be to show us a query that works: "JSON_TABLE( p" and "VALUES(seq_demo1.nextval, p)": p comes from nowhere... (And anyway there is no need of a FOR LOOP to INSERT from a JSON_TABLE) – p3consulting Feb 24 '23 at 07:44
  • Your question does not make sense as you use the cursor loop to find the `x` and `y` values from your input and then you ignore those values and insert the next sequence value and the original data into the table rather than the values from the cursor. What are you expecting to achieve? Please [edit] the question to include a [MRE] with details of how you are calling the procedure, the exact error message and which line causes the error, and what you expect the output to be so we can understand what you are trying to achieve. – MT0 Feb 24 '23 at 09:11
  • Is the error coming from the procedure, or from your call to the procedure? e.g. are you doing `aaa('some long string');` ? You might just need to build a CLOB value to pass in (with `to_clob()` and concatenation), or look at where the data is coming from and whether it can be passed in another way. Please include how you call it, and the full error stack you get. – Alex Poole Feb 24 '23 at 09:23

1 Answers1

0

If you have the table and sequence:

CREATE TABLE demo1(x NUMBER, y CLOB);
CREATE SEQUENCE seq_demo1;

Then your code does not raise that error when you call it:

DECLARE
  value CLOB;
BEGIN
  value := EMPTY_CLOB()
           || '{'
           || '"x":42,"y":"' || LPAD('a', 4000, 'a') || LPAD('a', 4000, 'a') || '"'
           || '}';
  aaa(value);
END;
/

Works and the table contains:

X Y
1 {"x":42,"y":"aaaaaaaaaaaaaaaa...<more a's>...aaaaaaa"}

If you are expecting to insert the x and y values then you do not need a cursor loop and can use INSERT ... SELECT ...:

CREATE OR REPLACE PROCEDURE aaa(p CLOB) AS
BEGIN
  INSERT INTO demo1(x, y)
    SELECT x, y
    FROM   JSON_TABLE(
             p,
             '$'
             COLUMNS(
               x NUMBER PATH '$.x',
               y CLOB   PATH '$.y'
             )
           );
END;
/

(But then why do you need the sequence?)

Then calling the procedure in the same way as before the table contains:

X Y
42 aaaaaaaaaaaaaaaa...<more a's>...aaaaaaa

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117