Dev Shed Forums

**Mitu08**

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;
/

**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

**Mitu08**

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"

**Mitu08**

AFter modifying it says the column "ADDRESS" has to be declared.

**Mitu08**

Originally Posted by Mitu08

AFter modifying it says the column "ADDRESS" has to be declared.

Experts.. Please help

**LKBrwn_DBA**

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/\!@#$%^&*()'','' ''),'' '')';

**Mitu08**

Thanks a lot. This helped me to fix the error and move on..

**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..

**LKBrwn_DBA**

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.

**Mitu08**

Originally Posted by LKBrwn_DBA

You are not populating the TBL_ARRAY, therefore it's NULL.

can you explain pls.. what should i do

**LKBrwn_DBA** · 11:43 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;
/

**Mitu08**

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.

**Mitu08**

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;
/

**LKBrwn_DBA**

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

**Mitu08**

Thanks a lot...

Dev Shed Forums

Thread: Dynamic SQL help required

Dynamic SQL help required