Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0

    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;
    /
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    860
    Rep Power
    388

    Talking


    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    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"
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    AFter modifying it says the column "ADDRESS" has to be declared.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    Originally Posted by Mitu08
    AFter modifying it says the column "ADDRESS" has to be declared.
    Experts.. Please help
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    860
    Rep Power
    388

    Cool


    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/\!@#$%^&*()'','' ''),'' '')';
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    Thanks a lot. This helped me to fix the error and move on..
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    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..
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    860
    Rep Power
    388
    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.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    You are not populating the TBL_ARRAY, therefore it's NULL.
    can you explain pls.. what should i do
  20. #11
  21. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    860
    Rep Power
    388

    Cool


    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.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    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.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    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;
    /
  26. #14
  27. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    860
    Rep Power
    388

    Talking


    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);
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    17
    Rep Power
    0
    Thanks a lot...
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo