1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help pl/sql

Discussion in 'SQL PL/SQL' started by gumacuk15, Dec 1, 2015.

  1. gumacuk15

    gumacuk15 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tallinn
    Code (SQL):
    /*
    CREATE table email (id NUMBER PRIMARY KEY, eesnimi varchar2(25), perenimi varchar2(25), email varchar2(35));
    insert into email values (1,'Vaino','Erilaid','');
    insert into email values (2,'Kristo','Harakas','');
    insert into email values (3,'Priit','Joorits','');
    insert into email values (4,'Paul','Joorits','');
    insert into email values (5,'Indrek','Melon','');
    insert into email values (6,'Kristi','Karusaar','');
    insert into email values (7,'Kristjan','Laukasaar','');
    insert into email values (8,'TГµnis','Memmo','');
    insert into email values (9,'Veigo','Lend','');
    insert into email values (10,'Tarmo','Huvtre','');
    */

    CREATE OR REPLACE FUNCTION create_email (eesnimi IN VARCHAR2, perenimi IN VARCHAR2, n IN NUMBER, m IN NUMBER)
    RETURN VARCHAR2
      IS
      BEGIN
      RETURN (REPLACE(REPLACE(REPLACE(REPLACE(LOWER(SUBSTR(eesnimi,1,n))||''||LOWER(SUBSTR(perenimi,1,m)),'?','o'),'?','a'),'?','o'),'?','u')||'@it.ee');
      END create_email;
    .
    SQL> /
    FUNCTION created.
    SQL> UPDATE email SET email =NULL;
    7 ROWS updated.
    SQL> DECLARE
      rec_eesnimi  email.eesnimi%TYPE;
      rec_perenimi  email.perenimi%TYPE;
      CURSOR  eposti_cursor  IS
                        SELECT eesnimi, perenimi
                        FROM email
                        WHERE email IS NULL;
      BEGIN
              OPEN eposti_cursor;
              LOOP
                       FETCH eposti_cursor INTO rec_eesnimi, rec_perenimi;
                       EXIT WHEN eposti_cursor%NOTFOUND;
                        BEGIN
                            UPDATE email SET email = create_email(rec_eesnimi, rec_perenimi, 1, 7)
                            WHERE eesnimi = rec_eesnimi AND perenimi = rec_perenimi;
                            COMMIT;
                                EXCEPTION
                                WHEN DUP_VAL_ON_INDEX
                                THEN
                                    UPDATE email SET email = create_email(rec_eesnimi, rec_perenimi, 2, 6)
                                    WHERE eesnimi = rec_eesnimi AND perenimi = rec_perenimi;
                                    COMMIT;
                            END;
             END LOOP;
           CLOSE eposti_cursor;
    END;
    .
    SQL> /

    Help please, my program creates emails for users.

    I have a problem with DUP_VAL_ON_INDEX THEN....

    My program creates always "pjoorits@it.ee" email for users:
    insert into email values (3,'Priit','Joorits','');
    insert into email values (4,'Paul','Joorits','');

    But it must create "pjoorits@it.ee" for:
    insert into email values (3,'Priit','Joorits','');

    And must create "pajoorit@it.ee" for:
    insert into email values (4,'Paul','Joorits','');

    That i should change in my program for this?
     
  2. gumacuk15

    gumacuk15 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tallinn
    delete post, please i found my problem)
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Can you please post your solution here. By that it will be helpful to others.