Wenlai's Tech Notes

Interval Partitions in Oracle 11g

2010-07-22T16:27:00.006-04:00

Among many other enhancements in Oracle 11g, interval partition is definitely a good one for DBAs. There is a good article here discussing it (and other partition enhancements).

In one of our projects, we are looking for sub-partitions within an interval partition, and it's well able to handle. Here's an extended discussion of the link above.

This time we have a interval partition, and list subpartitions inside.


CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST( code )
SUBPARTITION TEMPLATE
  ( SUBPARTITION CD_01 VALUES ('ONE'),
    SUBPARTITION CD_02 VALUES ('TWO'),
    SUBPARTITION CD_03 VALUES ('THREE')
  )
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0

By adding more data that expands the partitions, you'll see the subpartitions are generated as well.


INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'TWO', 'TWO', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0
SYS_P40              SYS_SUBP37           'ONE'               0
SYS_P40              SYS_SUBP38           'TWO'               1
SYS_P40              SYS_SUBP39           'THREE'             1

Subpartition names are automatically with system name, which is not I'm expecting: it's better to named as partition + subpartition_template, ie, SYS_P40_CD_01.

Encrypt in WebLogic

2009-04-10T16:56:00.004-04:00

A colleague gave me a piece of code to plug into my WebLogic 10 JDBC configure file:

<jdbc-data-source ...
...
<password-encrypted>{3DES}xxxxxxxxxxxxxxx</password-encrypted>
...
</jdbc-data-source>

When I copied over, and tried to start WebLogic, got an exception:

weblogic.management.ManagementRuntimeException: com.rsa.jsafe.JSAFE_PaddingException: Could not perform unpadding: invalid pad byte.

It's really not telling what you'll have to do. After some research, I figured out I need to re-encrypt the password. Here's the utility to use:

java -cp <weblogic_home>\server\lib\weblogic.jar -Dweblogic.RootDirectory=<your_domain_dir> weblogic.security.Encrypt <password>

Pasted the result to replace old password, it worked fine.

Slow When Bulk Inserting Records to Database Using Lotus Notes Agent

2008-05-01T14:32:00.003-04:00

I don't do Lotus Notes program. But our Notes developers told me that it was extremely slow to insert records into Oracle (SQL Server as well) database. One example is that 1 million rows took 8 hours.

After some investigation, I found that they were constructing full SQLs, instead of using parameters. Below is a piece of their code:

    Dim con As ODBCConnection
   Dim qry As ODBCQuery
   Dim result As ODBCResultSet

   Set qry = New ODBCQuery
   Set result = New ODBCResultSet
   con.ConnectTo("")
   Set qry.Connection = con

   for each document loop
       qry.SQL = "insert into person (fname,lname) values( '" & v_fname & 
                  "','" & v_lname & "' )"
       Set result.Query = qry
   end loop
   ...

The high-lite is the trouble. Whenever this was called, I noticed so many queries on Oracle data dictionary views. It seems to me that Notes was parsing the SQL, and the second statement sometimes took about 2 seconds.

To solve it, I did some research and found the parameters was useful.


    qry.SQL = "insert into person (fname,lname) values( ?fname?, ?lname? )"
    Set result.Query = qry
    for each document loop
        Call result.SetParameter( fname, "'" & v_fname & "'" )
        Call result.SetParameter( lname, "'" & v_lname & "'" )
    end loop

The performance improved greatly, one example is 30K records reduced time from 4 hours to 10 minutes. Still high, but Notes spends most of the time preparing data.

Accessing Non-exsits Item in Oracle Associative Array

2008-03-14T14:39:00.005-04:00

Look at this piece of code:


 set serveroutput on
 declare
  type MONTH_TYPE is table of varchar(20) index by binary_integer;

  month_table   MONTH_TYPE;
begin
  month_table(1) := 'Jan';
  month_table(2) := 'Feb';

  if month_table(3) is null then
    dbms_output.put_line( 'March is not defined.' );
  end if;
end;
/

What you'll get? You may think the print line.

However, you'll get an error:

    ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

