Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936: missing expression
ORA-00936: missing expression [message #212670] Sun, 07 January 2007 08:11 Go to next message
shriprog
Messages: 3
Registered: January 2007
Junior Member
Hi,

i am trying to write a basic procedure in oracle 10g. I also tried to find out the exact error

error: ORA-00936: missing expression

This is what i have tried in SQL *Plus

SQL> create procedure jjj
2 as
3 begin
4 select CountryCode from Country;
5 end;
6 /

Warning: Procedure created with compilation errors.

SQL> select Line,
2 Position,
3 Text,
4 from USER_ERRORS
5 where Name = 'jjj'
6 and Type = 'PROCEDURE'
7 order by Sequence;
from USER_ERRORS
*
ERROR at line 4:
ORA-00936: missing expression

Thanks in advance,
Sri
Re: ORA-00936: missing expression [message #212671 is a reply to message #212670] Sun, 07 January 2007 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
you need INTO clause
Re: ORA-00936: missing expression [message #212672 is a reply to message #212670] Sun, 07 January 2007 08:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have a trailing comma after your last column in the select-clause ("Text,"), so your query to display your messages is wrong.
If you really want to learn SQL and PL/SQL, you should start with a good book or tutorial.
Then take the time to read and understand each errormessage you get. Reread your entered statements.
And only AFTER you did that, and you still don't see where you went wrong, THEN post it here.

[Edit: Typo]

[Updated on: Sun, 07 January 2007 08:37]

Report message to a moderator

Re: ORA-00936: missing expression [message #212676 is a reply to message #212672] Sun, 07 January 2007 10:58 Go to previous messageGo to next message
shriprog
Messages: 3
Registered: January 2007
Junior Member
I apologize for the typo error. I have a sqlserver background and i am new to oracle.

but i don't understand why does a simple procedure like this has compilation errors. When i execute the sql statement

select CountryCode from Country;

i get the desired result but when i make it a procedure it does not compile.
i also tried INTO with the same compilation error


My original procedure is as follows:

SQL> create procedure jjj
2 as
3 begin
4 select CountryCode from Country;
5 end;
6 /

Warning: Procedure created with compilation errors.

Thanks
sri
Re: ORA-00936: missing expression [message #212681 is a reply to message #212676] Sun, 07 January 2007 12:50 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In SQL*Plus, as you've seen, your statement works fine.

But, as a part of an PL/SQL procedure, you MUST select the result INTO a variable. I guess there is more than one CountryCode in the table, so you'd either have to loop through the select result set (otherwise you'd get TOO-MANY-ROWS error), or use refcursor.

Here are some examples: First, refcursor:
SQL> CREATE OR REPLACE PROCEDURE prc_test (par_dpt OUT sys_refcursor)
  2  IS
  3  BEGIN
  4    OPEN par_dpt FOR SELECT dname FROM DEPT;
  5  END;
  6  /

PROCEDURE created.

SQL> var dpt refcursor;
SQL> EXEC prc_test(:dpt);

PL/SQL PROCEDURE successfully completed.

SQL> print dpt

DNAME
--------------
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL>
Second, cursor FOR loop:
SQL> create or replace procedure prc_test_2 is
  2  begin
  3    for cur_r in (select dname from dept)
  4    loop
  5      dbms_output.put_line(cur_r.dname);
  6    end loop;
  7* end;
SQL> /

Procedure created.

SQL> set serveroutput on
SQL> exec prc_test_2;
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>
Or like this:
SQL> create or replace procedure prc_test_3 is
  2    cursor cur_d is select dname from dept;
  3    cur_r cur_d%rowtype;
  4  begin
  5    open cur_d;
  6    loop
  7      fetch cur_d into cur_r;
  8      exit when cur_d%notfound;
  9
 10      dbms_output.put_line(cur_r.dname);
 11    end loop;
 12    close cur_d;
 13  end;
 14  /

Procedure created.

SQL> exec prc_test_3;
MANAGING
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>
The simplest way is, of course, ordinary SELECT ... INTO, but - as I've said - you MUST restrict it to a single value:
SQL> create or replace procedure prc_test_4
  2  is
  3    dpt dept.dname%type;
  4  begin
  5    select dname into dpt
  6      from dept
  7      where deptno = 10;
  8
  9    dbms_output.put_line(dpt);
 10  end;
 11  /

Procedure created.

SQL> exec prc_test_4;
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>


Finally: I believe you should read PL/SQL User's Guide and Reference - it will take some time, but you have to learn why is what happening. Otherwise, those examples, I'm afraid, won't mean much.
Re: ORA-00936: missing expression [message #212689 is a reply to message #212681] Sun, 07 January 2007 15:09 Go to previous message
shriprog
Messages: 3
Registered: January 2007
Junior Member
Thanks that helped a lot

sri
Previous Topic: Urgent Help
Next Topic: Pro*C to PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 12:21:35 CST 2016

Total time taken to generate the page: 0.15352 seconds