oracle stored procedure for application

Posted on 2006-05-31
Last Modified: 2010-04-23
Below is what my table looks like:

Appointment Time|  05/22/2005  |   05/23/2006 |  05/29/2005 | 05/30/2005
8:00                    |        5           |       2            |         4        |       3                      
9:00                    |        6           |       3            |         8        |       2
10:00                  |        0           |       0            |         1        |        4

I want to write a stored procedure in oracle 8 that will return the columns above to a DataReader object in that I will place in a DataTAble and then bind to a datagrid. Since oracle stores the date and time together, I would first have to determine all the unique Date values and all the unique time values that I have. Is there a sql statement that does this? Could I then store all the date values in a kind of date-array and the time values in a time-array to use in my logic below:

ForEach Date in (Select Date from Date-array order by Date ascending)

  Select values from <Table_Above>
  where Time is (Select Time from Time-array order by Time ascending)

End ForEach

How would I write this in oracle as I don't know oracle that well?
Question by:Sanmarie
    LVL 96

    Accepted Solution

    Cross-tab queries:

    Crosstab or Pivot Queries

    A crosstab query, sometimes known as a pivot query, groups your data in a slightly different way from those we have seen hitherto. A crosstab query can be used to get a result with three rows (one for each project), with each row having three columns (the first listing the projects and then one column for each year) -- like this:

    Project        2001        2002
         ID         CHF         CHF
        100      123.00      234.50
        200      543.00      230.00
        300      238.00      120.50


    Let's say you want to show the top 3 salary earners in each department as columns. The query needs to return exactly 1 row per department and the row would have 4 columns. The DEPTNO, the name of the highest paid employee in the department, the name of the next highest paid, and so on. Using analytic functions this almost easy, without analytic functions this was virtually impossible.

    SELECT deptno,
      MAX(DECODE(seq,1,ename,null)) first,
      MAX(DECODE(seq,2,ename,null)) second,
      MAX(DECODE(seq,3,ename,null)) third
    FROM (SELECT deptno, ename,
           OVER (PARTITION BY deptno
                 ORDER BY sal desc NULLS LAST) seq
           FROM emp)
    WHERE seq <= 3
    GROUP BY deptno

    LVL 21

    Assisted Solution

    Hi,this is a nice but very complex analytic function.I'm not sure it will work .May be more simple here to use a PLAIN PLSQL block with one record for all arrays rec_time_table
    type datearrt is table of date;
    datearr datearrt ;
    timearr datearrt ;
    CURSOR  recCUR  IS (Select * from rec_time_table order by Date ascending);
     recCURt   recCUR%ROWTYPE;  
    fetch bulk collect into datearr;
    fetch recCUR.time bulk collect into timearr;
    for i in recCUR  
    IF i.time = timearr.time
    select i.* into recCUR%ROWTYPE;
    end if;
    End loop;

    Author Comment

    Thanks all. Let me try the solutions and I will get back to you.


    Author Comment


    Thank you TheLearnedOne, I will use the select statement.

    Oleggold, could you explain CURSORs. I'm not sure what the cursor is doing. Is it a temporary storage area for the array? This is what I plan to do:

    1)Find out the columns given a date range and place in array
    2)Find out the unique times for the given date range and place in array
    3)Finally, I can use TheLearnedOne's select to return the cross-tab results


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    This video discusses moving either the default database or any database to a new volume.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now