If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > missing or invalid option

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-21-03, 11:34
bbk bbk is offline
Registered User
 
Join Date: Feb 2003
Location: Ontario, Canada
Posts: 19
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?
Reply With Quote
  #2 (permalink)  
Old 02-21-03, 11:51
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old 02-21-03, 12:35
bbk bbk is offline
Registered User
 
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?



Quote:
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.
Reply With Quote
  #4 (permalink)  
Old 02-21-03, 12:42
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Re: missing or invalid option

Quote:
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old 02-21-03, 12:56
bbk bbk is offline
Registered User
 
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




Quote:
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.
Reply With Quote
  #6 (permalink)  
Old 02-21-03, 15:20
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Re: missing or invalid option

Yes, well DBMS_OUPUT is a typo, isn't it!
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On