Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Cool


    Originally Posted by Mitu08
    Thanks a lot...
    No Problemo...
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    Implemented the same code in a different server/schema. while executing it facing the errors
    ORA-01031: insufficient privileges
    ORA-02063: preceding line from DBLNKXXXX
    ORA-06512: at "XXX.MASK", line 88
    ORA-06512: at line 1

    where Line 88-->EXECUTE IMMEDIATE Dml_Sql;

    Checked and found there are no dblinks in that user..and also the procedure is created in that user itself
    Pls guide...
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    Also i am getting the below error:

    SQL> exec mask('NRIC');
    BEGIN mask('NRIC'); END;
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "EXS.MASK", line 88
    ORA-06512: at line 1
  6. #19
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Cool


    Not knowing the real (actual) procedure you coded, it appears you need to consider the owner of the tables.
    Therefore change this:
    Code:
     
    . . .   E t c
    -- Add the owner:
       CURSOR C_Update
       IS
          SELECT Owner, Table_Name, Column_Name
            FROM All_Tab_Columns
           WHERE Owner NOT . . .
    . . .   E t c
       Rec_Add        Tbl_Array_Type;
    
       V_Owner        VARCHAR2 ( 40 );
       V_Table_Name   VARCHAR2 ( 200 );
       V_Column_Name  VARCHAR2 ( 100 );
    
       Bad_Parameter  EXCEPTION;
    BEGIN
    . . .   E t c
       FOR I IN 1 .. Rec_Add.COUNT
       LOOP
          V_Owner       := Rec_Add ( I ).Owner;
          V_Table_Name  := V_Owner || '.' || Rec_Add ( I ).Table_Name;
          V_Column_Name := Rec_Add ( I ).Column_Name;
          Dml_Sql       := '*** N/A ***';
    
          CASE . . .
    . . .   E t c


    Obviously you will need update permissions on ALL tables from ALL schemas that contain the desired columns.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    Hi..Thank U very much for your support.
    Instead of providing update permissions on all Tables/schemas I thought of doing it on a schema wise. So I used user_tab_columns instead of all_tab_columns and it works.
    Now, I face another challenge. Every schema has different table definitions.

    For example in schema(A) NAME field is described as NAME where as in schema(B) NAME is described as USERNAME.

    I changed the condition as Column_Name LIKE '%Pvalue%'; . Is it possible to use the CASE statements or other to update the fields with the '%Pvalue%'. Pls advise.

    SELECT Table_Name, Column_Name
    FROM user_Tab_Columns
    WHERE Column_Name LIKE '%Pvalue%';
  10. #21
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Cool


    Originally Posted by Mitu08
    Hi...
    . . .
    For example in schema(A) NAME field is described as NAME where as in schema(B) NAME is described as USERNAME.

    I changed the condition as Column_Name LIKE '%Pvalue%'; . Is it possible to use the CASE statements or other to update the fields with the '%Pvalue%'. Pls advise.
    What if there are TWO or more columns in the same table that match "LIKE '%Pvalue%'" condition?
    How you going to deal with that situation?
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    What if there are TWO or more columns in the same table that match "LIKE '%Pvalue%'" condition?
    How you going to deal with that situation? --> Still thinking about how to implement.

    Below are the steps i am trying.

    - Create the procedure in SYS.
    - execute the procedure with owner and column fields.

    Below is the procedure code i am trying

    CREATE OR REPLACE PROCEDURE Mask ( ovalue IN varchar2, Pvalue IN VARCHAR2 )
    IS
    Dml_Sql VARCHAR2 ( 1000 );
    CURSOR C_Update
    IS
    SELECT owner,Table_Name, Column_Name
    FROM all_Tab_Columns
    WHERE owner LIKE ovalue
    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_owner varchar2(40);
    V_Table_Name varchar2(200);
    V_Column_Name varchar2(200);
    Bad_Parameter EXCEPTION;

    BEGIN

    IF UPPER ( Pvalue ) NOT IN ('ADDRESS'
    , 'NRIC'
    , 'NAME'
    , '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_owner := Rec_Add(I).owner;
    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_owner ||'.'|| v_Table_Name ||
    ' SET ' || v_Column_Name || '=LTRIM(TRANSLATE(' || v_Column_Name || ',''0123456789/\!@#$%^&*()'','' ''),'' '')';

    WHEN UPPER ( Pvalue ) = 'NRIC'
    THEN
    Dml_Sql := 'UPDATE ' || v_owner ||'.'|| 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_owner ||'.'|| 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_owner ||'.'|| 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_owner ||'.'|| 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;
    /
    when executing i am facing the below error:

    SQL> exec mask('HDS','EMAIL');
    BEGIN mask('HDS','EMAIL'); END;

    *
    ERROR at line 1:
    ORA-01732: data manipulation operation not legal on this view
    ORA-06512: at "SYS.MASK", line 91
    ORA-06512: at line 1

    Pls advise
  14. #23
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    392

    Thumbs up


    Perhaps you are picking up also the "views".
    Change the cursor to:
    Code:
    -- Etc ...
       CURSOR C_Update
       IS
          SELECT T.Owner, T.Table_Name, C.Column_Name
            FROM All_Tables T, All_Tab_Columns C
           WHERE T.Owner       LIKE Ovalue
             AND T.Owner       = C.Owner
             AND T.Table_Name  = C.Table_Name
             AND Column_Name   LIKE Pvalue
           ;
    -- Etc ...
  16. #24
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    Thanks a lot for your support..

    Originally Posted by LKBrwn_DBA
    Perhaps you are picking up also the "views".
    Change the cursor to:
    Code:
    -- Etc ...
       CURSOR C_Update
       IS
          SELECT T.Owner, T.Table_Name, C.Column_Name
            FROM All_Tables T, All_Tab_Columns C
           WHERE T.Owner       LIKE Ovalue
             AND T.Owner       = C.Owner
             AND T.Table_Name  = C.Table_Name
             AND Column_Name   LIKE Pvalue
           ;
    -- Etc ...
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2014
    Posts
    21
    Rep Power
    0
    Redg the common colmns i have tbl structure like this

    Table A has columns (BLK_HSE_NO,STREET_NAME, UNIT_NO, POSTAL_CODE,ADDR_LINE1 and ADDR_LINE2). Is there anyway to generalise the program which can it uses the Address logic and mask it instead of mentioning individual case statements.. Can you pls share your ideas..
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo