1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Obsfucation procedure compiles but will not run

Discussion in 'SQL PL/SQL' started by Bobby_Lamp, Jun 21, 2012.

  1. Bobby_Lamp

    Bobby_Lamp Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello all, I want to preface this by saying that I am new to Oracle and new to these forums so bear with me if I'm not being concise enough on the problem. Basically I wrote this procedure (code below) to obsfucate a specific column within a table specified by the user in the procedure call. It is really frustrating because the procedure compiles, but when I try to pass in parameters it will not work and gives me errors. I am at my wit's end with this thing and any help is appreciated. Thanks!

    The basic code structure is that it will insert the values into a type of "control" table based on the active value, which will always be 1 or 0. Then based on the audit flag, which will always be 1 or 0, the procedure would either update the table with the dbms_random package or not depending on the value. Basically where the errors arise are when I call the procedure and pass in parameters. It has been giving me all kinds of crazy errors and stuff. The procedure call should look something like this: select schema.phi_scrub 'BLAMPE', 'TEST4', 'SSN', '1', '1'; Once again thanks in advance for the help and bear with me because I am very new to Oracle/plsql coding.

    create or replace
    PROCEDURE phi_scrub(p_schema_name in varchar2, p_table_name in varchar2, p_column_name in varchar2, p_active in varchar2, p_audit_flag in varchar2)
    AS

    BEGIN
    BEGIN
    FOR t in (SELECT schema_name, table_name,active FROM schema.encrypt_table WHERE schema_name=p_schema_name and table_name=p_table_name)
    LOOP
    BEGIN
    IF (t.active = 1) THEN
    dbms_output.put_line('Table already exists will not insert!');
    ELSE
    dbms_output.put_line('Inserting into encrypt_table!');
    INSERT INTO schema.encrypt_table (schema_name, table_name, column_name, active) VALUES (p_schema_name, p_table_name, p_column_name, p_active);
    commit;
    END IF;
    END;
    END LOOP;
    END;

    BEGIN
    FOR t2 in (SELECT schema_name, table_name, column_name, active FROM schema.encrypt_table WHERE active=1)
    LOOP
    BEGIN
    IF (p_audit_flag = '1') THEN
    execute immediate 'select count(*) from t2.table_name';
    dbms_output.put_line(' Rows available for update, change audit flag to 0 to perform update.');
    ELSE
    execute immediate 'update ' || t2.schema_name || '.' || t2.table_name ||' set ' || t2.column_name || ' = dbms_random(' || '''A''' || ', LENGTH(' || t2.column_name || '))';
    END IF;

    END;
    END LOOP;
    END;
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot call a procedure from a select; you write a function for that purpose. However you are not returning any values thus a procedure is probably what you need. To properly call a procedure you need either an anonymous PL/SQL block:

    Code (SQL):

    BEGIN
         phi_scrub('BLAMPE', 'TEST4', 'SSN', '1', '1');
    END;
    /
     
    or use an exec cmmand from the SQL prompt:

    Code (SQL):

    SQL> EXEC phi_scrub('BLAMPE', 'TEST4', 'SSN', '1', '1');
     
    Also note the syntax when calling a procedure requiring parameters; the parameter list is enclosed by ().
     
  3. Bobby_Lamp

    Bobby_Lamp Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David,


    It still isn't working though. It keeps telling me that the table or view doesn't exist, when it clearly does. :mad:
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You named a user 'schema'??
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You still not yet explained what error you are getting. tell us what error you are getting .
     
  6. Bobby_Lamp

    Bobby_Lamp Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hey sorry for not replying to the forum post I was out of office yesterday and I was sick so I didn't really check my email or anything. I did end up getting this procedure to work, but I basically had to start over from scratch. Here is the working code that I got. Basically the procedure pulls its information from a control table known as encrypt_table which is populated manually by the DBA(s) that would use this procedure. The procedure will then find the schema, table, and column and obsfucate the data in the specified column with random values using the dbms_random package. Pretty simple, but it was giving me fits the other day! I thank you all for your help!

    Code (Text):
    create or replace
    PROCEDURE        phi_scrub(p_audit_flag in varchar2)
    AS
    sql_query  varchar(2000);
    sql_query2  varchar(2000);
    sql_query3  varchar(2000);
    v1 varchar(2000);
    v2 varchar(2000);
    v3 varchar(2000);

    cursor phi_cur is select schema_name, table_name, column_name, active FROM blampe.encrypt_table  WHERE active=1;
    BEGIN
      IF p_audit_flag = '1'  THEN
              FOR t2 in phi_cur
              LOOP    
                  sql_query := 'select ';
                  sql_query := sql_query || ' count(*) from ' ||t2.schema_name||'.'||t2.table_name;        
                  execute immediate sql_query into v1;
                  dbms_output.put_line('TABLE:'||t2.table_name||'  Column: '||t2.column_name||' '||v1||' Rows available for update, change audit flag to 0 to perform update.');
              END LOOP;        
             
      ELSE
              FOR t2 in phi_cur
              LOOP
                  sql_query2 := 'update ';
                  sql_query3 := 'select ';
                  sql_query3 := sql_query3 ||' length('||t2.column_name||') from '||t2.schema_name||'.'||t2.table_name||' where rownum =1';
                    execute immediate sql_query3 into v2;
                    sql_query2 := sql_query2 || t2.schema_name || '.' || t2.table_name ||' set ' || t2.column_name || '= dbms_random.string('|| '''X'''|| ' , '||v2||' )';
                    execute immediate sql_query2;
                    commit;
                   
                    sql_query := 'select ';
                    sql_query := sql_query || ' count(*) from ' ||t2.schema_name||'.'||t2.table_name;        
                    execute immediate sql_query into v1;
                    dbms_output.put_line('TABLE:'||t2.table_name||'  Column: '||t2.column_name||' '||v1||' Rows updated and columns scrambled.');
                    --dbms_output.put_line(v2);      
              END LOOP;
      END IF;
    END;
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    coool :) Thank you for sharing the code in the forum.