Link to home
Start Free TrialLog in
Avatar of Sanmarie
Sanmarie

asked on

oracle stored procedure for vb.net application

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 vb.net 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?
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanmarie
Sanmarie

ASKER

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

Sanna

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

Sanna