Better String Aggregation since Oracle 11gR2

In my previous blog "String aggregate in Oracle", I mentioned the restriction of that function is no ordering. Actually since Oracle 11g Release 2, Oracle introduced a new function LISTAGG which provides the same functionality, and also with ordering. You can find details here.

Anyway, I'll show some quick examples in this blog too. To achieve the same result, the query below can be used:

    select employee_id,
           LISTAGG(job_id, '|') WITHIN GROUP (ORDER BY job_id) job_list
    from job_history
    group by employee_id;

And below is the result.

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

And you can see every item in job_list is order.

This new function can be used as analytical function as well.

SQLPlus connection without tnsnames.ora

You can connect using sqlplus with user/pwd@tnsname, or with tnsname string directly as:

sqlplus  user/pwd@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=1521)))(CONNECT_DATA=(SID=<sid>))'


This is cumbersome, and has a lot of problem on unix, since brackets "(" and ")" need backslashes.   Since Oracle 10g, there's a better way:  

  
sqlplus user/pwd@//host:1521/sid

I feel it's very useful, so noted down for future use.

Interval Partitions in Oracle 11g

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.

Slow When Bulk Inserting Records to Database Using Lotus Notes Agent

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

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

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

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

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.

Undrop table in Oracle

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

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

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.

Oracle Flashback Query Using AS OF TIMESTAMP

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

Trailing spaces in VARCHAR

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

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

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

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

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