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,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;
How to Earn Rs.25000 every month in internet without Investment?
Unable to locate statement causing error
Subscribe to:
Post Comments (Atom)
topics
-
▼
2007
(1406)
-
▼
November
(1359)
- Free Download SAP FI Certification study pdf books
- Implementing SAP R/3 on OS/400
- Update SAP Kernel in UNIX based
- Security Audit Log (BC-SEC).pdf
- Security Audit Log.pdf
- Securities,pdf
- Secure Store & Forward / Digital Signatures (BC-SE...
- Secure Network Communications (BC-SEC-SNC)
- Free download use ful T-codes
- I did not find any OSS notes appropriate for my pr...
- How to apply OSS notes number?
- What is OSS Notes number?
- Where can i access SAP OSS?
- WHAT IS OSS
- Disaster Recovery Plan to Restore Production System
- Steps to Install SAP Note in sap
- Difference Between SAP Notes and Support Package
- Question : Subject : Support packages testing
- Five Different "User Type"
- How to solve the Time Zone Definition Problems?
- Setting the User Decimals Format
- Schedule Manager
- Various Important SAP Basis T-Code
- Trace Functions in sap
- System Trace: Error Analysis in sap
- System Trace(ST01) in sap
- Roles and Authorizations Used in Background Proces...
- Deleting Multiple Spool Requests Simultaneously in...
- Logging and Tracing in spool
- Print and Output Management in spool
- Background Job Monitoring Monitor in CCMS
- Monitoring the Database Using the Alert Monitor
- Monitoring the Operating System Using the Alert Mo...
- Monitoring Memory Management Using the Alert Monitor
- Method Dispatching Monitor in CCMS
- Remote Application Server Status Monitor in CCMS
- GRMG Self-Monitoring Monitor in CCMS
- CCMS Selfmonitoring Monitor for System-Wide Data i...
- Logfile Monitoring Monitor in CCMS
- Logon Load Balancing Monitor in CCMS
- Transaction-Specific Dialog Monitor in CCMS
- Workload Collector Monitor in CCMS
- System Errors Monitor in CCMS
- System Configuration Monitor in CCMS
- Syslog Monitor in CCMS
- Spool System Monitor in CCMS
- Security Monitor in CCMS
- Performance Overview Monitor in CCMS
- Operating System Monitor in CCMS
- Filesystems Monitor in CCMS
- Entire System Monitor in CCMS
- Monitoring the Enqueue Service in CCMS
- Dialog per Application Server Monitor in CCMS
- Dialog Overview Monitor in CCMS
- Database Monitor in CCMS
- Transactional RFC and Queued RFC Monitor in CCMS
- Communications Monitor in CCMS
- Buffers Monitor in CCMS
- Background Job Monitoring Monitor(CCMS)
- Background Processing Monitor(CCMS)
- Availability and Performance Overview Monitor (CCMS)
- SAP CCMS Monitor Templates Monitor Set
- Creating and Changing a Monitoring Pause(CCMS)
- Creating and Changing Monitoring Rules(CCMS)
- Configuring Availability Monitoring(CCMS)
- Update Repositories(CCMS)
- Displaying Central Performance History Reports(CCMS)
- Displaying Report Properties
- Scheduling and Executing a Report
- Variables in Group Names
- Creating a Report Definition(CCMS)
- Maintaining Collection and Reorganization Schemata...
- Maintaining Collection and Reorganization Schemata...
- Creating and Editing a Calendar Schema(CCMS)
- Creating and Editing a Day Schema
- Customizing the Alert Monitor(CCMS)
- Resetting MTEs and Alerts(CCMS)
- Reorganizing Completed Alerts(CCMS)
- Display Completed Alerts(CCMS)
- Automatically Complete Alerts(CCMS)
- Completing Alerts(CCMS)
- Starting Methods (CCMS)
- Processing Alerts(CCMS_
- Displaying the Technical View: Central Performance...
- Displaying the Technical View: Threshold Values(CCMS)
- Displaying the Technical View: Status Autoreaction...
- Displaying the Technical View: Status Data Collector
- Displaying the Technical View: Method Allocation
- Displaying the Technical View: Info on MTE
- Display Types and Views of the Alert Monitor(CCMS)
- Properties of Status Attributes (CCMS)
- Properties of Performance Attributes(CCMS)
- Properties of Log Attributes (CCMS)
- General Properties of Monitoring Tree Elements(CCMS)
- Properties of Monitoring Objects and Attributes
- Elements of the Alert Monitoring Tree
- Alert Monitoring Tree(CCMS)
- Monitors(CCMS)
- Monitor Sets (CCMS)
- Elements of the Alert Monitor (CCMS)
-
▼
November
(1359)
No comments:
Post a Comment