How to get the values from the Input parameter of (Object Type Table) in procedure in Oracle PL SQL?

Question

Please anyone can assist me. I have a table type object.

CREATE OR REPLACE TYPE SI_PHONE_ACC_T AS TABLE OF SI_PHONE_ACC;

CREATE OR REPLACE TYPE SI_PHONE_ACC AS OBJECT
(
  PHONE_ACC  VARCHAR2(15);
);

Below is generally passed to the procedure as an input from UI end.

Example:

SI_PHONE_ACCT_T( SI_PHONE_ACC('123-345-6543'),SI_PHONE_ACC('999-999-9999'), SI_PHONE_ACC( 'ax878974545787wp')); -- first 2 are phone no., 3rd is account no.

Phonetable:

Emp_id--- phone_number

1 --     123-345-6543
2 --     999-999-9999
3---     897-897-8781

Account table:

Emp_id---  account_number

10 --     A0000
20 --     B0000
30---     ax878974545787wp

CREATE OR REPLACE PACKAGE BODY order_mgr 
IS

PROCEDURE ins_trees (  p_emp_details_in    IN  SI_PHONE_ACC_T )
BEGIN

 -- Now, I need to retrieve emp_ids from Phone and Account tables based on the phone or account numbers passed in the input parameter. please let me know how to do this.
 
---once I get those emp_id's, i need to insert into employee table which contains only one column emp_id.

--Please let me know how to do this.

FOR i IN 1 .. p_emp_details_in.count 
LOOP  
    INSERT into employee (emp_id)
      values(??);
  END IF;
END LOOP;
    
END;

show us describe Phonetable please. Is column phone_number really varchar2? — Sayan Malakshinov
– Sayan Malakshinov, Commented Jul 14, 2020 at 16:58
that's the example bascially. but as SI_PHONE_ACC_T type contains both the phone and account, i need something as varchar2 only. the actual table contains different values with varchar2 only. — Anitha
– Anitha, Commented Jul 14, 2020 at 17:03

Sayan Malakshinov · Accepted Answer · 2020-07-14 17:05:25Z

1

PROCEDURE ins_trees (  p_emp_details_in    IN  SI_PHONE_ACC_T )
BEGIN

    INSERT into employee (emp_id)
      select Emp_id 
      from table(p_emp_details_in) t
         , Phone_table t2 
      where t.PHONE_ACC = t2.phone_number
      union
      select Emp_id 
      from table(p_emp_details_in) t
         , Account_table t2 
      where t.PHONE_ACC = t2.account_number
      ;
    
END;
/

answered Jul 14, 2020 at 17:05

Sayan Malakshinov

8,7501 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get the values from the Input parameter of (Object Type Table) in procedure in Oracle PL SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related