SQL & PL/SQL Quick Reference

Peter Goldthorp June 2001, 2011

Kill a Session

select sid, serial#, username, status, server
from v$session;

       SID    SERIAL# USERNAME                       STATUS   SERVER
---------- ---------- ------------------------------ -------- ---------
        23         38                                ACTIVE   DEDICATED
        25          1                                ACTIVE   DEDICATED
        26      10516 SYSTEM                         ACTIVE   DEDICATED
        29         93 RNTMG2                         ACTIVE   DEDICATED

Pass the SID, SERIAL# combination to alter system.  Example: 

alter system kill session '29, 93';

Code to find median values

select round(PERCENTILE_DISC(0.875) within group (order by  ta.amount/ pu.unit_size * 12), 2) 
            class_a_median
,      round(PERCENTILE_DISC(0.5) within group (order by  ta.amount/ pu.unit_size * 12), 2) 
            class_b_median
,      round(PERCENTILE_DISC(0.125) within group (order by  ta.amount/ pu.unit_size * 12), 2) 
            class_c_median
from rnt_properties p
,    rnt_property_units pu
,    rnt_tenancy_agreement ta
where ta.unit_id = pu.unit_id
and pu.property_id = p.property_id
and  pu.unit_size is not null
and ta.amount_period = 'MONTH';

ASCII Character Set Formatting

Formatting to print multi-line text from pl/sql

The following code will replace 'LF' and 'CR' characters with '<br>'. This allows you to print them as native html instead of using <pre> </pre>.

procedure txt_prn(c_string in varchar2)
is
v_text varchar2(4001);
begin
     v_text := c_string;
     v_text := replace(v_text, chr(10), '<br>');
     v_text := replace(v_text, chr(12), '<br>');
     v_text := replace(v_text, chr(13), '<br>');
     v_text := replace(v_text, '<br><br>', '<br>');
     v_text := replace(v_text, '<br>', '<br>'||chr(10));
     htp.p('<p> ');  sheet
     htp.p(v_text);
     htp.p(' </p>');
end txt_prn;

chr(10) is the ascii LF character. http://www.robelle.com/library/smugbook/ascii.html has an ascii code reference

Javascript and Dynamic SQL

For dynamic code (sql or dhtml) pl/sql escape sequence for the quote(') character is another quote (''). In Javascript it's a backslash (\'). The following code can be used to format code which you are going to pass to the javascript document.writeln() function

function format_for_javascript(c_string in varchar2)
  return varchar2
  is
  v_text varchar2(4001);
  begin
     v_text := c_string;
     v_text := replace(v_text, chr(10), '<br>');
     v_text := replace(v_text, chr(12), '<br>');
     v_text := replace(v_text, chr(13), '<br>');
     v_text := replace(v_text, '''', '\''');
     return v_text;
  end format_for_javascript;

INPUT TRUNCATED TO 1 CHARACTERS

This is caused by not having a carriage return after the last line in the procedure. For example, If the last line is "/", then the message returned would be: "Input truncated to 1 characters." If the last line is "show errors", then the message returned would be: "Input truncated to 11 characters."

Find current charaterset

column c1 format a30
select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Generate a file for download via a web-browser

The following code will save the source code for a stored object when called via an iAS listener

procedure download_source(c_name  in dba_objects.object_name%type
                  , c_type  in dba_objects.object_type%type
                  , c_owner in dba_objects.owner%type) is

cursor cur_source(c_name  in dba_objects.object_name%type
                , c_type  in dba_objects.object_type%type
                , c_owner in dba_objects.owner%type)
is select text
   from dba_source
   where name = c_name
   and   type = c_type
   and   owner = c_owner;

cursor cur_vw_source(c_name  in dba_objects.object_name%type
                   , c_owner in dba_objects.owner%type)
is select text
   from dba_views
   where view_name = c_name
   and   owner = c_owner;

begin
  htp.p('Content-type: application/octet-stream'); 
  htp.p('Content-Disposition: attachment; filename='||c_name||'.sql'); 
  htp.p('Content-Transfer-Encoding: base64');	
  htp.p('');

  if c_type = 'VIEW' then
    for t_rec in cur_vw_source(c_name, c_owner) loop
      htp.prn(t_rec.text);
    end loop;
  else  
    for t_rec in cur_source(c_name, c_type, c_owner) loop
      htp.prn(t_rec.text);
    end loop;
  end if;

    htp.p('');
    htp.prn(chr(4));
end download_source;