Well, associative array is working the same way as table (is that why it's defined as TABLE of ...), and month_table is similar to select value from month_table into v..., so need to have an exception handling.

    set serveroutput on
 declare
   type MONTH_TYPE is table of varchar(20) index by binary_integer;

   month_table   MONTH_TYPE;
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   if month_table(3) is null then
     dbms_output.put_line( 'March is not defined.' );
   end if;
 exception
   when NO_DATA_FOUND then
     dbms_output.put_line( 'March is not found.' );
 end;
 /

Then, you'll get: March is not found.

Track Long Operations in Oracle

2008-01-03T14:49:00.000-05:00

You may need to run a process on 1 million rows and the whole process takes a few hours to finish. In Oracle, there is a way that you can track the process.

Here's a piece of example code in PL/SQL, it takes advantage of package DBMS_APPLICATION_INFO.


 declare
   -- main variables
   ...

   -- long op info
   v_rindex     PLS_INTEGER;
   v_slno       PLS_INTEGER;
   v_totalwork  NUMBER;
   v_sofar      NUMBER;
   v_obj        PLS_INTEGER;

   v_op_name    varchar(100) := 'My work';
   v_units      varchar(100) := 'rows processed';
 begin
   -- Calculate total work (number of rows to be processed, etc.)
   select count(*) into v_totalwork from ...;

   v_sofar := 0;
   v_rindex     := DBMS_APPLICATION_INFO.set_session_longops_nohint;

   for ... -- A loop to process your work
   loop
     ...  -- do your work here.

     -- log longops view.
     v_sofar := v_sofar + 1;
     if mod( v_sofar, 500 ) = 0 then   -- log your operation every 500 rounds
        DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                               slno        => v_slno,
                                               op_name     => v_op_name,
                                               target      => v_obj,
                                               context     => 0,
                                               sofar       => v_sofar,
                                               totalwork   => v_totalwork,
                                               target_desc => 'Some description here',
                                               units       => v_units);
     end if;

   end loop;

   -- mark the end
   DBMS_APPLICATION_INFO.set_session_longops(rindex   => v_rindex,
                                          slno        => v_slno,
                                          op_name     => v_op_name,
                                          target      => v_obj,
                                          context     => 0,
                                          sofar       => v_sofar,
                                          totalwork   => v_totalwork,
                                          target_desc => 'Some description here',
                                          units       => v_units);

 end;
 /

To view the status of the process, run query:


 select sid, serial#, opname, sofar, totalwork, start_time, last_update_time
 from v$session_longops where opname = 'My Work';

Columns sofar and totalwork show you how much work has been done so far.

Another Way to Reorgnize Table in Oracle

2007-11-14T15:54:00.000-05:00

After inserting/deleting from a table for a long time, the table may contain much spared space that hurts the full table scan greatly. Instead of export and import, or recreate it. Move it from one tablespace to another is another way.

Here's a case I did recently. I noticed a table is slow when running full table scan, however it contains only 30K rows, and each row is not that big.

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME        MB_BYTES   BLOCKS     EXTENTS
------------------- ---------- ---------- ----------
BAD_TABLE           220        28160      99

OK, move it to another tablespace:

alter table BAD_TABLE move tablespace tb_another;

Run that check again:

select segment_name, sum(bytes)/1024/1024 MB_Bytes, sum(blocks) blocks, sum(extents) extents
from user_segments
where segment_name = 'BAD_TABLE'
group by segment_name;

SEGMENT_NAME           MB_BYTES   BLOCKS     EXTENTS
---------------------- ---------- ---------- ----------
BAD_TABLE              32         4096       47

It's much smaller. Well, you may want to move it back to its original tablespace.

Oracle Database Console Credential Failures on Windows

2007-07-20T13:46:00.000-04:00

There is an error message in Oracle Database console:

Connection to host as user USER failed: ERROR: Wrong password for user

Usually you find this error when trying to connect to host, for example, you want to start/shutdown, or backup the database. It had confused me for a long time. Then I found an solution.

1. Provide the 'Log on as a batch job' privilege.

Go to control panel/administrative tools
click on "local security policy"
click on "local policies"
click on "user rights assignments"
double click on "log on as a batch job"
click on "add" and add the user(s) that you're going to use in database console.

2. Set credentials and test

Go to the Preferences link in the database console page
click on Preferred Credentials (link on the left menu)
under "Target Type: Host" click on "set credentials"
enter the OS user(s) for whom you have set "logon as a batch job" privilege
click on "Test"

Then you should see the backup is working.

Get Month End in SQL Server

