Create dynamic function in oracle

Question

I have many tables with the same structure. One of the structure is given as follows: The table name is TRACK_CONNECTION.

Now i want to create dynamic function decrypt_password_global such that it can take dynamic input values and decrypt the password from table and return the value. I want to pass table_name,column_name,table_id,table_id_value in function as a input parameter such that while executing the function decrypt_password_global for example for TRACK_CONNECTION table i will pass (TRACK_CONNECTION,PASS,ID,11). In such way i can use decrypt_password_global function for other table also. I already created decrypt_password function for decrpytion of password and it works fine. I just want to use decrypt_password function in decrypt_password_global function for decryption of value from table.

oratom · Accepted Answer · 2016-04-21 09:36:17Z

1

build a SELECT-String and use EXECUTE IMMEDIATE / INTO :

CREATE OR REPLACE FUNCTION decrypt_password( table_name IN varchar2,column_name IN varchar2,table_id varchar2,table_id_val varchar2 ) RETURN VARCHAR2
  IS
     encrypted_pas varchar2(100);
     decrypted_pas varchar2(100);

   BEGIN
      EXECUTE IMMEDIATE 'select ' || column_name || ' from ' || table_name || ' where ' || table_id || ' = ' || table_id_val
         INTO encrypted_pas;
         Select decrypt_password(encrypted_pas) into decrypted_pas from dual;


  END decrypt_password;

answered Apr 21, 2016 at 9:36

oratom

2911 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create dynamic function in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related