3 Replies Latest reply on May 5, 2013 12:23 PM by Billy~Verreynne

    Need help for using Execute Immediate

    971033
      I am new to PLSQL and need a help in performing a task through Execute Immediate.

      There is a table that FMT_COL that has the list of columns and table name.

      COLUMN_NAME1 COLUMN_NAME2 TABLE_NAME
      -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      AGE     FMT_INT_AGE     EMPLOYEE

      The EMPLOYEE Table has following data

      NAME     AGE     FMT_INT_AGE
      -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      EMP1      28     28
      EMP2      30     30

      I need to run a Select Query through Execute Immediate like Select COLUMN_NAME1, COLUMN_NAME2 from TABLE_NAME and insert the values into a table say VALIDATE_EMP that should have the column and table names that is used in the Execute Immediate along with a validation like DECODE(replace(translate(FMT_INT_AGE,'1234567890','##########'),'#'),NULL,'PASS','FAIL')

      VALIDATE_EMP table should look like this

      TABLE COLUMN_NAME COLUMN_VALUE COLUMN_NAME_FMT COLUMN_FMT_VALUE VALIDATION_STATUS
      -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      EMPLOYEE AGE 28 FMT_INT_AGE 28 PASS
      EMPLOYEE AGE 30 FMT_INT_AGE 30 PASS

      Please help me on this.

      TIA,
      Balaji

      Edited by: 968030 on May 4, 2013 10:59 PM
        • 1. Re: Need help for using Execute Immediate
          You don't need execute immediate for this, and, more importantly, you shouldn't use execute immediate for this.
          Execute immediate will parse your statement everytime.
          PL/SQL was invented to reduce parsing.
          For the rest, your request looks like homework, and if we are going to do your homework, you will never learn PL/SQL.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Need help for using Execute Immediate
            971033
            I tried achieving this by referring http://www.adp-gmbh.ch/ora/plsql/bc/execute_immediate.html

            The challenges I had was that I could not pass the table name as parameter and also could understand how to insert the column name that is used as part of execute immediate into a table as one of the values.
            • 3. Re: Need help for using Execute Immediate
              Billy~Verreynne
              Not knowing the table to insert to, means a flawed database model. And flawed application code.


              There needs to be sound and robust justification for using dynamic SQL. And seeing an example of that on the web and thinking "+oh, what a shiny idea, I'll copy and use it!+" is not justification.

              We see dynamic SQL questions here in this forum, every single day. I cannot recall when last I've seen a valid question about using dynamic SQL. Invariable it is the wrong approach, for the wrong reasons.

              Nothing in your postings makes me think your problem is an exception - and valid use of dynamic SQL. Or that you even bothered to read the manual.

              If you want to see how execute immediate works, it is covered in chapter 7 of the Oracle® Database PL/SQL Language Reference.