Home » SQL & PL/SQL » SQL & PL/SQL » How to Create variable out of Table Type Variable!
How to Create variable out of Table Type Variable! [message #676609] Mon, 24 June 2019 05:50 Go to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Hi,
Can we create a variable for a Table type variable ?

create table emp_test(id number,name varchar2(20),deptno number, sal number); 
 
insert into emp_test values(751,'Ward',30,1000);
insert into emp_test values(752,'Allen',30,1000);
insert into emp_test values(753,'turner',30,1000);
In the below Package I am creating another variable "v_typ_tab_dept_no" for table type variable "typ_tab_dept_no". Then using the "v_typ_tab_dept_no" as input parameter.
create or replace 
PACKAGE employee_details
    AS
    
    TYPE typ_tab_dept_no IS TABLE OF emp_test.deptno%type;
    
    v_typ_tab_dept_no typ_tab_dept_no:= typ_tab_dept_no();
    
    
    TYPE details
    IS
      RECORD
      (
        p_name   VARCHAR2(40),
        p_emp_id NUMBER );
        
   TYPE table_employees
   IS
     TABLE OF details;
     
     procedure get_employees(
         p_deptno in v_typ_tab_dept_no,
         p_sal IN emp_test.sal%TYPE,
         emp_rec OUT table_employees );
         
   END employee_details;

But I am getting an error as "Error(20,22): PLS-00488: 'V_TYP_TAB_DEPT_NO' must be a type".

Could anyone help me how to create a variable out of Table type variable.
Re: How to Create variable out of Table Type Variable! [message #676610 is a reply to message #676609] Mon, 24 June 2019 06:45 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Added Package Body

create or replace 
PACKAGE BODY employee_details
    AS
    PROCEDURE get_employees(
          p_deptno IN v_typ_tab_dept_no,
          p_sal IN emp_test.sal%TYPE,
          emp_rec OUT table_employees )
    IS
    begin
     select name, id bulk collect into emp_rec from emp_test et
     inner join table(p_deptno) t1
      ON et.deptno = t1.column_value
     where  sal > p_sal;
   END get_employees;
   END employee_details ;
   
Re: How to Create variable out of Table Type Variable! [message #676612 is a reply to message #676610] Mon, 24 June 2019 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table EMP_TEST as select * from emp;

Table created.

SQL> create or replace
  2  PACKAGE employee_details
  3      AS
  4
  5      TYPE typ_tab_dept_no IS TABLE OF emp_test.deptno%type;
  6
  7      v_typ_tab_dept_no typ_tab_dept_no:= typ_tab_dept_no();
  8
  9
 10      TYPE details
 11      IS
 12        RECORD
 13        (
 14          p_name   VARCHAR2(40),
 15          p_emp_id NUMBER );
 16
 17     TYPE table_employees
 18     IS
 19       TABLE OF details;
 20
 21       procedure get_employees(
 22           p_deptno in v_typ_tab_dept_no,
 23           p_sal IN emp_test.sal%TYPE,
 24           emp_rec OUT table_employees );
 25
 26     END employee_details;
 27  /

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE EMPLOYEE_DETAILS:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
20/6     PL/SQL: Declaration ignored
21/22    PLS-00488: 'V_TYP_TAB_DEPT_NO' must be a type
Line 22, you give a variable name for parameter type instead of the data type for p_deptno:
SQL> create or replace
  2  PACKAGE employee_details
  3      AS
  4
  5      TYPE typ_tab_dept_no IS TABLE OF emp_test.deptno%type;
  6
  7      v_typ_tab_dept_no typ_tab_dept_no:= typ_tab_dept_no();
  8
  9
 10      TYPE details
 11      IS
 12        RECORD
 13        (
 14          p_name   VARCHAR2(40),
 15          p_emp_id NUMBER );
 16
 17     TYPE table_employees
 18     IS
 19       TABLE OF details;
 20
 21       procedure get_employees(
 22           p_deptno in typ_tab_dept_no,
 23           p_sal IN emp_test.sal%TYPE,
 24           emp_rec OUT table_employees );
 25
 26     END employee_details;
 27  /

Package created.
Re: How to Create variable out of Table Type Variable! [message #676613 is a reply to message #676610] Mon, 24 June 2019 07:12 Go to previous messageGo to next message
BlackSwan
Messages: 26616
Registered: January 2009
Location: SoCal
Senior Member
Don't do in PL/SQL that which can be done in plain SQL.
Holding & manipulating data within session PGA totally defeats the goal of supporting a concurrent & multi-user OLTP environment.
What happens if & when table data is changed after the data is read into local variable?
Re: How to Create variable out of Table Type Variable! [message #676614 is a reply to message #676612] Mon, 24 June 2019 11:22 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Hi,
My requirement is UserA has a tabletype variable defined in a Package. This package has been granted to UserB. UserB package has a procedure with input parameter of UserA tabletype variable. I want to run UserB procedure from UserC. How can I refer tabletype variable of UserA from UserC.
Re: How to Create variable out of Table Type Variable! [message #676615 is a reply to message #676614] Mon, 24 June 2019 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what first thanking for the help I gave you?

Re: How to Create variable out of Table Type Variable! [message #676616 is a reply to message #676615] Mon, 24 June 2019 11:29 Go to previous messageGo to next message
ramya29p
Messages: 144
Registered: November 2007
Location: Chennai
Senior Member
Thank you Razz for your help. Could you please tell me the possible solution toy requirement.
Re: How to Create variable out of Table Type Variable! [message #676617 is a reply to message #676614] Mon, 24 June 2019 12:38 Go to previous message
BlackSwan
Messages: 26616
Registered: January 2009
Location: SoCal
Senior Member
ramya29p wrote on Mon, 24 June 2019 09:22
Hi,
My requirement is UserA has a tabletype variable defined in a Package. This package has been granted to UserB. UserB package has a procedure with input parameter of UserA tabletype variable. I want to run UserB procedure from UserC. How can I refer tabletype variable of UserA from UserC.
Why don't you direct your question to the individual who wrote the requirement since they obviously know how to do it?
Previous Topic: Incorrect work of the sql query with JOIN.
Next Topic: Automatic abropting the long running job
Goto Forum:
  


Current Time: Wed Nov 13 21:09:21 CST 2019