2007-02-16T11:36:00.000-05:00

SQL Server doesn't have a function to get the month end. Here shows one I wrote:


 ALTER  function dbo.sp_getmonthend ( @inputDate    DATETIME )
 /*
   This function returns the month end of a specific date.
 */
 RETURNS DATETIME
 BEGIN
     DECLARE @outputDate        DATETIME

     select @outputDate = CAST(YEAR(@inputDate) AS VARCHAR(4)) + '-' +
                        CAST(MONTH(@inputDate) AS VARCHAR(2)) + '-01'
     select @outputDate = dateadd( day, -1, dateadd( month, 1, @outputDate ) )
     return @outputDate
 END

Since there is 1st in every month, we forward 1 month from 1st of the month and backward a day.

Undrop table in Oracle

2007-02-13T14:07:00.000-05:00

I don't "undrop" table a lot. So once when I tried to "undrop" a table, I got an error:

sql> undrop table test1;
SP2-0734:unknown command beginning "undrop tab..." - rest of line ignored.

Well, I soon found, it's not "undrop", instead:

SQL> flashback table test1 to before drop rename to test2;
Flashback complete.

You can rename it if the previous name has been used by others.

Row Locks When Inserting - Oracle

2006-12-13T11:50:00.000-05:00

My co-worker told me his application hanging while inserting a row into a table. By looking at the Oracle Enterprise Manager, I noticed it's caused by foreign key. Here I show an example.

Open a SQL Plus, create the tables and foreign key.


    create table tmp_test 
    ( id         number(5) not null, 
      test_type  char(1), 
      comments varchar2(100),
      constraint pk_tmp_test primary key (id) 
    );

    create table tmp_test_type 
    ( test_type char(1), 
      description  varchar2(100),
      constraint pk_tmp_test_type primary key (test_type) );

    alter table tmp_test add constraint fk1_tmp_test foreign key ( test_type )
      references tmp_test_type;

Now try to insert a row without referring type. Expecting an error:


    insert into tmp_test values( 1, 'A', 'Just a test' );

    ORA-02291: integrity constraint (EAS_DEMO.FK1_TMP_TEST) violated - parent key not found

Open another session using SQL Plus, insert a row in test_test_type table but don't commit:


    insert into tmp_test_type values( 'A', 'Test type A' );

Run the insert again using the first SQL Plus, you'll find it hangs. In Oracle Enterprise Manager, you can see "enq: TX - row lock contention".

Commit the changes and cleanup:


    drop table tmp_test;
    drop table tmp_test_type;

Delete Duplicate Rows

2006-10-18T21:20:00.002-04:00

It's easy to delete duplicate rows in Oracle using cursor and rownum. Here shows an example.

Create table and insert some duplicate rows.

CREATE TABLE DUP_TEST
(
COMPANY_ID VARCHAR(8),
COMPANY_NAME VARCHAR(80),
ADDRESS VARCHAR(80)
);

-- Create test data, dup by company id
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address2');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address');
INSERT INTO DUP_TEST VALUES ('3', 'Company Three', 'Address3');

The duplicates are with company id 1 and 2. Let delete the duplicates.

set serveroutput on
declare
  rows_deleted integer := 0;
begin
  for cur in ( select company_id, count(*) cnt from DUP_TEST
               group by company_id having count(*) > 1 )
  loop
    delete from DUP_TEST
    where company_id = cur.company_id and rownum < cur.cnt;
    rows_deleted := rows_deleted + SQL%ROWCOUNT;
  end loop;
  dbms_output.put_line( 'records deleted: '  rows_deleted );
end;
/

Now show the result after deleting.

SQL> select * from dup_test;
COMPANY_ COMPANY_NAME ADDRESS
-------- -------------------- --------------------
1 Company One Address1
2 Company Two Address
3 Company Three Address3

It's a little bit diffcult to delete duplicate rows in SQL Server. I'll show one solution using a cursor. Consider the performance when you have large amount of data to search or delete.

declare cur cursor for select COMPANY_ID from DUP_TEST
declare @CompanyId int

open cur
fetch cur into @CompanyId

while @@fetch_status = 0
begin
if ( select count(*) from DUP_TEST where COMPANY_ID = @CompanyId ) > 1
begin
delete from DUP_TEST where current of cur
end
fetch cur into @CompanyId
end
close cur
deallocate cur
go

