0

I would like to insert my file (csv) into a BLOB column (Oracle), I use a Korn Shell script to call an insert sql script via a SQLPLUS statement. my problem is that I don't know how to pass my file as a parameter to retrieve it in my insert script.

Here is a preview of my call in the ksh file :

file_pl_sql = insert_blob.sql

sqlplus $login/$pwd @file_pl_sql "{$directory_name}/{$file_name}"

Here is my insert_blob.sql :

BEGIN 
    
INSERT INTO FILE_BLOB VALUES ('&1')
    
COMMIT;
END;

/
exit

Here I pass the path and file name to my sql file but my file is not on the database server. I think I have to transcode my file into bytes and transmit the bytes but I have no idea how to do it in ksh and how to retrieve it from my sql file. Thank you for your help.

Improve this question
5
  • study the services available in the Oracle DBMS_LOB package Commented Nov 30, 2024 at 14:32
  • Thank you for your answer, I think that the pl/sql is running on the oracle server which does not have access to my file which is on the linux server, therefore, I could not use DBMS_LOB, I am not sure from what I say Commented Dec 1, 2024 at 8:29
  • If you are programming in Ksh, it is trivial to scp the csv file from client-side to a directory on the Oracle-server, where it can be made available to DBMS_LOB once ownership/permissions are managed accordingly. If the file is very large, you can compress first, and decompress at the server. Commented Dec 1, 2024 at 10:08
  • Thank you for your feedback, I found a solution (which I have not yet tested) and which consists of using sqlloader to load the contents of the file from the ksh script to the blob column Commented Dec 1, 2024 at 12:41
  • A shell script is deeply the wrong tool for this job. sqlloader is indeed a much better tool. (In the past, I built my own Oracle client in Python to serve similar purposes -- SQLPlus, at least as of 15 years ago, was not very much designed for programmatic use; perhaps it's better today). Commented Dec 1, 2024 at 20:34
Related questions
4
Passing Parameter to SQL*Plus Script Containing ACCEPT From Shell Script
2
passing variable from bash shell to sql file to oracle table
6
Can Sqlplus read the contents of a file into a variable?
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.