-- dynamic_bind.sql

CREATE OR REPLACE PROCEDURE new_person
                    (pid OUT person.id%TYPE, 
                     lname   person.lastname%TYPE, 
                     fname   person.firstname%TYPE, 
                     m       person.mi%TYPE,
                     str     person.street%TYPE DEFAULT NULL, 
                     cty     person.city%TYPE DEFAULT NULL,
                     st      person.state%TYPE DEFAULT NULL, 
                     zp      person.zip%TYPE DEFAULT NULL,
                     area    person.area_code%TYPE DEFAULT NULL, 
                     phone   person.phone_number%TYPE DEFAULT NULL)
AS
BEGIN
   pid := person_id_seq.NEXTVAL;
   INSERT INTO person
   VALUES(pid, lname, fname, m, str, cty, st, zp, area, phone);
END;
/
DECLARE
   new_id NUMBER;
   f VARCHAR2(15) := 'Katrina';
   l VARCHAR2(25) := 'Bucci';
   m CHAR :=  'A';
BEGIN
   EXECUTE IMMEDIATE 'BEGIN new_person(:a, :b, :c, :d); END;' 
     USING IN OUT new_id, l, f, m; 
END;
/