It goes through the full table and search duplicates for each row, if found, delete the current of cursor.

Ref: moving from Sybase to SQL

2006-10-16T16:21:00.000-04:00

This is a good article discussing the difference between Sybase and SQL Server.

SQL Server - Append Query Result to .csv file

2006-10-05T16:12:00.000-04:00

While searching on the web, I found a lot talking about appending SQL Server data to Excel files. But not many about appending data to .csv file. After some research, I got one.

First, create the csv file and prepare the column header:

master..xp_cmdshell 'echo EmpID,LastName,FirstName > C:\temp\Employees.csv'

It creates a file c:\temp\Employees.csv with 3 columns.

Then, append the data using OPENROWSET:

INSERT INTO OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp',
[Employees#csv])
select EmployeeID, LastName, FirstName from NOrthwind..Employees
(9 row(s) affected)

Open the file c:\temp\Employees.csv, you'll see there are 9 rows appended.
Some points here are: 1) Database=C:\temp (need to put the folder here only), and 2) [Employees#csv] (need to replace . with # as the table name.

OK, it's similar to read the file back:

select * from OPENROWSET('Microsoft.Jet.OleDB.4.0',
'Text;Database=C:\temp', [Employees#csv])

1 Davolio Nancy
...
9 Dodsworth Anne

At the end, I'll show another way to read the data in csv file:

select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\temp;',
'select top 3 * from "Employees.csv"')

It seems you cannot append to csv using MSDASQL.

Drop Linked Server in SQL Server

2006-08-18T21:35:00.000-04:00

You have learned how to create linked server in SQL Server in Accessing Oracle From SQL Server. Sometimes you may want to drop it. I'll show you how.

First find and drop the logins associated with the linked server.
user master
go

select s.srvid, s.srvname, l.name login_name_associated
from dbo.sysxlogins x, dbo.syslogins l, dbo.sysservers s
where l.sid = x.sid and s.srvid = x.srvid and s.srvname = 'ora_test'
go
srvid srvname login_name_associated
----- --------- ------------------------
1 ora_test sa
1 ora_test analyst

Now sa and analyst need to be dropped from the linked server.

sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'analyst'
go
sp_droplinkedsrvlogin @rmtsrvname = 'ora_test', @locallogin = 'sa'
go

Then the linked server is ready to be dropped.

sp_dropserver @server = 'ora_test'
go

The logins have to be dropped from linked server first, otherwise an error message will show:

There are still remote logins for the server 'ora_test'.

Oracle Flashback Query Using AS OF TIMESTAMP

2006-08-17T16:28:00.000-04:00

Since Oracle 9i Release 2, you can use "AS OF SCN | TIMESTAMP" flashback query to query the "before" data of a changed table. It's useful to find the changes you made or recover errors.
I used TIMESTAMP more often and found it simple but efficient. I'll show some examples below.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

SQL> update employees set salary = salary * 1.05 where employee_id = 100;
1 row updated.

SQL> commit;

SQL> select salary from employees where employee_id = 100;
SALARY
----------
25200

Here shows the way to query the "before" data of yesterday:

SQL> select salary from employees AS OF TIMESTAMP sysdate - 1 where employee_id = 100;
SALARY
----------
24000

To show ten (10) minutes ago:

select salary from employees AS OF TIMESTAMP sysdate - 10/60/24 where employee_id = 100;

Or you can use function SYSTIMESTAMP instead of sysdate.

select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) where employee_id = 100;
select salary from employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) where employee_id = 100;

To recover the change:

SQL>update employees e1 set salary =
( select salary from employees AS OF TIMESTAMP sysdate - 1 e2
where e2.employee_id = e1.employee_id )
where employee_id = 100;
1 row updated.

SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000

You may sometimes want to save the before data to a temporary table, then compare and reccover.

create table tmp_employees nologging as select * from employees AS OF TIMESTAMP sysdate - 1;

The SELECT ... AS OF will not work after the table structure changes or table truncation.

SQL> truncate table tmp_objects;
Table truncated.

SQL> select count(*) from tmp_objects as of timestamp sysdate - 1/24/60;

ORA-01466: unable to read data - table definition has changed

Accessing Oracle From SQL Server

2006-07-30T13:55:00.000-04:00

