Home » Developer & Programmer » JDeveloper, Java & XML » file to base64 (need some tips)
file to base64 [message #531620] Thu, 17 November 2011 02:26 Go to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Good day, I have wroten code in JAVA. And I want to know if it's good or not so good or maybe don't do that thing.
Please give me some advices.

public TestServiceBean() {
try
{
  Blob value = null;
  BigDecimal idValue;
  Context ic = new InitialContext();
  ds = (DataSource)ic.lookup("....");
  if (ds != null)
  {
	System.out.println("OK");
	Connection conn = null;
	try
	{
		conn = ds.getConnection();
		PreparedStatement stmt = 
		conn.prepareStatement("SELECT 1000 AS id FROM DUAL WHERE 1=1");
		ResultSet rs = stmt.executeQuery();
		while (rs.next())
		{
			BigDecimal idValue = 
			rs.getBigDecimal(1, "100.00"); //something wrong here ?
		}
		
		byte [] buffer = new byte[2048];
		InputStream input = new FileInputStream(data);
		int data = input.read();
		BigDecimal data2;
		OutputStream output = data2.setBinaryStream(0L);
		while(data != -1)
		{
			int valueLen = (int)data.length();
			int valueLeft = 1;
			while (valueLeft <= 0)
			{
				output.write(buffer, buffer.length / 4, buffer.length);
				valueLeft = valueLen - 2048;
			}
			data = input.read();
		}
		input.close();
		string base64String = Convert.ToBase64String(data2);

		PreparedStatement stmt2 = 
		conn.prepareStatement("UPDATE TABLE_NAME SET base64 = 
		"'+ base64String +'" WHERE id="'+ idValue +'"");
		stmt2.executeQuery();

		conn.commit();
	}
	catch (SQLException e)
	{
		try
		{
			conn.rollback();
		}
		catch (SQLException ignore){}
		}
		finally
		{
			if (conn != null)
			{
				try
				{
					conn.close();
				}
				catch (SQLException ignore) {}
			}
			conn = null;
		}
  } else {
	  System.out.println("DS not found!");
  }
}
catch (Exception e)
{
  throw new RuntimeException(e);
}


I think that I have many mistakes but maybe not.

Best regards,
wtfn00b.
Re: file to base64 [message #531621 is a reply to message #531620] Thu, 17 November 2011 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the purpose of this code is?

Regards
Michel
Re: file to base64 [message #531624 is a reply to message #531621] Thu, 17 November 2011 02:41 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 10:28
And the purpose of this code is?

Regards
Michel



Oh sorry forgot to tell. I need to select uniqa id from table.
Upload file in system and make it into base64 and upload base64 string to database with UPDATE uniqa = id.

Working on:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
and
JDeveloper 11g R2 11.1.2.1.0

Best regards,
wtfn00b.
Re: file to base64 [message #531627 is a reply to message #531624] Thu, 17 November 2011 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read file to lob -> DBMS_LOB.LOADBLOBFROMFILE
Encode it -> UTL_ENCODE.BASE64_ENCODE

Do it in PL/SQL on database server, no need of Java.

Regards
Michel
Re: file to base64 [message #531628 is a reply to message #531627] Thu, 17 November 2011 03:00 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 10:56
Read file to lob -> DBMS_LOB.LOADBLOBFROMFILE
Encode it -> UTL_ENCODE.BASE64_ENCODE

Do it in PL/SQL on database server, no need of Java.

Regards
Michel


Hmm, can you please give me little bit sample of this two functions ?
And I need decode it not encode. Is there a similar function ?

Best regards,
wtfn00b.
Re: file to base64 [message #531630 is a reply to message #531628] Thu, 17 November 2011 03:03 Go to previous messageGo to next message
Littlefoot
Messages: 21559
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UTL_ENCODE package.
Re: file to base64 [message #531631 is a reply to message #531630] Thu, 17 November 2011 03:08 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Littlefoot wrote on Thu, 17 November 2011 11:03
UTL_ENCODE package.


I know that there is manual for this.
But I haven't done this before, like I told in one of my poste
that I'm new for PL/SQL. I have worked before in MS SQL.
So please can someone give me good working sample thats works.
Thank you.

Best regards,
wtfn00b.
Re: file to base64 [message #531632 is a reply to message #531628] Thu, 17 November 2011 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And I need decode it not encode.


I choose to point to BASE64_ENCODE because:
1/ The title is "file to base64"
2/ The function you use is "Convert.ToBase64String" (so encode)
3/ The name of the column is "base64"

Maybe there is something wrong somewhere.

Regards
Michel
Re: file to base64 [message #531633 is a reply to message #531632] Thu, 17 November 2011 03:12 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 11:09
Quote:
And I need decode it not encode.


I choose to point to BASE64_ENCODE because:
1/ The title is "file to base64"
2/ The function you use is "Convert.ToBase64String" (so encode)
3/ The name of the column is "base64"

Maybe there is something wrong somewhere.

Regards
Michel


Oh, sorry your right from file to base64 is encode and from base64 to file is decode. My bad.

Regards,
wtfn00b.
Re: file to base64 [message #531634 is a reply to message #531620] Thu, 17 November 2011 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By the way:
		PreparedStatement stmt = 
		conn.prepareStatement("SELECT 1000 AS id FROM DUAL WHERE 1=1");
		ResultSet rs = stmt.executeQuery();
		while (rs.next())
		{
			BigDecimal idValue = 
			rs.getBigDecimal(1, "100.00"); //something wrong here ?
		}


Why do you need to call Oracle to put 1000 into a local variable.

Regards
Michel
Re: file to base64 [message #531636 is a reply to message #531634] Thu, 17 November 2011 03:19 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 11:13
By the way:
		PreparedStatement stmt = 
		conn.prepareStatement("SELECT 1000 AS id FROM DUAL WHERE 1=1");
		ResultSet rs = stmt.executeQuery();
		while (rs.next())
		{
			BigDecimal idValue = 
			rs.getBigDecimal(1, "100.00"); //something wrong here ?
		}


Why do you need to call Oracle to put 1000 into a local variable.

Regards
Michel


From other database will be taken login and psw and return id of session. And I will get this id and put base64 in to the same database. But I don't have access to the database and will get only ID.

Regards,
wtfn00b.
Re: file to base64 [message #531641 is a reply to message #531636] Thu, 17 November 2011 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example of what you can do:
SQL> create table t (id integer, base64 blob);

Table created.

SQL> insert into t values (100, empty_blob());

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace directory FILESDIR as 'C:\';

Directory created.

SQL> host type c:\t.txt
I am Michel

SQL> CREATE OR REPLACE PROCEDURE p (p_id integer)
  2  IS
  3    tmp_lob  BLOB;
  4    dest_lob BLOB;
  5    file     BFILE;
  6    lg       PLS_INTEGER;
  7    i        PLS_INTEGER;
  8    piece    RAW(32000);
  9    src_off  PLS_INTEGER := 1;
 10    dst_off  PLS_INTEGER := 1;
 11  BEGIN
 12    select base64 into dest_lob from t where id = p_id for update;
 13    DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
 14    file := BFILENAME('FILESDIR','T.TXT');
 15    DBMS_LOB.OPEN (file);
 16    DBMS_LOB.LOADBLOBFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
 17    lg := DBMS_LOB.GETLENGTH (tmp_lob);
 18    i  := 1;
 19    WHILE i <= lg LOOP
 20      piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
 21      DBMS_LOB.APPEND (dest_lob, UTL_ENCODE.BASE64_ENCODE (piece));
 22      i := i + 32000;
 23    END LOOP;
 24  END;
 25  /

Procedure created.

SQL> exec p(100);

PL/SQL procedure successfully completed.

SQL> col val format a50
SQL> select id, utl_raw.cast_to_varchar2(utl_encode.base64_decode(base64)) val from t;
        ID VAL
---------- --------------------------------------------------
       100 I am Michel

1 row selected.

Regards
Michel
Re: file to base64 [message #531660 is a reply to message #531641] Thu, 17 November 2011 04:32 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Thank you a lot Michel.

I don't want to make new topic. But maybe someone know is it possible to check file or *.zip file for viruses ?

Best regards,
wtfn00b.
Re: file to base64 [message #531661 is a reply to message #531660] Thu, 17 November 2011 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not contain an antivirus package.
(Remember this is an Oracle forum, we don't answer question that are not related to Oracle.)

Regards
Michel
Re: file to base64 [message #531676 is a reply to message #531660] Thu, 17 November 2011 05:06 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
I tried your code and have this error:

PROCEDURE fileUpload compiled

Error starting at line 1 in command:
exec fileUpload(100)
Error report:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 937
ORA-06512: at "JANIS.FILEUPLOAD", line 15
ORA-06512: at line 1
22285. 00000 -  "non-existent directory or file for %s operation"
*Cause:    Attempted to access a directory that does not exist, or attempted
           to access a file in a directory that does not exist.
*Action:   Ensure that a system object corresponding to the specified
           directory exists in the database dictionary, or
           make sure the name is correct.

Re: file to base64 [message #531678 is a reply to message #531661] Thu, 17 November 2011 05:07 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 12:36
Oracle does not contain an antivirus package.
(Remember this is an Oracle forum, we don't answer question that are not related to Oracle.)

Regards
Michel


Okey, I didn't not know that oracle don't have this package.

Regards,
wtfn00b.
Re: file to base64 [message #531684 is a reply to message #531678] Thu, 17 November 2011 05:14 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
And I have one more question about this upload thing.
How can I check performance JAVA upload base64 or PL/SQL upload to BLOB ?
Or maybe someone know what is better/faster in % or time or smth.
Need reference on this question.

Best regards,
wtfn00b.
Re: file to base64 [message #531688 is a reply to message #531676] Thu, 17 November 2011 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried your code and have this error:


I first executed:

Quote:
create or replace directory FILESDIR as 'C:\';


Did you?

Regards
Michel
Re: file to base64 [message #531689 is a reply to message #531684] Thu, 17 November 2011 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can I check performance JAVA upload base64 or PL/SQL upload to BLOB ?


Execute both and see which ones is faster.

Regards
Michel
Re: file to base64 [message #531693 is a reply to message #531688] Thu, 17 November 2011 05:30 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 13:18
Quote:
I tried your code and have this error:


I first executed:

Quote:
create or replace directory FILESDIR as 'C:\';


Did you?

Regards
Michel


I modify procedure.

Steps:

1.
SQL> select * from files_base64
ID                     FILE_BLOB              
---------------------- ----------
100                    (BLOB)   

2.
SQL> create or replace directory FILESDIR as 'C:\';
directory FILESDIR created.

3.
SQL> host type c:\t.txt
test this now

4.
SQL>
CREATE OR REPLACE PROCEDURE hellow_world (p_id integer)
    IS
      tmp_lob  BLOB;
      dest_lob BLOB;
      file     BFILE;
      lg       PLS_INTEGER;
      i        PLS_INTEGER;
      piece    RAW(32000);
      src_off  PLS_INTEGER := 1;
     dst_off  PLS_INTEGER := 1;
   BEGIN
     select file_blob into dest_lob from files_base64 where id = p_id for update;
     DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
     file := BFILENAME('FILESDIR','T.TXT');
     DBMS_LOB.OPEN (file);
     DBMS_LOB.LOADBLOBFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
     lg := DBMS_LOB.GETLENGTH (tmp_lob);
     i  := 1;
     WHILE i <= lg LOOP
       piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
       DBMS_LOB.APPEND (dest_lob, UTL_ENCODE.BASE64_ENCODE (piece));
       i := i + 32000;
     END LOOP;
   END;

PROCEDURE hellow_world compiled

5.
SQL>exec hellow_world(100);



Error starting at line 1 in command:
exec hellow_world(100)
Error report:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 937
ORA-06512: at "JANIS.HELLOW_WORLD", line 15
ORA-06512: at line 1
22288. 00000 -  "file or LOB operation %s failed\n%s"
*Cause:    The operation attempted on the file or LOB failed.
*Action:   See the next error message in the error stack for more detailed
           information.  Also, verify that the file or LOB exists and that
           the necessary privileges are set for the specified operation. If
           the error still persists, report the error to the DBA.




Best regards,
wtfn00b.

[Updated on: Thu, 17 November 2011 05:58] by Moderator

Report message to a moderator

Re: file to base64 [message #531694 is a reply to message #531693] Thu, 17 November 2011 05:39 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
I forgot to tell that server is not on my local machine.

Best regards,
wtfn00b.
Re: file to base64 [message #531697 is a reply to message #531694] Thu, 17 November 2011 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaah! PL/SQL is only able to read/write on the database server.

Regards
Michel
Re: file to base64 [message #531701 is a reply to message #531697] Thu, 17 November 2011 05:53 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 13:48
Aaah! PL/SQL is only able to read/write on the database server.

Regards
Michel


Is it possible to create XML in PL/SQL and make like package with JAVA web service. And just upload base64 file blob into table column ? And whan needed take out this blob to user.

I need to create only upload package with xml.
File don't need to be put on server.
Or it's not possible to do this ?

JAVA web service from package who is taking file from local machine and past to database in base64 format. And whan needed taking out of database ?

Best regards,
wtfn00b.
Re: file to base64 [message #531703 is a reply to message #531701] Thu, 17 November 2011 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't really understand what you mean.

Quote:
Is it possible to create XML in PL/SQL


Yes.

Quote:
make like package with JAVA web service


You can create PL/SQL package and even Java package inside the database.

Quote:
just upload base64 file blob into table column ?


Is this not what my procedure does?

Quote:
I need to create only upload package with xml.
File don't need to be put on server.


As far as I know, it is not possible to access files outside database servers.

Quote:
And whan needed taking out of database ?


Use SELECT or PL/SQL to do it the opposite.

Regards
Michel
Re: file to base64 [message #531705 is a reply to message #531703] Thu, 17 November 2011 06:20 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Okey, Michel, I understand all the answers.
And your procedure is doing it. If file is placed on server than everything of your procedure is very good.
But I need to create package with XML and procedures who can do this:

Using soapUI (client side):
<id>100</id> [id is taking from table]
<filename>t.txt</filename> [t.txt contains "test this now", file is uploaded by user from local machine]
<base64>dGVzdCB0aGlzIG5vdw==</base64> [2 ways to create this: 1) MTOM with JAVA; 2) procedure like you wrote me]

Maybe is possible something else ?
User don't need to see base64 code.
User need only show/choice the file from his local machine.
Like is web pages you can use browser buttom to find file on your local machine.

So is it possible to do what I need with PL/SQL.
It will be very great if possible.

Best regards,
wtfn00b.

[Updated on: Thu, 17 November 2011 06:20]

Report message to a moderator

Re: file to base64 [message #531708 is a reply to message #531705] Thu, 17 November 2011 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Like is web pages you can use browser buttom to find file on your local machine.

And then put it on the application server or you can put it on the database server via a mounted file system on the application server and then call the procedure.

So, client send to application server the xm containing id, file name and content, the application server save this inside the database server (the whole xml file) using a mounted file system (or a network drive in Windows world) and call the PL/SQL procedure apssing it the xml file, in the end, the proceure reads the xml file and does what my procedure does.

Regards
Michel
Re: file to base64 [message #531709 is a reply to message #531708] Thu, 17 November 2011 06:53 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 14:49
Quote:
Like is web pages you can use browser buttom to find file on your local machine.

And then put it on the application server or you can put it on the database server via a mounted file system on the application server and then call the procedure.

So, client send to application server the xm containing id, file name and content, the application server save this inside the database server (the whole xml file) using a mounted file system (or a network drive in Windows world) and call the PL/SQL procedure apssing it the xml file, in the end, the proceure reads the xml file and does what my procedure does.

Regards
Michel



Yes like that. But I don't know how to do this Sad

Best regards,
wtfn00b.
Re: file to base64 [message #531713 is a reply to message #531709] Thu, 17 November 2011 07:30 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel can you please check your PM box, thank you.

Best regards,
wtfn00b.
Re: file to base64 [message #531715 is a reply to message #531713] Thu, 17 November 2011 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I can't code it for you I'm a beginner in Java and I know nothing to SOAP but if you start to code and post it I can help you to go further.
I can give you the PL/SQL procedure (when I have a little bit time) from what I said but you have to do the application server code.

Regards
Michel
Re: file to base64 [message #531719 is a reply to message #531715] Thu, 17 November 2011 07:50 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 15:48
Sorry I can't code it for you I'm a beginner in Java and I know nothing to SOAP but if you start to code and post it I can help you to go further.
I can give you the PL/SQL procedure (when I have a little bit time) from what I said but you have to do the application server code.

Regards
Michel


Okey, I will wait when you have time for PL/SQL.
Please post back.
Many thanks.

Best regards,
wtfn00b.
Re: file to base64 [message #531724 is a reply to message #531719] Thu, 17 November 2011 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not alone here, I think Barbara (that lives in USA West cost) will arrive in a couple of hours and will help you for this procedure if I have not time since then. She has more time and is more skilled than I am in PL/SQL. It is, for PL/SQL developer, few modifications from the procedure I already posted.

Regards
Michel
Re: file to base64 [message #531725 is a reply to message #531724] Thu, 17 November 2011 08:02 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Michel Cadot wrote on Thu, 17 November 2011 16:01
I'm not alone here, I think Barbara (that lives in USA West cost) will arrive in a couple of hours and will help you for this procedure if I have not time since then. She has more time and is more skilled than I am in PL/SQL. It is, for PL/SQL developer, few modifications from the procedure I already posted.

Regards
Michel


Great Smile

Best regards,
wtfn00b.
Re: file to base64 [message #531740 is a reply to message #531725] Thu, 17 November 2011 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just to clarify, I see in the XML string you pass you have the file content encoded, in table do you want the file content encoded or decoded?

Regards
Michel
Re: file to base64 [message #531794 is a reply to message #531740] Thu, 17 November 2011 12:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I am not sure that I understand the requirements. Here is what I think I understand. You want the user to be able to pass a file name and directory name to a procedure that will then upload a unique id, the file name, and the contents of the file in base 64 in xml format into a table and be able to select the decoded base 64 data. I have taken the modifications that you did to what Michel provided and modified that to do this below. Note that it requires that the file be somehow moved to the server before the procedure is executed.

SCOTT@orcl_11gR2> host type c:\my_oracle_files\t.txt
test this now

SCOTT@orcl_11gR2> CREATE TABLE files_base64
  2    (id	   INTEGER,
  3  	file_xml   XMLTYPE);

Table created.

SCOTT@orcl_11gR2> CREATE SEQUENCE id_seq;

Sequence created.

SCOTT@orcl_11gR2> create or replace directory FILESDIR as 'C:\my_oracle_files';

Directory created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE hellow_world
  2    (p_filename  VARCHAR2,
  3  	p_dir	    VARCHAR2)
  4  IS
  5    tmp_lob	    BLOB;
  6    file	    BFILE;
  7    src_off	    PLS_INTEGER := 1;
  8    dst_off	    PLS_INTEGER := 1;
  9    lg	    PLS_INTEGER;
 10    i	    PLS_INTEGER;
 11    v_id	    INTEGER;
 12    v_data	    VARCHAR2(32767);
 13    dest_lob     CLOB;
 14    piece	    RAW(32000);
 15  BEGIN
 16    DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
 17    file := BFILENAME(p_dir, p_filename);
 18    DBMS_LOB.OPEN (file);
 19    DBMS_LOB.LOADFROMFILE (tmp_lob, file, dbms_lob.lobmaxsize, src_off, dst_off);
 20    lg := DBMS_LOB.GETLENGTH (tmp_lob);
 21    i  := 1;
 22    SELECT id_seq.NEXTVAL INTO v_id FROM DUAL;
 23    v_data :=
 24  	 '<table>'
 25  	 || '<id>' || v_id || '</id>'
 26  	 || '<filename>' || p_filename || '</filename>'
 27  	 || '<base64>';
 28    DBMS_LOB.CREATETEMPORARY (dest_lob, false, dur=>dbms_lob.call);
 29    DBMS_LOB.WRITEAPPEND
 30  	 (dest_lob,
 31  	  LENGTH (v_data),
 32  	  v_data);
 33    WHILE i <= lg LOOP
 34  	 piece := DBMS_LOB.SUBSTR (tmp_lob, 32000, i);
 35  	 DBMS_LOB.WRITEAPPEND
 36  	   (dest_lob,
 37  	    LENGTH (UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (piece))),
 38  	    UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (piece)));
 39  	 i := i + 32000;
 40    END LOOP;
 41    v_data := '</base64></table>';
 42    DBMS_LOB.WRITEAPPEND
 43  	 (dest_lob,
 44  	  LENGTH (v_data),
 45  	  v_data);
 46    INSERT INTO files_base64 (id, file_xml)
 47    VALUES (v_id, XMLTYPE (dest_lob));
 48    DBMS_LOB.FREETEMPORARY (tmp_lob);
 49    DBMS_LOB.FREETEMPORARY (dest_lob);
 50    DBMS_LOB.CLOSE (file);
 51  END hellow_world;
 52  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec hellow_world ('t.txt', 'FILESDIR');

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> col	 file_xml format a65
SCOTT@orcl_11gR2> select * from files_base64
  2  /

        ID FILE_XML
---------- -----------------------------------------------------------------
         1 <table>
             <id>1</id>
             <filename>t.txt</filename>
             <base64>dGVzdCB0aGlzIG5vdw0K</base64>
           </table>


1 row selected.

SCOTT@orcl_11gR2> column id	  format a3
SCOTT@orcl_11gR2> column filename format a8
SCOTT@orcl_11gR2> column data	  format a65
SCOTT@orcl_11gR2> select extractvalue (x.column_value, '/table/id') id,
  2  	    extractvalue (x.column_value, '/table/filename') filename,
  3  	    utl_raw.cast_to_varchar2
  4  	      (utl_encode.base64_decode
  5  		(utl_raw.cast_to_raw
  6  		  (extractvalue (x.column_value, '/table/base64')))) data
  7  from   files_base64 t,
  8  	    table (xmlsequence (t.file_xml)) x
  9  /

ID  FILENAME DATA
--- -------- -----------------------------------------------------------------
1   t.txt    test this now

1 row selected.

SCOTT@orcl_11gR2>

Re: file to base64 [message #531806 is a reply to message #531794] Thu, 17 November 2011 14:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
If you have no other method of getting your file from the client to the server, you can use Oracle's SQL*Loader utility to load the data into a staging table, then select it from there. SQL*Loader can be executed using DBMS_SCHEDULER. DBMS_SCHEDULER can be used in a procedure and you can call that procedure from your existing procedure. I have provided a full demonstration below. You will need to make sure that you have all of the proper privileges. I have used dbms_lock.sleep to allow enough time for the load into the staging table to complete before trying to select data from it, but you may want to do something different with that. This is just a brief example. Also, my example is for Windows operating system. It may be somewhat different on other operating systems.

SCOTT@orcl_11gR2> host type c:\my_oracle_files\t.txt
test this now

SCOTT@orcl_11gR2> host type c:\my_oracle_files\test.ctl
load data
into table staging_tab
fields
(staging_col char(5000))

SCOTT@orcl_11gR2> CREATE TABLE staging_tab
  2    (staging_col  CLOB)
  3  /

Table created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE load_staging
  2    (p_filename IN VARCHAR2,
  3  	p_dir_path IN VARCHAR2)
  4  AS
  5  BEGIN
  6    DBMS_SCHEDULER.CREATE_JOB
  7  	 (job_name	      => 'test_job',
  8  	  job_type	      => 'EXECUTABLE',
  9  	  job_action	      => 'c:\windows\system32\cmd.exe',
 10  	  number_of_arguments => 3,
 11  	  start_date	      => SYSTIMESTAMP,
 12  	  enabled	      => FALSE);
 13    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
 14  	 ('test_job', 1, '/q');
 15    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
 16  	 ('test_job', 2, '/c');
 17    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
 18  	 ('test_job',
 19  	  3,
 20  	  'SQLLDR scott/tiger CONTROL=''c:\my_oracle_files\test.ctl'' LOG=''c:\my_oracle_files\test.log'' DATA='''
 21  	  || p_dir_path || p_filename || '''');
 22    DBMS_SCHEDULER.ENABLE ('test_job');
 23  END load_staging;
 24  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE TABLE files_base64
  2    (id	   INTEGER,
  3  	file_xml   XMLTYPE);

Table created.

SCOTT@orcl_11gR2> CREATE SEQUENCE id_seq;

Sequence created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE hellow_world
  2    (p_filename IN VARCHAR2,
  3  	p_dir_path IN VARCHAR2)
  4  IS
  5    tmp_lob	    CLOB;
  6    lg	    PLS_INTEGER;
  7    v_id	    INTEGER;
  8    v_data	    VARCHAR2(32767);
  9    dest_lob     CLOB;
 10  BEGIN
 11    load_staging (p_filename, p_dir_path);
 12    -- allow enough time for job to finish running:
 13    dbms_lock.sleep (15);
 14    DBMS_LOB.CREATETEMPORARY (tmp_lob, false, dur=>dbms_lob.call);
 15    SELECT staging_col INTO tmp_lob FROM staging_tab;
 16    lg := DBMS_LOB.GETLENGTH (tmp_lob);
 17    SELECT id_seq.NEXTVAL INTO v_id FROM DUAL;
 18    v_data :=
 19  	 '<table>'
 20  	 || '<id>' || v_id || '</id>'
 21  	 || '<filename>' || p_filename || '</filename>'
 22  	 || '<base64>';
 23    DBMS_LOB.CREATETEMPORARY (dest_lob, false, dur=>dbms_lob.call);
 24    DBMS_LOB.WRITEAPPEND
 25  	 (dest_lob,
 26  	  LENGTH (v_data),
 27  	  v_data);
 28  	 DBMS_LOB.APPEND
 29  	   (dest_lob,
 30  	    UTL_RAW.CAST_TO_VARCHAR2
 31  	      (UTL_ENCODE.BASE64_ENCODE
 32  		(UTL_RAW.CAST_TO_RAW (tmp_lob))));
 33    v_data := '</base64></table>';
 34    DBMS_LOB.WRITEAPPEND
 35  	 (dest_lob,
 36  	  LENGTH (v_data),
 37  	  v_data);
 38    INSERT INTO files_base64 (id, file_xml)
 39    VALUES (v_id, XMLTYPE (dest_lob));
 40    DBMS_LOB.FREETEMPORARY (dest_lob);
 41  END hellow_world;
 42  /

Procedure created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> exec hellow_world ('t.txt', 'c:\my_oracle_files\');

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> col	 file_xml format a65
SCOTT@orcl_11gR2> select * from files_base64
  2  /

        ID FILE_XML
---------- -----------------------------------------------------------------
         1 <table>
             <id>1</id>
             <filename>t.txt</filename>
             <base64>dGVzdCB0aGlzIG5vdw==</base64>
           </table>


1 row selected.

SCOTT@orcl_11gR2> column id	  format a3
SCOTT@orcl_11gR2> column filename format a8
SCOTT@orcl_11gR2> column data	  format a65
SCOTT@orcl_11gR2> select extractvalue (x.column_value, '/table/id') id,
  2  	    extractvalue (x.column_value, '/table/filename') filename,
  3  	    utl_raw.cast_to_varchar2
  4  	      (utl_encode.base64_decode
  5  		(utl_raw.cast_to_raw
  6  		  (extractvalue (x.column_value, '/table/base64')))) data
  7  from   files_base64 t,
  8  	    table (xmlsequence (t.file_xml)) x
  9  /

ID  FILENAME DATA
--- -------- -----------------------------------------------------------------
1   t.txt    test this now

1 row selected.

SCOTT@orcl_11gR2>

Re: file to base64 [message #532157 is a reply to message #531806] Mon, 21 November 2011 02:01 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Wow, thanks I will try this out right now.


Best regards,
wtfn00b.
Re: file to base64 [message #532347 is a reply to message #532157] Tue, 22 November 2011 01:31 Go to previous message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Works very good, thank you a lot Wink

Best regards,
wtfn00b.
Previous Topic: Issue in xml parsing through pl/sql
Next Topic: JAVA class
Goto Forum:
  


Current Time: Sun May 24 17:54:44 CDT 2020