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.

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') then
  

  owa.cgi_var_val(i) := REGEXP_REPLACE(owa.cgi_var_val(i),'([^!]*)!(.*)','\1\2');
  exit;
  

  end if;
  

  end loop;
  

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
• 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