Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Posts
    27

    Unanswered: Problem running this procedure

    Hi,
    I have written a procedure to update the employee records. The procedure is a s follows:
    CREATE OR REPLACE PROCEDURE Employeedetails (eno IN NUMBER, ename IN

    VARCHAR2, job IN VARCHAR2, mgr IN NUMBER, hire IN DATE, salary IN

    NUMBER, comm IN NUMBER, dno IN NUMBER)
    IS
    primkey EXCEPTION;
    noparams EXCEPTION;
    BEGIN

    IF(eno ="") THEN
    RAISE primkey;
    ELSIF(ename != NULL) THEN
    UPDATE EMP SET empname = ename WHERE empno = eno;
    ELSIF(job != NULL) THEN
    UPDATE EMP SET job = job WHERE empno = eno;
    ELSIF(mgr != NULL) THEN
    UPDATE EMP SET mgr = mgr WHERE empno = eno;
    ELSIF(hire !=NULL) THEN
    UPDATE EMP SET hiredate = hire WHERE empno = eno;
    ELSIF(salary != NULL) THEN
    UPDATE EMP SET sal = salary WHERE empno = eno;
    ELSIF(comm != NULL) THEN
    UPDATE EMP SET comm = comm WHERE empno = eno;
    ELSIF(dno != NULL) THEN
    UPDATE EMP SET deptno = dno WHERE empno = eno;
    ELSE
    RAISE noparams;
    END IF;
    EXCEPTION
    WHEN primkey THEN
    DBMS_OUTPUT.PUT_LINE('No primary key was issued');
    WHEN noparams THEN
    DBMS_OUTPUT.PUT_LINE('No parameters were passed');
    END Employeedetails;
    /

    When I run it, I am getting this following error:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

    But I have no field called "Name"...Could any one help me with this please....

  2. #2
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Change If eno = "" to If eno = 0.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It might be worth the time fro you to go back and do some reading on
    how to test for NULL & NOT NULL.
    FWIW - no equal sign is EVER used in testing for NULL/NOT NULL!
    P.S.
    It might help if you identified which line in your code generated the error.
    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by arvindram
    Change If eno = "" to If eno = 0.
    what if the user hasn'r entered anything for eno then..it will be taken as null and not 0 right??

  5. #5
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by anacedent
    It might be worth the time fro you to go back and do some reading on
    how to test for NULL & NOT NULL.
    FWIW - no equal sign is EVER used in testing for NULL/NOT NULL!
    P.S.
    It might help if you identified which line in your code generated the error.
    The error is at this line:
    CREATE OR REPLACE PROCEDURE Employeedetails (eno IN NUMBER, ename IN VARCHAR2, job IN VARCHAR2, mgr IN NUMBER, hire IN DATE, salary IN NUMBER, comm IN NUMBER, dno IN NUMBER

  6. #6
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by saisai
    The error is at this line:
    CREATE OR REPLACE PROCEDURE Employeedetails (eno IN NUMBER, ename IN VARCHAR2, job IN VARCHAR2, mgr IN NUMBER, hire IN DATE, salary IN NUMBER, comm IN NUMBER, dno IN NUMBER
    any help please......

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    if variable IS NULL

    if variable IS NOT NULL then
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by The_Duck
    if variable IS NULL

    if variable IS NOT NULL then
    Thank you, the program compiled properly..but when I try to execute the procedure as this:
    execute Employeedetails(8," "," "," "," ",5000," "," "); where I want ot update only the salary colm given the eno)PK)..I get the following error:
    ERROR at line 1:
    ORA-06550: line 1, column 25:
    PLS-00201: identifier ' ' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Any sugesstions please.......

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >execute Employeedetails(8," "," "," "," ",5000," "," ");
    Those look to me like double quote marks?
    Are they double quote marks?
    Strings are encased in single quote marks.
    Alternatively
    (8,NULL,NULL,NULL,NULL,5000,NULL,NULL);
    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
    Apr 2004
    Posts
    27
    Originally posted by anacedent
    >execute Employeedetails(8," "," "," "," ",5000," "," ");
    Those look to me like double quote marks?
    Are they double quote marks?
    Strings are encased in single quote marks.
    Alternatively
    (8,NULL,NULL,NULL,NULL,5000,NULL,NULL);
    thanks...I have a question..
    If I use single quotes for char typr, what should I use to show the numeric value as null??

  11. #11
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by anacedent
    >execute Employeedetails(8," "," "," "," ",5000," "," ");
    Those look to me like double quote marks?
    Are they double quote marks?
    Strings are encased in single quote marks.
    Alternatively
    (8,NULL,NULL,NULL,NULL,5000,NULL,NULL);
    and also when I used the above atmt, it complied properly but no data was changed in the database??

    Could anybody help what could have gone wrong??

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by saisai
    and also when I used the above atmt, it complied properly but no data was changed in the database??

    Could anybody help what could have gone wrong??
    Are you trying to set SALARY to 5000 where ENO is 8?
    I don't see any commits in your PROC.

    Are you planning to only pass two valid (non null) parmeters at any one time? Based on your proc you will only update 1 field regardless of how many you pass in.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by The_Duck
    Are you trying to set SALARY to 5000 where ENO is 8?
    I don't see any commits in your PROC.

    Are you planning to only pass two valid (non null) parmeters at any one time? Based on your proc you will only update 1 field regardless of how many you pass in.
    Yeah...I am trying to set salary to 5000 where eno is 8. I did commit after executing the procedure in SQLPLUS...

    Can you help me how to slove this problem....

  14. #14
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by The_Duck
    Are you trying to set SALARY to 5000 where ENO is 8?
    I don't see any commits in your PROC.

    Are you planning to only pass two valid (non null) parmeters at any one time? Based on your proc you will only update 1 field regardless of how many you pass in.
    I see that my procedure only updated one fields regardless of how many parameters I pass. I would like the proc to update all the fields for which I have passed the value thru parameters.

    It would be great if you could help me solve this....

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure if I remember well, but ... I recall your previous question where you wanted to simplify a multiple "IF ... END IF" procedure. Now this is the result.
    But, I'm afraid you'll have to rewrite it again in old manner as this "IF ... ELSIF ... END IF" procedure you put in this topic updates only the first field that fulfills your condition (which The Duck already told you (as if you don't know it yourself ).

    So, if I'm not wrong, it would be something like
    PHP Code:
    IF(eno =""THEN
       RAISE primkey
    ;
    END IF;

    IF (
    ename != NULLTHEN
       UPDATE EMP SET empname 
    ename WHERE empno eno;
    END IF;

    IF (
    job != NULLTHEN
       UPDATE EMP SET job 
    job WHERE empno eno;
    END IF;

    /* etc. */ 

Posting Permissions

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