Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to insert a folder from a directory into a table? (Oracle Data Base 11g r2)
Is it possible to insert a folder from a directory into a table? [message #676994] Mon, 05 August 2019 18:43 Go to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
I have a requirement where I have to take a complete folder with images which will be compressed in .zip. I am currently trying to insert that folder into a table but I am honestly not sure if this is possible.

Any questions or comments are appreciated.
Re: Is it possible to insert a folder from a directory into a table? [message #676995 is a reply to message #676994] Mon, 05 August 2019 18:48 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
It is possible when the table contains a BLOB column

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

BTW Oracle data base 11g r2 is a marketing label & NOT an actual version number to 4 decimal places
Re: Is it possible to insert a folder from a directory into a table? [message #677000 is a reply to message #676994] Tue, 06 August 2019 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any questions or comments are appreciated

Last message of your previous topic:

Michel Cadot wrote on Thu, 21 March 2019 06:36

And the source of ORA-29284 was?

It'd be appreciated you answer this question and help people with your issue.

Quote:
I am honestly not sure if this is possible.

It is possible.
Re: Is it possible to insert a folder from a directory into a table? [message #677003 is a reply to message #677000] Tue, 06 August 2019 09:34 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Michel Cadot wrote on Thu, 21 March 2019 06:36

And the source of ORA-29284 was?
The error messages I get are the following:
ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at line 8

Quote:
It is possible.
Could you share your idea on how to do this please

I am testing with the following code but I have had results:

DECLARE
    oNew         BLOB;
    oBFile       BFILE;
    v_path       VARCHAR2 (100) := 'EXT_DIR_TEST';
    v_fileName   VARCHAR2 (4000) := '';
BEGIN
    oBFile := BFILENAME (v_path, 'BlobFiles'); -- BlobFiles is the name of the folder I try to insert 
    DBMS_LOB.OPEN (oBFile, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.createtemporary (oNew, TRUE);
    DBMS_LOB.LOADFROMFILE (oNew, oBFile, DBMS_LOB.lobmaxsize);
    DBMS_LOB.CLOSE (oBFile);
    INSERT INTO HR_FOLDER_INSERT  VALUES ( oNew );
    COMMIT;
    DBMS_LOB.freetemporary (oNew);
END;
Re: Is it possible to insert a folder from a directory into a table? [message #677004 is a reply to message #677003] Tue, 06 August 2019 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The error messages I get are the following:
And the root of this error was? Please answer in your previous topic.

Re: Is it possible to insert a folder from a directory into a table? [message #677006 is a reply to message #677004] Tue, 06 August 2019 12:10 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Hi Michel,

I have already answered the previous topic.

Regards.
Re: Is it possible to insert a folder from a directory into a table? [message #677007 is a reply to message #677006] Tue, 06 August 2019 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
FWIW - if you are changing the actual content of the ZIP file by including "random" CHR(10) values,
the BLOB most likely will fail to unzip & reproduce the original contents.

You really need to verify that you can unzip the stored BLOB content & actually extract the original directory content.
Re: Is it possible to insert a folder from a directory into a table? [message #677009 is a reply to message #677007] Tue, 06 August 2019 13:30 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
BlackSwan wrote on Tue, 06 August 2019 12:36
FWIW - if you are changing the actual content of the ZIP file by including "random" CHR(10) values,
the BLOB most likely will fail to unzip & reproduce the original contents.

You really need to verify that you can unzip the stored BLOB content & actually extract the original directory content.
The first step I want to achieve is to be able to specifically insert the folder into the table, not just the contents of the folder.

I tried with this code:

  DECLARE
      oNew     BLOB;
      oBFile   BFILE;
      v_path varchar2(100) :='TOKS_HR_DIR_EXT_HDL';
      v_fileName varchar2(4000) := '';
   BEGIN
      oBFile := BFILENAME(v_path,null);
      DBMS_LOB.OPEN(oBFile, DBMS_LOB.LOB_READONLY);
      DBMS_LOB.createtemporary(oNew,TRUE);
      DBMS_LOB.LOADFROMFILE(oNew, oBFile, dbms_lob.lobmaxsize);
      DBMS_LOB.CLOSE(oBFile);
      INSERT INTO TOKS_HR_ARCHIVOS2  VALUES ( oNew );
      dbms_lob.freetemporary(oNew);     
   END;

but it throws the following errors:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at line 8

Thanks
Re: Is it possible to insert a folder from a directory into a table? [message #677011 is a reply to message #677009] Wed, 07 August 2019 01:23 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
You are passing NULL as the second argument to BFILENAME. You should be passing the name of the file.
Re: Is it possible to insert a folder from a directory into a table? [message #677012 is a reply to message #677011] Wed, 07 August 2019 11:16 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
I don't believe that you can store an entire directory "straight into" a Blob. You can certainly zip it first, and then insert the zip into a Blob. I don't think that Oracle has enough smarts to know what to do with a folder (versus a file).

You may be able to some code logic (possibly in SQL) to store the entire directory, by providing a data format. You could also look into storing it in XML format into a Blob.

JP
Re: Is it possible to insert a folder from a directory into a table? [message #677013 is a reply to message #677012] Wed, 07 August 2019 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can get a file list using the following undocumented procedure (if you have access to SYS to create it):
SYS> CREATE OR REPLACE PROCEDURE get_file_list (p_files VARCHAR2)
  2  IS
  3    l_null  varchar2(100) := NULL;
  4    l_files varchar2(100);
  5  BEGIN
  6    l_files := p_files;
  7    sys.dbms_backup_restore.searchfiles(l_files, l_null);
  8    FOR x IN (select fname_krbmsft fname from x$krbmsft) LOOP
  9      dbms_output.put_line(x.fname);
 10    END LOOP;
 11  END;
 12  /

Procedure created.

SYS> GRANT execute ON get_file_list TO Michel
  2  /

Grant succeeded.

SYS> conn michel/michel
Connected.
MICHEL> exec sys.get_file_list ('C:\TEMP\*.JPG')
C:\TEMP\Travel1.JPG
C:\TEMP\Travel2.JPG
C:\TEMP\Travel3.JPG
C:\TEMP\Travel4.JPG
C:\TEMP\Travel5.JPG

PL/SQL procedure successfully completed.
Now you can compress them in a single ZIP file using the very useful Anton Scheffer's as_zip package.
(Read the terms of use.)

[Updated on: Wed, 07 August 2019 12:25]

Report message to a moderator

Re: Is it possible to insert a folder from a directory into a table? [message #677019 is a reply to message #677013] Wed, 07 August 2019 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another way to get the list of the files is to use an external table with a preprocess step:
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS 'C:\TEMP';

Directory created.

SQL> CREATE TABLE files (
  2    file_date VARCHAR2(15),
  3    file_time VARCHAR2(15),
  4    file_size VARCHAR2(15),
  5    file_name VARCHAR2(50)
  6    )
  7  ORGANIZATION EXTERNAL (
  8    TYPE ORACLE_LOADER
  9    DEFAULT DIRECTORY temp_dir
 10    ACCESS PARAMETERS (
 11      RECORDS DELIMITED BY NEWLINE
 12      NOBADFILE
 13      NOLOGFILE
 14      PREPROCESSOR temp_dir:'list_files.bat'
 15      FIELDS TERMINATED BY WHITESPACE
 16    )
 17   LOCATION ('files.lst')
 18  )
 19  REJECT LIMIT UNLIMITED
 20  /

Table created.
The "list_files.bat" script just lists the desired files:
SQL> host type c:\temp\list_files.bat
@echo off
dir %~dp0\*.JPG
So to get the list you just have to query the table:
SQL> SELECT * FROM files WHERE file_name LIKE '%.JPG';
FILE_DATE       FILE_TIME       FILE_SIZE       FILE_NAME
--------------- --------------- --------------- --------------------------------------------------
02/04/2004      12:07           258ÿ767         Travel1.JPG
23/08/2004      17:55           374ÿ382         Travel2.JPG
23/08/2004      17:56           311ÿ217         Travel3.JPG
23/08/2004      17:56           201ÿ726         Travel4.JPG
23/08/2004      17:57           269ÿ047         Travel5.JPG

5 rows selected.
"files.lst" does not matter, it is just there for the need of the CREATE TABLE syntax.
In my example, JPG files, location file and script are in the same directory, this may not be your case, you have to change the access parameters and the bat script. In the same way, if you are on Linux/Unix you obviously have to change the shell script.

Re: Is it possible to insert a folder from a directory into a table? [message #677025 is a reply to message #677019] Thu, 08 August 2019 00:37 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You posted a new question from my work.
So why no feedback here?
Why no thank for the work I have done for you?

Previous Topic: read and validate file
Next Topic: Comprimir una imagen utilizando AS_ZIP
Goto Forum:
  


Current Time: Mon Sep 21 14:45:46 CDT 2020