24. Juli 2008

Einfacher BLOB Viewer: Mit Oracle11g und DBMS_EPG

English title:

Es ist ja mit nahezu allen Werkzeugen recht einfach, sich Tabelleninhalte anzusehen; selbst SQL*Plus reicht zum Ansehen von Tabellen völlig aus. Komplizierter wird es, wenn man eine BLOB-Spalte vor sich hat: Zwar kann SQL*Plus ab Oracle11g BLOB-Spalten anzeigen, man sieht jedoch nur die Bytes des Blobs im Hexcode. Möchte man nun wissen, was da eigentlich drin ist, muss man schon programmieren.
Viewing table data is even with SQL*Plus a very easy task. But if you want to view data in BLOB columns things get difficult. Although SQL*Plus in Oracle11g can display BLOB columns this is not helpful in all cases - it just shows the hex bytes. Seeing the hex bytes does not give a clue about the document contents - except one knows the binary formats very well. To see the document (Word, PDF, Image) programming efforts are required.
Wenn es um Bilder geht, kann der SQL Developer recht gut weiterhelfen, hat man es aber auch mit PDF oder anderen Dateien zu tun, geht auch das nicht mehr.
If it's just about images SQL Developer might be of good help - but when it comes to office or PDF documents we are stuck with the hex bytes again.
Da ich schon etwas häufiger vor dieser Situation stand, habe ich mir einen einfachen "BLOB Viewer" geschrieben. Als Betrachtungswerkzeug nutze ich den Webbrowser - der kann ja mit fast allem umgehen. Um mit dem Webbrowser auf die Datenbank zugreifen zu können, nutze ich das seit Oracle11g verfügbare Embedded PL/SQL Gateway; man kann das Tool auch in älteren Datenbankversionen benutzen, braucht dann jedoch einen Apache Webserver mit dem mod_plsql zum Zugriff auf die Datenbank durch das Web (für APEX-Nutzer kein Problem). Das Embedded PL/SQL Gateway nutzt den HTTP-Protokollserver der XML DB (den gibt es ja schon seit Oracle9i), um PL/SQL-Prozeduren per URL verfügbar zu machen. Die Prozedur selbst ist mit den "guten alten" HTP und OWA_UTIL-Paketen programmiert.
Since I had the situation frequently I wrote a "simple BLOB viewer" for a web browser. To access the database with the web browser I use the new Embedded PL/SQL Gateway introduced with Oracle11g; the viewer could also run in older database versions - but an additional Apache webserver with the mod_plsql is then required (no problem for APEX users). The "Embedded PL/SQL Gateway" uses XML DB's HTTP protocol server (which was introduced some time ago: Oracle9i). The procedure itself is implemented using the "good ol'" HTP, HTF and OWA_UTIL packages.
Hier ist also der Code (Das Skript selbst immer als SYS laufen lassen): Zunächst wird das "Embedded PL/SQL Gateway" mit dem Paket DBMS_EPG eingerichtet. Das Skript prompted nach dem Datenbankuser, für den der DAD eingerichtet werden soll. Wenn man SYS nimmt, kann man mit dem Viewer BLOBs der ganzen Datenbank ansehen.
So here's the code (please run the script as SYS): First it configures the DAD for the Embedded PL/SQL Gateway using the DBMS_EPG package. It prompts for the database schema to use - if you want to have a single URL for viewing the BLOBs of all database schemas use SYS here.
Dann kommt die eigentliche Prozedur: Ziel ist, dass man zumindest den Namen der Tabelle oder View und den Wert des Primärschlüssels angeben muss. Den Rest (PK-Spalte, BLOB-Spalte) versucht die Prozedur selbst herauszufinden. Man kann sie natürlich dennoch übergeben (für den Fall, dass mehrere BLOB-Spalten vorhanden oder kein Primary Key definiert ist). Wenn eine Tabelle einen zusammengesetzten Primärschlüssel hat, werden die Spalten einfach konkatentiert. Die Prozedur wird im Schema des zu Beginn des Skripts angegebenen Users erzeugt.
Then the actual procedure follows: The procedure's goal is to work with as least parameters as possible and to find out the missing items byself. So if a primary key is defined and the procedure is contained in the DAD's schema you just have to provide the table or view's name and the primary key value. Of course you can also explicitly provide the BLOB column, the mimetype column and the primary key column(s). If a table's primary key consists of more than one column the procedure concatenates them. The procedure is created in the DAD users' schema.
set verify off

accept schemaname default 'PARTNER' prompt '>> DAD einrichten für Datenbankschema: [PARTNER] '

begin
  dbms_epg.drop_dad(
     dad_name => 'SHOW_LOB_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'SHOW_LOB_DAD',
    path     => '/lobviewer/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'SHOW_LOB_DAD', 
    attr_name => 'database-username', 
    attr_value => upper('&schemaname.')
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'SHOW_LOB_DAD',
    user => upper('&schemaname.')
  );
end;
/
sho err

commit
/

