SQL & PL/SQL

I want to create a table type of the row type of some of my table.

For example:
     create or replace type tmp_Road is table of XUXUEHAN.tbl_Road%rowtype

But I get PLS-00201: identifier 'XUXUEHAN.tbl_Road' must be declared.

Where did I go wrong?
 

You can't use %rowtype in SQL - that is a PL/SQL construct.

You need to explicitly specify each column as an attribute.

Sorry, but i don't quite follow you.  Where should i use %rowtype?

You can't use %rowtype in SQL - that is a PL/SQL construct.

It means that %rowtype can only be used in procedures, packages, anonymous blocks etc.

In SQL,you shall first need to create the object type for the table containing all its columns e.g.

CREATE TYPE textdoc_typ AS OBJECT
    ( document_typ      VARCHAR2(32)
    , formatted_doc     BLOB
    ) ;

Then you create the nested table type e.g.

CREATE OR REPLACE TYPE T IS TABLE OF textdoc_typ

Thanks.

Actually, I need to write a function that return some valid records from a table. My table has 60+ columns, is there any convenient way to write the function?

Xuehan Xu wrote:

Thanks.

Actually, I need to write a function that return some valid records from a table. My table has 60+ columns, is there any convenient way to write the function?

Do you want your function to return a set of record from a table?

I guess you can use PL/SQL Tables and User-Defined Records

Try to go through some examples for the same and implement it.

Br,

Zaif

Actually, I need to write a function that return some valid records from a table.

If you want to use function, then you can use %rowtype, something like below:

create or replace function .... returns tmp_road as
   type tmp_road is table of xuxuehan.tbl_road%rowtype;
begin
 fetch your valid records into the collection
 return the collection;
end;

Hi, Moazzam

I tried your advice, but I get this error:

PLS-00498: Illegal use of a type before its declaration.

Hi, Zapex.

Thanks for your advice.

But I need to call the function from my application. Examples in PL/SQL Tables and User-Defined Records are all functions that used within the same pl/sql block.

How can I create the function that can be called from my app?

PLS-00498: Illegal use of a type before its declaration.

I do not know how are you using type in the function. See below code for yours reference:

CREATE OR REPLACE TYPE emp_t AS OBJECT(empno varchar2(100), sal number);
/
CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_t
/
CREATE OR REPLACE FUNCTION test_employee RETURN emp_tab AS
   v_empno   VARCHAR2 (100);
   v_sal     NUMBER;
   tab       emp_tab := emp_tab ();
   counter   NUMBER := 1;
   CURSOR c
   IS
      SELECT empno, sal FROM emp;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO v_empno, v_sal;
      EXIT WHEN c%NOTFOUND;
      tab.EXTEND;
      tab (counter) := emp_t (v_empno, v_sal);
      counter := counter + 1;
   END LOOP;
   CLOSE c;
   RETURN tab;
END;
/

SELECT * FROM TABLE(test_employee);

EMPNO                       SAL
-------------------- ----------
7369                        800
7499                       1600
7521                       1250
7566                       2975
7654                       1250
7698                       2850
7782                       2450
7788                       3000
7839                       5000
7844                       1500
7876                       1100
7900                        950
7902                       3000
7934                       1300
7934                       1300

CREATE OR REPLACE TYPE emp_t AS OBJECT(empno varchar2(100), sal number);

Can I save the burden to declare every element of the object? My table has 60+ columns, and I've already created the table in database.

Can I save the burden to declare every element of the object? My table has 60+ columns, and I've already created the table in database.

It depends as how are you using the collection returned. Can you elaborate as where are you using this function and for what purpose.

Hi,

As questioned previously, it depends on how you want to use the results.

There is a means to avoid "the burden of declaring every element" by using a packaged procedure.

See if code below can help you:

CREATE OR REPLACE PACKAGE xxpkg
AS
TYPE list_employees_t IS TABLE OF employees%ROWTYPE
  INDEX BY BINARY_INTEGER;
--
PROCEDURE p_get_employees (pt_employees IN OUT NOCOPY list_employees_t);
END xxpkg;
/


CREATE OR REPLACE PACKAGE BODY xxpkg
AS
PROCEDURE p_get_employees (pt_employees IN OUT NOCOPY list_employees_t) AS
  i NUMBER := 1;
BEGIN
   FOR c IN (SELECT * FROM employees)
   LOOP
      pt_employees(i)  := c;
      i := i+1;
   END LOOP;
END p_get_employees;
--
END xxpkg;
/


SET SERVEROUT ON
DECLARE
lt_emp   xxpkg.list_employees_t;
BEGIN
xxpkg.p_get_employees(lt_emp);
FOR i IN 1 .. lt_emp.COUNT
LOOP
  dbms_output.put_line('Emp.Id='||lt_emp(i).employee_id ||' Name='||lt_emp(i).last_name);
END LOOP;
END;
/


Emp.Id=100 Name=King
Emp.Id=101 Name=Kochhar
Emp.Id=102 Name=De Haan
Emp.Id=103 Name=Hunold
Emp.Id=104 Name=Ernst
Emp.Id=105 Name=Austin
Emp.Id=106 Name=Pataballa
Emp.Id=107 Name=Lorentz
Emp.Id=108 Name=Greenberg
Emp.Id=109 Name=Faviet
Emp.Id=110 Name=Chen
...

Thanks:-)

Hi RajenB

I created a package like this:

create or replace package test_x

as

  type tmp_Road is table of XUXUEHAN."tbl_Road"%ROWTYPE;

   function get(p_id number) return tmp_Road;

end;

/

create or replace package body test_x

as

