ORDS - Auto REST table feature

Got a question on how easy it is to use ORDS to perform insert | update | delete on a table.  Here's the steps.

1) Install ORDS ( cmd line or there's a new wizard in sqldev )

2) Enable the schema and table in this case klrice.emp; ( again there's a wizard in sqldev )

BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'KLRICE',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'klrice',
                       p_auto_rest_auth => FALSE);
    

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'KLRICE',
                       p_object => 'EMP',
                       p_object_type => 'TABLE',
                       p_object_alias => 'emp',
                       p_auto_rest_auth => FALSE);
    
    commit;

END;

3) Check the catalog.  Everything enabled will be in the catalog wether they be auto-enabled tables or lovingly crafted ones.  For this case, we can see /emp/ is there

4) Do work.  Here I'm inserting a new record.  The PK is referenced in the URI and in the JSON doc.  Notice the http method here is a PUT and that the content type is set to application/json.

5) The results.  It worked

6) Now for doing an update.

7) The results is that the response back from ORDS is the correct values.

8) Now go show the people hand coding REST what took 1 plsql block to enable !

REST Data Services and SQL Developer

The database tools team released 3 new GA releases and an update to our SQLCL.


Official Releases are here:

   SQL Developer, Modeler, and Data Miner:
       https://blogs.oracle.com/otn/entry/news_oracle_updates_development_tools
       https://blogs.oracle.com/datamining/entry/oracle_data_miner_4_1

  REST Data Services now with SODA
       https://blogs.oracle.com/otn/entry/news_oracle_rest_enables_oracle
       https://blogs.oracle.com/databaseinsider/entry/oracle_database_12c_as_a

Some of the feedback we received was that ORDS was difficult to setup and running.  This prompted an overhaul of the install process.  There is now a few ways to get going.  As with earlier releases, there is a command line.  There is now a 'simple' , 'advanced' , and an option to parameterize ( silent ) install.  The simple is the default and will ask minimal information.  The catch is that it will make assumptions.  Some of those assumptions like a USERS tablespace may not work for your database.  In this case try the advanced, it will prompt for _everything_ and then some for a fully customized install.

Taking the simple to install one step more, REST Data Services is now embedded into SQL Developer to be able to get up and running faster than ever.  Under the Tools menu there is now options to Manage,Run,Install, and Uninstall ( although why would anyone? ).

The first page of the installation will ask for 2 things.  First if there is a specific ORDS to use or to use the embedded one.  The version number is printed for which ever is chosen to ensure the proper decision.  The second is simply where to store the configuration.  This configuration is just the normal files and could be portable to take to any other ORDS to set it up.

The next is the database credentials and connect strings.  There is something new here in that the user is now ORDS_PUBLIC_USER.  This is a change that has come in the decoupling from Application Express.  Now ORDS is completely standalone and Apex is no longer required.

