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  |
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 #321071 is a reply to message #321070] |
Sun, 18 May 2008 23:30  |
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 #321077 is a reply to message #321071] |
Mon, 19 May 2008 00:07 |
 |
Barbara Boehmer
Messages: 9090
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>
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 12:32:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
