OraFAQ Forum: SQL & PL/SQL » ORA-00936: missing expression

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936: missing expression

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00936: missing expression [message #16841]

Tue, 14 December 2004 16:17

Sankar B. Mandalika
Messages: 20
Registered: November 2002

Junior Member

Hi All,

When I ran the procedure below, I am running into the following error:

The error is ORA-00936: missing expression

I tried to put DBMS_OUTPUT.PUT_LINE statements to see where it is failing. I received the following output

SQL> exec proc_load_user_privileges
The error is 6
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 1
The error is 3
The error is 4
The error is 6
The error is ORA-00936: missing expression

It looks like right after the loop is done, the error is happening. How can I zero in on the statement that is causing the problems? I tried to run all the sql statements by themselves in sql*plus and they are working fine. I am not sure where the syntax error is. Any help would be greatly appreciated.

Thanks,
Sankar.

======

Here is the procedure text

======

CREATE OR REPLACE PROCEDURE proc_load_user_privileges
AS

CURSOR cur_list_of_cols
IS
SELECT column_name FROM user_tab_columns WHERE table_name = 'TEMP_NSDA_USER_PRIVILEGE' AND column_name NOT IN ('SUN_ID', 'NODE_ID', 'OPERATION', 'FIRST_NM', 'LAST_NM', 'FUNCTIONAL_AREA_NM');

CURSOR cur_temp_nsda_user_privilege
IS
SELECT SUN_ID,NODE_ID,OPERATION,FIRST_NM,LAST_NM,FUNCTIONAL_AREA_NM FROM TEMP_NSDA_USER_PRIVILEGE;

v_current_sun_id VARCHAR2(30);
v_current_node_id NUMBER(20);
v_current_privilege_nm VARCHAR2(30);
v_current_user_privilege_val VARCHAR2(30);
v_current_column_name_in VARCHAR2(30);
v_block_str VARCHAR2(500);
v_dynamic_query_handle INTEGER;
feedback INTEGER;
v_curr VARCHAR2(30);
v_operation VARCHAR2(30);
v_first_nm VARCHAR2(30);
v_last_nm VARCHAR2(30);
v_functional_area VARCHAR2(30);

BEGIN
FOR rec_temp_nsda_user_priv IN cur_temp_nsda_user_privilege
LOOP
v_current_sun_id := rec_temp_nsda_user_priv.sun_id;
v_current_node_id := rec_temp_nsda_user_priv.node_id;
v_operation := rec_temp_nsda_user_priv.operation;
v_first_nm := rec_temp_nsda_user_priv.first_nm;
v_last_nm := rec_temp_nsda_user_priv.last_nm;
v_functional_area := rec_temp_nsda_user_priv.functional_area_nm;

IF v_operation = 'UPDATE' THEN
DELETE FROM NSDA_USER_PRIVILEGE_T
WHERE sun_id = v_current_sun_id;
END IF;
DBMS_OUTPUT.PUT_LINE('The error is 6');
FOR current_col IN cur_list_of_cols
LOOP
v_current_privilege_nm:= current_col.column_name;

v_block_str:=
'SELECT ' || v_current_privilege_nm || ' FROM TEMP_NSDA_USER_PRIVILEGE WHERE sun_id = ' || v_current_sun_id || ' AND node_id = ' || v_current_node_id ;

EXECUTE IMMEDIATE v_block_str INTO v_current_user_privilege_val;

BEGIN
SELECT
REPLACE(v_current_privilege_nm, '_', ' ')
INTO v_current_privilege_nm
FROM DUAL;

END;
DBMS_OUTPUT.PUT_LINE('The error is 1');

IF (v_current_user_privilege_val = 'Y')
THEN

INSERT INTO NSDA_USER_PRIVILEGE_T (sun_id, node_id, privilege_nm) VALUES (v_current_sun_id, v_current_node_id, v_current_privilege_nm);

ELSIF (v_current_user_privilege_val IS NOT NULL) AND (v_current_user_privilege_val != 'N')
THEN
v_current_user_privilege_val:= TO_NUMBER(v_current_user_privilege_val);
DBMS_OUTPUT.PUT_LINE('The error is 2');

INSERT INTO NSDA_USER_PRIVILEGE_T
(sun_id, node_id, privilege_nm, user_privilege_value)
VALUES
(v_current_sun_id,v_current_node_id,v_current_privilege_nm,v_current_user_privilege_val);

END IF;
DBMS_OUTPUT.PUT_LINE('The error is 3');

END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 4');

END LOOP;
DBMS_OUTPUT.PUT_LINE('The error is 5');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is ' || SQLERRM);
END proc_load_user_privileges;

Report message to a moderator

Re: ORA-00936: missing expression [message #16846 is a reply to message #16841]

Tue, 14 December 2004 20:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Seems that one of the parts of your dynamic sql string is NULL. Check for records of TEMP_NSDA_USER_PRIVILEGE where one of the following columns is NULL: SUN_ID or NODE_ID.

MHE

Report message to a moderator

Re: ORA-00936: missing expression [message #16849 is a reply to message #16841]

Tue, 14 December 2004 21:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

Check out Maaher's reply for this problem.
In general, if you want to 'zero in' on the real problem, you'd have to be able to understand the error Oracle gives you.
If I see the error 'ORA-00936: missing expression' in a PL/SQL block, first thing I think is 'probably some dynamic SQL' (and that's what Maaher thought too ;)

If you output the string you feed to the 'execute immediate', you would probably see where the error lies.

You see ? It's all (or at least a lot) about UNDERSTANDING the Oracle-errors.

btw, your method of using dbms_output is normally a good way to track where and when the error occurs.

hth

Report message to a moderator

Re: ORA-00936: missing expression [message #16861 is a reply to message #16849]

Wed, 15 December 2004 06:44

Sankar Mandalika
Messages: 3
Registered: December 2004

Junior Member

Thanks very much, Frank! Truly appreciate your help and kind words. Sorry, I seem to have upset you with my post. I thought posting a question of that nature in the newbies section is ok but I guess I was wrong. Please accept my most sincere apologies for having wasted your precious time.

Report message to a moderator

Re: ORA-00936: missing expression [message #16862 is a reply to message #16846]

Wed, 15 December 2004 06:45

Sankar Mandalika
Messages: 3
Registered: December 2004

Junior Member

Thanks very much Maaher. Truly appreciate your help. I will follow along the lines of your suggestion.

Report message to a moderator

Re: ORA-00936: missing expression [message #16866 is a reply to message #16841]

Wed, 15 December 2004 09:35

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Sankar, be sure to use bind variables on that dynamic SQL:

v_block_str:= 
  'SELECT ' || v_current_privilege_nm || 
  ' FROM TEMP_NSDA_USER_PRIVILEGE' ||
  ' WHERE sun_id = :sun_id AND node_id = :node_id';
   
EXECUTE IMMEDIATE v_block_str 
  INTO v_current_user_privilege_val
  USING v_current_sun_id, v_current_node_id;

Also, that REPLACE on the privilege name should only be one line of code. Instead of:

BEGIN
SELECT 
  REPLACE(v_current_privilege_nm, '_', ' ') 
  INTO v_current_privilege_nm
  FROM DUAL;
END;

just:

v_current_privilege_nm := REPLACE(v_current_privilege_nm, '_', ' ');

Report message to a moderator

Re: ORA-00936: missing expression [message #16882 is a reply to message #16861]

Wed, 15 December 2004 21:12

Frank
Messages: 7901
Registered: March 2000

Senior Member

I think you misinterpreted my post. I was not upset at all. You asked how to debug a case like you had, and I pointed out that understanding Oracle is important for this.
So, no apologies needed :)

Frank

Report message to a moderator

Re: ORA-00936: missing expression [message #16895 is a reply to message #16866]

Thu, 16 December 2004 06:36

Sankar Mandalika
Messages: 3
Registered: December 2004

Junior Member

Thank you, Todd, for the detailed explanation. It really helped, especially the simplification you suggested for the usage of the REPLACE. Thanks again.

Report message to a moderator

Re: ORA-00936: missing expression [message #18156 is a reply to message #16866]

Sat, 12 February 2005 12:00

ning
Messages: 1
Registered: February 2005

Junior Member

I have a problem .I tried to insert data to database but I cann't.I can tried insert number to database but can not insert word to database.
what happen it.
Please send auswer to me .

Report message to a moderator

Previous Topic:	Oracle sequence resetting
Next Topic:	(Urgent)How to deal with long column in Oracle8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 23 06:28:30 CDT 2025