August 19th, 2014, 05:09 AM
-
Dynamic SQL help required
Hi All,
I am trying to update a common column in multiple tables that exists under particular schema. Below is the script i am trying and facing the error "Invalid reference to variable 'TABLE_NAME'"
Please let me know how to rectify this issue.
CREATE OR REPLACE PROCEDURE dynamic_mask(pvalue in varchar2)
IS
TYPE TBL_ARRAY_TYPE IS TABLE OF varchar2(60) INDEX BY binary_integer;
TBL_ARRAY TBL_ARRAY_TYPE;
DML_SQL varchar2(100);
CURSOR c_update IS
select table_name from all_tab_columns where column_name=pvalue and owner not in ('SYS','SYSMAN','APEX_030200','DBSNMP','WMSYS');
rec_add c_update%ROWTYPE;
BEGIN
IF UPPER(pvalue) NOT IN ('ADDRESS','NRIC','FNAME','PHONE','EMAIL') THEN
DBMS_OUTPUT.PUT_LINE('COULMN DOES NOT EXIST');
END IF;
OPEN c_update;
FOR I IN TBL_ARRAY.FIRST..TBL_ARRAY.LAST
LOOP
FETCH c_update INTO rec_add;
EXIT WHEN c_update%NOTFOUND;
IF UPPER(pvalue) = UPPER('ADDRESS') THEN
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET '||rec_add.table_name.ADDRESS=LTRIM(TRANSLATE(rec_add.address,'0123456789/\!@#$%^&*()',' '),' ');
elsif UPPER(pvalue) = UPPER('NRIC') THEN
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET '||rec_add.table_name.NRIC = substr(rec_add.NRIC,1,3)||substr(rec_add.NRIC,6,1)||substr(rec_add.NRIC,5,1)||substr(rec_add.NRIC,4, 1)||substr(rec_add.NRIC,7,3);
elsif UPPER(pvalue) = UPPER('FNAME') THEN
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET '||rec_add.table_name.FNAME = substr(rec_add.fname,1,2)||substr(rec_add.lname,1,2)||substr(rec_add.fname,5,1)||substr(rec_add.lnam e,4,1);
elsif UPPER(pvalue) = UPPER('PHONE') THEN
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET '||rec_add.table_name.PHONE = substr(rec_add.phone,1,3)||substr(rec_add.phone,6,1)||substr(rec_add.phone,5,1)||substr(rec_add.phon e,4,1)||substr(rec_add.phone,7,3);
elsif UPPER(pvalue) = UPPER('EMAIL') THEN
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET '||rec_add.table_name.EMAIL= substr(rec_add.email,1,instr(rec_add.email,'@',1,1)-1) || 'where rec_add.table_name.NRIC=rec_add.NRIC';
END IF;
EXECUTE IMMEDIATE DML_SQL USING pvalue;
END LOOP;
CLOSE c_update;
END;
/
August 19th, 2014, 02:35 PM
-
You need to use actual column NAME like this:
Code:
-- - Etc
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET ADDRESS='''||LTRIM(TRANSLATE(rec_add.address,'0123456789/\!@#$%^&*()',' '),' ')||'''';
-- - Etc
August 20th, 2014, 09:22 AM
-
Originally Posted by LKBrwn_DBA
You need to use actual column NAME like this:
Code:
-- - Etc
DML_SQL := 'UPDATE '||rec_add.table_name || ' SET ADDRESS='''||LTRIM(TRANSLATE(rec_add.address,'0123456789/\!@#$%^&*()',' '),' ')||'''';
-- - Etc
I tried changing the above line and got the error "PLS-00302: component 'ADDRESS' must be declared"
August 20th, 2014, 10:25 PM
-
AFter modifying it says the column "ADDRESS" has to be declared.
August 21st, 2014, 04:01 AM
-
Originally Posted by Mitu08
AFter modifying it says the column "ADDRESS" has to be declared.
Experts.. Please help
August 21st, 2014, 10:04 AM
-
Originally Posted by Mitu08
After modifying it says the column "ADDRESS" has to be declared.
The table referred to in "rec_add.table_name" has to have a column named 'ADDRESS':
Code:
DML_SQL := 'UPDATE ' || Rec_Add.Table_Name
|| ' SET ' || Rec_Add.Column_Name || '=LTRIM(TRANSLATE('
|| Rec_Add.Column_Name || ',''0123456789/\!@#$%^&*()'','' ''),'' '')';
August 23rd, 2014, 11:46 PM
-
Thanks a lot. This helped me to fix the error and move on..
August 25th, 2014, 10:41 PM
-
I was able to compile the procedure without any issues. But while executing it says "PL/SQL: numeric or value error" in the line FOR I IN TBL_ARRAY.FIRST..TBL_ARRAY.LAST .
It is taking the value as null.
I am confused on how to assign the values to an arrays as it's taking it dynamically. Please give me some ideas..
August 26th, 2014, 09:17 AM
-
Originally Posted by Mitu08
I was able to compile the procedure without any issues. But while executing it says "PL/SQL: numeric or value error" in the line FOR I IN TBL_ARRAY.FIRST..TBL_ARRAY.LAST .
It is taking the value as null.
I am confused on how to assign the values to an arrays as it's taking it dynamically. Please give me some ideas..
You are not populating the TBL_ARRAY, therefore it's NULL.
August 26th, 2014, 09:45 AM
-
Originally Posted by LKBrwn_DBA
You are not populating the TBL_ARRAY, therefore it's NULL.
can you explain pls.. what should i do
August 26th, 2014, 11:49 AM
-
For starters begin with this:
Code:
CREATE OR REPLACE PROCEDURE Dynamic_Mask ( Pvalue IN VARCHAR2 )
IS
Dml_Sql VARCHAR2 ( 100 );
CURSOR C_Update
IS
SELECT Table_Name, Column_Name
FROM All_Tab_Columns
WHERE Owner NOT IN ('SYS', 'SYSMAN', 'APEX_030200', 'DBSNMP', 'WMSYS')
AND Column_Name LIKE Pvalue;
TYPE Tbl_Array_Type IS TABLE OF C_Update%ROWTYPE
INDEX BY BINARY_INTEGER;
Rec_Add Tbl_Array_Type;
V_Table_Name C_Update.Table_Name%TYPE;
V_Column_Name C_Update.Column_Name%TYPE;
Bad_Parameter EXCEPTION;
BEGIN
IF UPPER ( Pvalue ) NOT IN ('ADDRESS'
, 'NRIC'
, 'FNAME'
, 'PHONE'
, 'EMAIL'
, '%%'
)
THEN
RAISE Bad_Parameter;
END IF;
OPEN C_Update;
FETCH C_Update BULK COLLECT INTO Rec_Add;
CLOSE C_Update;
FOR I IN 1 .. Rec_Add.COUNT
LOOP
V_Table_Name := Rec_Add ( I ).Table_Name;
V_Column_Name := Rec_Add ( I ).Column_Name;
Dml_Sql := '*** N/A ***';
CASE
WHEN UPPER ( Pvalue ) = 'ADDRESS'
THEN
Dml_Sql := 'UPDATE ' || v_Table_Name ||
' SET ' || v_Column_Name || '=LTRIM(TRANSLATE('
|| v_Column_Name || ',''0123456789/\!@#$%^&*()'','' ''),'' '')';
WHEN UPPER ( Pvalue ) = 'NRIC'
THEN
Dml_Sql := 'UPDATE ' || v_Table_Name ||
' SET ' || v_Column_Name
|| '= SUBSTR(' ||V_Column_Name ||',1,3)
||SUBSTR(' ||V_Column_Name ||',6,1)
||SUBSTR(' ||V_Column_Name ||',5,1)
||SUBSTR(' ||V_Column_Name ||',4,1)
||SUBSTR(' ||V_Column_Name ||',7,3)';
--
-- Put the rest of your code here --
--
END CASE;
IF Dml_Sql != '*** N/A ***' THEN
EXECUTE IMMEDIATE Dml_Sql;
END IF;
END LOOP;
EXCEPTION
WHEN Bad_Parameter
THEN
Raise_Application_Error ( -20001, '!Error: column does not exist: ' || Pvalue );
END;
/
Last edited by LKBrwn_DBA; August 26th, 2014 at 12:43 PM.
August 26th, 2014, 11:15 PM
-
Thanks a lot . Updated the code and tried. Facing two errors " subprogram or cursor 'C_UPDATE' reference is out of scope on the lines V_Table_Name C_Update.Table_Name%TYPE; V_Column_Name C_Update.Column_Name%TYPE;
the declaration of the type of this expression is incomplete or malformed on V_Table_Name := Rec_Add(I).Table_Name; V_Column_Name := Rec_Add(I).Column_Name; and the Dml_Sql statements.
August 27th, 2014, 02:14 AM
-
Code:
CREATE OR REPLACE PROCEDURE Mask ( Pvalue IN VARCHAR2 )
IS
Dml_Sql VARCHAR2 ( 100 );
CURSOR C_Update
IS
SELECT Table_Name, Column_Name
FROM All_Tab_Columns
WHERE Owner NOT IN ('SYS', 'SYSMAN', 'APEX_030200', 'DBSNMP', 'WMSYS')
AND Column_Name LIKE Pvalue;
TYPE Tbl_Array_Type IS TABLE OF C_Update%ROWTYPE
INDEX BY BINARY_INTEGER;
Rec_Add Tbl_Array_Type;
V_Table_Name C_Update.Table_Name%TYPE;
V_Column_Name C_Update.Column_Name%TYPE;
Bad_Parameter EXCEPTION;
BEGIN
IF UPPER ( Pvalue ) NOT IN ('ADDRESS'
, 'NRIC'
, 'FNAME'
, 'PHONE'
, 'EMAIL'
, '%%'
)
THEN
RAISE Bad_Parameter;
END IF;
OPEN C_Update;
FETCH C_Update BULK COLLECT INTO Rec_Add;
CLOSE C_Update;
FOR I IN 1 .. Rec_Add.COUNT
LOOP
V_Table_Name := Rec_Add(I).Table_Name;
V_Column_Name := Rec_Add(I).Column_Name;
Dml_Sql := '*** N/A ***';
CASE
WHEN UPPER ( Pvalue ) = 'ADDRESS'
THEN
Dml_Sql := 'UPDATE ' || v_Table_Name ||
' SET ' || v_Column_Name || '=LTRIM(TRANSLATE('
|| v_Column_Name || ',''0123456789/\!@#$%^&*()'','' ''),'' '')';
WHEN UPPER ( Pvalue ) = 'NRIC'
THEN
Dml_Sql := 'UPDATE ' || V_Table_Name ||
' SET ' || V_Column_Name
|| '= SUBSTR(' ||V_Column_Name ||',1,3)
||SUBSTR(' ||V_Column_Name ||',6,1)
||SUBSTR(' ||V_Column_Name ||',5,1)
||SUBSTR(' ||V_Column_Name ||',4,1)
||SUBSTR(' ||V_Column_Name ||',7,3)';
WHEN UPPER ( Pvalue ) = 'FNAME'
THEN
Dml_Sql := 'UPDATE ' || V_Table_Name ||
' SET ' || V_Column_Name
|| '= SUBSTR(' ||V_Column_Name ||',1,2)
||SUBSTR(' ||V_Column_Name ||',6,1)
||SUBSTR(' ||V_Column_Name ||',5,1)
||SUBSTR(' ||V_Column_Name ||',3,1)
||SUBSTR(' ||V_Column_Name ||',7,2)';
WHEN UPPER ( Pvalue ) = 'PHONE'
THEN
Dml_Sql := 'UPDATE ' || V_Table_Name ||
' SET ' || V_Column_Name
|| '= SUBSTR(' ||V_Column_Name ||',1,2)
||SUBSTR(' ||V_Column_Name ||',6,1)
||SUBSTR(' ||V_Column_Name ||',4,1)
||SUBSTR(' ||V_Column_Name ||',5,1)
||SUBSTR(' ||V_Column_Name ||',7,3)';
WHEN UPPER ( Pvalue ) = 'EMAIL'
THEN
Dml_Sql := 'UPDATE ' || V_Table_Name ||
' SET ' || V_Column_Name
|| '= SUBSTR(' ||V_Column_Name ||',2,instr(' ||V_Column_Name ||',''@'',1,1)-3)';
END CASE;
IF Dml_Sql != '*** N/A ***' THEN
EXECUTE IMMEDIATE Dml_Sql;
END IF;
END LOOP;
EXCEPTION
WHEN Bad_Parameter
THEN
Raise_Application_Error ( -20001, '!Error: column does not exist: ' || Pvalue );
END;
/
August 27th, 2014, 09:51 AM
-
To make it easy, replace these:
Code:
V_Table_Name C_Update.Table_Name%TYPE;
V_Column_Name C_Update.Column_Name%TYPE;
With these:
Code:
V_Table_Name VARCHAR2(100);
V_Column_Name VARCHAR2(100);
August 28th, 2014, 04:27 AM
-