The PL/SQL web toolkit allows you to generate web pages directly from an Oracle8i or Oracle9i database. These pages may be requested by Oracle9iAS or, as in this example, the databases built in webserver. This article assumes the default installation of the HTTP server has taken place and the HTTP server is running.
First we must set up a Database Access Descriptor (DAD) to allow the webserver to connect to the database when a specific URL is requested.
The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.
Next we create the PL/SQL code that will actually produce the web pages. This should be loaded into, or accessable from, the SCOTT schema since this is where the DAD points to.
CREATE OR REPLACE PACKAGE web_app AS PROCEDURE dept_insert_req; PROCEDURE dept_insert_act( p_deptno IN VARCHAR2, p_dname IN VARCHAR2, p_loc IN VARCHAR2, p_submit IN VARCHAR2); END web_app; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY web_app AS PROCEDURE dept_insert_req IS BEGIN HTP.Print( '<html>' || ' <head>' || ' <title>Insert DEPT</title>' || ' </head>' || ' <body>' || ' <h1>Insert DEPT</h1>' || ' <form name="insert" action="web_app.dept_insert_act" method="post">' || ' <table border="1" cellspaceing="3">' || ' <tr>' || ' <td>DEPTNO</td>' || ' <td><input type="text" name="p_deptno" size="2"></td>' || ' </tr>' || ' <tr>' || ' <td>DNAME</td>' || ' <td><input type="text" name="p_dname" size="14"></td>' || ' </tr>' || ' <tr>' || ' <td>LOC</td>' || ' <td><input type="text" name="p_loc" size="13"></td>' || ' </tr>' || ' <tr>' || ' <td colspan="2" align="center">' || ' <input type="submit" name="p_submit" value="Submit">' || ' </td>' || ' </tr>' || ' </table>' || ' </form>' || ' </body>' || '</html>' ); END dept_insert_req; PROCEDURE dept_insert_act( p_deptno IN VARCHAR2, p_dname IN VARCHAR2, p_loc IN VARCHAR2, p_submit IN VARCHAR2) IS BEGIN HTP.Print( '<html>' || ' <head>' || ' <title>Insert DEPT</title>' || ' </head>' || ' <body>' || ' <h1>Insert DEPT</h1>'); BEGIN INSERT INTO dept (deptno, dname, loc) VALUES (p_deptno, p_dname, p_loc); COMMIT; HTP.Print(' Insert DEPT Completed Successfully!<br><br>'); EXCEPTION WHEN OTHERS THEN HTP.Print(' Error: ' || SQLERRM || '<br><br>'); END; HTP.Print(' <table border="1" cellspaceing="3">'); Owa_Util.CellsPrint('SELECT * FROM dept'); HTP.Print( ' </table>' || ' </body>' || '</html>'); END dept_insert_act; END web_app; / SHOW ERRORS
Once the DAD is configured and the web_app package is loaded into the SCOTT schema we can test it by opening a browser and requesting the following address, where "yourServer" is the name of the Oracle server.
http://yourServer:7778/pls/SCOTT/web_app.dept_insert_req
Enter some values into the form and press the submit button. The resulting page should show a successful completion message or an error message. In addition the contents of the DEPT
table are displayed as an HTML table.
This article has only scratched the surface of the PL/SQL web toolkit.
For more informationn see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/9i/plsql-web-toolkit-9i