|
Unable to locate statement causing error |
Views: 1321
|
Thread Tools | Rate Thread |
#1
|
|||
|
|||
Unable to locate statement causing error
Unable to locate statement causing error
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 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. 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,FUNCTION AL_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_priv ilege_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; |