function get(p_id number) return tmp_Road as

  tab tmp_Road := tmp_Road();

begin

  select * bulk collect into tab from XUXUEHAN."tbl_Road" where "ID" = p_id;

  return tab;

end;

end test_x;

But when I called it like "select * from TABLE(test_x.get('1'));", the compiler always says that "invalid datatypes".  Where did I go wrong? Thanks.

Actually, I need to write a function that return some valid records from a table. My table has 60+ columns, is there any convenient way to write the function?
. . .
But I need to call the function from my application

So now you finally tell us what the REAL problem is that you are trying to solve.

But why did you mark an answer CORRECT that uses a function that you can NOT call from your application? That makes no sense to me.

Only SQL types are exposed and can be used outside of PL/SQL.

Functions that return data to clients should normally return a REF CURSOR to the client. The client then FETCHES the data using the cursor.

If you actually need to return rows of data you should use a PIPELINED FUNCTION.

If you had told us to begin with what you really needed to do I could have just given you the link to this thread where I answer that question:

https://community.oracle.com/thread/2461722?tstart=0

The example code I provide in that thread uses %rowtype in PL/SQL but because it is a pipelined function Oracle creates hidden SQL types (in 11g) behind the scenes to make it work properly.

You can see those 'hidden' types if you query 'user_objects'. They will have names prefixed with 'SYS_PLSQL_'. See this thread from two years ago for another example. The reply by MichaelS of Jun 29, 2012 5:08 PM shows how to view the 'hidden' types.

. Re: Pipe line function 
 
 MichaelS Jun 29, 2012 5:08 PM (in response to Solomon Yakobson) Currently Being Moderated 
in 11g such types are hidden and it is a bit more work to show themThey are still visible in user_objects though:
SQL> select * from v$version where rownum = 1
/
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
1 row selected.
SQL> select object_name from user_objects where object_type = 'TYPE'
/
OBJECT_NAME                                                                     
--------------------------------------------------------------------------------
SYS_PLSQL_292346_DUMMY_1                                                        
SYS_PLSQL_292346_9_1                                                            
SYS_PLSQL_292345_DUMMY_1                                                        
SYS_PLSQL_292345_66_1                                                           
4 rows selected.

Oracle creates those 'hidden' types to support PIPELINED functions. If you examine the DDL for those types you will see that Oracle has created SQL types that match the definitions of the PL/SQL types that were used in the code. One of those PL/SQL types was defined with %ROWTYPE; so that is one way to define a SQL type without using all of the column names - let Oracle do it for you and then just copy the DDL and rename the type.

Next time you ask a question tell us the actual PROBLEM you are trying to solve; not the solution you think you want to use. Because you didn't do that you delayed getting the real 'correct' answer to your question.

Remember - people can only help you base on the information you post. When you post wrong or incomplete information you are likely to get: 1) no help, 2) help with the wrong question or 3) help that won't really solve your problem.

But when I called it like "select * from TABLE(test_x.get('1'));", the compiler always says that "invalid datatypes".  Where did I go wrong? Thanks.

I've told you twice now where you went 'wrong'.

You can NOT use PL/SQL types in SQL. The problem with RajenB's solution (which you marked 'correct') is that it is NOT a PIPELINED function.

Hi, rp0428

I need to call it in a "select" statment, because the interactive report in APEX required that. I'm trying to build an interactive report that can select those rows that with their IDs equal to the IDs that I passed to it. But, as the number of IDs that provided to the interactive report is unknown at the time of building the application. So, I need to write a function.

Is there any way to do this?

I can't help you if you won't take the time to actually read the information I gave you.

I gave you a link directly to actual working sample code that does what you now say you need to do.

I need to call it in a "select" statment, because the interactive report in APEX required that. 

That sample code uses a 'select' statement. Did you look at that code?

select * from table(pkg4.get_emp(20));

You aren't going to get the help you need if you don't tell us, in English, what PROBLEM you are really trying to solve.

So far you:

1. ask how to create a SQL type that uses %ROWTYPE - I told you that isn't possible.

2. ask how to create a function that you can call in SQL to return data and then you mark as correct an answer that won't work when called from SQL

3. changed your question to ask about returning data from a function - I told you to return a REF CURSOR or use a PIPELINED function

4. now you switch to passing a list of IDs to a function that you want to call from SQL

I'm trying to build an interactive report that can select those rows that with their IDs equal to the IDs that I passed to it. But, as the number of IDs that provided to the interactive report is unknown at the time of building the application. So, I need to write a function.

How is anyone supposed to understand what problem you really have? You keep changing the requirements!

Oracle forms, APEX and other web apps construct LOVs (list of values) all of the time and allow users to multi-select from that list. Then that list is submitted as an array to the server (often a stored procedure) and the selected items are used as a 'table' in a query to get the results.

That is a VERY COMMON usage. The function generally returns a REF CURSOR to the client and the client FETCHES the data. But you can use a PIPELINED function and return the results that way as the example code I gave you shows.

As for getting the LOVs to the procedure/function your APEX would construct a VARRAY or a NESTED TABLE that is defined as a SQL type and pass that as a parameter to the procedure/function. The PIPELINED function would treat that as a table:

SELECT * from myTable where id in (select id from table(p_varray));

As I said - that is a very common solution to your problem.

See the PL/SQL Language doc section 'Understanding PL/SQL Collection Types

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#CHDBHJEI

That table 5-1 shows you which collection types can be used at the schema level - meaning they can be used in SQL.

Do I have to use PIPELINED FUNCTION/?

Sorry for my poor English

I got it, it has to be a pipelined function. Thank you:-)