1 PACKAGE dbms_psp AUTHID CURRENT_USER IS
2
3 /*
4 * Constants and Types
5 */
6 -- PSP text content buffer
7 TEXT_SIZE CONSTANT PLS_INTEGER := 2000;
8 TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
9
10 -- PSP page name and PL/SQL procedure name table
11 NAME_SIZE CONSTANT PLS_INTEGER := 256;
12 TYPE name_table IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
13
14 -- Page length table
15 TYPE length_table IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
16
17 -- Page compilation error info
18 TYPE page_error IS RECORD (
19 name VARCHAR2(256), -- name of PSP page that causes the error
20 line PLS_INTEGER, -- line where the error occurs
21 position PLS_INTEGER, -- position where the error occurs
22 text VARCHAR2(4000) -- the error message
23 );
24 TYPE page_errors IS TABLE OF page_error INDEX BY BINARY_INTEGER;
25
26 /**********************************************************************
27 * API to specify sources of PSP pages:
28 *
29 * PSP pages may exist in two different sources. They may either
30 * be stored in a document table or they may be stored temporarily
31 * in memory. When this package is invoked to compile a PSP page into
32 * a PL/SQL stored procedure, it tries to locate the specified page
33 * from those that has been stored in memory. If it cannot be found,
34 * the package will then look for page from the document table.
35 *
36 * The package assumes the name of the document table (or view),
37 * name of the document name and content columns. The structure of
38 * the document table (or view) looks like this in the caller's schema:
39 *
40 * TABLE wwv_document (
41 * ...
42 * name VARCHAR2(xxx),
43 * content LONG RAW,
44 * ...);
45 *
46 * If the name of the table (or view) or the columns are different,
47 * use the API set_docuemnt_table() to change it. This has to
48 * be called in every database session as this package does not keep
49 * the new settings permenantly.
50 *
51 * To add PSP pages in memory temporarily, use the API add_in_memory_page()
52 * or add_in_memory_pages(). Use clear_in_memory_pages() to remove the
53 * pages in memory to free up resources.
54 */
55
56 /*------------------------ set_document_table -------------------------
57 * NAME
58 * set_docuemnt_table
59 * DESCRIPTION
60 * Sets the document table information. DBMS_PSP locates PSP pages
61 * from the document table as specified. This API has to be called in
62 * every database session as this package does not keep the new
63 * settings permenantly.
64 * PARAMETER
65 * doc_table - name of document table
66 * name_col - name of column that specifies the document name
67 * content_col - name of column that stores the document content
68 * RETURNS
69 * None
70 */
71 PROCEDURE set_document_table(doc_table IN VARCHAR2 DEFAULT 'wwv_document',
72 name_col IN VARCHAR2 DEFAULT 'name',
73 content_col IN VARCHAR2 DEFAULT 'content');
74
75 /*------------------------ add_in_memory_page ------------------------
76 * NAME
77 * add_in_memory_page
78 * DESCRIPTION
79 * Add a PSP page in memory. DBMS_PSP locates a PSP page from its
80 * memory. If the page is not found, it looks for the page in the
81 * document table. The page added will be kept in memory until
82 * clear_in_memory_pages() is called, or the database session ends.
83 * NOTE
84 * A page added by this API overrides pages with the same name added
85 * by this API or in the document table.
86 * PARAMETER
87 * name - name of the PSP page
88 * content - content of the page
89 * RETURNS
90 * None
91 */
92 PROCEDURE add_in_memory_page(name IN VARCHAR2,
93 content IN text_table);
94
95 /*------------------------ add_in_memory_pages -----------------------
96 * NAME
97 * add_in_memory_pages
98 * DESCRIPTION
99 * Add multiple PSP pages in memory. DBMS_PSP locates a PSP page from its
100 * memory. If the page is not found, it looks for the page in the
101 * document table. The pages added will be kept in memory until
102 * clear_in_memory_pages() is called, or the database session ends.
103 * NOTE
104 * Pages added by this API override pages with the same name added
105 * by this API or in the document table.
106 * PARAMETER
107 * names - names of the PSP pages
108 * contents - contents of the page
109 * lengths - length of each page as specified by the no. of rows
110 * each page spans in "contents". The pages are assumed
111 * to appear in the same order in "names", "contents"
112 * (taking into account that a page may span multiple rows)
113 * and "lengths"
114 * RETURNS
115 * None
116 */
117 PROCEDURE add_in_memory_pages(names IN name_table,
118 contents IN text_table,
119 lengths IN length_table);
120
121 /*------------------------ clear_in_memory_pages ---------------------
122 * NAME
123 * clear_in_memory_pages
124 * DESCRIPTION
125 * Clears all PSP pages in memory.
126 * PARAMETER
127 * None
128 * RETURNS
129 * None
130 */
131 PROCEDURE clear_in_memory_pages;
132
133 /**********************************************************************
134 * API to compile PSP pages:
135 *
136 * In order to compile a PSP page, the page must have been stored
137 * in the document table or added in memory with the API above.
138 */
139
140 /*---------------------------- compile_page ---------------------------
141 * NAME
142 * compile_page
143 * DESCRIPTION
144 * Compiles a PSP page to a PL/SQL stored procedure
145 * PARAMETER
146 * name - name of the page to compile
147 * errors - errors produced during compilation
148 * replace_old - should the PL/SQL stored procedure be created
149 * "CREATE OR REPLACE" that replaces the old procedure
150 * RETURNS
151 * the name of the PL/SQL stored procedure generated for this PSP page
152 */
153 FUNCTION compile_page(name IN VARCHAR2,
154 errors OUT page_errors,
155 replace IN BOOLEAN DEFAULT FALSE)
156 RETURN VARCHAR2;
157
158 /*---------------------------- compile_pages --------------------------
159 * NAME
160 * compile_pages
161 * DESCRIPTION
162 * Compiles multiple PSP pages to PL/SQL stored procedures
163 * PARAMETER
164 * names - names of the pages to compile
165 * errors - errors produced during compilation
166 * replace - should the PL/SQL stored procedures be created
167 * "CREATE OR REPLACE" that replace the old procedures
168 * RETURNS
169 * the names of the PL/SQL stored procedures generated for the PSP pages
170 */
171 FUNCTION compile_pages(names IN name_table,
172 errors OUT page_errors,
173 replace IN BOOLEAN DEFAULT FALSE)
174 RETURN name_table;
175
176 /***********************************************************************
177 * Private API:
178 *
179 * The following API are intended to be used solely by loadpsp utility.
180 */
181
182 /*---------------------------- compile_page ----------------------------
183 * NAME
184 * compile_page
185 * DESCRIPTION
186 * Compile a PSP page to a PL/SQL stored procedure while keeping
187 * the page in memory.
188 * PARAMETER
189 * name - name of the page
190 * content - content of the page
191 * replace_old - should the PL/SQL stored procedure be created
192 * "CREATE OR REPLACE" that replaces the old procedure
193 * is_error - is the message return an error message?
194 * RETURNS
195 * a message indicated the page is processed, or an error message if
196 * error occurs.
197 */
198 FUNCTION compile_page(name IN VARCHAR2,
199 content IN text_table,
200 replace IN PLS_INTEGER,
201 is_error OUT PLS_INTEGER)
202 RETURN VARCHAR2;
203
204 /*---------------------------- compile_page ----------------------------
205 * NAME
206 * compile_page
207 * DESCRIPTION
208 * Compile a PSP page to a PL/SQL stored procedure.
209 * PARAMETER
210 * name - name of the page
211 * replace - should the PL/SQL stored procedure be created
212 * "CREATE OR REPLACE" that replaces the old procedure
213 * is_error - is the message return an error message?
214 * RETURNS
215 * a message indicated the page is processed, or an error message if
216 * error occurs.
217 */
218 FUNCTION compile_page(name IN VARCHAR2,
219 replace IN BOOLEAN,
220 is_error OUT BOOLEAN)
221 RETURN VARCHAR2;
222
223 END;