0

I have created a stored procedure which does insertion into a table. How can I insert json data which can be up to 64KB? The column request_params is of type blob.

create or replace PROCEDURE INSERT_USER
(in_user_id IN VARCHAR2,
in_user_name IN VARCHAR2,
in_request_params IN VARCHAR2) 
AS 
BEGIN
    insert into user_table(user_id,user_name,request_params) 
    values(in_user_id,in_user_name,in_request_params);

END INSERT_USER;
Chrᴉz remembers Monica
  • 1,829
  • 1
  • 10
  • 24
Ankita
  • 621
  • 2
  • 9
  • 25
  • 1
    If the column is BLOB, why is `in_request_parms` a string? Even if you made it a CLOB instead of VARCHAR2 to allow the size you need, you'd need to then convert that to a BLOB. Is the caller actually passing a string, or does/can it pass a BLOB directly? – Alex Poole Jun 10 '22 at 07:51
  • how to pass blob from JAVA applictain ? I mean can I covert JSON string to blob in JAVA – Ankita Jun 10 '22 at 07:52
  • I think I got the answer https://stackoverflow.com/questions/44609535/how-to-convert-json-object-to-blob-in-java – Ankita Jun 10 '22 at 07:55

1 Answers1

0

The column request_params is of type blob.

Then use a BLOB:

create or replace PROCEDURE INSERT_USER(
  in_user_id        IN VARCHAR2,
  in_user_name      IN VARCHAR2,
  in_request_params IN BLOB
)
AS 
BEGIN
  insert into user_table(user_id,user_name,request_params) 
  values(in_user_id,in_user_name,in_request_params);
END INSERT_USER;
/

or the same types as the table's columns (which would also be a BLOB):

create or replace PROCEDURE INSERT_USER(
  in_user_id        IN user_table.user_id%TYPE,
  in_user_name      IN user_table.user_name%TYPE,
  in_request_params IN user_table.request_params%TYPE
)
AS 
BEGIN
  insert into user_table(user_id,user_name,request_params) 
  values(in_user_id,in_user_name,in_request_params);
END INSERT_USER;
/

how to pass blob from JAVA applictain ? I mean can I covert JSON string to blob in JAVA

Use the PreparedStatement.setBinaryStream or PreparedStatement.setBlob functions. An example is in this answer (and if you have a JSON string then just get the bytes from the string and stream that to the blob).

MT0
  • 143,790
  • 11
  • 59
  • 117
  • request_params from java is JSON string – Ankita Jun 10 '22 at 09:11
  • @Ankita A JSON string is just a string and a string is just a series of bytes. – MT0 Jun 10 '22 at 09:12
  • I tried something https://stackoverflow.com/questions/44609535/how-to-convert-json-object-to-blob-in-java But java is giving error – Ankita Jun 10 '22 at 09:15
  • @Ankita "I tried something ... But java is giving an error" is not a constructive statement as it does not tell us exactly what code you used in your attempt or what the error was so there is nothing that we can debug. If you are having issues with this then you should [ask a new question](https://stackoverflow.com/questions/ask) with a complete [MRE]. (Rather than trying to debug it in comments). – MT0 Jun 10 '22 at 09:18
  • However, I would suggest that you use `new ByteArrayInputStream(yourJSONString.getBytes());` and pass it to `PreparedStatement.setBinaryStream` rather than trying to create a `BLOB` object. – MT0 Jun 10 '22 at 09:22