Oracle Tips and Tricks — David Fitzjarrell

September 29, 2008

It’s Real Refreshment

Filed under: General — dfitzjarrell @ 17:40

For those who suffer from endless curiosity the prospect of poking around the data dictionary is considered fun, and in the quest of such fun one can run across an interesting view or two. DBA_REFRESH_CHILDREN could be one of those views, and for the inquisitive an examination is in order.

DBA_REFRESH_CHILDREN lists all of the objects affected by every refresh group configured in the given database. [There are also views which are a bit more restricted: ALL_REFRESH_CHILDREN (listing all refresh groups and affected objects accessible by the connected user) and USER_REFRESH_CHILDREN (listing all refresh groups and affected objects owned by the connected user).] Of course it provides more information, such as the associated job number, the rollback/undo segment the group uses, the interval between refreshes and the date for the next refresh (among other details). The view description is as follows:

SQL> desc dba_refresh_children
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(30)
 ROWNER                                    NOT NULL VARCHAR2(30)
 RNAME                                     NOT NULL VARCHAR2(30)
 REFGROUP                                           NUMBER
 IMPLICIT_DESTROY                                   VARCHAR2(1)
 PUSH_DEFERRED_RPC                                  VARCHAR2(1)
 REFRESH_AFTER_ERRORS                               VARCHAR2(1)
 ROLLBACK_SEG                                       VARCHAR2(30)
 JOB                                                NUMBER
 NEXT_DATE                                          DATE
 INTERVAL                                           VARCHAR2(200)
 BROKEN                                             VARCHAR2(1)
 PURGE_OPTION                                       NUMBER(38)
 PARALLELISM                                        NUMBER(38)
 HEAP_SIZE                                          NUMBER(38)
 
SQL>

The PUSH_DEFERRED_RPC column indicates, for updatable materialized views, whether or not to push any changes made to the snapshot data to the master table or master materialized view before the refresh begins. The valid values are Y and N, where Y indicates Oracle will push the changes from the snapshot to the master and N (the default) indicates Oracle will not.

It’s fairly straightforward to extract information from the view, as most of the columns names aren’t ambigous; a generalized report might look like this:

SQL> select owner, name, type, refgroup, job, next_date, interval
  2  from dba_refresh_children;
 
OWNER      NAME                        TYPE       REFGROUP  JOB  NEXT_DATE INTERVAL
---------- --------------------------- --------- --------- ---- ---------- --------------------------
NARBOW     YARN_ORDS_PENDING_MV        SNAPSHOT         13   95  30-SEP-08 TRUNC(SYSDATE + 1) + 4/24
NARBOW     YARN_ORDS_BACKORD_MV        SNAPSHOT         14   96  30-SEP-08 TRUNC(SYSDATE + 1) + 5/24
NARBOW     DISCONTINUED_STOCK_NOS_MV   SNAPSHOT         53  134  30-SEP-08 TRUNC(SYSDATE+1)+5/24
BORTUST    RAW_MATL_BACKORD_MV         SNAPSHOT         11  414  01-JAN-00 sysdate+365
 
SQL>

Job 414 in refresh group 11 is broken. It’s broken because the next run date is Jan 1, 4000, the default date Oracle uses for jobs which shouldn’t run:

SQL> select owner, name, type, refgroup, job, to_char(next_date, 'DD-MON-RRRR') next_date, interval, broken
  2  from dba_refresh_children
  3  where refgroup = 11;
 
OWNER      NAME                  TYPE        REFGROUP  JOB   NEXT_DATE INTERVAL                   B
---------- --------------------- ---------- --------- ---- ----------- -------------------------- -
BORTUST    RAW_MATL_BACKORD_MV   SNAPSHOT          11  414 01-JAN-4000 sysdate+365                Y
 
SQL>

The DBA_REFRESH_CHILDREN view is a good ‘one-stop shop’ for information which can report on the health of your refresh jobs. You can find the broken jobs:

SQL> select owner, name, job, refgroup
  2  from dba_refresh_children
  3  where  broken = 'Y'
  4  /
 
OWNER      NAME                                 JOB REFGROUP
---------- ----------------------------------- ---- --------
BORTUST    RAW_MATL_BACKORD_MV                  414       11
 
SQL>

