Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: Can someone help with a few script issues

    This is the script. It has the table and data making code with it.

    I am having two problems:

    First, Oracle is throwing a system exception 'ROW_NOT_FOUND' and skipping my code to handle it myself (if SQL%ROWCOUNT = 1 then). All the examples I find use an exception block, which I am prohibited from using.

    Next, I need to format it in a certain way, with text left justified and numbers right justified. I can't seem to get the numbers right.

    It should look like this..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> CREATE TABLE emp
      2  ( EMPNO	     NUMBER(4) PRIMARY KEY,
      3    ENAME	     VARCHAR2(10),
      4    JOB		     VARCHAR2(9),
      5    MGR		     NUMBER(4),
      6    HIREDATE 	     DATE,
      7    SAL		     NUMBER(7, 2),
      8    COMM	     NUMBER(7, 2),
      9    DEPTNO		     NUMBER(4) REFERENCES DEPT(DEPTNO));
      DEPTNO		NUMBER(4) REFERENCES DEPT(DEPTNO))
            		                     *
    ERROR at line 9:
    ORA-00942: table or view does not exist
    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.

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Thanks, anacedent. I added the dept table create to the script.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> INSERT INTO emp VALUES(7876, 'ADAMS',  'CLERK',	7788, '12-JAN-83', 1100,    0, 20);
    INSERT INTO emp VALUES(7876, 'ADAMS',  'CLERK',    7788, '12-JAN-83', 1100,    0, 20)
                                                             *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    
    
    SQL> INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK',	7782, '22-JAN-82', 1300,    0, 10);
    INSERT INTO emp VALUES(7934, 'MILLER', 'CLERK',    7782, '22-JAN-82', 1300,    0, 10)
                                                             *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    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.

  5. #5
    Join Date
    Sep 2009
    Posts
    12
    That is odd, it works for me. I just tested it again. I am not using the command line. I am loading and running from SQL Developer. Perhaps that is the reason you are getting errors.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Characters between single quote marks are STRING data types.
    'this is a string, 2009-09-22, not a date'
    When NLS_DATE_FORMAT differ, errors occur loading DATE fields.
    use TO_DATE() function to convert strings to DATE.
    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.

  7. #7
    Join Date
    Sep 2009
    Posts
    12
    I see, I just wonder why it works for me. I used this script on both 10g and 11g.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your NLN_DATE_FORMAT matches the string. Do not get in the habit of using it for anything but adhoc queries. Your application WILL fail eventually!
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Sep 2009
    Posts
    12
    Ok, thank you for the advice. I will fix it when I get home.

    Mostly, I just need to know how to override Oracle's "ORA-01403: no data found" exeption without using an exception block. Seems stupid, but that is what I am required to do.

  10. #10
    Join Date
    Sep 2009
    Posts
    12
    I changed the strings to dates and updated the script

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I changed the strings to dates and updated the script
    With regards to date formats what are the differences between the following:
    'dd/mm/yy'
    'dd/MON/yy'
    'dd/mm/RR'
    Does CaSe matter within date mask?

    One way to avoid the ORA-01403: no data found exception is a kludge.
    SELECT COUNT(*) ...


    IF COUNT(*) = 0 THEN do not do existing SELECT
    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.

  12. #12
    Join Date
    Sep 2009
    Posts
    12
    I see what you mean, anacedent. Case matters. I do appreciate your advice on making my scripts better. I have not been doing this long and I want to learn to do it right.

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Using select count(*)... will work, but an exception block is the correct way to do it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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