Working with PL/SQL REFCURSORS & Oracle REST Data Services

I’ve shown pieces of this before, but not everything in a single post. Here’s how to retrieve your REFCURSORs in an HTTPS Web Request to the Oracle Database and your PL/SQL…with ORDS!

Feel free to judge me on my PL/SQL. I freely admit that I am more of a PL/SQL ‘Coder’ than a professional developer. The idea is, yes, here’s some REFCURSORs, and here’s how to get those from ORDS and HTTPS requests.

What We Want

I want to make an HTTP(s) call to ORDS, and have it return to me all of the songs for a given ALBUM.

And, I want this to happen via a REFCURSOR being returned by some PL/SQL.

That’s it.

We have several options available to make this available, from LOW CODE to even ZERO CODE.

Option One: Just an anonymous block

In this scenario, there’s no actual function or procedure being ‘stored’ or defined in the database. ALL of the code is defined in the HANDLER, via an anonymous block.

Stored procedures are more desirable for several reasons, most importantly it allows for reuse and database optimization (it can run faster!)

Don’t worry, I’ll share ALL of the code below.

What’s happening:

declaring a sys_refcursor, opening it for my SELECT
assigning the bind variable :output value to said refcursor
defining an input parameter for the HTTP Request header (album), surfaced in the plsql blocks as ‘:title’
do the GET with the header set, and we have the records returned

Option Two: SELECT f() from…

I’ll show you the underlying function at the end of this post, but the only important bit is that your PL/SQL should return a single REFCURSOR.

What’s happening:

GET handler with a SELECT
defining an input parameter for the HTTP Request header (title), surfaced in the plsql blocks also as ‘:title’

I could make this simpler. My Service Template URI could be /refcursor/select/:title

Then instead of setting a request header, I’d just do a GET on …/refcrusor/select/Ten

I didn’t do this so I wouldn’t have to worry about URL encoding spaces, reserved characters, etc.

Option Three: RESTFul Web Service

This option will look VERY simple, and it is, but in a real world scenario you’d be doing much more than what I’m showing. Again, I don’t have any error handling built in, I’m not providing any links back to the caller of the APIs…this is just showing you how to get your REFCURSOR.

I have a procedure which takes an input for the ALBUM, and returns REFCURSOR. And I’m going to call it with an anonymous PL/SQL block.

So, I get the advantages of the stored procedure can be cached by the database and reused by other developers/users of the database. Not to mention source controlled, unit tested, profiled, documented, …

What’s happening:

POST handler of PLSQL
defining an output parameter for the HTTP response of type ‘RESULTSET’ (album), surfaced in the plsql blocks also as ‘:album_out
In a plsql handler, ORDS knows a RESULTSET out parameter can be a SYS_REFCURSOR

Option Four: AutoPLSQL

We have a stored procedure that has an OUT parameter. In fact, it’s the same one as shown above, but we’re not going to write ANY code. We’re not going to worry about declaring parameters, writing the anonymous block, etc.

All we’re going to do is ENABLE the object, and then call it.

This calls the ORDS PL/SQL API for us to register the program for ORDS.

And now let’s call it.

What’s happening:

ORDS has registered the PL/SQL object, published a new endpoint (POST)
the input parameters are grabbed off the HTTP Request Body, attribute name will equal the parameter name
the out parameters are returned as JSON Document attributes

The Data + Code

My DATA:

Yes, this is just SUBSET of my iTunes library. Did you know you can export your iTunes library to CSV? It’s another good location to find some ‘fun’ data if you get bored looking at EMPLOYEEs all day.

CREATE TABLE ITUNES (
	NAME               VARCHAR2(128)
	, ARTIST             VARCHAR2(128)
	, COMPOSER           VARCHAR2(256)
	, ALBUM              VARCHAR2(128)
	, GROUPING           VARCHAR2(26)
	, GENRE              VARCHAR2(26)
	, HOW_BIG            NUMBER(38, 0)
	, TIME               NUMBER(38, 0)
	, DISC_NUMBER        NUMBER(38, 0)
	, DISC_COUNT         NUMBER(38, 0)
	, TRACK_NUMBER       NUMBER(38, 0)
	, TRACK_COUNT        NUMBER(38, 0)
	, YEAR               NUMBER(38, 0)
	, DATE_MODIFIED      DATE
	, DATE_ADDED         DATE
	, BIT_RATE           NUMBER(38, 0)
	, SAMPLE_RATE        NUMBER(38, 0)
	, VOLUME_ADJUSTMENT  NUMBER(38, 0)
	, KIND               VARCHAR2(128)
	, EQUALIZER          VARCHAR2(26)
	, COMMENTS           VARCHAR2(256)
	, PLAYS              NUMBER(38, 0)
	, LAST_PLAYED        DATE
	, SKIPS              NUMBER(38, 0)
	, LAST_SKIPPED       DATE
	, MY_RATING          VARCHAR2(26)
	, LOCATION           VARCHAR2(1024)
);
 
