#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Posts
    3
    Rep Power
    0

    Thumbs down Switching characters using Update


    All,

    I'm trying to write a function that can decode a message by switching pairs of characters.
    I'm trying dynamic sql as below and facing the error PLS-00103.
    Please advise what's wrong
    *********
    Code:
    CREATE OR REPLACE PROCEDURE "replace" ( Ovalue IN VARCHAR2, Tvalue IN VARCHAR2, Pvalue IN VARCHAR2 )
    IS
      Dml_Sql        VARCHAR2 ( 1000 );
      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.Table_Name   LIKE Tvalue
             AND T.Owner       = C.Owner
             AND T.Table_Name  = C.Table_Name
             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 ('STDID'
                                 ,'STDNAME'
                                 ,'%%'
                                 )
       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 ) = UPPER('STDNAME')
            THEN
    
               Dml_Sql     := 'UPDATE ' || v_owner ||'.'|| v_Table_Name ||
                               '   SET ' || V_Column_Name
                                         || '= (regexp_replace(||V_Column_Name ||,'(.)(.)', '\2\1'))';
    
                                         
    
        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;
    /
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Posts
    3
    Rep Power
    0
    All, I am a student and in process of doing project for my degree. Appreciate your help
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2015
    Posts
    3
    Rep Power
    0
    Able to fix it.. thanks

IMN logo majestic logo threadwatch logo seochat tools logo