2 Replies Latest reply: Mar 31, 2013 6:30 PM by sb92075 RSS

    Running DBMS_SQL.EXECUTE in Oracle Select Statment

    1000202
      HI i have table Emp which has 3 column namely name,st_date and end_date. table has 3 records as below:
      name st_date end_date
      X sysdate+10 sysdate+50
      Y sysdate+12 sysdate+30
      Z sysdate+15 sysdate+35

      Now if i run the run select statment select * from emp, it will show above values. but i need the values in the columns to be executed and shown in the select statemtn with alias column i.e. assign sysdate is : 01-jan-2013
      so i need the o/p to be:
      X 20-Jan-2013 19-Feb-2013
      Y 22-Jan-2013 30-Jan-2013
      Z 15-Jan-2013 04-feb-2013

      i dont want to create a function in plsql and pass the values to it. if u use select name,dbms_sql.execute(dbms_sql.parse(dbms_sql.open(),'select st_date from emp'))) is ok for me.

      Advance Thanks for your help!!
      i have restriction not able to create function, so i need to try in the sql statment only!!
        • 1. Re: Running DBMS_SQL.EXECUTE in Oracle Select Statment
          sybrand_b
          This design is severely flawed. If you would just store the 'delta', the number of days you want to add, as number, instead of the string 'sysdate+35'
          you could just select ..., sysdate + end_date_delta etc
          and you wouldn't need the disastrous construct you propose.
          Database is about proper design, not about hacking yourself out.
          The person who disallowed you to create functions was very sensible.
          -----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Running DBMS_SQL.EXECUTE in Oracle Select Statment
            sb92075
            997199 wrote:
            HI i have table Emp which has 3 column namely name,st_date and end_date. table has 3 records as below:
            name st_date end_date
            X sysdate+10 sysdate+50
            Y sysdate+12 sysdate+30
            Z sysdate+15 sysdate+35

            Now if i run the run select statment select * from emp, it will show above values. but i need the values in the columns to be executed and shown in the select statemtn with alias column i.e. assign sysdate is : 01-jan-2013
            so i need the o/p to be:
            X 20-Jan-2013 19-Feb-2013
            Y 22-Jan-2013 30-Jan-2013
            Z 15-Jan-2013 04-feb-2013

            i dont want to create a function in plsql and pass the values to it. if u use select name,dbms_sql.execute(dbms_sql.parse(dbms_sql.open(),'select st_date from emp'))) is ok for me.

            Advance Thanks for your help!!
            i have restriction not able to create function, so i need to try in the sql statment only!!
            How do I ask a question on the forums?
            SQL and PL/SQL FAQ


            what datatype are st_date & end_date?