REM INSERTING INTO ITUNES
SET DEFINE OFF;
SET SCAN OFF;
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Rock ''N Roll Train','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',9363335,261,1,1,1,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:21:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:01 Rock ''N Roll Train.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Skies On Fire','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7662076,214,1,1,2,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:25:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:02 Skies On Fire.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Big Jack','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',8489031,237,1,1,3,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:29:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:03 Big Jack.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Anything Goes','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7245978,202,1,1,4,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-06-08 04:02:00','YYYY-MM-DD HH:MI:SS'),1,to_date('2011-08-31 02:29:00','YYYY-MM-DD HH:MI:SS'),NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:04 Anything Goes.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('War Machine','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',6829327,189,1,1,5,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:32:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:05 War Machine.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Smash ''N Grab','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',8827606,246,1,1,6,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-06-08 04:09:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:06 Smash ''N Grab.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Spoilin'' For A Fight','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7074305,197,1,1,7,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-06-08 04:12:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:07 Spoilin'' For A Fight.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Wheels','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7488899,208,1,1,8,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-06-08 04:16:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:08 Wheels.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Decibel','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7644213,213,1,1,9,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:40:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:09 Decibel.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Stormy May Day','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',6711052,190,1,1,10,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:43:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:10 Stormy May Day.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('She Likes Rock ''N Roll','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',8309414,232,1,1,11,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:47:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:11 She Likes Rock ''N Roll.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Money Made','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',9159773,255,1,1,12,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-06-08 04:30:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:12 Money Made.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Rock ''N Roll Dream','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',10047608,280,1,1,13,15,2008,to_date('2008-11-23 11:20:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,2,to_date('2011-08-31 02:55:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:13 Rock ''N Roll Dream.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Rocking All The Way','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7270268,202,1,1,14,15,2008,to_date('2008-11-23 11:21:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-08-31 03:34:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:14 Rocking All The Way.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Black Ice','AC/DC','Young, Angus/Young, Malcolm','Black Ice',NULL,'Rock',7336246,205,1,1,15,15,2008,to_date('2008-11-23 11:21:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:10:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,1,to_date('2011-08-31 03:38:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:AC_DC:Black Ice:15 Black Ice.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Young Lust','Aerosmith','Jim Vallance/Joe Perry/Steven Tyler','Pump',NULL,'Rock',6255923,259,NULL,NULL,1,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 04:42:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:01 Young Lust.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('FINE','Aerosmith','Joe Perry/Steven Tyler','Pump',NULL,'Rock',6020487,249,NULL,NULL,2,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 04:46:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:02 FINE.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Going Down Love In An Elevator','Aerosmith','Joe Perry/Steven Tyler','Pump',NULL,'Rock',8170420,339,NULL,NULL,3,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 04:52:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:03 Going Down Love In An Elevator.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Monkey On My Back','Aerosmith','Joe Perry/Steven Tyler','Pump',NULL,'Rock',5722577,237,NULL,NULL,4,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:04 Monkey On My Back.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Water Song Janie''s Got A Gun','Aerosmith','Steven Tyler/Tom Hamilton','Pump',NULL,'Rock',8034570,333,NULL,NULL,5,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:05 Water Song Janie''s Got A Gun.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Dulcimer Stomp The Other Side','Aerosmith','Jim Vallance/Steven Tyler','Pump',NULL,'Rock',7156573,296,NULL,NULL,6,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 05:00:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:06 Dulcimer Stomp The Other Side.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('My Girl','Aerosmith','Joe Perry/Steven Tyler','Pump',NULL,'Rock',4588591,190,NULL,NULL,7,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 05:03:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:07 My Girl.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Don''t Get Mad, Get Even','Aerosmith','Joe Perry/Steven Tyler','Pump',NULL,'Rock',6972914,289,NULL,NULL,8,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 05:08:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:08 Don''t Get Mad, Get Even.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Hoodoo Voodo Medicine Man','Aerosmith','Brad Whitford/Steven Tyler','Pump',NULL,'Rock',6726376,279,NULL,NULL,9,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',1,to_date('2011-08-31 05:13:00','YYYY-MM-DD HH:MI:SS'),NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:09 Hoodoo Voodo Medicine Man.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('What It Takes','Aerosmith','Desmond Child/Joe Perry/Steven Tyler','Pump',NULL,'Rock',9393704,389,NULL,NULL,10,NULL,1989,to_date('2007-12-18 07:04:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 05:48:00','YYYY-MM-DD HH:MI:SS'),192,44100,1,'MPEG audio file',NULL,'aryan',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Aerosmith:Pump:10 What It Takes.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Black Dog','Led Zeppelin','Jimmy Page/John Paul Jones/Robert Plant','Led Zeppelin IV',NULL,'Rock',4747266,296,NULL,NULL,1,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# 410E8E21                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:01 Black Dog.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Rock and Roll','Led Zeppelin','Jimmy Page/John Bonham/John Paul Jones/Robert Plant','Led Zeppelin IV',NULL,'Rock',3535602,220,NULL,NULL,2,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# FEF31124                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:02 Rock and Roll.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('The Battle of Evermore','Led Zeppelin','Jimmy Page/Robert Plant','Led Zeppelin IV',NULL,'Rock',5625816,351,NULL,NULL,3,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# 64C26E33                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:03 The Battle of Evermore.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Stairway to Heaven','Led Zeppelin','Jimmy Page/Robert Plant','Led Zeppelin IV',NULL,'Rock',7706835,481,NULL,NULL,4,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# FEF1CECC                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:04 Stairway to Heaven.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Misty Mountain Hop','Led Zeppelin','Jimmy Page/John Paul Jones/Robert Plant','Led Zeppelin IV',NULL,'Rock',4462218,278,NULL,NULL,5,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# F40289FC                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:05 Misty Mountain Hop.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Four Sticks','Led Zeppelin','Jimmy Page/Robert Plant','Led Zeppelin IV',NULL,'Rock',4552497,284,NULL,NULL,6,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# 87E65A1B                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:06 Four Sticks.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Going to California','Led Zeppelin','Jimmy Page/Robert Plant','Led Zeppelin IV',NULL,'Rock',3452011,215,NULL,NULL,7,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# 7A377780                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:07 Going to California.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('When the Levee Breaks','Led Zeppelin','Jimmy Page/John Bonham/John Paul Jones/Memphis Minnie/Robert Plant','Led Zeppelin IV',NULL,'Rock',6846675,427,NULL,NULL,8,NULL,1971,to_date('2007-12-18 08:57:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:18:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,', AG# 7550D521                ',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Led Zeppelin:Led Zeppelin IV:08 When the Levee Breaks.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Wanna Be Startin'' Somethin''','Michael Jackson','Michael J. Jackson','Thriller',NULL,'Pop',12579181,364,1,1,1,9,1982,to_date('2009-06-19 01:08:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:01 Wanna Be Startin'' Somethin''.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Baby Be Mine','Michael Jackson','R. Temperton','Thriller',NULL,'Pop',8833645,260,1,1,2,9,1982,to_date('2009-06-19 01:08:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:02 Baby Be Mine.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Thriller','Michael Jackson',NULL,'Thriller',NULL,'Pop',11850625,359,1,1,4,9,1982,to_date('2009-06-19 01:09:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:04 Thriller.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Beat It','Michael Jackson','M. J. Jackson','Thriller',NULL,'Pop',9094271,258,1,1,5,9,1982,to_date('2009-06-19 01:09:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:05 Beat It.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Billie Jean','Michael Jackson','Michael J. Jackson','Thriller',NULL,'Pop',10229462,294,1,1,6,9,1982,to_date('2009-06-19 01:09:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:06 Billie Jean.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Human Nature','Michael Jackson','John Bettis/S. Porcaro','Thriller',NULL,'Pop',9022808,246,1,1,7,9,1982,to_date('2009-06-19 01:10:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:07 Human Nature.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('P.Y.T. (Pretty Young Thing)','Michael Jackson','James Ingram/Quincy Jones','Thriller',NULL,'Pop',7894434,239,1,1,8,9,1982,to_date('2009-06-19 01:10:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:08 P.Y.T. (Pretty Young Thing).m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('The Lady In My Life','Michael Jackson','R. Temperton','Thriller',NULL,'Pop',10104475,299,1,1,9,9,1982,to_date('2009-06-19 01:10:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson:Thriller:09 The Lady In My Life.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('The Girl Is Mine','Michael Jackson & Paul McCartney',NULL,'Thriller',NULL,'Pop',7488957,222,1,1,3,9,1982,to_date('2009-06-19 01:08:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:12:00','YYYY-MM-DD HH:MI:SS'),256,44100,NULL,'AAC audio file',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Michael Jackson & Paul McCartney:Thriller:03 The Girl Is Mine.m4a');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Smells Like Teen Spirit','Nirvana','Dave Grohl/Krist Novoselic/Kurt Cobain','Nevermind',NULL,'Alternative',4869525,303,NULL,NULL,1,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 00000A33 00000A4B 00002B49 000023D0 0003347E 0003347E 00006A5B 00006AD9 0003347E 000111B5',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:01 Smells Like Teen Spirit.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('In Bloom','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',4123433,257,NULL,NULL,2,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 0000074A 0000077D 00001A2B 0000119E 0002BF37 0000EA8E 00005BF7 00006953 0002BF65 00029855',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:02 In Bloom.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Come as You Are','Nirvana','Kurt Cobain','Nevermind',NULL,'Alternative',3549574,221,NULL,NULL,3,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 000006E7 000006EE 00001B81 00001B05 0002E675 0002E675 00007537 00008000 0000EA8E 0000EA8E',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:03 Come as You Are.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Breed','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',2985328,185,NULL,NULL,4,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 000007BF 00000E6B 00001730 00001EF9 0001D4D7 00009C57 00006A5D 00006206 00011170 0001D4D7',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:04 Breed.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Lithium','Nirvana','Kurt Cobain','Nevermind',NULL,'Alternative',4156025,259,NULL,NULL,5,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 0000061E 00000599 000019A0 00001A79 000186CE 000186CE 00006E19 00006F91 0002E675 00038270',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:05 Lithium.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Polly','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',2876658,179,NULL,NULL,6,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 00000114 00000106 0000059A 0000052E 00027145 00027145 0000585A 00005038 00027145 00027145',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:06 Polly.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Territorial Pissings','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',2332908,145,NULL,NULL,7,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 0000088D 00000921 0000156B 000012B0 000186B7 000186E5 000055CC 00005A25 0001ADB0 0000C37E',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:07 Territorial Pissings.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Drain You','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',3625227,225,NULL,NULL,8,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 000006F7 0000068A 000017A5 00001554 00015FD5 00015FD5 000063ED 0000609D 000138AE 0003347E',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:08 Drain You.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Lounge Act','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',2554835,159,NULL,NULL,9,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 000006D2 000008EF 000012F1 00001AC5 0001FC15 0001FC15 00005124 0000543B 000222F7 000222F7',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:09 Lounge Act.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Stay Away','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',3444670,214,NULL,NULL,10,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 0000079F 00000987 00002E98 000029D6 0000C37E 0000C37E 00006580 0000757A 0000C37E 0002E647',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:10 Stay Away.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('On a Plain','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',3186372,198,NULL,NULL,11,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 00000822 000007D5 0000127B 0000116F 000222F7 000222F7 00005C13 00005CCD 0001ADF5 0001ADF5',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:11 On a Plain.mp3');
INSERT INTO ITUNES (NAME,ARTIST,COMPOSER,ALBUM,GROUPING,GENRE,HOW_BIG,TIME,DISC_NUMBER,DISC_COUNT,TRACK_NUMBER,TRACK_COUNT,YEAR,DATE_MODIFIED,DATE_ADDED,BIT_RATE,SAMPLE_RATE,VOLUME_ADJUSTMENT,KIND,EQUALIZER,COMMENTS,PLAYS,LAST_PLAYED,SKIPS,LAST_SKIPPED,MY_RATING,LOCATION) VALUES ('Something in the Way','Nirvana','Kurt Cobain/Nirvana','Nevermind',NULL,'Alternative',19773042,1235,NULL,NULL,12,NULL,1991,to_date('2007-12-18 09:00:00','YYYY-MM-DD HH:MI:SS'),to_date('2010-11-02 06:23:00','YYYY-MM-DD HH:MI:SS'),128,44100,1,'MPEG audio file',NULL,' 0000044C 0000060F 00003C14 00003675 001005D5 0010C90E 00006074 000068B6 001276A7 001276A7',NULL,NULL,NULL,NULL,NULL,'Macintosh HD:Users:wvu1999:Music:iTunes:iTunes Media:Music:Nirvana:Nevermind:12 Something in the Way.mp3');
commit;

The RESTFul Web Services Module Code:

 
-- Generated by ORDS REST Data Services 21.1.0.r0840007
-- Schema: HR  Date: Tue Mar 30 03:10:45 2021 
--
 
BEGIN
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'refcursor',
      p_base_path      => '/refcursor/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'refcursor',
      p_pattern        => 'select',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'refcursor',
      p_pattern        => 'select',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'select itunes_refcur(:title) from dual');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'refcursor',
      p_pattern            => 'select',
      p_method             => 'GET',
      p_name               => 'title',
      p_bind_variable_name => 'title',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'refcursor',
      p_pattern        => 'procedure',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'refcursor',
      p_pattern        => 'procedure',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'begin
     itunes_album(title => :title, the_record => :album_out);
end;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'refcursor',
      p_pattern            => 'procedure',
      p_method             => 'POST',
      p_name               => 'album',
      p_bind_variable_name => 'album_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'refcursor',
      p_pattern        => 'anon',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'refcursor',
      p_pattern        => 'anon',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
	l_cur SYS_REFCURSOR;
BEGIN
	OPEN l_cur FOR
		SELECT NAME
		     , ARTIST
		     , COMPOSER
		     , ALBUM
		     , TIME "Seconds"
		     , TRACK_NUMBER
		         || '' of ''
		         || nvl(TRACK_COUNT, 0) "Track"
		     , YEAR
		  FROM itunes
		 WHERE album = :title
         ORDER BY TRACK_NUMBER;
	:ret := l_cur;
END;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'refcursor',
      p_pattern            => 'anon',
      p_method             => 'GET',
      p_name               => 'output',
      p_bind_variable_name => 'ret',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'refcursor',
      p_pattern            => 'anon',
      p_method             => 'GET',
      p_name               => 'album',
      p_bind_variable_name => 'title',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
 
COMMIT;
 
END;

The PL/SQL functions and procedures:

CREATE OR REPLACE FUNCTION itunes_refcur (title IN VARCHAR2) RETURN SYS_REFCURSOR AS
   c SYS_REFCURSOR;
BEGIN
   OPEN c FOR
      SELECT NAME
		     , ARTIST
		     , COMPOSER
		     , ALBUM
		     , TIME "Seconds"
		     , TRACK_NUMBER
		         || ' of '
		         || NVL(TRACK_COUNT, 0) "Track"
		     , YEAR
		  FROM itunes
		 WHERE album = title
			ORDER BY track_number ASC;
   RETURN c;
END itunes_refcur;
/
 
CREATE OR REPLACE PROCEDURE itunes_album (
    title        IN VARCHAR2
   ,the_record   OUT SYS_REFCURSOR
)
    AS
BEGIN
        OPEN the_record FOR
								SELECT NAME
		     , ARTIST
		     , COMPOSER
		     , ALBUM
		     , TIME "Seconds"
		     , TRACK_NUMBER
		         || ' of '
		         || NVL(TRACK_COUNT, 0) "Track"
		     , YEAR
		  FROM itunes
		 WHERE album = title
			ORDER BY track_number ASC;
  END itunes_album;
/

A few last notes:

worth repeating: these examples are BARE BONES, not suitable for copy/paste to production
AUTO will cost a bit more, we look up the parameters for example before we execute the procedure, but again there’s no code to write or maintain
your code can return as many refcursors and other outputs as you want, I stuck with one to make it simpler
there’s no reason why you can put your logic into packages, again, I kept it simpler

Four Options for working with ORDS & PLSQL REFCURSORs

What We Want

Option One: Just an anonymous block

Option Two: SELECT f() from…

Option Three: RESTFul Web Service

Option Four: AutoPLSQL

The Data + Code

Write A Comment Cancel Reply

What We Want

Option One: Just an anonymous block

Option Two: SELECT f() from…

Option Three: RESTFul Web Service

Option Four: AutoPLSQL

The Data + Code

Creating Users & Granting Storage Quota using Database Actions

CI/CD with Oracle Database and APEX

Related Posts

PL/SQL debugger now available in SQL Developer for VS Code

Python Flask w/JavaScript to demo Oracle REST APIs

Where is the Code Outline in SQL Developer, for VS Code?

Write A Comment Cancel Reply