Register Oracle PL SQL

Oracle allow you to add your html script in plsql code. It’s called PSP (PL/SQL Server Pages).
PL/SQL Server Pages (PSP) are server-side scripts that include dynamic content, including the results of SQL queries, inside Web pages. You can author the web pages in an HTML authoring tool and insert blocks of PL/SQL code.

Below step to register PSP :


1. Create package or procedure or function using toad. Below example procedure which will be made.

CREATE OR REPLACE procedure sample1 as                        

begin
                                                                        htp.prn(‘html code’);
                                        

end;             

  
2. Register that procedure in oracle application.

Navigation : System Administrator -> Security -> Web PL/SQL
.
Please fill field “name” with value “sample1″ and chose “procedure” in field “type”.

3. Create Oracle Application Function to call sample1 procedure from oracle application.
Navigation : System Administrator -> Application -> Function.

In tab description, please fill field “function” with value “sample_function1″ and fill “sample_function1″ in field “user_function_name”.                    
                                                                         

In tab properties, please chose field “type” with value “SSWA plsql function that opens a new window (Kiosk Mode)”

In tab web html, please fill field “HTML Cell” with value “sample1″.

4. Register function to Menu

Navigation : System Administrator -> Application -> Menu.

Please fill menu, user menu name, and description with your menu data.

In field “prompt”, fill with your menu display name. And fill field “function” with value “sample1″.