If connecting to the ORDS_PUBLIC_USER fails ( as in user doesn't exist ),  the wizard will prompt for the credentials needed to install ORDS.

When using APEX or any OWA toolkit based applications, the db user for those to be executed as needs to be entered.

In ORDS 2.x, the repository where the REST definitions were stored was inside Application Express' repository.  ORDS 3.0 has it's on dedicated ORDS_METADATA database user which holds this information.  This means that there could be definitions in both places.  For Apex 5.0, the screens in the sql workshop will continue to operate against the tables in the Apex repository.  That means to continue to operate this wizard will have to be filled out for the 2.0 users to access this data.

When operating like this.  ORDS will use the metadata from both the 3.0 repository and the Apex repository.  This allows the flexibility to the developer where they would continue to develop.  The new features such as Auto Enablement will only be in the new ORDS 3.0 repository.

What this means to you.
  - Continue to use the APEX screens, everything will work just fine
  - Using the REST definitions in SQL Developer, will go into the new ORDS schema
  - If there is a conflict, ORDS repository will be chosen over the Apex repository.

This screen will give the option to start ORDS up as soon as the wizard finishes installing and setting up.  Also is the location for the static file, think /i/ for apex.

The ORDS Administrator and ORDS RESTful User are used to connect to the running ORDS from SQL Developer for remote administrations.

A quick final review of the options and it's up and running.

Publish data over REST with Node.js

Of course the best way to expose database data over REST is with Oracle REST Data Services.  If you haven't read over the Statement of Direction, it's worth the couple minutes it takes.  The auto table enablement and filtering is quite nice.

For anyone interested in node.js and oracle, this is a very quick example of publishing the emp table over REST for use by anyone that would prefer REST over sql*net.  For example from javascript,php,perl,...



The current best driver for node.js to access oracle is node-oracle which is found here: https://github.com/joeferner/node-oracle


Here's a short set of steps to get going.

1. Install node, http://nodejs.org/download/

2. Setup the environment's oracle variables

export PATH=$PATH:.  
export ORACLE_HOME=/path/to/my/oracle/product/11.2.0/dbhome_1
export OCI_LIB_DIR=$ORACLE_HOME/lib
export OCI_INCLUDE_DIR=$ORACLE_HOME/rdbms/public
export OCI_VERSION=11 #Integer. Optional, defaults to '11'
export NLS_LANG=.UTF8 # Optional, but required to support international characters
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3. run npm install oracle

That's all there is to getting then env setup for oracle.  The other node package I use in this example is express.

4. To install that just type:  npm install express To learn more about express, it's home is here http://expressjs.com/

var express = require('express');
var app = express();
// This make a uri end point for the REST call
// http://myhost/emp will return the data
app.get('/emp', function(req, res){
  empList(req,res)
  }
);

var oracle = require('oracle');
var conn;

var connectData = {
    hostname: "127.0.0.1",
    port: 1521,
    database: "XE", 
    user: "klrice",
    password: "fortim"
}

// connect
//var conn="";
oracle.connect(connectData, function(err, connection) {
    if (err) { console.log("Error connecting to db:", err); return; }
    console.log('connected');
    conn =connection;
});

// function registered in the /emp declaration
function empList(req,res){
     var empList = "select * from emp ";
    conn.execute(empList, [], function(err, results) {
            if (err) { console.log("Error executing query:", err); return; }

            // use the JSON function to format the results and print
            res.write(JSON.stringify(results));
            res.end();
    });
}

// listen on port 8000
app.listen(8000);

The result is that the node script is listening on port 8000 for /emp and will return this JSON.

APEX Listener supported App Servers

     With the latest news on Glassfish, I thought it may be a good time to review the options for the APEX Listener to deploy.  The huge caveat is this is as of today, 11/6/2013 , the future can change anything however there’s nothing planned.

The Licenses

I'm just putting the important parts here for reference.  They are linked to the entire license.

OTN License

 The APEX Listener is only available via OTN.  When that is downloaded, this is the license  everyone has to agree to:

“We grant you a nonexclusive, nontransferable limited license to use the programs solely for your internal business operations subject to the terms of this agreement and the license grants and restrictions set forth with your licensing of such Oracle database programs. We also grant you a nonexclusive, nontransferable limited license to use the programs for purposes of developing your applications. This agreement does not grant any rights or licenses to Oracle database programs.”

Database License

• A restricted-use license for Oracle Application Server Containers for J2EE (OC4J) is included with all editions (except for Oracle Database Express Edition). This embedded version is provided solely to support Oracle Enterprise Manager (Database and Grid Control), Advanced Queuing Servlet, Ultra Search, Application Express, and Warehouse Builder, and may not be used or deployed for other purposes.

• A restricted-use license for Oracle Application Express Listener is included with all editions (except for Oracle Database Express Edition) solely to support connectivity to Oracle Application Express, which may be installed in the Oracle Database. Running Oracle Application Express Listener on a different server does not require that that server be licensed to use the Oracle Database.

Supported Application Servers

The APEX Listener’s documentation lists the supported webservers for deployment.

Supported Java EE Application Servers

Oracle Application Express Listener supports the following Java EE application servers: 

Application Server	Supported Release
Oracle WebLogic Server	11g Release 1 (10.3.3) or later
Oracle GlassFish Server	Release 3 or later

Clarifications on the Application Servers

I think some of the confusion is the wording of “Oracle Glassfish Server.” This was not intended to limit support to the commercial version of Glassfish.  We support the APEX Listener no matter which Glassfish you are using.  The support for the APEX Listener is tied directly to the database support agreement.  To state the obvious, the APEX Listener team is not supporting any application server but our code base running inside that application server.  If an issue is in the webserver itself arises, the customer will have to track that issue down with the webserver.

This table of supported Application Server will soon be updated to the following.

Application Server	Supported Release
Oracle WebLogic Server	11g Release 1 (10.3.3) or later
GlassFish Server	Release 3 or later
Apache Tomcat	Release 6 or later

How to use RESTful to avoid DB Links with ā'pěks

So the question came up of avoiding a db link by using the APEX Listener's RESTful services to get at the same data.  This is all in the context of an Apex application so apex_collections is the obvious place to stuff transient data that could be used over a session.

Step 1:  Make the RESTful Service.
The only catch is to turn pagination off ( make it zero ).  I didn't need it for now so this code that follows doesn't account for it.

Step 2. Install PL/JSON 

  In the future this can be done with built in support for JSON in the database.


Step 3.  Glue code.

declare
-- APEX
  g_collection_name varchar2(32767) := 'json_data';
  
-- JSON Portion
  l_url  varchar2(32767) := 'http://myhost.mydomain.com/apex/dbtools/features/latestRequests';
  l_jsonObj json;
  l_jsonObj2 json;
  l_jsonKeys json_list;
  l_key varchar2(32767);
  l_value varchar2(32767);
  
  l_tempdata json_value;
  l_temparray json_list;
  i number;  
  ii number;  
 
 values_array dbms_sql.varchar2_table;

  procedure varray2collection(p_values dbms_sql.varchar2_table) as
    l_sql varchar2(32767) := 'begin APEX_COLLECTION.ADD_MEMBER (p_collection_name =>:NAME,';  
    i number;
    c number;
    ret number;
  begin
  -- build up the begin..end sql block
    for i in 1..p_values.count loop
      l_sql := l_sql || 'p_c00'|| i ||'=>:c'||i||',';
    end loop;
    
    -- chop off the last comma
    l_sql := substr(l_sql,1,length(l_sql)-1);
    -- add the end
    l_sql := l_sql || '); end;';
    dbms_output.put_line('SQL='||l_sql);
    
    c := DBMS_SQL.OPEN_CURSOR;
    dbms_output.put_line('c='||c);
    dbms_sql.parse(c,l_sql,dbms_sql.native);
    -- bind in the collection name
    dbms_sql.bind_variable(c,':NAME'||i,g_collection_name);

    -- bind in the values
    for i in 1..p_values.count loop
        dbms_sql.bind_variable(c,':c'||i,p_values(i));
    end loop;
    ret := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
    exception when others then
       dbms_sql.close_cursor(c);
  end;
  
  -- simple function to remove the leading and trailing "
  function trimQuotes(s varchar2) return varchar2 is
    l_value varchar2(32767);
  begin
    l_value := substr(s,2);
    l_value := substr(l_value,1,length(l_value)-1);
    return l_value;
  end;
  
  -- function to grab the URL and return a JSON object
  function getURL(p_url varchar2) return JSON is
       -- HTTP Portion
      l_http_request   UTL_HTTP.req;
      l_http_response  UTL_HTTP.resp;
      l_clob           CLOB;
      l_text           VARCHAR2(32767);
  begin
      -- Initialize the CLOB.
      DBMS_LOB.createtemporary(l_clob, FALSE);
      -- Make a HTTP request and get the response.
      l_http_request  := UTL_HTTP.begin_request(l_url);
      l_http_response := UTL_HTTP.get_response(l_http_request);
      -- Copy the response into the CLOB.
       BEGIN
          LOOP
             UTL_HTTP.read_text(l_http_response, l_text, 32766);
             DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
      END LOOP;
      EXCEPTION  WHEN UTL_HTTP.end_of_body THEN
         null;
      END;
       UTL_HTTP.END_RESPONSE(l_http_response);

    -- Relase the resources associated with the temporary LOB.
    dbms_output.put_line('Length:'|| DBMS_LOB.getlength(l_clob));
  return json(l_clob);
  end geturl;
  
BEGIN
-- apex collection
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(g_collection_name);

-- JSON
    l_jsonObj := getURL(l_url);
    -- grab the items 
    l_temparray:= json_list(l_jsonObj.get('items'));
--    temparray := json_list(tempdata);
    dbms_output.put_line( 'Count:'|| l_temparray.count);
    for i in 1..l_temparray.count loop
         -- get the Nth item
          l_tempdata :=    l_temparray.get(i);
          dbms_output.put_line( 'Record['|| i||']:'|| l_tempdata.to_char);
          -- bring the Nth item into it's own json object
         l_jsonObj2 := json(l_tempdata);
         -- get all the keys to walk
         l_jsonKeys := l_jsonobj2.get_keys();
                   for ii  in 1..l_jsonKeys.count  loop
                      l_key := trimQuotes(l_jsonKeys.get(ii).to_char);
                      l_value :=  trimQuotes(l_jsonObj2.get(l_key).to_char);
                      dbms_output.put_line(l_key || '=' ||l_value);
                      -- build up the array to pass to the apex collection
                      values_array(ii) := l_value;
                   end loop;
                   dbms_output.put_line('LENGTH:'||values_array.count);
                   -- send it to the collection
                   varray2collection(values_array);
        end loop;
end;
/

Step 4. Make a page in Application Express

  The glue code is in the process named Get JSON.
  The interactive report is simply: select * from apex_collections.

Step 5. It's alive

Oh the ā'pěks is from the oraclenerd store.  If you don't have a shirt already, you should.

RESTful Cursor support for JSON

Just a real quick blog before I forget.  In the latest APEX Listener 2.0.4 patch, there's support for nested cursors.  There is two gotchas.  First make sure to to disable ( make 0 ) the pagination of the REST definition.  The second is this only works at the top level, so not nested nests of nests.

This is a very quick example of tables and nested in each table the columns and indexes that tables has.

select t.table_name,
  cursor(select column_name
             from user_tab_cols tc 
             where tc.table_name = t.table_name) cols ,

  cursor(select index_name
             from user_indexes i 
             where i.table_name = t.table_name) indexes
 from user_tables t

The resulting JSON looks like this.

Chunked File loading with APEX Listener + HTML5

  I just found the HTML5 File API the other day so I had to see what I could do with the APEX Listener's RESTful services. There's a bunch of blogs on what can be done such as on HTML5rocks  .

  The end result is that the new File api let's javascript get details of the file and slice it up into parts. Then I made a pretty simple REST end point to receive the chunks and put them back together again.

The actual sending part of the javascript is here

function sendChunk(chunkNumber){
        var reader = new FileReader();
        var start = chunkSize * (chunkNumber-1);
        var end = start + chunkSize -1;
      // create the slice of the file
        var fileContent = selectedFile.slice(start, end);   
      // grab the length
        var length = fileContent.size;
      
      // read the slice of the file
        reader.readAsArrayBuffer(fileContent);      
      
      $.ajax({
        url: uri,
        type: "POST",
        data: fileContent,
        processData: false,
        beforeSend: function(xhr) {
          // pass in the chunk size,offset,name 
          // as headers
                        xhr.setRequestHeader('x-chunknumber', chunkNumber);
                        xhr.setRequestHeader('x-filename', selectedFile.name);
                        xhr.setRequestHeader('x-offset', start );
                        xhr.setRequestHeader('x-chunksize', length );
                        xhr.setRequestHeader('x-content-type', selectedFile.type );

                    },
        success: function (data, status) {
          console.log(data);
          console.log(status);
          bytesUploaded += length;
          // set the percent complete 
          var percentComplete = ((bytesUploaded / selectedFile.size) * 100).toFixed(2);          
          $("#fileUploadProgress").text(percentComplete + " %");
          
          // make a link to the REST that can deliver the file
          $("#downloadLink").html("New File");
          
          // if there's more chunks send them over
          if ( chunkNumber < chunks ) {
             sendChunk(chunkNumber+1);
          }
        },
        error: function(xhr, desc, err) {
          console.log(desc);
          console.log(err);
        }
      });
      
    }    
 

The next step is to make the HTTP Headers into bind variable so the plsql block will be able to use them.

declare
  p_b         blob;
  p_body      blob;
  p_offset    number;
  p_filename  varchar2(4000);
  p_raw       long raw;
  p_chunksize varchar2(200);
  p_status    varchar2(200);
begin
  -- pull the binds into locals
  p_offset    := :OFFSET + 1;
  p_body      := :body;
  p_filename  := :filename;
  p_chunksize := :chunksize;

  -- NOT FOR PRODUCTION OR REAL APPS
  -- If there is a file already with this name nuke it since this is chunk number one.
  if ( :chunkNumber = 1 ) then
    p_status := 'DELETING';
     delete from chunked_upload
      where filename = p_filename;
  end if;

  -- grab the blob storing the first chunks
  select blob_data
         into p_b
    from chunked_upload
   where filename = p_filename
    for update of blob_data;

  p_status :=' WRITING';

  -- append it
  dbms_lob.append(p_b, p_body);

commit;
exception 
  -- if no blob found above do the first insert
  when no_data_found then
     p_status :=' INSERTING'; 
     insert into CHUNKED_UPLOAD(filename,blob_data,offset,content_type)
      values ( p_filename,p_body,p_offset,:contenttype);  
commit;
  when others then
      -- when something blows out print the error message to the client
      htp.p(p_status);
      htp.p(SQLERRM);
end;

A very simple html page for testing it all out.

Here's a quick video of how it all works.

Here's the complete Javascript/html for this sample.


JS Bin

SQL Developer meets REST

  SQL Developer is bringing the ability to define all the APEX Listener's REST abilities.  There's many advantages to adding this in.  The first and probably most important to me who typos and doesn't see it, is the ability to test the query or plsql before publishing it and getting a 500 error and not knowing why.

  This screenshot shows many other advantages of adding in.  The REST definition screens will have a fully functioning SQL Worksheet to test and tune the source of the REST call.  That means code insight, explain ,autotrace, sql tuning advisor, grids of data, and anything else.  

  The differences over a normal worksheet are obviously the name of the tab is the handler method and the name of the REST end point.  Then there's the additional tabs for defining in and out parameters.

  Then the other thing I frequently don't get correct is testing the URL out.  I forget if I added a slash or what the module was or typo or something to get the URL wrong.  The new details tab shows what the URL is going to be.

A quick video of making a new REST call in SQL Developer:

Using REST to search in JQuery

Building on yesterday's post,  here's adding a search to the same JQuery list: SQL Developer Exchange Requests


SQL Developer Exchange Requests

The REST Template is only different by adding in a bind variable and adjusting the select some:

The other thing I added into the JQuery list is the weight of the searched items.  Which can be seen in the blue bar here:

The javascript changes are also fairly small. I made a function named search which takes in what the text is to search on. Then few lines to bind the function and the listviewbeforefilter event together.

// attach to the before filter in the list
     $( "#results" ).on( "listviewbeforefilter", function ( e, data ) {
          // grab the value of the search field
          var $ul = $( this ),
  $input = $( data.input ),
  value = $input.val();
          // if blanked go back to the recent list
          if ( value == '' ) {
              loadRecent();
          } else {
            // debugging
              console.log("Loading Search:"+value);
            search(value);
          }
        });


 function search(search){
   console.log("searching for '" + search + "'");
    // out with the old
   $("#results").children().remove();

    // in with the new
    $.ajax({
      url: "http://apex.oracle.com/pls/apex/dbtools/features/search?text=" + search ,
      type: "GET",
      success: function (data) {
        console.log(data);
        if ( data.items.length == 0 ){ noResults();}
        ko.applyBindings(data, document.getElementById("searchPage"));
        $('#results').listview('refresh');
      }
    });
  }

Combining RESTful, JQuery ,and KnockoutJS

Here's a very easy example of how to join RESTful services with JQuery and some data binding from KnockoutJS.  This is a page that shows the last 100 requested features in the SQL Developer Exchange.

SQL Developer Exchange Requests

The REST service is very simple as it just strips out a <p> that the editor injects and a little format on the date.

I did this all on jsbin.com so it's nice and easy to share: http://jsbin.com/emadiv/5  The knockoutJS template is the key.  When JSON is returned from the APEX Listener it has a parent node named "items"  The template is bound to the list with this line:

           data-bind="template: { name: 'overview', foreach: items}"

Then for each child under items it will loop and apply the template.  The template is just html inside a script table with the type set to text/html

<!-- Knockout syntax for the template which is the list items -->  
<script id="overview" type="text/html">
<li data-role="list-divider"><!--/ko--></li>
<!-- bind in the link to drill down to the exchange-->
<li> <a data-bind="attr: { href: 'https://apex.oracle.com/pls/apex/f?p=43135:7:0::NO::P7_ID:'+ id }"  alt="">
        <h2>
<!--ko text: title --><!--/ko--></h2>
<span data-bind="text: description"></span>
    </li>
</script>

Then the Javascript which loads the data and applies the template

var requests = "http://apex.oracle.com/pls/apex/dbtools/features/recent";

  // When the page init's go load it up.
$(document).on("pageinit", "#recentRequests", function() {
    $.ajax({
      url: requests ,
      type: "GET",
      success: function (data) {
        // go apply the template to what was retreived
        ko.applyBindings(data, document.getElementById("recentRequests"));
        $('#results').listview('refresh');
      }
    });
});

This barely touches the surface of what can be done with just getting the data out of the database via the APEX Listener.  For example,  the query in this REST is returning 100 rows but pagination is set to 25.  I'll get that hooked up and expand this demo later.  As I tinker with more widgets such as http://www.jqwidgets.com/ I'll write those up also.

Nicer URLs for APEX, yet another option, Part 2

   Here's taking the nicer URLs one more step and I think I have 2 more that will follow next week. I have been doing with some testing with the Online Learning Library .


In case the screenshot is too small, the URL is

https://apex.oracle.com/pls/apex/oll/test/OLLPROD/content/P24_CONTENT_ID/4191/P24_PREV_PAGE/1?sessionid=0

The URI Template is easy to follow and fairly portable.  I'm working on a generic way to do this in any app but here's the one we're using for this specific page.



        test/{app}/{page}/{item1}/{item1v}/{item2}/{item2v}?sessionid={session}

Then the Apex Listener translates those things in  { }  into binds which can be used in this plsql block to run the F procedure and get a page out of Apex.

declare
/* buffer to build up the value */
 p varchar2(32767);

begin

  /* add in app,page, and session */
  p:=:app||':'||:page||':'||:session;

  /* move along nothing to see here */
  p:= p||'::::';

  /* add in item names */
  p:= p|| :item1;
  if ( :item2 is not null ) Then
    p:= p|| ','||:item2;
  end if;
 p:=p||':';

 /* addin item values */
  p:= p|| :item1v;

  if ( :item2 is not null ) Then
    p:= p|| ','||:item2v;
  end if;
  
  f(p=>p);

end;

   I have to mention things could be rendered badly if anything is reading owa_util package to get assumptions from the URL.  One thing that I know didn't work for me was the use of apex_util.get_blob_file but that's easy to fix from yesterday's post.

Using RESTful to deliver Images for APEX

   Who doesn't want things to be faster.  Here's a very easy way to get images in the database out faster.  The normal way to get images embedded into and Application Express app is to use apex_util.get_blob.  It's tried and test and optimized for doing just that so you may assume it doesn't get faster to get images served out.  Here's a quick screenshot of loading a test page in chrome of 2 identical images and by identical, I mean the same BLOB from the same table with the same ID.

The shorter timeline is the RESTful delivery.  Mileage will of course vary by load and latency but with what I've tested REST delivery is always faster.

For 5 reloads this was the timings I got:

apex_util REST % Faster
  
259             137     89.05%
362             311     16.40%
250             129     93.80%
245             130     88.46%
269             126     113.49%
  

Now introducing RESTful type of Media Resource which makes this very easy to do.  Colm initially blogged about it a while back.  In a nutshell, it's a query that wants 2 columns the first being the content type , mime, and the second being a lob or anything to be delivered.  Colm showed how to deliver XML in his post.  This is how to serve up images.


In the URI Template, you'll notice {id} which is the ID of the image to be served and used as a bind to query.  The select is quite simple to follow.

The table for this example is quite simple but obviously could be anything as long as there's a mime and lob to deliver.

CREATE TABLE  myimages
   ( IMAGE_ID NUMBER NOT NULL ENABLE, 
 IMAGE_BLOB BLOB NOT NULL ENABLE, 
 IMAGE_MIMETYPE VARCHAR2(255)
   )
/