I have read access to an Oracle database with minimum control. I am trying to build a PowerShell (as the database is on Windows) script that can take few parameters and process the data based on the File Type of Blob. Right now I am exporting things from SQL Developer by right clicking and then save them based on this from SO. However, it will take forever to do so for hundreds of tables. I am not an expert with Oracle. So, trying to do it an easy way with SQL (if not PL/SQL) where it can export the BLOB column to a local Windows Drive so that my PowerShell Script can take care of renaming the file either as Image or Text based on the parameter I pass.
Here are the structures of my tables
CREATE TABLE JLR.RUNNERS
(
ENTITY_TYPE_ID VARCHAR2(32 CHAR) DEFAULT SYS_GUID() NOT NULL
, NAME VARCHAR2(64 CHAR) NOT NULL
, CAPTION VARCHAR2(64 CHAR) NOT NULL
, RUNNER_ICON BLOB
) ;
CREATE TABLE JLR.WINNERS
(
ENTITY_TYPE_ID VARCHAR2(32 CHAR) DEFAULT SYS_GUID() NOT NULL
, NAME VARCHAR2(64 CHAR) NOT NULL
, CAPTION VARCHAR2(64 CHAR) NOT NULL
, WINNER_FILE CLOB
, WINNER_ICON BLOB
);
For the above tables, if I pass the blob column as a parameter, the SQL should export each record as separate files into a location I pass as another parameter. Some tables might have more than one blob column as mentioned above and I should be able to extract them into two separate sub-directories with the column name for the table. (It would actually be nice if this export process can tell me what is the extension type of the file as there might .docx, .xlsx, .pptx, .pdf, etc.)
PS: I can't use any other tool other than SQL and/or PL/SQL to do this.
