Importing the scott schema in Oracle on Amazon RDS

Oracle Amazon RDS doesn't allow you to connect as sysdba, and you don't have access to the local file system, so you can't run the rdbms/admin/scott.sql script, as you would otherwise do. Instead:

1. Download the demo scripts
2. As master user:
1. Change password of the scott user¹: alter user scott identified by password ;
2. Grant some rights to scott:
1. grant connect to scott;
2. grant create table to scott;
3. grant execute any type to scott;
4. grant unlimited tablespace to scott;
5. grant create any trigger to scott;
3. Connect as scott/password
4. Run SQL in scott.sql

¹ The scott user exists by default in Oracle RDS instances, but I am not sure what the password is out of the box. (It isn't tiger.)

Oracle: Expose your data as XML over HTTP

Once you have enabled the HTTP protocol in the Oracle listener configuration, you can access data stored in tables as XML data through HTTP.

1. Download and install SQL Developer.
2. Connect as sys to your database, under Other Users, right click on HR, choose Edit User.
   
   
   
3. Set a password for HR, uncheck Password expired and Account is Locked.

Now you can go to http://localhost:8889/oradb/HR/COUNTRIES to retrieve all the rows in countries table as an XML document. When asked for authentication, enter the HR login you set earlier.

You can select precisely the data you want using XPath. For instance, this will return the row for Argentina in the countries table: http://localhost:8889/oradb/HR/COUNTRIES/ROW[COUNTRY_ID='AR'].

The oradb part of the URL points to the DBUriServlet which serves what Oracle calls DBUris over HTTP. See the Oracle documentation for a full description the DBUris syntax.

Setting up the Oracle listener for HTTP, WebDAV, and FTP

Oracle provides a facility for the data you have in your database to be exposed as XML documents that you can access through simple URLs. For this facility to be enabled, the first thing you will need to do is to setup the Oracle listener. Edit your C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora (or equivalent on your system) and setup the listener to look as the follows. Note that nanjing.local is my host name; change this as appropriate.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = nanjing.local)(PORT = 1521))
)
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=nanjing.local)(PORT=8889))
(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=nanjing.local)(PORT=2100))
(Presentation=FTP)(Session=RAW))
)

Now you should be able to access http://localhost:8889/ with your web browser and with WebDAV. Stay tuned for more you can do from this URL.