SQL Server supports linked server to execute SQL commands against another database or data file. With the help of Oracle SQL*Net, connection from SQL Server to Oracle is fairly easy (comparing to Oracle to SQL Server using Oracle Generic Connectivity -- I'll discuss it later).

Below shows the steps. The target Oracle database is named TEST and with sample schema HR, password HR.

1. You need to install Oracle SQL*Net on the SQL Server box. The simple way is to install the Oracle client.

2. Use Oracle Net Configuration Assistant to configure a SQL*Net local service name. Or you can simply add an entry into file $ORACLE_HOME/network/admin/tnsname.ora:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE_SID)
)
)

Replace ORACLE_HOST and ORACLE_SID with your oracle server name and SID or service name.

3. Add linked server in SQL Server. Login as sa:

p_addlinkedserver @server = 'ora_test'
, @srvproduct = 'Oracle'
, @provider = 'MSDAORA'
, @datasrc = 'test'

Where ora_test is the linked server you're going to created (or you can name it whatever you want), test is the local service name of SQL*Net. Keep product and provider Oracle and MSDAORA respectively.

4. Map login on SQL Server, this will give the login permission to access the linked Oracle server.

sp_addlinkedsrvlogin @rmtsrvname = 'ora_test'
, @useself = false
, @locallogin = 'analyst'
, @rmtuser = 'hr'
, @rmtpassword = 'hr'

The example above gives analyst (a SQL Server login) permission to access hr schema in Oracle.

5. Test

select * from ora_test..HR.EMPLOYEES

You'll see:

100 Steven King SKING 515.123.4567 1987-06-17 00:00:00.000 AD_PRES 24000.00 NULL NULL 90
...

The object names in Oracle are in upper case. So you'll need to spell the schema and object names in upper case unless they're defined in lower or mixed case. In the example above, if you want select * from ora_test..HR.employees, you'll be informed that such table does not exist. Because SQL Server will translate the SQL to: select * from ora_test..HR."employees".

Trailing spaces in VARCHAR

2006-07-07T16:43:00.000-04:00

I just noticed that many people are misunderstanding the trailing spaces in VARCHAR field. The truth is they're not trimmed automatically. At least in the databases I know, Oracle, Sybase, SQL Server, DB2, MySQL, etc.

Many know that when you save "abc" into char(5), two trailing spaces will be padded. And when you save "abc" into varchar(5), no space will be added.

How about save "abc " into varchar(5)? Well, the database will keep the trailing space. This sounds reasonable. Many make mistakes when copying data from char field to varchar field or changing the char type to varchar type. Remember, you'll need to trim, at least right trim them.

Multiply records

2006-06-29T16:41:00.000-04:00

In one of my task, I needed to multiply some records in a table, so that they can have more chances to be picked up in a random draw. I did it in SQL Server, so I'll show it in SQL. It's similar in Oracle.
What I need to do is to Cartesian join the target records with a table with a certain number of records.

Create a test table and insert some records.

create table test_multiply( id int, type varchar(10), comments varchar(100) )
go
insert into test_multiply values ( 1, 'single', 'This row will show only once' )
insert into test_multiply values ( 2, 'times 10', 'This row will repeat 10 times' )
go

Now we're to multiply the second record 10 times, we join the records with a table contains 9 records.

insert into test_multiply
select a.* from test_multiply a
inner join ( select top 9 id from sysobjects ) x on 1=1
where a.type = 'times 10'
go
(9 rows affected)

Check the result.

select id, type, count(*) cnt from test_multiply group by id, type
go
id type cnt
----------- ---------- -----------
1 single 1
2 times 10 10

Similar in Oracle.

