I wrote recently about using REST to upload a file to your table with ORDS.
I want to extend that and share more code. I want you to see how to not only load the pics, but then how to get a collection of items, with links to those items, and in this case, also links to the media (pics) which we’ve uploaded.
AND IT’S SO LITTLE CODE.
What I’ll share:
- table ddl
- a few rows as INSERTs
- the REST module definition
- POSTMAN demo of the pic upload via POST
The Table
It’s a PEOPLE table, I built it to store folks who would participate in a conference.
And the code – including the INSERTs:
CREATE TABLE "PEOPLE" ( "FIRST_NAME" VARCHAR2(100) NOT NULL ENABLE, "MIDDLE_INIT" VARCHAR2(100), "LAST_NAME" VARCHAR2(100) NOT NULL ENABLE, "TITLE" VARCHAR2(100), "EMAIL" VARCHAR2(255) NOT NULL ENABLE, "ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 10000 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, "STREET1" VARCHAR2(100) NOT NULL ENABLE, "STREET2" VARCHAR2(100), "STREET3" VARCHAR2(100), "CITY" VARCHAR2(100) NOT NULL ENABLE, "STATE" VARCHAR2(2) NOT NULL ENABLE, "ZIP" VARCHAR2(12) NOT NULL ENABLE, "COUNTRY" VARCHAR2(100), "ACE" NUMBER(*,0), "BIO_PIC" BLOB ) NO INMEMORY ; ALTER TABLE "PEOPLE" ADD CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("ID") USING INDEX ENABLE; COMMENT ON COLUMN "PEOPLE"."FIRST_NAME" IS 'given name'; COMMENT ON COLUMN "PEOPLE"."MIDDLE_INIT" IS 'first 1 or 2 letters of middle name'; COMMENT ON COLUMN "PEOPLE"."LAST_NAME" IS 'familyi name'; COMMENT ON COLUMN "PEOPLE"."TITLE" IS 'Mr, Mrs, Dr, Evil Dr, ...'; COMMENT ON COLUMN "PEOPLE"."EMAIL" IS 'primary method of contact, can be biz or personal'; COMMENT ON COLUMN "PEOPLE"."ID" IS 'system generated'; COMMENT ON COLUMN "PEOPLE"."STREET1" IS 'mailing address 1'; COMMENT ON COLUMN "PEOPLE"."STREET2" IS 'mailing address line 2'; COMMENT ON COLUMN "PEOPLE"."STREET3" IS 'mailing address line 3'; COMMENT ON COLUMN "PEOPLE"."CITY" IS 'city address'; COMMENT ON COLUMN "PEOPLE"."STATE" IS '2 char abb for state or province'; COMMENT ON COLUMN "PEOPLE"."ZIP" IS 'zip code allows for last 4 postal code digits'; COMMENT ON COLUMN "PEOPLE"."COUNTRY" IS 'if null, we assume USA!'; COMMENT ON COLUMN "PEOPLE"."ACE" IS '0=No, 1=ACED, 2=ACE, 3=ACE Associate'; COMMENT ON COLUMN "PEOPLE"."BIO_PIC" IS 'image for presenters if necessary'; REM INSERTING INTO PEOPLE SET DEFINE OFF; INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Jeff',NULL,'Smith',NULL,'[email protected]','909 Nort St',NULL,NULL,'Cary','NC','27513',NULL,4); INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Scott',NULL,'Spendolini',NULL,'[email protected]','123456 Password Dr',NULL,NULL,'Reston','VA','90210',NULL,1); INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Linda',NULL,'Hoover',NULL,'[email protected]','404 Redwood Dr',NULL,NULL,'Wilmington','NC','12345',NULL,NULL); INSERT INTO PEOPLE (FIRST_NAME,MIDDLE_INIT,LAST_NAME,TITLE,EMAIL,STREET1,STREET2,STREET3,CITY,STATE,ZIP,COUNTRY,ACE) VALUES ('Jeff',NULL,'Test',NULL,'[email protected]','a',NULL,NULL,'town','DC','91100',NULL,NULL);
The REST Module
Here are a few URI patterns and handlers.
So a GET on peeps/ will retrieve a list of people, with links to their records and to their pictures.
a GET on peeps/{id} will get the person record – I need this to make the link I build for the GET on peeps/ output
a GET on media/:id will get the image for a particular person – I also need this to make the link I build for the GET on peeps/ output
a POST on media/:id will upload the image
Here is that code.
-- Generated by Oracle SQL Developer REST Data Services 17.3.0.271.1803 -- Exported REST Definitions from ORDS Schema Version 17.3.0.244.09.12 -- Schema: HR Date: Tue Nov 07 15:20:07 EST 2017 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'abstract', p_base_path => '/abstract/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'abstract', p_pattern => 'media/:id', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'abstract', p_pattern => 'media/:id', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 0, p_mimes_allowed => '', p_comments => NULL, p_source => 'DECLARE PEEPS_PIC BLOB := :body; how_big integer; BEGIN ADD_PEOPLE_PIC(B_PIC => PEEPS_PIC, P_ID => :id, BLOB_SIZE => how_big); :bytes := how_big; END;' ); ORDS.DEFINE_PARAMETER( p_module_name => 'abstract', p_pattern => 'media/:id', p_method => 'POST', p_name => 'bytes', p_bind_variable_name => 'bytes', p_source_type => 'RESPONSE', p_param_type => 'INT', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'abstract', p_pattern => 'media/:id', p_method => 'GET', p_source_type => 'resource/lob', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'SELECT ''image/png'', bio_pic FROM PEOPLE where id = :id' ); ORDS.DEFINE_TEMPLATE( p_module_name => 'abstract', p_pattern => 'peeps/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'abstract', p_pattern => 'peeps/', p_method => 'GET', p_source_type => 'json/collection', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'SELECT id AS "$self", first_name, last_name, EMAIL, ''../media/'' || id as "$bio_pic" FROM PEOPLE order by ID' ); ORDS.DEFINE_TEMPLATE( p_module_name => 'abstract', p_pattern => 'peeps/{id}', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'abstract', p_pattern => 'peeps/{id}', p_method => 'GET', p_source_type => 'json/query;type=single', p_items_per_page => 0, p_mimes_allowed => '', p_comments => NULL, p_source => 'SELECT first_name, middle_init, last_name, title, email, id, street1, street2, street3, city, state, zip, country, CASE ace WHEN 0 THEN ''No'' WHEN 1 THEN ''ACE Director'' WHEN 2 THEN ''ACE'' WHEN 3 THEN ''ACE Associate'' WHEN 4 THEN ''ACE Alumni'' ELSE ''Unknown'' END ACE FROM PEOPLE WHERE id =:id' ); COMMIT; END;
The ‘Demo’
So assuming you have put this in a REST enabled schema and have ORDS up and running I can start doing stuff.
I’ve highlighted the links in the response output. It might not be great REST to provide the link for the picture alongside the item link, maybe I should have included that on the item GET itself, but I wanted it there, so I put it there. Do what works for you, but don’t be surprised if someone says, ‘that is not REST.’
Here’s how I generated those links, the code behind the GET handler is VERY simple. Click the pic to go to ORACLE-BASE’s example of how to generate LINKS with ORDS.
Let’s go look at those links.
Ok, here’s how I’m adding the pictures, and a quick demo.
My post HANDLER code is very simple, if you scroll up and look at it, you’ll see this line:
ADD_PEOPLE_PIC(B_PIC => PEEPS_PIC, P_ID => :id, BLOB_SIZE => how_big);
That’s a procedure call. So when I call my POST, ORDS end’s up running a stored procedure. Here’s that PL/SQL – but it accepts an integer on the request header, a file via the POST :body, and it returns the size of the uploaded file in the POST body response.
CREATE OR REPLACE PROCEDURE ADD_people_pic ( P_ID IN INTEGER , B_PIC IN BLOB , BLOB_SIZE OUT INTEGER ) AS BEGIN UPDATE PEOPLE SET BIO_PIC = B_PIC WHERE ID = P_ID; COMMIT; SELECT DBMS_LOB.getlength(bio_pic) INTO BLOB_SIZE FROM people WHERE id = P_ID; END add_people_pic;
Very simple code, definitely not production worthy, but it will load a file to a table, assuming you get the PK right. So let’s see that ‘live.’
I have a table, I have a person. Now I need to find their PK, and make sure they don’t already have a picture. Then we’ll make the REST call, and go re-check the record to make sure everything’s OK.
But Jeff, I really want this to be REST-y.
Ok, let’s look at the GET we have on the item, and have it include a link to the picture.
I want the person page to pull up their data, and I want a link to their picture.
And in my browser…
10 Comments
Hi Jeff,
What if I need to generate the BLOB on the fly like using AOP report engine and I have an end point to return and invoice or sticker,…etc. based on an input parameter. I need to write a PL/SQL code to generate the report then return the BLOB. Using the Media Resource type allows only SQL statement.
I tried to write a function that returns the BLOB and use it in the SQL statement, but I am using DMS in the generation of the report, so I am not allowed to sue DML in a query.
Need a hand here please.
Regards,
Omar
Would it be acceptable to make your call to generate your BLOB, and have the response be a 30X redirect with a link to the media resource GET Handler to actually retrieve your file?
Hi Jeff,
Can you explain with an example please.
Thank you so much for this – I am doing something different, but this save my sanity in getting POST data into plsql so I can handle it!
Hi Jeff,
This post about blob was a life saver for me. Thanks Jeff. I do have a question though, what if I have multiple file to upload, I mean how can I de-reference :body variable for another blob column?
You don’t. You’d need a REST POST call per file.
If you’re inserting BLOBs into multiple columns per row, you’d have to have a handler per column, or code it such that you have a dynamic service that says put this file into this column.
Thank you once again. creating handler per column in my opinion wouldn’t be that elegant, dynamic service like using a variable of type collection would be a good idea.
HTTP forms would let you POST up multiple files in a single call, but in REST you just do one at a time. So you need to code your handlers for such. Or you could try sending the entire set of files in a stream and chunk it back out…Kris talks about that here.
Hi Jeff,
I have problem with creating “PEOPLE” table from Your example. Both SQLplus and SQL Developer throws an error “ORA-00907: missing right parenthesis”. I managed to find out that the source of the problem is “NOSCALE” option for identity column. If I remove this option, the table is created without any problem.
I tested it on Oracle Database 12c Standard Edition Release 12.2.0.1.0 – 64bit Production.
Any clue how to resolve this problem? On another 12.2 DB it runs smoothly.
Best regards
Michal Wyszynski
It runs on my 12.2 db fine as well. You can just delete the noscale keyword and not worry about it – it’s not key to the demo/example.