Skip to main content

Viewing the contents of an Oracle cursor in Toad

  1. Prefix your cursor variable with a colon ":"
    2.  
begin 
    event_api.get_activity_data(1, null,'2013', null, null, null,:pResultSet); 
end;
  2. Toad will prompt you for the type. Select CURSOR
  3. And voila! You will have the cursor dumped into a data grid within Toad!

Comments

Post a Comment

Popular posts from this blog

Sorting an ICollection

Have you ever wanted to sort an ICollection? It took me awhile to figure this one out so I thought I should blog it. I originally posted this quite awhile ago. Since then I have discovered a much easier way to sort a collection. Here's an update on sorting collections using LINQ. Much simpler: var orderedList = customer.Users .OrderBy(x => x.UserType) .ToList(); And here is my original post on the subject. You decide which one looks easier. private IList<EventReqFormSection> GetSections() { ICollection<EventReqFormSection> sections = EventReqFormSection.GetByEventReqFormId(_businessObject.Id, ObjectManager); // sort by Display Sequence List<EventReqFormSection> sortedValues = new List<EventReqFormSection>(sections); sortedValues.Sort(new EventReqFormSection.DisplaySequenceSort()); return sortedValues; } The above function retrieves an ICollection puts it into a List object and calls the sort method usin...
2 comments
Read more

Example of an Oracle script using a cursor

SET SERVEROUTPUT ON; declare cursor myCursor is (select auth_code, lastname, dob, driverlicensenumber, driverlicensestate from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0); vAuthCode snap_driver_record.auth_code%type; vLastName snap_driver_record.lastname%type; vDob snap_driver_record.dob%type; vLicenseNumber snap_driver_record.driverlicensenumber%type; vLicenseState snap_driver_record.driverlicensestate%type; vCrashCount integer; vInspCount integer; vCrashTot integer; vInspTot integer; vDirCount integer; begin select count(*) into vDirCount from snap_driver_record where auth_date >= sysdate - 13 and auth_sequence=0; open myCursor; loop fetch myCursor into vAuthCode, vLastName, vDob, vLicenseNumber, vLicenseState; EXIT WHEN myCursor%NOTFOUND; -- get crash record count select count(*) into vCrashCount from crash_driver cd ...
Post a Comment
Read more

Oracle SQL - Nested Case statements

To follow is an example of an anonymous procedure using a nested case statement in SQL. The cursor declaration is 'ad hoc' for use in Toad. BEGIN OPEN :pResultSet FOR SELECT pc.cdbcustomerid, CASE WHEN (pc.typeid = 1 OR pc.typeid = 2) THEN (CASE WHEN ( pc.motor_passenger_carrier IS NULL OR pc.motor_passenger_carrier = 'Not Specified') OR ( pc.inter_intra_state IS NULL OR pc.inter_intra_state = 'Not Specified') OR ( pc.discovered_psp_via IS NULL OR pc.discovered_psp_via = 'No Information Provided') THEN 'No' ELSE 'Yes...
Post a Comment
Read more