and it’s nice to see there is only one. Of course discovering WHY the job is broken is another task; it’s quite likely the source table or view has changed and no longer matches the destination definition, and the insert operation fails with either an ORA-00913 (too many values), an ORA-00947 (not enough values) or an error stating a data type mismatch. A search of the alert log may provide the answer; it may not, and the source code for the materialized view will be necessary to understand which local or remote objects were involved. And, in a large shop with a number of DBAs it may be as simple as asking a question. Make certain you’re not ‘spinning your wheels’, though, as the users may not need the view or the job anymore and any effort to fix it would be effort wasted. Again, a quick question to the right people may save you hours of unnecessary work.

The PURGE_OPTION column is probably the most ambiguous of the bunch, and it refers to the method of purging the transaction queue after each ‘push’ (refresh); 1 indicates a quick purge, and 2 indicates a precise purge. These apply to deferred transactions (which can be used to refresh materialized views). A ‘quick’ purge is less costly in resources, but may cause deferred transaction records to remain visible for a period of time after the purge. A ‘precise’ purge consumes more resources but it does offer the benefit of a complete queue flush leaving no lingering traces.

I’ve said this before, and I’ll say it again: having the proper tools for the job at hand can make that job so much easier; when dealing with snapshot/materialized view refreshes the DBA_REFRESH_CHILDREN view can save you time and effort in monitoring jobs and diagnosing problems.

Now, that’s refreshing!

September 26, 2008

How Dynamic

Filed under: pl/sql — dfitzjarrell @ 18:58

Passing a list of values to a function or procedure should be, well, simple, and it is, really, unless you have a dynamic list of unknown length. Simply trying to use the supplied string, as-is, can be disappointing:

SQL> --
SQL> -- Let's try this the simple way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        lv_ct number;
  5  
  6        cursor get_empinfo is
  7        select count(*)
  8        from emp
  9        where deptno in subtype_list;
 10  
 11  begin
 12  
 13    open get_empinfo;
 14    fetch get_empinfo into lv_ct;
 15    close get_empinfo;
 16  
 17    return lv_ct;
 18  end;
 19  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- The function created without error
SQL> --
SQL> -- Let's see if it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;
select instring_list_test('10,20,30') from dual
       *
ERROR at line 1:
ORA-01722: invalid number 
ORA-06512: at "BING.INSTRING_LIST_TEST", line 14 


SQL> 
SQL> --
SQL> -- That's silly, it should work ...
SQL> --

Since ‘10,20,30’ isn’t a number, and Oracle can’t magically separate the individual values the function call fails. It would succeed were there one value in this dynamic list, but real-life situations usually aren’t that simple and straightforward. Whatever shall we do? We need to ‘get dirty’ and actually code a way for Oracle to separate the values, make them numbers and populate a table, dynamically, so we can select from that table and generate a usable list. So, let’s try this again and see if we can get this to do what we want:

SQL> --
SQL> -- Let's try this again
SQL> --
SQL> -- We'll create a table type first
SQL> --
SQL> create or replace type InNumTab is table of number;
  2  /

Type created.

