Oracle PL/SQL Tutorial/System Packages/HTP

Build query form with HTP

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE query_form
  2     (p_table_txt IN VARCHAR2) IS
  3     CURSOR cur_cols IS
  4     SELECT column_name
  5     FROM   user_tab_columns
  6     WHERE  table_name = UPPER(p_table_txt);
  7  BEGIN
  8     HTP.HTMLOPEN;
  9     HTP.HEADOPEN;
 10     HTP.HTITLE("Query the " || p_table_txt || " table!");
 11     HTP.HEADCLOSE;
 12     HTP.BODYOPEN;
 13     HTP.FORMOPEN(OWA_UTIL.GET_OWA_SERVICE_PATH||"do_query");
 14     HTP.FORMHIDDEN("p_table_txt", p_table_txt);
 15     HTP.FORMHIDDEN("COLS", "dummy");
 16     FOR cur_cols_rec IN cur_cols LOOP
 17        HTP.FORMCHECKBOX("COLS", cur_cols_rec.column_name);
 18        HTP.PRINT(cur_cols_rec.column_name);
 19        HTP.NL;
 20     END LOOP;
 21     HTP.FORMSUBMIT(NULL, "Execute Query");
 22     HTP.FORMCLOSE;
 23     HTP.BODYCLOSE;
 24     HTP.HTMLCLOSE;
 25  END query_form;
 26  /
Procedure created.

Create HTML List tags

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE list_tables IS
  2     CURSOR cur_tables IS
  3        SELECT table_name
  4        FROM   user_tables
  5        ORDER BY table_name;
  6  BEGIN
  7     HTP.HTMLOPEN;
  8     HTP.HEADOPEN;
  9     HTP.TITLE("Display a List");
 10     HTP.HEADCLOSE;
 11     HTP.BODYOPEN;
 12     HTP.ULISTOPEN;
 13        FOR cur_tables_rec IN cur_tables LOOP
 14           HTP.LISTITEM(cur_tables_rec.table_name);
 15        END LOOP;
 16     HTP.ULISTCLOSE;
 17     HTP.BODYCLOSE;
 18     HTP.HTMLCLOSE;
 19  END list_tables;
 20  /
Procedure created.
SQL>

Display employee in HTML format using HTP package

SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_emps (i_job IN VARCHAR2 DEFAULT "%")
  2  AS
  3     CURSOR emp_cur
  4     IS
  5        SELECT * FROM emp WHERE job LIKE i_job ORDER BY ename;
  6     emp_rec emp_cur%ROWTYPE;
  7  BEGIN
  8     HTP.print ("<title>EMP table<title>");
  9     HTP.print ("<UL>");
 10     OPEN emp_cur;
 11     LOOP
 12        FETCH emp_cur INTO emp_rec;
 13        EXIT WHEN emp_cur%NOTFOUND;
 14        HTP.print ("<LI>" || emp_rec.ename || "</LI>");
 15        HTP.print ("<LI>" || emp_rec.job || "</LI>");
 16        HTP.print ("<LI>" || emp_rec.hiredate || "</LI>");
 17        HTP.print ("<LI>" || emp_rec.sal || "</LI>");
 18     END LOOP;
 19     CLOSE emp_cur;
 20  END;
 21  /
Procedure created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Display records in a HTML table

SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE webProc
  2  IS
  3  BEGIN
  4    htp.p("<HTML>");
  5    htp.p("<HEAD>");
  6    htp.p("<TITLE>Organization Records</TITLE>");
  7    htp.p("</HEAD>");
  8    htp.p("<BODY>");
  9    htp.p("<H1>Organization Records</H1>");
 10    htp.p("<TABLE BORDER="1 ">");
 11    htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
 12    for idx in (select h.product_description,o.rupany_long_name
 13                from company o,product h
 14                where o.product_id =h.product_id
 15                order by h.product_id )loop
 16      htp.p("<TR>");
 17      htp.p("<TD>"||idx.product_description||"</TD>");
 18      htp.p("<TD>"||idx.rupany_long_name||"</TD>");
 19      htp.p("</TR>");
 20    end loop;
 21    htp.p("</TABLE>");
 22    htp.p("</BODY>");
 23    htp.p("</HTML>");
 24  end;
 25  /
Procedure created.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.
SQL>

Display records in a HTML table with parameters

SQL>
SQL>
SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE webProc_with_param(ip_product_id NUMBER)
  2  IS
  3  BEGIN
  4    htp.p("<HTML>");
  5    htp.p("<HEAD>");
  6    htp.p("<TITLE>Organization Records</TITLE>");
  7    htp.p("</HEAD>");
  8    htp.p("<BODY>");
  9    htp.p("<H1>Organization Records</H1>");
 10    htp.p("<TABLE BORDER="1 ">");
 11    htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
 12    for idx in (select h.product_description,o.rupany_long_name
 13                from company o,product h
 14                where o.product_id =h.product_id
 15                and h.product_id =ip_product_id
 16                order by h.product_id )loop
 17      htp.p("<TR>");
 18      htp.p("<TD>"||idx.product_description||"</TD>");
 19      htp.p("<TD>"||idx.rupany_long_name||"</TD>");
 20      htp.p("</TR>");
 21    end loop;
 22    htp.p("</TABLE>");
 23    htp.p("</BODY>");
 24    htp.p("</HTML>");
 25  end;
 26  /
