Home » SQL & PL/SQL » SQL & PL/SQL » Help required on assigning value to Tabletype variable!
Help required on assigning value to Tabletype variable! [message #672263] Tue, 09 October 2018 04:29 Go to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
Hi All,
could anyone tell me other possibility of assigning the value into the tabletype variable in pl/sql.

create table emp_test(id number,name varchar2(20),deptno number, sal number)

I have created the below package for inserting data into emp_test table.

Package Specification
create or replace 
PACKAGE employee_details
    AS
    TYPE details
    IS
      RECORD
      (
         P_EMP_ID number,
         P_NAME   varchar2(40),
         P_SAL number);
   TYPE tab_employees
   IS
     table of DETAILS;
     p_deptno number;
     procedure write_employees(
         P_DEPTNO in number,
         p_tab_employees IN tab_employees );
END employee_details;


PACKAGE BODY
   create or replace 
PACKAGE BODY employee_details
    AS
    PROCEDURE write_employees(
          P_DEPTNO in number,
         p_tab_employees IN tab_employees)
    IS
    begin
DBMS_OUTPUT.PUT_LINE('test count' || P_TAB_EMPLOYEES.COUNT );
for I in 1..P_TAB_EMPLOYEES.COUNT LOOP
insert into EMP_TEST(id,name,DEPTNO,SAL) values(
P_TAB_EMPLOYEES(i).P_EMP_ID,P_TAB_EMPLOYEES(i).P_NAME,P_DEPTNO,P_TAB_EMPLOYEES(i).P_SAL
);

end loop;
   end WRITE_EMPLOYEES;
END employee_details ;

using the below procedure for passing the value to write into the table.

 DECLARE
  P_DEPTNO number;
  P_TAB_EMPLOYEES EMPLOYEE_DETAILS.TAB_EMPLOYEES:=EMPLOYEE_DETAILS.TAB_EMPLOYEES();
BEGIN
  P_DEPTNO := 10;


P_TAB_EMPLOYEES.extend;
P_TAB_EMPLOYEES(1).P_EMP_ID:=125;
P_TAB_EMPLOYEES(1).P_NAME:='Scott';
P_TAB_EMPLOYEES(1).P_SAL:=1000;


P_TAB_EMPLOYEES.extend;
P_TAB_EMPLOYEES(2).P_EMP_ID:=126;
P_TAB_EMPLOYEES(2).P_NAME:='Henry';
P_TAB_EMPLOYEES(2).P_SAL:=2000;

  EMPLOYEE_DETAILS.WRITE_EMPLOYEES(
    P_DEPTNO => P_DEPTNO,
    P_TAB_EMPLOYEES => P_TAB_EMPLOYEES
  );
end;

My query is instead of passing the value as below, do we have any option to pass in a single line. I mean all P_EMP_ID,P_NAME,P_SAL in a single line.
P_TAB_EMPLOYEES(1).P_EMP_ID:=125;
P_TAB_EMPLOYEES(1).P_NAME:='Scott';
P_TAB_EMPLOYEES(1).P_SAL:=1000;


Re: Help required on assigning value to Tabletype variable! [message #672264 is a reply to message #672263] Tue, 09 October 2018 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
P_TAB_EMPLOYEES(1) := details(125, 'Scott', 1000);
Re: Help required on assigning value to Tabletype variable! [message #672265 is a reply to message #672264] Tue, 09 October 2018 05:43 Go to previous messageGo to next message
ramya29p
Messages: 122
Registered: November 2007
Location: Chennai
Senior Member
I have already tried as below

P_TAB_EMPLOYEES(1) := EMPLOYEE_DETAILS.TAB_EMPLOYEES(125, 'Scott', 1000);

But getting Error as "PLS-00306: wrong number or types of arguments in call to 'TAB_EMPLOYEES'"
Re: Help required on assigning value to Tabletype variable! [message #672267 is a reply to message #672265] Tue, 09 October 2018 06:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
PL/SQL RECORD type doesn't have constructor, so cookiemonster's suggestion will not work for recordds. You have to assign value to each record attribute separately as you were doing originally.

SY.

Re: Help required on assigning value to Tabletype variable! [message #672268 is a reply to message #672267] Tue, 09 October 2018 08:05 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
I used the record type name, not the table type name on the right hand side for reason.
If it was a SQL type (which as Solomon points out, it isn't) then my code would have worked.
Your version wouldn't work in any circumstances.
Re: Help required on assigning value to Tabletype variable! [message #672269 is a reply to message #672265] Tue, 09 October 2018 08:22 Go to previous message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
RECORD type is targeted to fetching data. So you could do something like:

DECLARE
    P_TAB_EMPLOYEES EMPLOYEE_DETAILS.TAB_EMPLOYEES:=EMPLOYEE_DETAILS.TAB_EMPLOYEES();
BEGIN
    P_TAB_EMPLOYEES.EXTEND;
    SELECT 125,'Scott',1000
      INTO P_TAB_EMPLOYEES(1)
      FROM DUAL;
END;
/

However performance will be slightly worse than straight assignment.

SY.
Previous Topic: How to merge 2 rows from a table to 1 row (2 columns)
Next Topic: https://community.oracle.com/message/14958599#14958599
Goto Forum:
  


Current Time: Fri Oct 19 23:20:55 CDT 2018