PL/SQL Procedure Exec errors

[sankar6254]

Hi,

Here is a PL/SQL procedure that I created. I am trying to execute it and I am running into the error described below. Any help would be greatly appreciated.

Thanks,
Sankar.


CREATE OR REPLACE PROCEDURE proc_load_user_privileges

AS





/* The following cursor retrieves list of all of the privilege names which is

basically all columns in table TEMP_NSDA_USER_PRIVILEGE except 'sun_id', 'node_id'

*/





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');



-- Get all rows from the temporary table TEMP_NSDA_USER_PRIVILEGE

CURSOR cur_temp_nsda_user_privilege

IS

SELECT *

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

/*

Loop thru all the rows of the temporary nsda_user_privilege

table and for each row, using Dynamic-SQL query, figure out the value for

the 'privilege name' columns i.e. VIEW_ATTACHMENT, FINANCE_VALIDATOR

from the temporary table TEMP_NSDA_USER_PRIVILEGE..



If the value for the 'privilege name' TEMP_NSDA_USER_PRIVILEGE is 'Y', then the

column name of the table 'TEMP_NSDA_USER_PRIVILEGE' is set as value for the column 'privilege_nm'

of the table 'NSDA_USER_PRIVILEGE'



If the value for the 'privilege name' TEMP_NSDA_USER_PRIVILEGE is 'N' and is NOT NULL, then the

column name of the table 'TEMP_NSDA_USER_PRIVILEGE' is set as value for the column 'privilege_nm'

of the table 'NSDA_USER_PRIVILEGE'

and the 'value' of the column name of the table 'TEMP_NSDA_USER_PRIVILEGE' is set as value for the

column 'user_privilege_value'



*/





--Begin looping thru all the records in the TEMP_NSDA_USER_PRIVILEGE Table

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;





/* Take the user specific information and insert it into NSDA_USER table

by calling the procedure 'insert_nsda_user'.



*/



--EXEC insert_nsda_user_t(v_current_sun_id, v_first_nm, v_last_nm);





/* If the current record in TEMP_NSDA_USER_PRIVILEGE table, is an update to

one or more existing records, then all the records related to that

sun_id are deleted..

This approach avoids the processing needed to figure out which field within

a record was updated..



Also one record in TEMP_NSDA_USER_PRIVILEGE table

results in multiple inserts or updates to the table NSDA_USER_PRIVILEGE.



*/



IF v_operation = 'UPDATE' THEN

DELETE FROM NSDA_USER_PRIVILEGE_T

WHERE sun_id = v_current_sun_id;

END IF;



--Loop thru the list of column names of the table TEMP_NSDA_USER_PRIVILEGE

FOR current_col IN cur_list_of_cols

LOOP





v_current_privilege_nm:= current_col.column_name;

DBMS_OUTPUT.PUT_LINE('The current privilege name is ' || v_current_privilege_nm);



v_block_str:= 'BEGIN

SELECT v_current_privilege_nm INTO v_current_user_privilege_val '

|| 'FROM TEMP_NSDA_USER_PRIVILEGE

WHERE sun_id = v_current_sun_id

AND node_id = v_current_node_id;

END;' ;



EXECUTE IMMEDIATE v_block_str;

--The dynamic SQL query related statements end here







/* The short PL-SQL block below does the following:-

Since the 'privilege name' related column names in TEMP_NSDA_USER_PRIVILEGE have

underscore (e.g. FINANCE_VALIDATOR) character, they need to be replaced with whitespace

to make them as e.g. FINANCE VALIDATOR,

so that they can be stored as values for the column 'privilege_nm' in the table

NSDA_USER_PRIVILEGE.



*/

BEGIN

SELECT

REPLACE(v_current_privilege_nm, '_', ' ')

INTO v_current_privilege_nm

FROM DUAL;



END;

/* The short PL-SQL block ends here */



--Checks to see the value of the user_privilege_value

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 value of current user privilege value is ' || TO_CHAR (v_current_user_privilege_val));





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;







END LOOP;



END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('The error is ' || SQLERRM);

END proc_load_user_privileges;



**************************************************
The temp_nsda_user_privilege table is created as follows:

create table temp_nsda_user_privilege
(
SUN_ID VARCHAR2(30),
NODE_ID NUMBER(38),
OPERATION VARCHAR2(10),
FIRST_NM VARCHAR2(30),
LAST_NM VARCHAR2(30),
FUNCTIONAL_AREA_NM VARCHAR2(30),
VIEW_ATTACHMENT VARCHAR2(40),
FINANCE_VALIDATOR VARCHAR2(40),
DEAL_MARGIN VARCHAR2(20),
UPLOAD_ATTACHMENT VARCHAR2(20)
)
==================================================
HERE IS WHAT HAPPENS WHEN I CREATE & EXECUTE THIS PROCEDURE
==================================================

SQL> @load_nsda_user_privileges-v2.sql
165 /

Procedure created.

SQL> exec proc_load_user_privileges;
The current privilege name is VIEW_ATTACHMENT
The error is ORA-06550: line 4, column 33:
PL/SQL: ORA-00904:
"V_CURRENT_NODE_ID": invalid identifier
ORA-06550: line 2, column 10:
PL/SQL:
SQL Statement ignored

PL/SQL procedure successfully completed.

[DaPi]

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

EXECUTE IMMEDIATE v_block_str;

That's not how you introduce parameters into dynamic SQL.

Have a look at examples of bind variables here and USING: http://download-west.oracle.com/docs...ynam.htm#13131

P.S. you don't need begin & end or the ;.

[reydp]

I didn't read the whole procedure, I only quote this
from Dapi's observation.

try changing the value of v_block_str with this:
v_block_str:=
'SELECT '||v_current_privilege_nm||
' INTO '||v_current_user_privilege_val
|| ' FROM TEMP_NSDA_USER_PRIVILEGE WHERE sun_id = '||
v_current_sun_id||' AND node_id = '||v_current_node_id;

[DaPi]

reydp:
a) that won't work - you can't use INTO like that
b) better get into the habit of using bind variables now. Binds are you friends (like Google).

It will need to be something like:

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

[reydp]

my bad Dapi,
I should have known this, because I use this before.

[pgamez]

Avoid the use of sql concatenation in user-entered variables or you will be hacked (SQL Injection).

Agree with DaPi, Bind are your friends.