oracle aide

August 31, 2007

Oracle locks cheat sheet

Filed under: 1z0-042, locks, oca — oracleaide @ 6:13 pm
SQL Statement Row
locks?
Mode
of
table lock
Lock Modes Permitted?
RS RX S SRX X
SELECT…FROM table…
INSERT INTO table RX
UPDATE table … RX
DELETE FROM table … RX
SELECT … FROM table FOR UPDATE OF … RS
LOCK TABLE table IN ROW SHARE MODE RS
LOCK TABLE table IN ROW EXCLUSIVE MODE RX
LOCK TABLE table IN SHARE MODE S
LOCK TABLE table IN
SHARE ROW EXCLUSIVE MODE
SRX
LOCK TABLE table IN EXCLUSIVE MODE X

  • RS: row share
  • RX: row exclusive
  • S: share
  • SRX: share row exclusive
  • X: exclusive
  •  a lock.

  •  A lock, if no conflicting row locks
    are held by another transaction.
    Otherwise, waits occur.

  • The source.

    August 30, 2007

    Comma-separated list of columns

    Filed under: Analytic functions — oracleaide @ 10:14 pm

    This SQL saved me from writing loopy code in a client-side application. I am using this SELECT to populate combo boxes.
    Removing the first comma is not a problem.
    I have left it on purpose – it adds an empty entry on top of each drop-down list.


    alter session set current_schema = scott

    select table_name, max_col_id col_count, col_list
      from (
        select table_name, column_id,
            max(column_id)
              over (partition by table_name) max_col_id,  
            col_list
          from (  
            select ss.table_name, column_name,
                column_id,
                sys_connect_by_path(cc.column_name, ',') col_list
              from all_tab_columns cc,  
                (select table_name
                   from all_tables
                   where owner =
                   sys_context ('userenv', 'current_schema')) ss
              where cc.table_name = ss.table_name and
                cc.owner = sys_context ('userenv', 'current_schema')
              connect by prior cc.column_id = cc.column_id - 1 and
              prior cc.table_name = cc.table_name and
              prior cc.owner = sys_context ('userenv', 'current_schema')
              start with column_id = 1)
          )
      where column_id = max_col_id

    The output is copied directly from Toad:

    table_name col_count col_list
    BONUS 4 ,ENAME,JOB,SAL,COMM
    DEPT 3 ,DEPTNO,DNAME,LOC
    EMP 8 ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
    SALGRADE 3 ,GRADE,LOSAL,HISAL

    August 27, 2007

    Application Express. A Movable Feast.

    Filed under: Apex — oracleaide @ 1:45 am

    A deserved praise from a modest weekend code warrior:

  • Oracle Apex Portal is a development tool that is always online.
  • It is accessible from anything with a browser.
  • It is platform independent.
  • It is free from security hassles of VPN and Remote Desktop.
  • It is free.
  • I do not feel like I work overtime on my weekend when I use apex.oracle.com for prototyping.
    It is more like playing games online.

    August 21, 2007

    Generic table browser / editor with Application Express

    Filed under: Apex, DBMS_XMLQuery, XSLT, XSU — oracleaide @ 8:21 pm

    The Uber Browser demo was published on apex.oracle.com.
    The application is long gone (Oracle regularly cleans up demo accounts), but the source code is still available,
    It uses the low cost pagination query described in the previous post.

    Since the apex.oracle.com site does not hide users schemas from each other – Uber Browser becomes a dangerous tool.

    To protect tables of other developers I had to limit it to my own schema and two demo tables.

    Under the hood: a good mix of Dynamic SQL with Oracle XML gadgets (DBMS_XMLQuery + XSLT).
    Very few moving parts.

    August 17, 2007

    Low cost pagination query

    Filed under: Apex — oracleaide @ 9:01 pm

    Here is a little entertaining project I dealt with lately – to write a generic table browser using Application Express.

    Since the browser is generic, i.e. SELECT statement is not known until run time – I cannot use most of the RAD gadgets of the IDE and many useful APEX features, like the built-in pagination.

    The solution is simple:

  • use Dynamic SQL,
  • select one page of rows at a time,
  • convert it into XML,
  • run through a style sheet using DBMS_XMLQuery and
  • get a complete HTML page at the end.
  • Handmade pagination is trivial – unless you select from a very large table.
    Here is an example of SQL I came up with:
    (more…)

    August 10, 2007

    The “Missing IN or OUT parameter at index:: 1” error in DBMS_XMLQuery

    Filed under: XSU — oracleaide @ 2:44 pm

    Surprisingly enough the error means what it says – a missing bind variable.
    Not excusing the weird behavior of the (otherwise lovely) DBMS_XMLQuery package.

    This query works fine in my Toad, in spite of the unusual place for a bind variable.


    select :r_max from dual where rownum <= :r_max

    If I try to use DBMS_XMLQuery to turn the query results into XML I get an error.


    declare
      ctx dbms_xmlquery.ctxHandle;
    begin
      ctx := dbms_xmlquery.newContext(
        'select :r_max from dual where rownum <= :r_max');
      dbms_xmlquery.setBindValue(ctx, 'r_max', 10);
      dbms_output.put_line(dbms_xmlquery.getxml(ctx));
      dbms_xmlquery.closeContext(ctx);
    end;

    The error is:


    oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index:: 1

    (more…)

    August 7, 2007

    Spinal Tap

    Filed under: ASM — oracleaide @ 3:04 am

    The obscure initialization parameter named ASM_POWER_LIMIT has the range of values from 0 to 11.
    Is this a reference to the amplifier that goes to 11 from This is Spinal Tap?

    Create a free website or blog at WordPress.com.