insert into test_multiply
select a.* from test_multiply a
inner join ( select 1 from all_objects where rownum < 1="1" type =" 'times">

PowerMultiSet in Oracle 10g

2006-06-19T16:40:00.000-04:00

In one of my task, I needed to find all the possible combinations of a few strings. By searching the functions in Oracle, I found an interesting and powerful function: PowerMultiSet.

Oracle says the function returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table.

The submultisets are just the combinations of all elements (you're guaranteed :-) ) and will have 2ⁿ-1 sets.

Let see with an example:

create or replace type NUM_LIST_TYPE is table of number;
/
create or replace type NUM_LIST_LIST_TYPE is table of NUM_LIST_TYPE;
/

select NUM_LIST_TYPE( 1, 2, 3 ) base_set from dual;

BASE_SET
-----------------------
NUM_LIST_TYPE(1, 2, 3)

It shows three elements in the set: 1, 2 and 3. The run PowerMultiSet.

select cast( powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) as NUM_LIST_LIST_TYPE ) power_set from dual;

POWER_SET
--------------------------------------------------------------------------------
NUM_LIST_LIST_TYPE(NUM_LIST_TYPE(1), NUM_LIST_TYPE(2), NUM_LIST_TYPE(1, 2), NUM_
LIST_TYPE(3), NUM_LIST_TYPE(1, 3), NUM_LIST_TYPE(2, 3), NUM_LIST_TYPE(1, 2, 3))

Let's make it easy to read:

1
2
1 2
3
1 3
2 3
1 2 3

However, you have to create the type that is nested table of nested tables and cast the result into it. Otherwise, you'll get an error.

select powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) power_set from dual;

ORA-22833: Must cast a transient type to a persistent type

The number of elements in the input nested table cannot exceed 32. The number of submulties in such case would be 2³² -1, that's 4 gig.

String aggregate in Oracle

2006-06-09T16:22:00.000-04:00

You may have known aggregate functions well, for example, sum, count, max, min, etc. How about add up strings? After searching on the web for a while, I found a function (See the code at the bottom).

The main function is STRING_AGG, which you can use the same way as other aggregate functions. Example:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;

Here's the output in SQL Plus:

      EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT,AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP,SA_MAN
          200 AD_ASST,AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The delimiter by default is comma (,), and is controlled by a store package STRING_AGG_CONTROL. To change it, you can use:

     begin string_agg_control.g_delim := '|'; end;
   /

Then run the sql again:

    select employee_id, string_agg( job_id ) job_list from job_history group by employee_id;
  EMPLOYEE_ID JOB_LIST
  ----------- ----------------------------------------
          101 AC_ACCOUNT|AC_MGR
          102 IT_PROG
          114 ST_CLERK
          122 ST_CLERK
          176 SA_REP|SA_MAN
          200 AD_ASST|AC_ACCOUNT
          201 MK_REP

  7 rows selected.

The function is implemented using an Oracle user defined type with the help of User-Defined Aggregates Interface. You can find more information in this doc.
Sounds perfect? Not that much -- you cannot order the strings. That means the source strings appear in the aggregate string randomly.

The script is attached:


create or replace package string_agg_control IS
  g_delim  varchar2(100) := ',';
end;
/

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
  g_delim   VARCHAR2(100),  -- Delimiter to seperate the strings

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg( NULL, string_agg_control.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || self.g_delim || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, self.g_delim ), self.g_delim );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || self.g_delim || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Testing cursor variable in SQL Plus

2006-05-22T20:21:00.000-04:00

In Oracle stored functions and procedures, you may often return or pass out a result set using cursor variable. Here're some ways I found you can test those cusor variables using SQL Plus. It uses the Oracle hr schema for the example.

Now I created a package:

create or replace package test_refcursor as
TYPE DynamicCursorType IS REF CURSOR;
procedure test( id in number,
result out DynamicCursorType );
end test_refcursor;
/

Create test package body:

create or replace package body test_refcursor as
procedure test (
id in number,
result out DynamicCursorType
)
as
begin
open result for select department_id,department_name
from departments where department_id < id;
end;
end test_refcursor;
/

The procedure test returns all the departments with department_id lower than passed in ID.

Now the first way to test it in a PL/SQL block:

set serveroutput on
declare
p test_refcursor.DynamicCursorType;
v_id number(4);
v_name varchar2(100);
begin
test_refcursor.test( 50, p );
-- The cusor has already opened, no need to open again.
loop
fetch p into v_id, v_name;
exit when p%NOTFOUND;
dbms_output.put_line( v_id || ', ' || v_name );
end loop;
end;
/

The result you can see:

10, Administration
20, Marketing
30, Purchasing
40, Human Resources

There's another way even easier in SQL Plus:

var p refcursor
exec test_refcursor.test( 50, :p );
print p

It'll print as if you select the data from a table:

DEPARTMENT_ID DEPARTMENT_NAME
------------- ----------------------------------
10 Administration
20 Marketing
30 Purchasing
40 Human Resources