Procedure created.
SQL>
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL> drop table product;
Table dropped.

Display table names in user_tables in HTML format with HTP.PRINT

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE list_tables IS
  2     CURSOR cur_tables IS
  3        SELECT table_name
  4        FROM   user_tables
  5        ORDER BY table_name;
  6  BEGIN
  7     HTP.PRINT("<HTML>");
  8     HTP.PRINT("<HEAD>");
  9     HTP.PRINT("<TITLE>Display a List Tables</TITLE>");
 11     HTP.PRINT("</HEAD>");
 12     HTP.PRINT("<BODY>");
 13     HTP.PRINT("<UL>");
 14        FOR cur_tables_rec IN cur_tables LOOP
 15           HTP.PRINT("<LI>" || cur_tables_rec.table_name);
 16        END LOOP;
 17     HTP.PRINT("</UL>");
 18     HTP.PRINT("</BODY>");
 19     HTP.PRINT("</HTML>");
 20  END list_tables;
 21  /
Procedure created.
SQL>

HTP.PRINT with to_char method

SQL>
SQL> CREATE OR REPLACE PROCEDURE hockey_pass (p_person_txt IN VARCHAR2) IS lv_assists_num PLS_INTEGER;
  2  BEGIN
  3     SELECT 1 INTO   lv_assists_num FROM dual;
  4     HTP.PRINT(p_person_txt || " has " || to_char(lv_assists_num) || " assists this season");
  5  END hockey_pass;
  6  /
Procedure created.
SQL>
SQL>

output a form with action

SQL>
SQL> CREATE OR REPLACE PROCEDURE webProc_with_param_form
  2  IS
  3  BEGIN
  4    htp.p("<HTML>");
  5    htp.p("<HEAD>");
  6    htp.p("<TITLE>Organization Records</TITLE>");
  7    htp.p("</HEAD>");
  8    htp.p("<BODY>");
  9    htp.p("<H1>Organization Records</H1>");
 10    htp.p("<P>Press the <b>Run </b>button.</P>");
 11    htp.p("<FORM method="get" action="">");
 12    htp.p("<P>Hrc Code
 13      <INPUT type="text" name="ip_product_id" maxlength="4" size="4">
 14      </P>");
 15    htp.p("<P>
 16      <INPUT type="submit" value="Run">
 17      </P>");
 18    htp.p("</FORM>");
 19    htp.p("</BODY>");
 20    htp.p("</HTML>");
 21  end;
 22  /
Procedure created.
SQL>

Output a html table

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_test
  2  IS
  3  BEGIN
  4    htp.p("<HTML>");
  5    htp.p("<HEAD>");
  6    htp.p("<TITLE>Records</TITLE>");
  7    htp.p("</HEAD>");
  8    htp.p("<BODY>");
  9    htp.p("<H1>Records</H1>");
 10    htp.p("<TABLE BORDER="1 ">");
 11    htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
 12    htp.p("<TR>");
 13    htp.p("<TD>Director</TD>");
 14    htp.p("<TD>Office of DirectorInc.</TD>");
 15    htp.p("</TR>");
 16    htp.p("<TR>");
 17    htp.p("<TD>Director</TD>");
 18    htp.p("<TD>Office</TD>");
 19    htp.p("</TR>");
 20    htp.p("</TABLE>");
 21    htp.p("</BODY>");
 22    htp.p("</HTML>");
 23  end;
 24  /
Procedure created.

Use HTP package to create HTML page structure

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE pageopen (p_in_title_txt IN VARCHAR2) IS
  2  BEGIN
  3     HTP.HTMLOPEN;
  4     HTP.HEADOPEN;
  5     HTP.TITLE (p_in_title_txt);
  6     HTP.HEADCLOSE;
  7     HTP.BODYOPEN;
  8     HTP.HEADER (1, p_in_title_txt, "CENTER");
  9  END pageopen;
 10  /
Procedure created.
SQL>

Using HTP.PRINT and HTF.BR to output a HTML

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE showvals
  2     (p_a_txt IN VARCHAR2 DEFAULT NULL,
  3      p_b_txt IN VARCHAR2 DEFAULT NULL) IS
  4  BEGIN
  5     HTP.PRINT("a = "|| p_a_txt || HTF.BR);
  6     HTP.PRINT("b = "|| p_b_txt || HTF.BR);
  7  END showvals;
  8  /
Procedure created.
SQL>