Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    19

    Unanswered: missing or invalid option

    here is my code:
    SET SERVEROUTPUT ON
    DECLARE
    numbrows NUMBER (2) := '&numbrows';
    name s_dept.name%TYPE;
    CURSOR c_emp IS
    SELECT last_name,dept_id
    FROM s_emp;
    TYPE last_dept_table_type IS TABLE OF
    c_emp%ROWTYPE
    INDEX BY BINARY_INTEGER;
    last_dept_table last_dept_table_type;
    CURSOR c_dept IS
    SELECT name,id
    FROM s_dept;
    TYPE dept_table_type IS TABLE OF
    c_dept%ROWTYPE
    INDEX BY BINARY_INTEGER;
    dept_table dept_table_type;
    BEGIN
    OPEN c_dept;
    OPEN c_emp;
    FOR i IN 1..numbrows LOOP
    FETCH c_emp INTO last_dept_table(i);
    EXIT WHEN c_emp%NOTFOUND;
    FETCH c_dept INTO dept_table(i);
    EXIT WHEN c_emp%NOTFOUND;
    IF last_dept_table(i).dept_id = dept_table(i).id THEN
    DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_table(i).name);
    END IF;
    END LOOP;
    CLOSE c_emp;
    CLOSE c_dept;
    END;
    /

    and this is my problem:

    Enter value for numbrows: 5
    old 3: numbrows NUMBER (2) := '&numbrows
    new 3: numbrows NUMBER (2) := '5';
    SET SERVEROUTPUT ON
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

    Can anybody help?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: missing or invalid option

    1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.

    2) If you are using SQL Plus, what version of SQL Plus, e.g.:

    SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

    When I run your code I get:

    Enter value for numbrows:
    old 2: numbrows NUMBER (2) := '&numbrows';
    new 2: numbrows NUMBER (2) := '';

    Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.

  3. #3
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    19

    Re: missing or invalid option

    I am running this in SQL Plus oracle 9i
    Do you know why I am getting this error?



    Originally posted by andrewst
    1) Are you running this in SQL Plus, or some other tool? SET SERVEROUTPUT ON is a SQL Plus command.

    2) If you are using SQL Plus, what version of SQL Plus, e.g.:

    SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003

    When I run your code I get:

    Enter value for numbrows:
    old 2: numbrows NUMBER (2) := '&numbrows';
    new 2: numbrows NUMBER (2) := '';

    Then of course I get lots of errors, as I don't have the right tables. But note that the &numbrows substitution happens on line 2 not 3, which is correct because SET SERVEROUTPUT ON is not part of the PL/SQL being run.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: missing or invalid option

    Originally posted by bbk
    I am running this in SQL Plus oracle 9i
    Do you know why I am getting this error?
    No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0

    NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:

    Code:
    SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
    
    (c) Copyright 1998 Oracle Corporation.  All rights reserved.
    Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.

  5. #5
    Join Date
    Feb 2003
    Location
    Ontario, Canada
    Posts
    19

    Re: missing or invalid option

    Sorry about that :
    Release 9.2.0.1.0 - Production on Fri Feb 21 12:53:24 2003

    (c) 1982, 2002, Oracle Corporation. All rights reserved.

    I tried leaving a blank line and now i get the following msg:
    Enter value for numbrows: 5
    old 2: numbrows NUMBER (2) := '&numbrows';
    new 2: numbrows NUMBER (2) := '5';
    DBMS_OUPUT.PUT_LINE (last_dept_table(i).last_name||' '||last_dept_table(i).dept_id||' '||dept_
    *
    ERROR at line 27:
    ORA-06550: line 27, column 7:
    PLS-00201: identifier 'DBMS_OUPUT.PUT_LINE' must be declared
    ORA-06550: line 27, column 7:
    PL/SQL: Statement ignored




    Originally posted by andrewst
    No, I don't. As I said, I DON'T get the error when I run the same script in SQL Plus 8.0.5.0.0

    NOTE: I'm talking about the SQL PLUS version here, not the Oracle version. I mean the very top banner:

    Code:
    SQL*Plus: Release 8.0.5.0.0 - Production on Fri Feb 21 16:47:22 2003
    
    (c) Copyright 1998 Oracle Corporation.  All rights reserved.
    Try leaving a blank line between the SET SERVEROUTPUT ON command and the DECLARE. At the moment your SQL Plus seems to think the SET command is an invalid part of the PL/SQL block, whereas my version manages to recognise it as a separate SET command.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: missing or invalid option

    Yes, well DBMS_OUPUT is a typo, isn't it!

Posting Permissions

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