Pass BLOB to stored procedure from SQL*Plus

Question

How to call a stored procedure from SQL*Plus with blob input parameters?

Below a sample of the SP to be called:

procedure p_dummy_proc (sessionId varchar2, accountId integer, lobData blob, oId out number) is

    appo_id                number;

begin

    select sequenceName.nextval into appo_id from dual;

    insert into dummyTab (id, session_id, account_id, lob_data)
         values (appo_id,
                 sessionId,
                 accountId,
                 lobData)
      returning appo_id
           into oId;

exception
    when others then
        --do something
        return;
end;

The lobData should be a .NET object converted in byte array, but could be anything else (e.g. a PDF document).

EDIT: specifically, I need to test the correct call for LOBs of size greater than 4000 bytes.

MT0 · Accepted Answer · 2025-04-02 14:45:41Z

Declare a variable of a BLOB type.
Create a temporary BLOB.
Convert your data, in chunks, to a RAW and append it to your BLOB.
Pass the BLOB variable to the procedure as you would any other variable.

Like this (using random data):

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;
/

Which, for the setup:

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;
/

Then, after running the block, the query:

SELECT id, session_id, account_id, LENGTH(lob_data) FROM dummyTab;

Outputs:

ID	SESSION_ID	ACCOUNT_ID	LENGTH(LOB_DATA)
1	ABC	123	8000

fiddle

marc_s · Accepted Answer · 2025-06-15 09:00:28Z

0

-- Example Oracle Stored Procedure --
PROCEDURE StoredProcedure (p_blob  blob,
                           p_id out number)
BEGIN
    INSERT INTO ...
END;

-- Script to call the stored procedure --

DECLARE
    v_clob  CLOB;
    v_blob  BLOB;
    v_dest_offset NUMBER := 1;
    v_src_offset NUMBER := 1;
    o_id NUMBER;
BEGIN
    -- Create temporary LOBs
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
    DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);

    -- Generate a CLOB with 5000 characters
    DBMS_LOB.WRITE(v_clob, 5000, 1, RPAD('A', 5000, 'A'));

    DBMS_OUTPUT.PUT_LINE('CLOB Length: ' || DBMS_LOB.GETLENGTH(v_clob));

    -- Convert CLOB to BLOB
    DBMS_LOB.APPEND(v_blob, UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(v_clob, 5000, 1)));

    DBMS_OUTPUT.PUT_LINE('BLOB Length: ' || DBMS_LOB.GETLENGTH(v_blob));

    -- Call stored procedure
    Package.StoredProcedure (p_blob => v_blob,
                             p_id   => o_id);

    DBMS_OUTPUT.PUT_LINE('Output: ' || o_id);

    -- Free temporary LOBs
    DBMS_LOB.FREETEMPORARY(v_clob);
    DBMS_LOB.FREETEMPORARY(v_blob);

EXCEPTION 
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;

edited Jun 15 at 9:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 2 at 15:54

Cristian

974 bronze badges

1 Comment

Chait Apr 2 at 16:01

Hello, please consider adding some explanation to the code as how the code solves the problem. Thank you!

Collectives™ on Stack Overflow

Pass BLOB to stored procedure from SQL*Plus

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related