Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Help with Syntax Please

    Hello again,

    Could someone please help me correct/modify this syntax so it works.
    Code:
    CREATE OR REPLACE PROCEDURE testing123
                                      (User_ID OUT INT,
                                       Hardware_ID OUT INT,
                                       Approver_ID OUT INT,
                                       First_Name IN VARCHAR2,
                                       Last_Name IN VARCHAR2,
                                       Email IN VARCHAR2,
                                       Extension IN VARCHAR2,
                                       Last_Modified_By IN VARCHAR2,
                                       Required_Date IN DATE,
                                       Equipment IN VARCHAR2,
                                       Purpose IN VARCHAR2,
                                       Return_Date IN DATE,
                                       Approver_Name IN VARCHAR2,
                                       Approver_Email IN VARCHAR2)
                                   
    IS
    BEGIN
    if (First_name and Last_Name and email) <> (Select first_name, Last_Name, Email from users) Then
    
    INSERT INTO Users (User_ID, First_Name, Last_Name, Email, Extension,   Create_Date, Last_Modified_By)
    VALUES (Corp_UserID_seq.nextval, First_Name, Last_Name, Email, Extension, sysdate, Last_Modified_By)
    RETURNING User_ID INTO User_ID;
    INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
    VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
    RETURNING Hardware_ID INTO Hardware_ID;
    INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
    VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
    RETURNING Approver_ID INTO Approver_ID;
    
    else
    
    INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
    VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
    RETURNING Hardware_ID INTO Hardware_ID;
    INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
    VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
    RETURNING Approver_ID INTO Approver_ID;
    END IF;
    COMMIT;    
    END;
    Is this not possible? I basically need to say that if some of my parameters(First_Name, last_name and email) do not match what is currently in my users database table then do something...........else do something else.....

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    create a unique index on your users database on first_name,last_name, and email then write the routine as follows

    Code:
    CREATE OR REPLACE PROCEDURE testing123
                                      (User_ID OUT INT,
                                       Hardware_ID OUT INT,
                                       Approver_ID OUT INT,
                                       First_Name IN VARCHAR2,
                                       Last_Name IN VARCHAR2,
                                       Email IN VARCHAR2,
                                       Extension IN VARCHAR2,
                                       Last_Modified_By IN VARCHAR2,
                                       Required_Date IN DATE,
                                       Equipment IN VARCHAR2,
                                       Purpose IN VARCHAR2,
                                       Return_Date IN DATE,
                                       Approver_Name IN VARCHAR2,
                                       Approver_Email IN VARCHAR2)
                                   
    IS
    BEGIN
    
      INSERT INTO Users (User_ID, First_Name, Last_Name, Email, Extension,   Create_Date, Last_Modified_By)
      VALUES (Corp_UserID_seq.nextval, First_Name, Last_Name, Email, Extension, sysdate, Last_Modified_By)
      RETURNING User_ID INTO User_ID;
    
      INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
      VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
      RETURNING Hardware_ID INTO Hardware_ID;
    
      INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
      VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
      RETURNING Approver_ID INTO Approver_ID;
    
      COMMIT;    
    
    exception
      when dup_val_on_index then
    
        select a.user_id
        into user_id
        from users a
        where a.first_name = first_name
        and   a.last_name = last_name
        and   a.email     = email;
    
      INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
      VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
      RETURNING Hardware_ID INTO Hardware_ID;
    
      iNSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
      VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
      RETURNING Approver_ID INTO Approver_ID;
    COMMIT;    
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Beilstwh,

    Thanks so much for the response. I tried what you said but I am getting an error when creating the unique index on the users table. I also tried deleting the table and recreating it but still no success. The error I am getting is as follows:

    Code:
    Error in create Index
    There was an error creating the INDEX:
    java.sql.SQLException: ORA-01450: Maximum key length(6398) exceeded
    I have googled this error but I really don't understand what it is saying
    Code:
    ORA-01450: maximum key length (string) exceeded 
    Cause: The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system. The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1. 
    Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system. See also your operating system-specific Oracle documentation
    Your help is again appreciated. Thanks

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What are the lengths of your three columns? Your email doesn't have to be over 128 characters and the first and last name having a maximum of 100 characters each would be fine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Oh man,

    This is fantastic. Beilstwh I really appreciate this. You have helped me immensely. I didn't know it was the character length issue. I did what you suggested and works perfect. I will now change all my field sizes. Thanks again Beilstwh. Is there no way to reward users on this site because I would like to reward you? Thanks again.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your thanks are more then enough. It might sound silly, but I really enjoy helping other programmers, just like they help me when I have a problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by MrhelpMe
    Is there no way to reward users on this site because I would like to reward you?
    Some of us accept PayPal
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2006
    Posts
    140
    Hi Experts,

    Sorry I need to reopen this issue. Beilstwh offered an excellent example that I have been using however now during user testing I have encountered the following error
    Code:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    [Oracle][ODBC][Ora]ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "WEBAPPS.PROC_DETAILS_INFO_INSERT", line 41 ORA-00001: unique constraint (WEBAPPS.USERS_INDEX) violated ORA-06512: at line 1
    What is happening is I selected 3 users with the same last name and for some reason 2 of the 3 users got entered i.e Jim Bean and Sly Bean but Lina Bean crashed. Any ideas on database design and constraints for a user table and anyway to fix this? I was thinking 2 users could have the same name. Should I just put the constraint on the email? How do others do this? Thanks again and sorry for the inconvenience.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since you've not followed many/most of the posting guidelines as enumerated in the #1 STICKY post at the top of this forum,
    I am not going waste my time trying to guess what you are doing & what migt be a possible solution.
    http://www.dbforums.com/showthread.php?t=1031644
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Could you paste in the insert, I need to see what you are doing to figure out what the problem might be.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Hello beilstwh and thank you thank you.
    The insert is the same as the original post nothing has changed. It is the same stored procedure. You offered the help to add the below stored procedure and then create a unique index on my users database table on first_name,last_name, and email. I did exactly this. The code is exactly like you told me. Thanks again.
    Code:
    CREATE OR REPLACE PROCEDURE testing123
                                      (User_ID OUT INT,
                                       Hardware_ID OUT INT,
                                       Approver_ID OUT INT,
                                       First_Name IN VARCHAR2,
                                       Last_Name IN VARCHAR2,
                                       Email IN VARCHAR2,
                                       Extension IN VARCHAR2,
                                       Last_Modified_By IN VARCHAR2,
                                       Required_Date IN DATE,
                                       Equipment IN VARCHAR2,
                                       Purpose IN VARCHAR2,
                                       Return_Date IN DATE,
                                       Approver_Name IN VARCHAR2,
                                       Approver_Email IN VARCHAR2)
                                   
    IS
    BEGIN
    
      INSERT INTO Users (User_ID, First_Name, Last_Name, Email, Extension,   Create_Date, Last_Modified_By)
      VALUES (Corp_UserID_seq.nextval, First_Name, Last_Name, Email, Extension, sysdate, Last_Modified_By)
      RETURNING User_ID INTO User_ID;
    
      INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
      VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
      RETURNING Hardware_ID INTO Hardware_ID;
    
      INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
      VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
      RETURNING Approver_ID INTO Approver_ID;
    
      COMMIT;    
    
    exception
      when dup_val_on_index then
    
        select a.user_id
        into user_id
        from users a
        where a.first_name = first_name
        and   a.last_name = last_name
        and   a.email     = email;
    
      INSERT INTO Hardware (Hardware_ID, User_ID, Required_Date, Description, Purpose, Return_Date, Create_Date, Last_Modified_By)
      VALUES(Corp_HardwareID_seq.nextval, User_ID, Required_Date, Equipment, Purpose, Return_Date, sysdate,Last_Modified_By)
      RETURNING Hardware_ID INTO Hardware_ID;
    
      iNSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Approver_Email, Create_Date, Last_Modified_By) 
      VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, Approver_Email, sysdate,Last_Modified_By)
      RETURNING Approver_ID INTO Approver_ID;
    COMMIT;    
    END;






    You told me before to add this stored procedure and then create a unique index on the users tables on

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Did you sequence get reset. Try the following

    select Corp_UserID_seq.nextval from dual;

    select count(*)
    from users
    where use_id = <number returned by above query>;

    If the count is not zero, someone reset your sequence or manually inserted into your users table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Apr 2006
    Posts
    140
    No my sequence did not get reset. I tried what you said adding 76 as the value and I got zero. Any other ideas Beilstwh? Thanks again for all your help.

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    sure, Issue the following test and let me know if anything is found.

    select first_name,last_name,email
    from users
    group by first_name,last_name,email
    having count(*) > 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Apr 2006
    Posts
    140
    Hello again,

    I get no data returned when I issue the code that you mentioned. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •