时间戳

首页 > 解决方案 > Oracle - store large string in CLOB

plsql - Oracle - store large string in CLOB

问题描述

I need to save a procedure body into a Clob column with a use of variable. String is longer than 4000 characters, so I can't use VarChar2, but with CLOB variable I receive error "ORA-01422: exact fetch returns more than requested number of rows". Same error appears with Varchar2. My PL/SQL block:

DECLARE
txt_procedure CLOB;

BEGIN

SELECT text INTO txt_procedure
FROM all_source
WHERE name = 'My_procedure'
ORDER BY line;

INSERT INTO TABLE1(ID,DATE,CLOB_COLUMN)
VALUES (my_seq.NEXTVAL,'11.10.2018',txt_procedure);

END;
/

How could I insert procedure body into clob column ?

标签： plsqloracle11g

解决方案

As you will get multiple rows from your query for every line of your source, the following might help:

DECLARE
    txt_procedure CLOB;
BEGIN
    FOR source_r IN ( SELECT text 
                        FROM all_source
                       WHERE name = 'My_procedure'
                       ORDER BY line
                    )
    LOOP
        txt_procedure := txt_procedure || chr(10) || source_r.text;
    END LOOP;

    INSERT INTO TABLE1(ID,DATE,CLOB_COLUMN)
    VALUES (my_seq.NEXTVAL,'11.10.2018',txt_procedure);
END;
/

UPDATE

As an alternative, you might also use the DBMS_METADATA package for this:

DECLARE
    txt_procedure CLOB;
BEGIN
    txt_procedure := DBMS_METADATA.get_ddl(
                         object_type => 'PROCEDURE',
                         name        => 'My_procedure',
                         owner       => 'YOUR_SCHEMA'
                     );

    INSERT INTO TABLE1(ID,DATE,CLOB_COLUMN)
    VALUES (my_seq.NEXTVAL,'11.10.2018',txt_procedure);
END;
/

推荐阅读