Oracle procedure returning a table

MurraySobol · Apr 13, 2012

Here is my SQL:
CREATE OR REPLACE TYPE t_col
AS
object
(name_and_address_id varchar2(26),
full_name varchar2(200),
parent_record_id varchar2(26),
pass number
)
;
/

CREATE OR REPLACE TYPE id_table
AS
TABLE OF t_col;
/

CREATE OR REPLACE FUNCTION f_get_name_address_list
(name_and_address_id IN varchar2,
full_name IN varchar2,
parent_record_id IN varchar2,
pass IN number
)
RETURN id_table
IS
v_ret id_table;
BEGIN
SELECT CAST(multiset
(INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
1
FROM s1_name_and_address c
WHERE c.name_and_address_id = name_address_id
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
2
FROM s1_name_and_address c
WHERE c.parent_record_id <> c.name_and_address_id
AND c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.pass = 1
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
3
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 2
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
4
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 3
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
5
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 4
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
6
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 5
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
7
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 6
)
UNION
INSERT INTO id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
8
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 7
)
) AS id_table
)
INTO v_ret
FROM dual;

return v_ret;
END f_get_name_address_list;
/

And here is the error I get:
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
F_GET_NAME_ADDRESS_LIST FUNCTION 1 12 24
PL/SQL: ORA-00936: missing expression
ERROR 0

F_GET_NAME_ADDRESS_LIST FUNCTION 2 11 4
PL/SQL: SQL Statement ignored
ERROR 0

Any assistance would be appreciated.

Dagon · Apr 13, 2012

I've never heard of anyone doing a CAST(MULITSET) on an insert statement before. CAST(MULTISET) converts the output of a select statement into an object array, so I don't see how it would work with an insert statement.

Dagon · Apr 13, 2012

Also, I've never heard of anyone using UNIONS with INSERTS either.

MurraySobol · Apr 13, 2012

Here is the link I used to create my SQL:

http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

I removed the "UNION"s, same result.

Dagon · Apr 13, 2012

Example:

Code:

select 
  cast(
  multiset(
    select

Your code:

Code:

SELECT CAST(multiset
                      (INSERT INTO id_table

Can you spot the difference?

MurraySobol · Apr 13, 2012

I made the changes as you suggested and have progress to a new error.
Here is the SQL:
CREATE OR REPLACE FUNCTION f_get_name_address_list
(name_and_address_id IN varchar2,
full_name IN varchar2,
parent_record_id IN varchar2,
pass IN number,
name_address_id IN varchar2
)
RETURN id_table
IS
v_ret id_table;
BEGIN
SELECT CAST(multiset
(SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
1
FROM s1_name_and_address c
WHERE c.name_and_address_id = name_address_id
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
2
FROM s1_name_and_address c
WHERE c.parent_record_id <> c.name_and_address_id
AND c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.pass = 1
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
3
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 2
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
4
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 3
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
5
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 4
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
6
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 5
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
7
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 6
)
UNION
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
8
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 7
)
) AS id_table
)
INTO v_ret
FROM dual;

return v_ret;
END f_get_name_address_list;
/
And here is the new error:
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
F_GET_NAME_ADDRESS_LIST FUNCTION 1 28 39
PL/SQL: ORA-04044: procedure, function, package, or type is not allowed here
ERROR 0

F_GET_NAME_ADDRESS_LIST FUNCTION 2 12 4
PL/SQL: SQL Statement ignored
ERROR 0

I added an INPUT parameter: name_address_id; this seems to have caused the problem.

Dagon · Apr 16, 2012

The problem is that you are trying to access id_table as if it were a table:

Code:

  WHERE c.parent_record_id IN
                              (SELECT x.name_and_address_id
                                 FROM id_table x
                                WHERE x.parent_record_id <> x.name_and_address_id
                                  AND x.pass = 5
                              )

I'm not sure what you are trying to achieve here - id_table is a type, so it doesn't actually store any data and you can't select from it as if it is a table.

MurraySobol · Apr 16, 2012

Fot anyone that is following this thread, I am trying to convert an SQL Server Function to an Oracle Function (or Procedure).
Here is the SQL Server Function:
CREATE FUNCTION f_get_name_address_list
(@name_address_id varchar(26))
RETURNS @id_table
TABLE (name_and_address_id varchar(26),
full_name varchar(200),
parent_record_id varchar(26),
pass integer
)

AS
BEGIN

/* Usage:
SELECT * FROM f_get_name_address_list('PACMA')
*/

DECLARE @Pass int

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
1
FROM s1_name_and_address c
WHERE c.name_and_address_id = @name_address_id

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
2
FROM s1_name_and_address c
WHERE c.parent_record_id <> c.name_and_address_id
AND c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.pass = 1
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
3
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 2
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
4
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 3
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
5
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 4
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
6
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 5
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
7
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 6
)

INSERT INTO @id_table
SELECT c.name_and_address_id,
c.full_name,
c.parent_record_id,
8
FROM s1_name_and_address c
WHERE c.parent_record_id IN
(SELECT x.name_and_address_id
FROM @id_table x
WHERE x.parent_record_id <> x.name_and_address_id
AND x.pass = 7
)

RETURN

END

and it works.

Above is my last attempt to convert it to Oracle.
If anyone has experience with Oracle <-> SQL Server conversions, I would appreciate your advice.

Thanks.

LKBrwnDBA · Apr 16, 2012

Perhaps what you need is a hierarchical query, something like this:

Code:

CREATE OR REPLACE FUNCTION f_get_name_address_list
  ( p_name_and_address_id IN VARCHAR2 )
RETURN SYS_REFCURSOR IS
  v_ret          SYS_REFCURSOR;
BEGIN
  OPEN v_ret FOR
    SELECT c.name_and_address_id,
           c.full_name,
           c.parent_record_id,
           LEVEL pass
    FROM s1_name_and_address c
    START WITH c.name_and_address_id = p_name_and_address_id
    CONNECT BY PRIOR c.name_and_address_id = c.parent_record_id;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

LKBrwnDBA · Apr 16, 2012

Ooops, you need to add the return statement:

Code:

CREATE OR REPLACE FUNCTION f_get_name_address_list
  ( p_name_and_address_id IN VARCHAR2 )
RETURN SYS_REFCURSOR 
IS
  v_ret          SYS_REFCURSOR;
BEGIN
  OPEN v_ret FOR
    SELECT c.name_and_address_id,
           c.full_name,
           c.parent_record_id,
           LEVEL pass
    FROM s1_name_and_address c
    START WITH c.name_and_address_id = p_name_and_address_id
    CONNECT BY PRIOR c.name_and_address_id = c.parent_record_id;

  RETURN v_ret;
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

MurraySobol · Apr 16, 2012

I tried you suggestion but got this error:
F_GET_NAME_ADDRESS_L
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected

1 row selected.

I dont know how to resolve the above error.

LKBrwnDBA · Apr 16, 2012

Post some sample data...
[ponder]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

LKBrwnDBA · Apr 16, 2012

PS: Perhaps all you need to add is the NOCYCLE option:
Change and try this:

Code:

  CONNECT BY NOCYCLE PRIOR c.name_and_address_id = c.parent_record_id;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Oracle procedure returning a table

MurraySobol

Technical User

Dagon

MIS

Dagon

MIS

MurraySobol

Technical User

Dagon

MIS

MurraySobol

Technical User

Dagon

MIS

MurraySobol

Technical User

LKBrwnDBA

MIS

LKBrwnDBA

MIS

MurraySobol

Technical User

LKBrwnDBA

MIS

LKBrwnDBA

MIS

Similar threads

Part and Inventory Search

Sponsor