2 Replies Latest reply: Jun 2, 2012 4:55 AM by Billy~Verreynne RSS

    ORACLE DYNAMIC SQL

    user4485803
      i am writing generic code for deletion based on DYN input table.
      i have problem to build SQL AND CALUSE.
      HERE IS THE CODE.

      STEP 1: CREATE INPUT TABLE

      TABLE : DYN

      APP_MODEULE APP_MODEULE_ID SNO EMPNO
      -----------------------------------------------------------------------------
      X123 CAR 1 1
      X145 CAR 2 1


      STEP 1: CREATE GENERATE SP TO DELETE

      CREATE OR REPLACE PROCEDURE TESTING_SQL
      (
      P VARCHAR2 , --- APP_MODULE
      S VARCHAR2 , -- APP_MODEULE_ID
      C INTEGER , -- SNO
      F INTEGER) -- EMPNO
      AS
      cursor c_tables
      is
      select
      TABLE_NAME FROM dyn WHERE APP_MODULE = P AND app_module_ID = S;


      cursor c_cols
      is
      select i.column_name
      from all_tab_columns i
      where i.owner = 'XXX'
      and i.table_name IN ( SELECT TABLE_NAME FROM dyn WHERE APP_MODULE = P AND app_module_ID = S )
      order by column_id;

      where_clause VARCHAR2(32767);
      and_clause VARCHAR2(32767);
      begin

      for rec in c_tables loop

      where_clause := NULL ;

      for col in c_cols loop

      if where_clause is NULL then
      where_clause := ' WHERE ' || col.column_name || '=' || C ;
      else
      where_clause := where_clause || ' AND ' || col.column_name || '=' || F;
      end if;
      end loop;


      begin
      EXECUTE IMMEDIATE 'DELETE FROM '||REC.table_name|| where_clause ;

      dbms_output.put_line(REC.table_name||', '||SQL%ROWCOUNT);
      -- commit;
      exception
      when others then
      dbms_output.put_line(REC.table_name||', '||SQLERRM);
      rollback;
      end;
      end loop;
      end;
      /
      set serveroutput off
      spool off

      PL/SQL RUN CALL: ( I HOPE I HAVE PROBLEM WITH BUILDING WHERE CLAUSE & AND CONDITION,PLEASE HELP ME TO RESOLVE THIS ISSUE )

      SET SERVEROUTPUT ON
      DECLARE
      P VARCHAR2(32767);
      S VARCHAR2(32767);

      BEGIN
      P := NULL;
      S := NULL;

      VINLOAD.TESTING_SQL ( 'CTS', 'CAR' , 3, 472863 );
      --MMIT;
      END;


      WHILE RUNNING I AM GETTING ERROR: PLEASE HELP ME TO FIX THIS ISSUE

      ORA-00933: SQL command not properly ended
        • 1. Re: ORACLE DYNAMIC SQL
          sb92075
          user4485803 wrote:
          i am writing generic code for deletion based on DYN input table.
          BAD, NON-SCALABLE & INEFFICIENT way to code!
          i have problem to build SQL AND CALUSE.
          HERE IS THE CODE.

          STEP 1: CREATE INPUT TABLE

          TABLE : DYN

          APP_MODEULE APP_MODEULE_ID SNO EMPNO
          -----------------------------------------------------------------------------
          X123 CAR 1 1
          X145 CAR 2 1


          STEP 1: CREATE GENERATE SP TO DELETE

          CREATE OR REPLACE PROCEDURE TESTING_SQL
          (
          P VARCHAR2 , --- APP_MODULE
          S VARCHAR2 , -- APP_MODEULE_ID
          C INTEGER , -- SNO
          F INTEGER) -- EMPNO
          AS
          cursor c_tables
          is
          select
          TABLE_NAME FROM dyn WHERE APP_MODULE = P AND app_module_ID = S;


          cursor c_cols
          is
          select i.column_name
          from all_tab_columns i
          where i.owner = 'XXX'
          and i.table_name IN ( SELECT TABLE_NAME FROM dyn WHERE APP_MODULE = P AND app_module_ID = S )
          order by column_id;

          where_clause VARCHAR2(32767);
          and_clause VARCHAR2(32767);
          begin

          for rec in c_tables loop

          where_clause := NULL ;

          for col in c_cols loop

          if where_clause is NULL then
          where_clause := ' WHERE ' || col.column_name || '=' || C ;
          else
          where_clause := where_clause || ' AND ' || col.column_name || '=' || F;
          end if;
          end loop;


          begin
          EXECUTE IMMEDIATE 'DELETE FROM '||REC.table_name|| where_clause ;

          dbms_output.put_line(REC.table_name||', '||SQL%ROWCOUNT);
          -- commit;
          exception
          when others then
          dbms_output.put_line(REC.table_name||', '||SQLERRM);
          rollback;
          end;
          end loop;
          end;
          /
          set serveroutput off
          spool off

          PL/SQL RUN CALL: ( I HOPE I HAVE PROBLEM WITH BUILDING WHERE CLAUSE & AND CONDITION,PLEASE HELP ME TO RESOLVE THIS ISSUE )

          SET SERVEROUTPUT ON
          DECLARE
          P VARCHAR2(32767);
          S VARCHAR2(32767);

          BEGIN
          P := NULL;
          S := NULL;

          VINLOAD.TESTING_SQL ( 'CTS', 'CAR' , 3, 472863 );
          --MMIT;
          END;


          WHILE RUNNING I AM GETTING ERROR: PLEASE HELP ME TO FIX THIS ISSUE

          ORA-00933: SQL command not properly ended
          The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
          Then print the variable before passing it to EXECUTE IMMEDIATE.
          COPY the statement & PASTE into sqlplus to validate its correctness.


          delete, remove, & eliminate all EXCEPTION code
          For reason why check these links.

          http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

          http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

          http://tkyte.blogspot.com/2007/03/challenge.html
          • 2. Re: ORACLE DYNAMIC SQL
            Billy~Verreynne
            user4485803 wrote:

            WHILE RUNNING I AM GETTING ERROR: PLEASE HELP ME TO FIX THIS ISSUE
            Why the issue/problem?

            Because of the totally idiotic use of dynamic SQL.

            Because of the totally brain dead approach to solving problems using SQL and PL/SQL.

            Because of a total lack of ignorance of Oracle concepts and fundamentals.

            Because of kicking database and server performance in the nuts - repeatedly.

            Because of pretending that code injection is not one of the major security concerns today.

            Want me to continue?

            Dynamic SQL, the way you are doing it, is wrong. There are no "fixes" to your "issues/errors".

            There is simply trashing the code. Forgetting about idiotic generic approaches. And doing it properly.

            Are you programmer enough to do it properly and correctly? Or are you just another code monkey?