Home » SQL & PL/SQL » SQL & PL/SQL » Converting an table of objects to ref cursor (Oracle 9i)
Converting an table of objects to ref cursor [message #321057] Sun, 18 May 2008 20:59 Go to next message
dzpraka
Messages: 3
Registered: May 2008
Junior Member
Hi,

How do i convert an table of objects to a ref cursor ?

I have a collection type called "hybrid_locations" which is a collection of a object type called "r_hybrid_loc_type". I have a funtion that returns the collection type "hybrid_locations" .
however i need another overloaded function that returns a ref cursor. Can suggestions ?
Re: Converting an table of objects to ref cursor [message #321070 is a reply to message #321057] Sun, 18 May 2008 23:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8627
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE TYPE r_hybrid_loc_type AS OBJECT
  2    (col1  NUMBER,
  3  	col2  VARCHAR2 (1));
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE hybrid_locations AS TABLE OF r_hybrid_loc_type;
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION a_function
  2    RETURN hybrid_locations
  3  AS
  4    v_return hybrid_locations := hybrid_locations();
  5  BEGIN
  6    v_return.EXTEND;
  7    v_return (1) := r_hybrid_loc_type (1, 'A');
  8    v_return.EXTEND;
  9    v_return (2) := r_hybrid_loc_type (2, 'B');
 10    RETURN v_return;
 11  END a_function;
 12  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE types
  2  AS
  3    TYPE v_ref IS REF CURSOR;
  4  END types;
  5  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION another_function
  2    RETURN types.v_ref
  3  AS
  4    v_return types.v_ref;
  5  BEGIN
  6    OPEN v_return FOR SELECT t.* FROM TABLE (a_function) t;
  7    RETURN v_return;
  8  END another_function;
  9  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := another_function

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

      COL1 C
---------- -
         1 A
         2 B

SCOTT@orcl_11g>

Re: Converting an table of objects to ref cursor [message #321071 is a reply to message #321070] Sun, 18 May 2008 23:30 Go to previous messageGo to next message
dzpraka
Messages: 3
Registered: May 2008
Junior Member
Hi,

I tried the same, but while unit testing this in Ounit, I get a
"ORA-06530 :Reference to Un-Intialised composite" error

Here is the a sample code

FUNCTION translate_to_refcursor (
i_geometry MDSYS.SDO_GEOMETRY,
i_gps_accuracy NUMBER DEFAULT NULL,
i_road_section_id sd_gps_lrs_centrelines.road_section_id%TYPE
DEFAULT NULL,
i_carriageway_code sd_gps_lrs_centrelines.ccode_id%TYPE
DEFAULT NULL,
i_lane_code VARCHAR2 DEFAULT NULL
)
RETURN p_ref_cur
IS
o_lrs p_ref_cur;
l_pipe_qry VARCHAR2 (512);
BEGIN
--l_pipe_qry := 'select * from TABLE(hybrid_locations)';
-- Function sd_gps_chainage_connector.TRANSLATE returns
-- "hybrid_location"
OPEN o_lrs FOR
SELECT *
FROM TABLE
(CAST
(sd_gps_chainage_connector.TRANSLATE
(i_geometry,
i_gps_accuracy,
i_road_section_id,
i_carriageway_code,
i_lane_code
) AS hybrid_locations
)
);
RETURN o_lrs;
END;

Re: Converting an table of objects to ref cursor [message #321074 is a reply to message #321071] Sun, 18 May 2008 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I prefer the way Barbara posted her code.
Please read OraFAQ Forum Guide, "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Also use SQL*Plus and copy and paste the session.

Regards
Michel

[Updated on: Sun, 18 May 2008 23:36]

Report message to a moderator

Re: Converting an table of objects to ref cursor [message #321077 is a reply to message #321071] Mon, 19 May 2008 00:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8627
Registered: November 2002
Location: California, USA
Senior Member
Translate is an Oracle reserved word, so you should change your function name to something else. I don't have 9i to test on, but I seem to recall that you may need to use an alias: alias.* from table (cast ((...))) alias. You also need to be careful that your column types match. You must return a sql object type, not a pl/sql type. If this does not help, then please post a copy and paste of a run of all of the components of your code, as I have done below, including line numbers and the line number that the error occurs on. The error that you have posted is typically raised from a portion of the code that you have not posted, in the other function, where you have apparently failed to initialize some collection.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE r_hybrid_loc_type AS OBJECT
  2    (i_geometry	    MDSYS.SDO_GEOMETRY,
  3  	i_gps_accuracy	    NUMBER,
  4  	i_road_section_id   NUMBER,
  5  	i_carriageway_code  NUMBER,
  6  	i_lane_code	    VARCHAR2(1));
  7  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE hybrid_locations AS TABLE OF r_hybrid_loc_type;
  2  /

Type created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE sd_gps_chainage_connector
  2  AS
  3    TYPE p_ref_cur IS REF CURSOR;
  4    FUNCTION translate_to_obj
  5    (i_geometry	    MDSYS.SDO_GEOMETRY,
  6  	i_gps_accuracy	    NUMBER,
  7  	i_road_section_id   NUMBER,
  8  	i_carriageway_code  NUMBER,
  9  	i_lane_code	    VARCHAR2)
 10  	 RETURN hybrid_locations;
 11    FUNCTION translate_to_refcursor
 12    (i_geometry	    MDSYS.SDO_GEOMETRY,
 13  	i_gps_accuracy	    NUMBER,
 14  	i_road_section_id   NUMBER,
 15  	i_carriageway_code  NUMBER,
 16  	i_lane_code	    VARCHAR2)
 17  	 RETURN p_ref_cur;
 18  END sd_gps_chainage_connector;
 19  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY sd_gps_chainage_connector
  2  AS
  3    FUNCTION translate_to_obj
  4    (i_geometry	    MDSYS.SDO_GEOMETRY,
  5  	i_gps_accuracy	    NUMBER,
  6  	i_road_section_id   NUMBER,
  7  	i_carriageway_code  NUMBER,
  8  	i_lane_code	    VARCHAR2)
  9  	 RETURN hybrid_locations
 10    IS
 11  	 v_return hybrid_locations := hybrid_locations();
 12    BEGIN
 13  	 v_return.EXTEND;
 14  	 v_return (1) := r_hybrid_loc_type
 15  			   (i_geometry,
 16  			    i_gps_accuracy,
 17  			    i_road_section_id,
 18  			    i_carriageway_code,
 19  			    i_lane_code);
 20  	 v_return.EXTEND;
 21  	 v_return (2) := r_hybrid_loc_type (NULL, 1, 2, 3, 'A');
 22  	 RETURN v_return;
 23    END translate_to_obj;
 24    FUNCTION translate_to_refcursor
 25    (i_geometry	    MDSYS.SDO_GEOMETRY,
 26  	i_gps_accuracy	    NUMBER,
 27  	i_road_section_id   NUMBER,
 28  	i_carriageway_code  NUMBER,
 29  	i_lane_code	    VARCHAR2)
 30  	 RETURN p_ref_cur
 31    IS
 32  	 o_lrs p_ref_cur;
 33    BEGIN
 34  	 OPEN	o_lrs FOR
 35  	 SELECT t.*
 36  	 FROM	TABLE (CAST (translate_to_obj
 37  			       (i_geometry,
 38  				i_gps_accuracy,
 39  				i_road_section_id,
 40  				i_carriageway_code,
 41  				i_lane_code)
 42  		  AS hybrid_locations)) t;
 43  	 RETURN o_lrs;
 44    END translate_to_refcursor;
 45  END sd_gps_chainage_connector;
 46  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := sd_gps_chainage_connector.translate_to_refcursor (NULL, 3, 2, 1, 'B')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

I_GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES
--------------------------------------------------------------------------------
I_GPS_ACCURACY I_ROAD_SECTION_ID I_CARRIAGEWAY_CODE I
-------------- ----------------- ------------------ -

             3                 2                  1 B


             1                 2                  3 A


SCOTT@orcl_11g>

Re: Converting an table of objects to ref cursor [message #321090 is a reply to message #321077] Mon, 19 May 2008 01:12 Go to previous message
dzpraka
Messages: 3
Registered: May 2008
Junior Member
yep , it works

thanks heaps Cool
Previous Topic: How to optmise the SQL Statement to delete one lakh records ?
Next Topic: to print star in pl/sql
Goto Forum:
  


Current Time: Mon Dec 05 05:11:09 CST 2016

Total time taken to generate the page: 0.17357 seconds