create or replace procedure &schemaname..show(
  p_table_view   in varchar2,
  p_id_col       in varchar2 default null,
  p_blob_col     in varchar2 default null,
  p_mimetype_col in varchar2 default null,
  p_owner        in varchar2 default SYS_CONTEXT('userenv', 'current_schema'),
  p_id           in varchar2 default '1'
) is
  v_sql       varchar2(32767);
  v_mimetype  varchar2(200)    := null;
  v_blob      blob;
  v_lob_col   varchar2(200)    := null;
  v_id_col    varchar2(200)    := null;
  v_data_type varchar2(200)    := null;
begin
  if p_blob_col is null then
   begin
    select 
      case
        when data_type = 'ORDIMAGE' then '"SYS_ALIAS$"."'||column_name ||'"."SOURCE"."LOCALDATA"' 
        else '"SYS_ALIAS$"."'||column_name ||'"'
      end into v_lob_col 
    from all_tab_columns 
    where table_name = p_table_view and owner = p_owner and data_type in ('BLOB', 'ORDIMAGE')
    and rownum <= 1;
   exception 
    when NO_DATA_FOUND then
     raise_application_error(-20000, 'table "'||p_table_view||'" has no BLOB column');
   end;
  else
   begin
    select data_type into v_data_type from all_tab_columns
    where table_name = p_table_view and owner = p_owner
    and column_name = p_blob_col;
    if v_data_type = 'BLOB' then 
     v_lob_col := '"SYS_ALIAS$".' || dbms_assert.enquote_name(p_blob_col);
    elsif v_data_type = 'ORDIMAGE' then 
     v_lob_col := '"SYS_ALIAS$".'||dbms_assert.enquote_name(p_blob_col)||'."SOURCE"."LOCALDATA"'; 
    else 
     raise_application_error (-20000, 'Supplied column "'||p_blob_col||'" is not a BLOB or ORDIMAGE column.');
    end if;
   exception
    when NO_DATA_FOUND then 
     raise_application_error (-20000, 'Supplied BLOB column "'||p_blob_col||'" does not exist.');
   end;
  end if;  
  if p_id_col is null then 
    v_id_col := '';
    for pkcols in (
      select ucc.column_name 
      from all_constraints uc, all_cons_columns ucc
      where uc.table_name = ucc.table_name
      and uc.constraint_name = ucc.constraint_name
      and uc.constraint_type='P'
      and uc.table_name = p_table_view
      and ucc.owner = p_owner
      order by ucc.position
    ) loop
      v_id_col := v_id_col || '"SYS_ALIAS$"."'||pkcols.column_name ||'"||';
    end loop;
    v_id_col := substr(v_id_col, 1, length(v_id_col) - 2);
    if v_id_col is null then 
      raise_application_error(-20000, 'PK column could not be determined');
    end if;
  else 
    v_id_col := '"SYS_ALIAS$".'||dbms_assert.enquote_name(p_id_col);
  end if;


  if p_mimetype_col is null then
    v_sql := 'select '||v_lob_col|| ' ' ||
             'from '||dbms_assert.schema_name(p_owner)||'.'||dbms_assert.enquote_name(p_table_view)||' SYS_ALIAS$ '||
             'where '||v_id_col||' = :a';
    execute immediate v_sql
    into v_blob
    using p_id;
  else 
    v_sql := 'select '||v_lob_col||', '||dbms_assert.enquote_name(p_mimetype_col)||' '||
             'from '||dbms_assert.schema_name(p_owner)||'.'||dbms_assert.enquote_name(p_table_view)||' SYS_ALIAS$ '||
             'where '||v_id_col||' = :a';
    execute immediate v_sql
    into v_blob, v_mimetype
    using p_id;
  end if;

  owa_util.mime_header(v_mimetype, false);
  htp.p('Content-Length: '||dbms_lob.getlength(v_blob));
  htp.p('Content-Disposition: inline');
  owa_util.http_header_close;
  wpg_docload.download_file(v_blob);
exception when others then
  owa_util.mime_header('text/plain', true);
  htp.p(dbms_utility.format_error_stack);
  htp.p(dbms_utility.format_call_stack);
  htp.p;
  htp.p(v_sql);
  htp.p;
  htp.p('V_LOB_COL: '||v_lob_col);
end;
/
sho err
Ein (möglicher) Aufruf im Browser könnte dann so aussehen (es wird nur die Tabelle und der Wert des Primärschlüssels übergeben). Den Rest findet die Prozedur selbst raus:
One possible procedure call looks like this (only the table name and the primary key value are provided). The procedure determines the other values.
http://localhost:8080/lobviewer/show?P_TABLE_VIEW=DOKUMENT_TAB&P_ID=3
Eine andere Variante übergibt alle Parameter explizit:
Another call: This example passes all values explicitly.
http://localhost:8080/lobviewer/show?
  P_TABLE_VIEW=DOKUMENT_TAB&
  P_OWNER=PARTNER&
  P_ID_COL=ID&
  P_MIMETYPE_COL=MIMETYPE&
  P_BLOB_COL=DOKUMENT&
  P_ID=2

14. Juli 2008

Mehr Informationen über den PL/SQL-Code: PL/Scope

