By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE dummyTab (
id NUMBER,
session_id VARCHAR2(20),
account_id NUMBER,
lob_data BLOB
);
CREATE SEQUENCE sequenceName;
CREATE PROCEDURE p_dummy_proc (
i_session_id IN DUMMYTAB.SESSION_ID%TYPE,
i_account_id IN DUMMYTAB.ACCOUNT_ID%TYPE,
i_lob_Data IN DUMMYTAB.LOB_DATA%TYPE,
o_id OUT DUMMYTAB.ID%TYPE
)
IS
BEGIN
INSERT INTO dummyTab (
id,
session_id,
account_id,
lob_data
) VALUES (
sequenceName.NEXTVAL,
i_session_id,
i_account_id,
i_lob_data
) RETURNING id INTO o_id;
END;
/
SELECT * FROM USER_ERRORS;
DECLARE
v_blob BLOB;
v_id DUMMYTAB.ID%TYPE;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
DBMS_LOB.APPEND(v_blob, UTL_RAW.CAST_TO_RAW(DBMS_RANDOM.STRING('p', 4000)));
DBMS_LOB.APPEND(v_blob, UTL_RAW.CAST_TO_RAW(DBMS_RANDOM.STRING('p', 4000)));
p_dummy_proc('ABC', 123, v_blob, v_id);
DBMS_OUTPUT.PUT_LINE(v_id);
END;
/
1 rows affected
dbms_output:
1
SELECT id, session_id, account_id, LENGTH(lob_data) FROM dummyTab;
| ID | SESSION_ID | ACCOUNT_ID | LENGTH(LOB_DATA) |
|---|---|---|---|
| 1 | ABC | 123 | 8000 |