Home » SQL & PL/SQL » SQL & PL/SQL » Selecting data from a Package that returns a Table Set
Selecting data from a Package that returns a Table Set [message #271963] Wed, 03 October 2007 08:34 Go to next message
harveyb
Messages: 5
Registered: October 2007
Junior Member
Hi All,

I'm trying to create a package that returns information built from an sql query (using bulk collect)

this is my code

CREATE OR REPLACE PACKAGE GetClientID
AS
TYPE clientv_id_record_t IS RECORD
(
client_id clientv.client_id%TYPE
);
		
TYPE client_table_t IS TABLE OF clientv_id_record_t;
	
FUNCTION returnClientID
(
input_client_id IN clientv.client_id%TYPE
)
RETURN client_table_t;
END GetClientID;

CREATE OR REPLACE PACKAGE BODY GetClientID
AS
FUNCTION returnClientID
(
input_client_id IN clientv.client_id%TYPE
)
RETURN client_table_t
IS
client_data client_table_t:=client_table_t();
	
BEGIN
SELECT clientv.client_id
BULK COLLECT INTO client_data
FROM dcsdba.clientv
WHERE clientv.client_id=UPPER(input_client_id);
RETURN client_data;		
END;
END GetClientID;


Now if i do returnClientID('myclient') and do a count on the result returned, i get a data count that is of the correct number of records I'm expecting (4 to be precise).

However, I want to do a select statement based off the result set.

So I'm doing this

SELECT client.client_id
FROM TABLE
(
GetClientID.returnClientID('myclient')
AS
GetClientID.client_table_t
)
client;


But this does not work (however I folled this example from the O'Reilly Pl/SQL Oracle 4th Edition Book and they seem to think it works fine).

The error I get is

Error starting at line 1 in command:
SELECT client.client_id FROM
TABLE(GetClientID.returnClientID('myclient') AS GetClientID.client_table_t) client
Error report:
SQL Error: ORA-00907: missing right parenthesis


now it is saying I am missing my bracket here
TABLE(GetClientID.returnClientID('myclient')**HERE**


So it is basically saying why are you trying to cast.

but if i do this

TABLE(GetClientID.returnClientID('myclient')) client


Then it complains and says invalid data type.

Does anyone know what is going wrong?

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #271965 is a reply to message #271963] Wed, 03 October 2007 08:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to use user defined types in SQL, then they have to be declared as types in SQL, not in Pl/Sql.

Your type definitions are in a package header, and SQL has no knowledge or comprehension of them.

You need to use the CREATE OR REPLACE TYPE... syntax to define them in SQL.

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #271967 is a reply to message #271965] Wed, 03 October 2007 08:44 Go to previous messageGo to next message
harveyb
Messages: 5
Registered: October 2007
Junior Member
if i do this

CREATE TYPE clientv_id_record_t IS OBJECT
(
client_id dbuser.clientv.client_id%TYPE
);


it then complains and says

PLS-00329: schema-level type has illegal reference to dbuser.clientv

So now i cant get the type of the row outside of the scope.

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #271969 is a reply to message #271967] Wed, 03 October 2007 08:48 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Nope, you cannot reference schema objects since the data type of a column can change. You need to use a valid SQL data type for your columns.

MHE

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #271973 is a reply to message #271963] Wed, 03 October 2007 09:15 Go to previous messageGo to next message
harveyb
Messages: 5
Registered: October 2007
Junior Member
Ok so Now I'm doing this

CREATE TYPE SPG_TYPE_clientIDRECORD_O_t IS OBJECT
(
client_id VARCHAR2(40)
);


CREATE TYPE SPG_TYPE_clientIDTable_T
IS TABLE OF SPG_TYPE_clientIDRECORD_O_t;


This creates my Types in SQL

now I want to build just a function, no need for a package if my types are externally declared, so I do

CREATE OR REPLACE FUNCTION
SPG_Gen_getClientID_F
(
input_client_id IN dbuser.clientv.client_id%TYPE,
input_account IN dbuser.client_groups.client_group%TYPE
)

RETURN dbuser.SPG_TYPE_clientIDTable_T

AS

client_data
dbuser.SPG_TYPE_clientIDTable_T:=dbuser.SPG_TYPE_clientIDTable_T();

BEGIN

IF UPPER(input_client_id) IS NOT NULL THEN

SELECT clientv.client_id
BULK COLLECT INTO client_data
FROM dbuser.clientv
WHERE clientv.client_id=UPPER(input_client_id);

ELSIF UPPER(input_account) IS NOT NULL THEN

SELECT client_group_clients.client_id
BULK COLLECT INTO client_data
FROM dbuser.client_groups
WHERE client_groups.client_group=UPPER(input_account);

END IF;

RETURN client_data;				

END;


Now i get the error of:


PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR PL/SQL

the returned datatype is of VARCHAR2(40), so they shouldnt be incosistent, any ideas?

[Updated on: Wed, 03 October 2007 09:16]

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #272107 is a reply to message #271973] Thu, 04 October 2007 01:21 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You're very close now but if you look how you define your "SPG_TYPE_clientIDTable_T" you see that it isn't a table of VARCHAR2 or CHAR. You have defined a table of UDT (User Defined Type), namely "SPG_TYPE_clientIDRECORD_O_t IS OBJECT". I've created a small example based on the HR demo schema. Have a look at the construct in the SQL queries: I transform the VARCHAR2 (along with the number and the date) to an UDT.
CREATE TYPE t_emprec IS OBJECT ( last_name   VARCHAR2(20) 
                               , hire_date   DATE
                               , salary      NUMBER
                               );
/
CREATE TYPE t_emptable IS TABLE OF t_emprec;
/

CREATE OR REPLACE FUNCTION get_emps ( input_emp   IN   employees.employee_id%TYPE
                                    , input_dept  IN   employees.department_id%TYPE
                                    )
   RETURN t_emptable
AS
   employee_data   t_emptable := t_emptable();
BEGIN
   IF UPPER (input_emp) IS NOT NULL
   THEN
    
      SELECT t_emprec( e.last_name
                     , e.hire_date
                     , e.salary
                     )
      BULK COLLECT INTO employee_data
      FROM   employees e
      WHERE  e.employee_id = input_emp;
      
   ELSIF input_dept IS NOT NULL
   THEN
    
      SELECT t_emprec( e.last_name
                     , e.hire_date
                     , e.salary
                     )
      BULK COLLECT INTO employee_data
      FROM   employees e
      WHERE  e.department_id = input_dept;
      
   END IF;

   RETURN employee_data;
END get_emps;
/
sho err

SELECT emp.last_name
     , emp.salary
FROM TABLE( get_emps(101, NULL)) emp
/

SELECT *
FROM TABLE( get_emps(NULL, 100)) emp
/

DROP FUNCTION get_emps
/

DROP TYPE t_emptable
/                                       
DROP TYPE t_emprec
/
And thank you for providing the necessary information to us in such a clear way! It's nice to see a thread like this.

MHE

[Updated on: Thu, 04 October 2007 01:21]

Report message to a moderator

Re: Selecting data from a Package that returns a Table Set [message #272110 is a reply to message #272107] Thu, 04 October 2007 01:23 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
An alternative would be to create a simple table type:
CREATE TYPE t_emptable IS TABLE OF VARCHAR2(30);
/

CREATE OR REPLACE FUNCTION get_emps ( input_emp   IN   employees.employee_id%TYPE
                                    , input_dept  IN   employees.department_id%TYPE
                                    )
   RETURN t_emptable
AS
   employee_data   t_emptable := t_emptable();
BEGIN
   IF UPPER (input_emp) IS NOT NULL
   THEN
    
      SELECT e.last_name
      BULK COLLECT INTO employee_data
      FROM   employees e
      WHERE  e.employee_id = input_emp;
      
   ELSIF input_dept IS NOT NULL
   THEN
    
      SELECT e.last_name
      BULK COLLECT INTO employee_data
      FROM   employees e
      WHERE  e.department_id = input_dept;
      
   END IF;

   RETURN employee_data;
END get_emps;
/
sho err

SELECT emp.column_value
FROM TABLE( get_emps(101, NULL)) emp
/

SELECT *
FROM TABLE( get_emps(NULL, 100)) emp
/

DROP FUNCTION get_emps
/

DROP TYPE t_emptable
/
MHE

[Updated on: Thu, 04 October 2007 01:23]

Report message to a moderator

Previous Topic: ORA-00947: not enough values
Next Topic: using dyanamic performance tables in functions and procs
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 06 01:15:58 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.