English title: Deep Dive into your PL/SQL code: PL/Scope

  • Wo hatte ich die Variable V_AMOUNT verwendet?
  • Wo in meinem PL/SQL-Code sind die Aufrufe auf MYPROC?
  • Wo waren die SQL-Abfragen auf die Tabelle EMP?
All diese Fragen können in Oracle11g auch ohne Find & Replace beantwortet werden; denn es gibt PL/Scope. PL/Scope sammelt, sobald es aktiviert ist, Informationen über die Verwendung von "Identifiers" - ob sie verwendet werden und wenn ja, wo sie verwendet werden. Wichtig ist allerdings, dass man es vor dem Kompilieren oder Re-Kompilieren des PL/SQL Codes wie folgt aktiviert ...
  • Where in the PL/SQL code is the declaration of V_DATE?
  • Where in the PL/SQL code are the calls to MYPROC?
  • Where are the SQL queries on EMP?
Those and other questions are most frequently answered by performing the development environments' find & replace functions. But Oracle11g has a feature for this: PL/Scope. PL/Scope collects, when activated, information about the usage of identifiers in the PL/SQL code and if yes, where the identifiers were used. But before this information is provided PL/Scope must be activated.
alter session set PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
Wie nehmen als Beispiel mal die Table Function aus dem Blog Posting vom März. Zuerst also PL/Scope aktivieren, dann rekompilieren wir die Funktion um die Informationen zu sammeln ...
We'll take the Table Function of a previous blog posting as example. Firstly we activate PL/Scope, then we perform a recompile of the procedure in order to collect the identifier usage information ...
alter function tilgungsplan compile;
... und dann stehen die Informationen in den Dictionary Views *_IDENTIFIERS zur Verfügung. So könnte man nun abfragen, wo die Variable v_datum genutzt wird ...
... and then the information is provided in the dictionary views *_IDENTIFIERS. Now we can check where the variable v_datum was used ...
SQL> select line, col, type, usage from user_identifiers where name='V_DATUM' order by line, col;

      LINE        COL TYPE               USAGE
---------- ---------- ------------------ -----------
        10          3 VARIABLE           DECLARATION
        17          3 VARIABLE           ASSIGNMENT
        20         27 VARIABLE           REFERENCE
        35          5 VARIABLE           ASSIGNMENT
        35         16 VARIABLE           REFERENCE
        36         29 VARIABLE           REFERENCE

6 Zeilen ausgewählt.
Man sieht also: In Zeile 10 wird die Variable deklariert, in Zeile 17 bekommt sie einen Wert zugewiesen, in Zeile 20wird sie verwendet und so weiter und so fort ...
As we can see: It's declaration is in line 10, in line 17 a value gets assigned to it, in line 20 it is being used and so forth ...
Oder man könnte abfragen, wo das Paket UTL_HTTP überall verwendet wird (klar, das geht auch mit USER_DEPENDENCIES, aber PL/Scope zeigt auch, wo im Code das Paket verwendet wurde) ...
The next query is about the usage of UTL_HTTP (it's clear that USER_DEPENDENCIES also provides some information, but PL/Scope provides fine-grained information - where exactly was this package used) ...
SQL> select object_name, line, col, type, usage from user_identifiers where name='UTL_HTTP' order by line,col

OBJECT_NAME           LINE        COL TYPE               USAGE
--------------- ---------- ---------- ------------------ -----------
MY_PROCEDURE             3          9 SYNONYM            REFERENCE
MY_PROCEDURE             4          9 SYNONYM            REFERENCE
MY_PROCEDURE            37         10 SYNONYM            REFERENCE
MY_PROCEDURE            41          3 SYNONYM            REFERENCE
MY_PROCEDURE            42          3 SYNONYM            REFERENCE
MY_PROCEDURE            44          3 SYNONYM            REFERENCE
MY_PROCEDURE            45         11 SYNONYM            REFERENCE
MY_PROCEDURE            48          5 SYNONYM            REFERENCE
MY_PROCEDURE            51          4 SYNONYM            REFERENCE
Nun ist es eines, solche Informationen in SQL*Plus zu selektieren - besser ist es, wenn man PL/Scope direkt in der Entwicklungsumgebung nutzen kann - und im neuesten SQL Developer 1.5.1 (Build 5440) ist es auch drin. Wenn Ihr dort auf Ansicht und dann auf Extended Search klickt, dann könnt Ihr PL/Scope nutzen - Probiert es aus ...
Now it's not very convinient so query this information in SQL*Plus - it would be much better when the development environment would directly support this. And the newest SQL Developer 1.5.1 (build 5440) supports it actually. Just click on View and then on Extended Search and the PL/Scope functionality gets available on the right hand side.



Mehr Info zum Thema findet sich im Handbuch Advanced Application Developer's Guide (Chapter 8). Darin findet Ihr auch eine Aufstellung, in welchen Zusammenhängen Ihr die Verwendung von Identifiers abfragen könnt.
More information is available in the Advanced Application Developer's Guide (Chapter 8). This also contains an overview on the identifier types which are queryable in the *_IDENTIFIER views.

Beliebte Postings