{"id":58,"date":"2009-10-27T10:34:00","date_gmt":"2009-10-27T09:34:00","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/2009\/10\/hello-world-with-oracle-xe\/"},"modified":"2009-10-27T10:34:00","modified_gmt":"2009-10-27T09:34:00","slug":"hello-world-with-oracle-xe","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2009\/10\/hello-world-with-oracle-xe\/","title":{"rendered":"Hello World with Oracle XE"},"content":{"rendered":"

I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX.
\nIt’s a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database \ud83d\ude42<\/p>\n

Oracle XE comes with a pre-configured XDB HTTP listener for APEX.
\nI don’t need the full APEX engine for my simple request server, just a PL\/SQL package using PL\/SQL Web Toolkit will do just fine and there is no need to use PHP or some other scripting engine outside the database.<\/p>\n

Here are the steps I used to configure Oracle XE XDB HTTP listener with additional path (DAD) to my custom procedure.<\/p>\n

Make HTTP listener to listen on all network interfaces, not only localhost:<\/p>\n

\nEXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);\n<\/pre>\n
Oracle XE has pre-defined DAD for APEX – \/apex\/
\nThe following code will create a new DAD \/mobileapp\/, associate it with schema MOBILEAPP and authorize it to run without asking the user for a password.<\/p>\n
\nBEGIN\n  DBMS_EPG.create_dad (\n    dad_name => 'mobileapp',\n    path     => '\/mobileapp\/*');\n\n  DBMS_EPG.set_dad_attribute (\n    dad_name   => 'mobileapp',\n    attr_name  => 'default-page',\n    attr_value => 'home');\n\n  DBMS_EPG.set_dad_attribute (\n    dad_name   => 'mobileapp',\n    attr_name  => 'database-username',\n    attr_value => 'MOBILEAPP');\n\n  DBMS_EPG.set_dad_attribute (\n    dad_name   => 'mobileapp',\n    attr_name  => 'nls-language',\n    attr_value => 'american_america.al32utf8');\n\n  DBMS_EPG.authorize_dad (\n    dad_name => 'mobileapp',\n    user     => 'MOBILEAPP');\nEND;\n\/\n<\/pre>\n
I used the following DAD attributes:<\/p>\n\n\n\n\n\n
default-page<\/th>\nspecifies the default procedure name, that will be used when the procedure name is not specified in a HTTP URL<\/td>\n<\/tr>\n
database-username<\/th>\nassociated database schema name<\/td>\n<\/tr>\n
nls-language<\/th>\nNLS client value, for a web application the important part is the charset. al32utf8 means the result page is in UTF-8.<\/td>\n<\/tr>\n
DBMS_EPG.authorize_dad<\/th>\nThis will disable the HTTP basic authentication<\/td>\n<\/tr>\n<\/table>\n
There are other interesting DAD attributes and the current XDB HTTP configuration and APEX DAD configuration can be views using this SQL:<\/p>\n
\nSQL> SELECT   DBMS_XDB.CFG_GET().getClobVal() FROM DUAL;\n<\/pre>\n
\n...\n              \n                APEX<\/servlet-name>\n                PL\/SQL<\/servlet-language>\n                APEX<\/display-name>\n                \n                  ANONYMOUS<\/database-username>\n                  apex<\/default-page>\n                  wwv_flow_file_objects$<\/document-table-name>\n                  docs<\/document-path>\n                  wwv_flow_file_mgr.process_download<\/document-procedure>\n                  american_america.al32utf8<\/nls-language>\n                  wwv_flow_epg_include_modules.authorize<\/request-validation-function>\n                <\/plsql>\n              <\/servlet>\n...\n<\/pre>\n
Now all MOBILEAPP PL\/SQL procedures and packages can be accessed over HTTP and the HTTP URL has the following form:
\nhttp:\/\/oraclexeserver:8080\/mobileapp\/procedurename<\/b>
\nor
\nhttp:\/\/oraclexeserver:8080\/mobileapp\/packagename.procedurename<\/b><\/p>\n
So, a simple hello world procedure using PL\/SQL Web Toolkit could look like this:<\/p>\n
\nCREATE OR REPLACE PROCEDURE mobileapp.home IS\nBEGIN\n  HTP.htmlopen;\n  HTP.headopen;\n  HTP.title('Page title');\n  HTP.headclose;\n  HTP.bodyopen;\n  HTP.print('Hello world');\n  HTP.bodyclose;\n  HTP.htmlclose;\nEND;\n\/\n<\/pre>\n
HTTP URL for this procedure will be http:\/\/oraclexeserver:8080\/mobileapp\/home<\/b> or just http:\/\/oraclexeserver:8080\/mobileapp\/<\/b> (note the default-page<\/b> DAD attribute).
\nThe proceudres like HTP.htmlopen<\/b> will just generate HTML tags.<\/p>\n
Passing HTTP GET parameters is very easy and they will be set as a procedure parameters with the same name. Parameter data type is VARCHAR2 or if HTTP request has more than one parameter with the same name, then TABLE OF VARCHAR2 data type is used.<\/p>\n
\nCREATE OR REPLACE PROCEDURE mobileapp.paramtest(p1 IN VARCHAR2 DEFAULT NULL,\n  p2 IN VARCHAR2 DEFAULT NULL) IS\nBEGIN\n  IF p1 IS NULL THEN\n    HTP.p('Hello world');\n  ELSE\n    HTP.p('Hello p1='||p1||' p2='||p2);\n  END IF;\nEND;\n\/\n<\/pre>\n
This procedure can be called with HTTP URL:
\nhttp:\/\/oraclexeserver:8080\/mobileapp\/paramtest?p1=value1&p2=value2<\/b>
\nBoth parameters are optional (procedure parameters have DEFAULT value set).<\/p>\n
If the procedure raises an exception or suitable procedure name with correct argument list cannot be found, then HTTP error code 404 (page not found) is the result.<\/p>\n","protected":false},"excerpt":{"rendered":"
I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX. It’s a small application for a customer demo, so it sounded like a perfect opportunity to have my first […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,23],"class_list":["post-58","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-xdb"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/58","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=58"}],"version-history":[{"count":0,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/58\/revisions"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=58"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=58"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=58"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}