-1

We need to INSERT or UPDATE data of table consultant_skill , create needed functions, procedures … that accepts consultant id, skill id, and certification status for the task. The procedure should be user friendly enough to handle all possible errors such as consultant id, skill id do not exist OR certification status is different than ‘Y’, ‘N’. Make sure to display: Consultant last, first name, skill description and the confirmation of the DML performed (hint: Do not forget to add COMMIT inside the procedure)

CREATE OR replace PROCEDURE nw (p_c_id          NUMBER, 
                                p_s_id          NUMBER, 
                                p_certification VARCHAR2) 
AS 
  v_c_id          NUMBER := p_c_id; 
  v_s_id          NUMBER := p_s_id; 
  v_certification VARCHAR2(20); 
  flag            NUMBER(3); 
BEGIN 
  SELECT count(*) 
  INTO   flag 
  FROM   consultant_skill 
  WHERE  c_id = v_c_id 
  AND    skill_id = v_s_id; 

  dbms_output.Put_line (flag); 
  IF flag > 0 THEN 
    UPDATE consultant_skill 
    SET    skill_id = p_s_id, 
           certification = p_certification 
    WHERE  c_id = v_c_id; 

  ELSE 
    dbms_output.Put_line ('bye bye'); 
  END IF; 
END; 
/

Stuck on update itself.. yet to try insert in else block.. first trying on update part.. dnt know if it is write or not

But on compiling the samd it is showing ora 00001: unique constraint violated

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
user10997164
  • 35
  • 1
  • 6
  • Hello, and welcome to SO. Please take some time to read [tour], and post a [mcve]. You question too broad. People here expect to see some level of effort put in to the question. SO is not a code writing service. Thanks. – OldProgrammer Jan 31 '19 at 17:07
  • Hii, m trying to help somebody who is unwell.. i tried doing it.. though m nt a programmer – user10997164 Jan 31 '19 at 17:08
  • I can share wat i tried writing – user10997164 Jan 31 '19 at 17:09
  • Then please edit the question and insert the code, and what the specific issue is. THanks – OldProgrammer Jan 31 '19 at 17:13
  • Edited, after cont. Effort of two days i was able to right this code.. bt m unable to fix the updaye query.. is shows some ora 0001 error – user10997164 Jan 31 '19 at 17:16
  • Between thnks fr ur valueable revert.. i appreciate. – user10997164 Jan 31 '19 at 17:17
  • I do not see code and error messages in the question body. Please add that. ( hit the EDIT button under the question. – OldProgrammer Jan 31 '19 at 17:20
  • CREATE OR REPLACE PROCEDURE nw (p_c_id NUMBER, p_s_id NUMBER, p_certification VARCHAR2) AS v_c_id NUMBER := p_c_id; v_s_id NUMBER := p_s_id; v_certification VARCHAR2(20); flag NUMBER(3); BEGIN SELECT count(*) INTO flag from consultant_skill WHERE c_id = v_c_id AND skill_id = v_s_id; DBMS_OUTPUT.PUT_LINE (flag); IF flag > 0 THEN UPDATE consultant_skill SET skill_id = p_s_id, certification = p_certification WHERE c_id = v_c_id; ELSE DBMS_OUTPUT.PUT_LINE ('bye bye'); END IF; END; / – user10997164 Jan 31 '19 at 17:29
  • Was not able to update question too.. – user10997164 Jan 31 '19 at 17:31
  • CREATE OR REPLACE PROCEDURE sw (p_c_id NUMBER, p_s_id NUMBER, p_certification VARCHAR2) AS v_c_id NUMBER(6) := p_c_id; v_s_id NUMBER(3); v_certification VARCHAR2(8); --v_last VARCHAR(20); --v_first VARCHAR(20); --v_description VARCHAR(50); BEGIN SELECT skill_id, certification INTO v_s_id, v_certification FROM consultant_skill WHERE c_id = v_c_id; DBMS_OUTPUT.PUT_LINE(' con id ' || v_c_id|| ' skill id '|| v_s_id || ' certification' || v_certification || ' New values are: New skill id '|| p_s_id || ' new certification ' || p_certification); UPDATE consultant_skill SET ski – user10997164 Jan 31 '19 at 17:34
  • UPDATE consultant_skill SET skill_id = p_s_id, certification = p_certification WHERE c_id = v_c_id; COMMIT; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Your select returns many rows MY FRIEND!'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' new consultant inserted : New consultant id '|| v_c_id || ' New skill id ' || p_s_id || ' certification ' || p_certification); INSERT INTO consultant (c_id) VALUES (v_c_id); COMMIT; INSERT INTO skill(skill_id) VALUES (p_s_id); COMMIT; INSERT INTO consultant_skill (c_id – user10997164 Jan 31 '19 at 17:35
  • INSERT INTO consultant_skill (c_id, skill_id, certification) VALUES (v_c_id, p_s_id, p_certification); COMMIT; --SELECT c_last, c_first -- INTO v_last, v_first ---- FROM consultant -- WHERE c_id = p_c_id; DBMS_OUTPUT.PUT_LINE(' We insert a new consultant for you: New consultant id '|| v_c_id || ' New skill id ' || p_s_id || ' certification ' || p_certification); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Somthing is wrong '); END; / – user10997164 Jan 31 '19 at 17:35
  • This one is wat i tried before.. but its slso giving tht error nd many others – user10997164 Jan 31 '19 at 17:36

1 Answers1

0

There is a unique key constraint on a combination of columns in the table. The update causes more than 1 record in the table to have the same values for this combination of columns. Make sure to ensure the uniqueness of the combination of columns, not only when inserting rows, but also when updating existing rows...

jopie
  • 1
  • 1