Self-Service Banner Musings
Oracle Documentation ( mod_plsql ):
- SSB uses Oracle's Apache module called mod_plsql. mod_plsql takes the parameters from apache and passes them through to procedures in Oracle.
-
Links:
- The documentation for the 10g version of mod_plsql is at http://download.oracle.com/docs/cd/B14099_19/web.1012/b15896/toc.htm
- The 11g mod_plsql docs are generally better, although they may have features you can't use yet. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_web.htm
Running from console:
- You can run SSB procedures with SQL Plus or SQL Developer, but it's a little wonky.
-
Sessions not initiated from mod_plsql have no cgi environmental variables set. This will cause "ORA-06502: PL/SQL: numeric or value error" errors from the owa procedures. The following commands will create some junk cgi variables that will satisfy most of the owa calls.
exec owa.num_cgi_vars := 0;
exec owa.cgi_var_name (1) := 1;
exec owa.cgi_var_val (1) := 1;
exec owa.init_cgi_env (1, owa.cgi_var_name, owa.cgi_var_val);
-
Run the following to get a procedure's HTML output via DBMS.
set serveroutput on;
exec Your_Package.Your_Procedure;
exec owa_util.showpage;
- DBMS output is limited to 255 chars per line and 1 million chars per run... so your html may get chopped.
- SQL Developer has a special "OWA Output" tab that you can enable. If you have server output turned off then owa output will go there( Don't use owa_util.showpage() ). This solves the DBMS line chopping problem but isn't very useful because "OWA Output" can't be used while debugging.
- Some standard SSB header functions may fail. I'm only interested in the output my own code so I don't run the header functions when running from the console.
-
Sessions not initiated from mod_plsql have no cgi environmental variables set. This will cause "ORA-06502: PL/SQL: numeric or value error" errors from the owa procedures. The following commands will create some junk cgi variables that will satisfy most of the owa calls.
Debugging:
- The first thing you should do is create some error messages. mod_plsql defaults to giving you a generic 404 page when your page crashes. You can add an EXCEPTION section that gives you basic information like the error codes and the source-code line-number of your crash.
EXCEPTION
WHEN OTHERS THEN
htp.p('<h2>Error:</h2>' || '<h3>Error message</h3>"' || SQLERRM || '"<br />');
htp.p('<h3>More Info:</h3>' || (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE) || '<br />');
- The easy way to debug should be to use remote-debugging by adding something like "DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.0.23', 4000 );" to your page. I haven't gotten this working... There is what appears to be a good forum post about this at http://forums.oracle.com/forums/message.jspa?messageID=1175783
- If you can't get remote debugging working( I couldn't ), but still need a full debugger you aren't totally out of luck. Just run the program from a console and debug there.
Killing Runaways:
-
Hitting 'stop' in the web-browser doesn't stop the running pl-sql. This locks the package until the pl-sql finishes which means you can't change/compile the procedure until the pl-sql finishes. You will need to kill runaway pages with a command like "ALTER SYSTEM KILL SESSION '525,2748';" where 525 is the PID and 2748 is the serial #.
To find what has locked your package try the following SQL:-- replace 'YourPackageName' with your package's name
SELECT s.sid, s.serial#, s.username, s.status,
a.owner, a.object, a.type,
to_char(s.logon_time, 'YYYY-MM-DD HH24:MI') as logon_time,
round((sysdate-logon_time)*24,3) as hours_ago
FROM v$session s, v$access a
WHERE s.sid = a.sid
and a.object = 'YourPackageName';
Trace:
-
You can get a trace output file to find performance problems. Use something like:
alter session set tracefile_identifier ="gabe_1";
alter session set timed_statistics=TRUE;
alter session set STATISTICS_LEVEL = ALL ;
alter session set sql_trace = TRUE;
exec Your_Package.Your_Procedure; -- read the 'Running from console' section
alter session set sql_trace = FALSE;
Flexible Parameters:
- These will make your life easier... they are explained at http://download.oracle.com/docs/cd/B10464_05/web.904/b10357/concept.htm#1005765
- SSB authentication fails because of the leading '!' in the URL.
-
Re-write the CGI variable that contains the path_info before calling any SSB functions to fix the SSB authentication '!' issue.
for i in 1..owa.num_cgi_vars loop
if (upper(owa.cgi_var_name(i)) = 'PATH_INFO') thenend loop;
owa.cgi_var_val(i) := REGEXP_REPLACE(owa.cgi_var_val(i),'([^!]*)!(.*)','\1\2');end if;
exit;
Regular Expressions with owa_pattern:
-
you can't group expressions; '(' and ')' are only for backreferences
- '|' is just a character; there is no "OR" except for "[]" which only works on characters
- consider using REGEXP_LIKE() and friends, which have more robust regex capabilities.
- still pretty handy for backreferences
- http://download.oracle.com/docs/cd/B14099_19/web.1012/b15896/pspatt.htm#sthref698
Simple stuff you need to know(but might not)
- mod_plsql always adds a COMMIT to the end of your page.
-
Case Statements
-
two different versions(same as SQL); the following are equivalent
- case when x=1 then htp.p('Hi'); when x=2 then htp.p('Bye'); end;
- case x when 1 then htp.p('Hi'); when 2 then htp.p('Bye'); end;
- SSB will runtime error if x:=3 in the above examples. The way to fix this is to add "else null;" to ignore other values
-
two different versions(same as SQL); the following are equivalent
- If a webpage returns a value too big for the parameter you will get a runtime error.
-
If a variable is assigned to another variable of a different type or size that can't hold the full value you will get a runtume error.
- example: "x varchar2(3):='abc'; y varchar2(2); y := x;" generates a runtime error
- If multiple parameters are passed to a single parameter you will get the first parameter and no error... go figure