Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Pass varray variables into stored procedure - basic
- create table emails (
- user_id varchar2(10),
- friend_name varchar2(20),
- email_address varchar2(20));
- create or replace type email_array as varray(100) of varchar2(20);
- /
- show errors
- create or replace type friend_array as varray(100) of varchar2(20);
- /
- show errors
- create or replace procedure update_address_book(
- p_user_id in varchar2,
- p_friend_name friend_array,
- p_email_addresses email_array)
- is
- begin
- delete from emails where user_id = p_user_id;
- forall i in indices of p_email_addresses
- insert into emails (user_id, friend_name, email_address)
- values (p_user_id, p_friend_name(i), p_email_addresses(i));
- end update_address_book;
- declare
- type email_list is varray(100) of varchar2(20);
- type friend_list is varray(100) of varchar2(20);
- emails email_list;
- friends friend_list;
- begin
- emails :=email_list('khwaja@gmail.com','sayya@gmail.com','mayya@gmail.com');
- friends := friend_list('kwaja','sayya','mayya');
- execute update_address_book('1',emails,friends);
- end;
- declare
- emails email_array;
- friends friend_array;
- begin
- emails := email_array('khwaja@gmail.com','sayya@gmail.com','mayya@gmail.com');
- friends := friend_array('kwaja','sayya','mayya');
- update_address_book('1',friends,emails);
- end;
- /
Add Comment
Please, Sign In to add comment