SQL> 
SQL> --
SQL> -- Now we'll use that table type to massage
SQL> -- the supplied string into a usable list
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24  
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32  
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38  
 39    --
 40    -- 'Walk' the provided string
 41    -- The loop exits when no value separator is found
 42    -- We expect the value separator to be a comma
 43    --
 44    loop
 45        exit when instr(subtype_list, ',', startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,',', 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, ',', startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55  
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63  
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70  
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30') from dual;

INSTRING_LIST_TEST('10,20,30')
------------------------------
                            27

SQL> select instring_list_test('10,20') from dual;

INSTRING_LIST_TEST('10,20')
---------------------------
                         12

SQL> select instring_list_test('10') from dual;

INSTRING_LIST_TEST('10')
------------------------
                       3

SQL> select instring_list_test('10,20,30,40') from dual;

INSTRING_LIST_TEST('10,20,30,40')
---------------------------------
                               42

SQL> select instring_list_test('10,20,30,40,50,60') from dual;

INSTRING_LIST_TEST('10,20,30,40,50,60')
---------------------------------------
                                     60

SQL> select instring_list_test('10,20,30,40,50') from dual;

INSTRING_LIST_TEST('10,20,30,40,50')
------------------------------------
                                  54

SQL> 

Notice that the length of the list is immaterial as the loop keeps running until the desired record separator is no longer present; we need to code one additional parse of the supplied string to extract the last value and place it in our dynamic table. We then use the CAST and TABLE functions when we query this ‘table’; the result is a list of values, rather than the original string, which makes IN very happy indeed.

So what if you don’t want to always use a comma to separate your data values? Don’t panic, we can re-write the function to accept a second parameter, the record separator:

SQL> --
SQL> -- We'll try this another way, passing in the desired record separator value
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4        --
  5        -- The parsed value
  6        --
  7        lv_subtyp number;
  8        --
  9        -- The table we'll populate
 10        --
 11        lv_sublist InNumTab := InNumTab();
 12        --
 13        -- A place for the result
 14        --
 15        lv_ct number;
 16        --
 17        -- A variable so we can 'walk' the string
 18        --
 19        startpos number:=1;
 20        --
 21        -- Record counter to extend the table
 22        --
 23        rec     number:=1;
 24
 25        --
 26        -- Query using the dynamic IN list
 27        --
 28        cursor get_empinfo (enums InNumTab) is
 29        select count(*)
 30        from emp
 31        where deptno in (select column_value from table(cast(enums as InNumTab)));
 32
 33  begin
 34    --
 35    -- Extend the table so we can start populating it
 36    --
 37    lv_sublist.extend(rec);
 38
 39    --
 40    -- 'Walk' the provided string
 41    --
 42    -- We provide the desired record separator
 43    --
 44    loop
 45        exit when instr(subtype_list, recsep, startpos) = 0;
 46        lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47        lv_sublist(rec) := lv_subtyp;
 48        startpos := instr(subtype_list, recsep, startpos)+1;
 49        rec := rec+1;
 50        --
 51        -- After each addition we extend the table
 52        --
 53        lv_sublist.extend(rec);
 54    end loop;
 55
 56    --
 57    -- We extend the table one more time to hold our last value
 58    --
 59    rec := rec+1;
 60    lv_sublist.extend(rec);
 61    lv_subtyp := substr(subtype_list, startpos);
 62    lv_sublist(rec) := lv_subtyp;
 63
 64    --
 65    -- Get the count
 66    --
 67    open get_empinfo(lv_sublist);
 68    fetch get_empinfo into lv_ct;
 69    close get_empinfo;
 70
 71    --
 72    -- Return the value to the caller
 73    --
 74    return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

And it works like a charm, returning correct values for the counts requested. Is that cool, or what?

In Oracle 10g and later releases the MEMBER OF operator can be used in place of the table function used in the prior two examples:

SQL>
SQL> --
SQL> -- We'll try this yet another way
SQL> --
SQL> create or replace function instring_list_test(subtype_list varchar2, recsep varchar2)
  2  return number
  3  is
  4          --
  5          -- The parsed value
  6          --
  7          lv_subtyp number;
  8          --
  9          -- The table we'll populate
 10          --
 11          lv_sublist InNumTab := InNumTab();
 12          --
 13          -- A place for the result
 14          --
 15          lv_ct number;
 16          --
 17          -- A variable so we can 'walk' the string
 18          --
 19          startpos number:=1;
 20          --
 21          -- Record counter to extend the table
 22          --
 23          rec     number:=1;
 24
 25          --
 26          -- Query using the dynamic IN list
 27          --
 28          cursor get_empinfo (enums InNumTab) is
 29          select count(*)
 30          from emp
 31          where deptno member of enums;
 32
 33  begin
 34      --
 35      -- Extend the table so we can start populating it
 36      --
 37      lv_sublist.extend(rec);
 38
 39      --
 40      -- 'Walk' the provided string
 41      --
 42      -- We expect the value separator to be a comma
 43      --
 44      loop
 45          exit when instr(subtype_list, recsep, startpos) = 0;
 46          lv_subtyp := substr(subtype_list, startpos, instr(subtype_list,recsep, 1)-1);
 47          lv_sublist(rec) := lv_subtyp;
 48          startpos := instr(subtype_list, recsep, startpos)+1;
 49          rec := rec+1;
 50          --
 51          -- After each addition we extend the table
 52          --
 53          lv_sublist.extend(rec);
 54      end loop;
 55
 56      --
 57      -- We extend the table one more time to hold our last value
 58      --
 59      rec := rec+1;
 60      lv_sublist.extend(rec);
 61      lv_subtyp := substr(subtype_list, startpos);
 62      lv_sublist(rec) := lv_subtyp;
 63
 64      --
 65      -- Get the count
 66      --
 67      open get_empinfo(lv_sublist);
 68      fetch get_empinfo into lv_ct;
 69      close get_empinfo;
 70
 71      --
 72      -- Return the value to the caller
 73      --
 74      return lv_ct;
 75  end;
 76  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> --
SQL> -- Let's test again, this time with our modified function
SQL> --
SQL> -- We'll find it works
SQL> --
SQL> select instring_list_test('10,20,30',',') from dual;

INSTRING_LIST_TEST('10,20,30',',')
----------------------------------
                                27

SQL> select instring_list_test('10;20',';') from dual;

INSTRING_LIST_TEST('10;20',';')
-------------------------------
                             12

SQL> select instring_list_test('10',',') from dual;

INSTRING_LIST_TEST('10',',')
----------------------------
                           3

SQL> select instring_list_test('10:20:30:40',':') from dual;

INSTRING_LIST_TEST('10:20:30:40',':')
-------------------------------------
                                   42

SQL> select instring_list_test('10-20-30-40-50-60','-') from dual;

INSTRING_LIST_TEST('10-20-30-40-50-60','-')
-------------------------------------------
                                         60

SQL> select instring_list_test('10,20,30,40,50',',') from dual;

INSTRING_LIST_TEST('10,20,30,40,50',',')
----------------------------------------
                                      54

SQL>

The problem, though simple to state, isn’t quite as simple to solve, as proven above. That’s because Oracle is a database, it isn’t your brain, so it can’t draw on prior experience and know that ‘10,20,30’ is glorified shorthand for 10,20,30. And because it’s software (really, it is) running on a computer, it does what you tell it to do, whether or not those instructions provide the output you’d intended. Since it can’t think like you do you’ll have to start ‘thinking’ like it does, and that may throw a wrench into your logical picture of the situation. Knowing what to do with that wrench is half of the battle.

Passing dynamic lists to procedures and functions is possible, it just isn’t as easy as you’d like to think, especially the first time through. But, knowing how to navigate such a situation makes life a bit easier in the IT realm, which may make you pretty nifty.

Of course such knowledge is no substitute for the ultimate cool of driving a Lamborghini to the dollar store …

September 18, 2008

That’s Fetching!

Filed under: General — dfitzjarrell @ 16:35

Sometimes, despite the best of intentions, things don’t go exactly as planned. And that’s really annoying when you’re writing database-centric applications and the presumptions you made regarding the data turn out to be … wrong. One possible undesired outcome revolves around returning more rows than initially expected. And Oracle has a couple of ways to tell us that we need to re-think how our code processes that data.

Let’s build a couple of tables and use them to illustrate these points:

SQL> create table lotsa_data(
  2        data_id number,
  3        data_set number,
  4        data_val varchar2(40),
  5        proc_dt  date
  6  );

Table created.

SQL> 
SQL> create table ref_data(
  2        data_id number,
  3        data_set number
  4  );

Table created.

SQL> 

So, let’s now load those tables with data:

SQL> begin
  2        for i in 1..10000 loop
  3         insert into lotsa_data
  4         values(i, mod(i, 17), 'Test data statement '||i, sysdate);
  5         insert into ref_data
  6         values(i, mod(i,17));
  7        end loop;
  8  
  9        commit;
 10  
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> 

Now comes the fun part: let’s use a PL/SQL block to update some of the records in the LOTSA_DATA table. We’ll use the RETURNING INTO clause to attempt to retrieve values from the modified records:

SQL> declare
  2        dataid number;
  3        dataset number;
  4        dval varchar2(40);
  5  begin
  6        update lotsa_data
  7        set data_id = data_id + 10
  8        where data_set = 16
  9        returning data_id, data_set, data_val into dataid, dataset, dval;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 6 


SQL> 

It appears we’ve updated far more than one row, and Oracle wasn’t exactly happy about that. We expected to modify one row of data, and we updated a considerably greater number than that, so the variables which were intended to contain the returned values couldn’t process that request. Oracle throws the ORA-01422 error to indicate we’d overflow the placeholders and, well, it won’t allow that to happen. Can we fix this so it will work? Certainly; we’ll use a collection and BULK COLLECT instead:

SQL> declare
  2        type ldat_tab_typ is table of lotsa_data%rowtype index by binary_integer;
  3  
  4        d_tab ldat_tab_typ;
  5  begin
  6        update lotsa_data
  7        set data_id = data_id + 10
  8        where data_set = 16
  9        returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
 10  
 11        for i in 1..d_tab.count loop
 12         dbms_output.put_line(d_tab(i).data_id||'  '||d_tab(i).data_set||' '||d_tab(i).data_val);
 13        end loop;
 14  end;
 15  /
26  16  Test data statement 16 
43  16  Test data statement 33 
60  16  Test data statement 50 
77  16  Test data statement 67
94  16  Test data statement 84
111  16  Test data statement 101 
128  16  Test data statement 118 
145  16  Test data statement 135
162  16  Test data statement 152
179  16  Test data statement 169
349  16  Test data statement 339
366  16  Test data statement 356
383  16  Test data statement 373
400  16  Test data statement 390
417  16  Test data statement 407
434  16  Test data statement 424
451  16  Test data statement 441
468  16  Test data statement 458
485  16  Test data statement 475
502  16  Test data statement 492
519  16  Test data statement 509
536  16  Test data statement 526
553  16  Test data statement 543
570  16  Test data statement 560
587  16  Test data statement 577
604  16  Test data statement 594
621  16  Test data statement 611
638  16  Test data statement 628
655  16  Test data statement 645
672  16  Test data statement 662
689  16  Test data statement 679
706  16  Test data statement 696
723  16  Test data statement 713
740  16  Test data statement 730
757  16  Test data statement 747
[... lots more data here ...]
7591  16  Test data statement 7581
7608  16  Test data statement 7598
7625  16  Test data statement 7615
7642  16  Test data statement 7632
[... and more here ...]
9580  16  Test data statement 9570
9597  16  Test data statement 9587
9614  16  Test data statement 9604
9631  16  Test data statement 9621
9648  16  Test data statement 9638
9665  16  Test data statement 9655
9682  16  Test data statement 9672
9699  16  Test data statement 9689
9716  16  Test data statement 9706
9733  16  Test data statement 9723
9750  16  Test data statement 9740
9767  16  Test data statement 9757
9784  16  Test data statement 9774
9818  16  Test data statement 9808
9835  16  Test data statement 9825
9852  16  Test data statement 9842
9869  16  Test data statement 9859
9886  16  Test data statement 9876
9903  16  Test data statement 9893
9920  16  Test data statement 9910
9937  16  Test data statement 9927
9954  16  Test data statement 9944
9971  16  Test data statement 9961
9988  16  Test data statement 9978
10005  16  Test data statement 9995

PL/SQL procedure successfully completed.

SQL> 

Voila!! No error generated, and the update succeeded. What a comforting thought. (Note that not all of the result set has been included, as it was a LONG list.)

(If this error is generated by internal code accessing data dictionary views [say, when using the exp or imp utilities] it’s likely that one or more views have been corrupted and need to be rebuilt. The easiest way to do that is to:

Shutdown the database
Take a cold backup
Start it in restricted mode
Run $ORACLE_HOME/rdbms/admin/catalog.sql to rebuild the data dictionary

Do not proceed until you have a good cold backup of the database in its current state, as something could go horribly wrong with the catalog rebuild requiring a restore and a call to Oracle support.

This isn’t likely to occur, but stranger things have happened. If you’re not comfortable with this then contact Oracle support and have them assist you in resolving the issue.)

There are occasions when Oracle errors checking for extra rows in an exact fetch and returns an ORA-01423 message. This error is the ‘tip of the iceberg’, as other errors which caused/contribute to this are reported. After receiving one of these errors it’s necessary to check the entire error stack to reveal the underlying problem.

Of course an ORA-01422 isn’t the only error Oracle can generate when there are too many rows:

SQL> select data_id, data_set, data_val, proc_dt
  2  from lotsa_data
  3  where data_id = (select data_id from ref_data where data_set = 16)
  4  /
where data_id = (select data_id from ref_data where data_set = 16)
                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> 

This one is easier to fix, as it requires only a change from ‘=’ to ‘IN’:

SQL> select data_id, data_set, data_val, proc_dt
  2  from lotsa_data
  3  where data_id IN (select data_id from ref_data where data_set = 16)
  4  /

[Lots of data returned]

SQL>

It’s good to know the data an application can generate, but it’s also good to know how to fix coding blunders and missteps should they arise because the data didn’t match the initial assumptions; someone (yes, maybe even you) may make a mistake. It’s no crime, we all make them. And being able to recover from them is the key to successful application design and implementation.

And that, in the vernacular of old, is ‘fetching’.

September 16, 2008

Execute This!

Filed under: General,pl/sql — dfitzjarrell @ 17:54

A while back I wrote on the causes and solutions for apparently missing tables and views, but I didn’t address the problem of ‘missing’ packages/procedures/functions. Silly me. Let’s correct that.

Oracle newsgroups and blogs are filled with suggestions, tips, techniques and scripts intended to help the DBA with his or her chores, and many of these offerings utilize ‘standard’ packages and procedures installed by Oracle at database creation. Of course some of these packages/procedures/functions aren’t meant for the common, every-day user to utilize, and the privileges on those objects are limited to specific types of accounts. Some of these are even restricted to use by SYS as SYSDBA and no one else. There are many, though, that are suitable for any user to execute, provided that user has the requisite privileges. And, unfortunately, such privileges may not have been granted to the user desiring access; calling or attempting to describe such procedures/packages/functions then results in the following undesired output:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

And, from a PL/SQL block you can get the following unnerving message:

PLS-00201: identifier 'dbms_lock' must be declared

But, hey, you KNOW it’s there, because all of these wonderful scripts can’t be wrong. And they’re not; the user account in use simply has not been granted execute privilege on that package. And the same rules apply here that I listed in my prior post:

* the user has no execute privilege on the package/procedure/function
* a synonym is missing and the user is attempting to access the object by name

How to fix this glaring omission? Either grant execute on the desired object to the requesting user, or create a synonym to allow access by name. How can you tell which is required? If this:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

and this:

SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL>

are the end results then the user has no execute privilege on the package/procedure/function. If, however, access by name fails:

SQL> desc dbms_lock
ERROR:
ORA-04043: object dbms_lock does not exist


SQL>

but access by owner.name succeeds:

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL>

then the issue is a missing synonym. Knowing the corrective action required (and, of course, taking that action) will allow the user to access the desired package/procedure/function.

Knowing what packages/procedures/functions you CAN access is information which is fairly easy to obtain:

select owner, object_name
from all_objects
where object_type in ('PACKAGE','FUNCTION','PROCEDURE');

You’ll get a list (possibly a LONG list) of packages, procedures and functions (and the assiciated owners) which you’re allowed to execute:

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            STANDARD
SYS                            DBMS_STANDARD
SYS                            DBMS_REGISTRY
SYS                            DBMS_REGISTRY_SERVER
SYS                            XML_SCHEMA_NAME_PRESENT
SYS                            UTL_RAW
SYS                            PLITBLM
SYS                            SYS_STUB_FOR_PURITY_ANALYSIS
SYS                            PIDL
SYS                            DIANA
SYS                            DIUTIL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            SUBPTXT2
SYS                            SUBPTXT
SYS                            DBMS_PICKLER
SYS                            DBMS_JAVA_TEST
SYS                            DBMS_SPACE_ADMIN
SYS                            DBMS_LOB
SYS                            UTL_SYS_COMPRESS
SYS                            UTL_TCP
SYS                            UTL_HTTP
SYS                            DBMS_TRANSACTION_INTERNAL_SYS
SYS                            DBMS_SQL


OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            DBMS_SYS_SQL
SYS                            DBMS_OUTPUT
SYS                            DBMS_LOGSTDBY
SYS                            DBMS_SESSION
SYS                            DBMS_LOCK
SYS                            UTL_FILE
SYS                            DBMS_TYPES
SYS                            GETTVOID
SYS                            XMLSEQUENCEFROMXMLTYPE
SYS                            XQSEQUENCEFROMXMLTYPE
SYS                            XMLSEQUENCEFROMREFCURSOR
...

If the package/procedure/function is in that list, but you still can’t access it by name you’re simply missing a synonym. And, if it’s not in that list you have no access to that object so you’ll need to discuss that issue with your DBA.

[ A special case can exist through PL/SQL where a user has access to a table/view/package/procedure/function by virtue of a role but PL/SQL can’t ‘see’ it. Many packages/procedures/functions are compiled with AUTHID DEFINER (the default) and, as such, won’t use privileges granted through a role. Modifying the procedure to be AUTHID CURRENT_USER can fix that problem and allow PL/SQL to traverse the entire privilege tree. If the procedure cannot be modified (such as Oracle-supplied packages, procedures and functions) the only way to ‘fix’ that is to have privileges directly on the object in question. If you find yourself in this boat talk with your DBA to see if he/she can accomodate you.]

I’ll state again in this post that not all Oracle users are destined to access or use all of the installed packages/procedures/functions Oracle supplies. There may be very good reasons in your organization for not having access to a specific package, procedure or function, so don’t be surprised if your request is met with a glorious

“Nope, sorry, can’t do that.”

Security is the watchword of late, and some organizations may frown upon just any user having privilege to execute certain code, because granting such access may open security holes in the database. Pete Finnigan has an excellent website listing the security issues with Oracle releases; it’s worth the time to peruse his site to get a feel for what could disrupt an Oracle installation and give you a ‘heads up’ on why, possibly, you can’t use a certain package or procedure.

It never hurts to ask. Just don’t be surprised if the answer is “No” because there is probably a very good reason for that response.

September 3, 2008

To Err Is Human

Filed under: General,pl/sql — dfitzjarrell @ 14:23

Someone asked

“how oracle is keeping track of last few ora- errors”

and the answer is a fairly simple “it isn’t”. (Granted, Oracle does record the severe errors in the database alert log, but those aren’t all of the errors that can be generated by any stretch of the imagination.) It can, though, if you ask it to.

Since Oracle 8.1.6 system event triggers have been available, and one of those triggers is an AFTER SERVERERROR trigger, which can log most errors you encounter. Setting up your database to do that is a fairly simple task (the following works in 10.2 and later releases):

SQL> CREATE TABLE stats$error_log (
  2          err_dt          TIMESTAMP,
  3          db_user         VARCHAR2(30),
  4          msg_stack       VARCHAR2(2000),
  5          sqltxt          VARCHAR2(1000))
  6  tablespace tools;

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER log_server_errors
  2    AFTER SERVERERROR
  3    ON DATABASE
  4  DECLARE
  5            v_sqltext VARCHAR2(1000);
  6            nl_sqltext ora_name_list_t;
  7    BEGIN
  8            -- Capture entire error text
  9            FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
 10              v_sqltext := v_sqltext || nl_sqltext(i);
 11            END LOOP;
 12
 13            INSERT INTO STATS$ERROR_LOG
 14            (err_dt, db_user, msg_stack, sqltxt)
 15            VALUES
 16            (systimestamp,
 17             sys.login_user,
 18             dbms_utility.format_error_stack, v_sqltext);
 19
 20    END log_server_errors;
 21  /

Trigger created.

SQL>

Now you’re set to log most errors in the STATS$ERROR_LOG table:

SQL> select a.object_name
  2  from dba_objects a, dba_objects b, dba_objects c
  3  order by 1;
from dba_objects a, dba_objects b, dba_objects c
     *
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


SQL>

Let’s query that table and see what it reports:

SQL> select * from stats$error_log;

ERR_DT
---------------------------------------------------------------------------
DB_USER
------------------------------
MSG_STACK
--------------------------------------------------------------------------------
SQLTXT
--------------------------------------------------------------------------------
01-SEP-08 09.35.15.159854 AM
******
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
select a.object_name from dba_objects a, dba_objects b, dba_objects c order by 1


SQL>

We’ve preserved the error stack and the ‘offending’ SQL along with the executing USER (obscured for security reasons) and the timestamp when the error was logged (which is approximately when the error occurred). Nice.

How many errors can you preserve in this log table? It all depends upon how much disk space you have to use. It should go without saying (but, hey, I’ll say it anyway) that this table needs to be monitored so it doesn’t become unwieldy; such data needs to be examined on a regular and fairly frequent basis so that problem areas can be addressed quickly and hopefully resolved.

Which errors won’t you capture? It appears to be a short list:

ORA-01403: no data found 
ORA-01422: exact fetch returns more than requested number of rows
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-01034: ORACLE not available
ORA-04030: out of process memory when trying to allocate string bytes (string, string)

Pretty much any other error should be logged in the table for examination at a later time, which is convenient for the DBA as sometimes he or she cannot address an error when it occurs, or the application doesn’t pass such error text through to the end-user. And you, as a DBA, can’t address errors of which you’re not informed.

An AFTER SERVERERROR trigger may not be something you want to have running all of the time, but it is nice to know you can fire one up and capture problem statements and the errors they create should you need to do so. Just as a carpenter doesn’t use a hammer for every task database tools like event triggers aren’t the solution to every problem. But it is nice to know they’re available when and if you need them, because having the right tools for the job makes the task so much easier.

Blog at WordPress.com.