Home » Other » Client Tools » Error: "ORA-00900: invalid SQL statement" after executing procedure (Oracle 10g Express Edition, Windows 7)
icon5.gif  Error: "ORA-00900: invalid SQL statement" after executing procedure [message #487906] Mon, 03 January 2011 05:10 Go to next message
jakepolak
Messages: 3
Registered: January 2011
Junior Member
Hello,

I am newb to Oracle PL/SQL and I have got this problem.

CREATE OR REPLACE PROCEDURE test
IS
  CURSOR cusers IS SELECT user_name, user_date FROM users;
  uname users.user_name%TYPE;
  udate users.user_date%TYPE;
BEGIN
  OPEN cusers;
  LOOP
     FETCH cusers INTO uname, udate;
     EXIT WHEN cusers%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('name: ' || uname || ' date: ' || udate);
  END LOOP;
  CLOSE cusers;
END;


When I try to execute this procedure I get following error:
ORA-00900: invalid SQL statement


Compilation of procedure is successful.

SQL code for creating the USERS table is here:

CREATE TABLE  "USERS" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USER_NAME" VARCHAR2(50) NOT NULL ENABLE, 
	"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, 
	"USER_PASS" VARCHAR2(50) NOT NULL ENABLE, 
	"USER_DATE" DATE NOT NULL ENABLE, 
	 CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
   )



Any ideas?

Jake
Re: Error: "ORA-00900: invalid SQL statement" after executing procedure [message #487910 is a reply to message #487906] Mon, 03 January 2011 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did not get any error.
Could you use SQL*Plus and copy and paste your session like that:
SQL> CREATE TABLE  "USERS" 
  2     ( "USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
  3   "USER_NAME" VARCHAR2(50) NOT NULL ENABLE, 
  4   "USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE, 
  5   "USER_PASS" VARCHAR2(50) NOT NULL ENABLE, 
  6   "USER_DATE" DATE NOT NULL ENABLE, 
  7    CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
  8     )
  9  
SQL> 
SQL> /

Table created.

SQL> CREATE OR REPLACE PROCEDURE test
  2  IS
  3    CURSOR cusers IS SELECT user_name, user_date FROM users;
  4    uname users.user_name%TYPE;
  5    udate users.user_date%TYPE;
  6  BEGIN
  7    OPEN cusers;
  8    LOOP
  9       FETCH cusers INTO uname, udate;
 10       EXIT WHEN cusers%NOTFOUND;
 11       DBMS_OUTPUT.PUT_LINE('name: ' || uname || ' date: ' || udate);
 12    END LOOP;
 13    CLOSE cusers;
 14  END;
 15  
 16  /

Procedure created.

SQL> exec test;

PL/SQL procedure successfully completed.

Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Error: "ORA-00900: invalid SQL statement" after executing procedure [message #487919 is a reply to message #487910] Mon, 03 January 2011 06:02 Go to previous messageGo to next message
jakepolak
Messages: 3
Registered: January 2011
Junior Member
Dear Michel,

thank you for testing my procedure.

I have just tried to execute it from command line and it worked. But it still does not work if I try to execute it from the "Web Browser Database Manager". I tried it in FF, Opera and in IE8 with the same results... Have you experienced the same problem or is it just me?

The database package version is 10.2.0.1.

Thanks,
Jake

Re: Error: "ORA-00900: invalid SQL statement" after executing procedure [message #487920 is a reply to message #487919] Mon, 03 January 2011 06:08 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know "Web Browser Database Manager", I only use SQL*Plus.

Regards
Michel
Previous Topic: Several Short Fetch To Retrieve Records
Next Topic: Spool without SQL Statement and Commands in PLSQL
Goto Forum:
  


Current Time: Thu Mar 28 09:57:46 CDT 2024