4 Replies Latest reply: Jan 22, 2013 4:53 PM by DBA112 RSS

    Dynamic SQL

    DBA112
      Dear Experts,

      I'm trying to prepare a dynamic insert statement as below, dynamic SQL code and the result are display below:

      I see problem with insert statement that has a blank space after my instance number. How do I over come the space. Problem is the "instance_number" not getting inserted in to a new table where I'm trying load the data
      select 'insert into table1 ('''||instance_number||''',',''''||service_name||''',','to_date('''||end_time||''',''MM-DD-YYYY''),',''||sum(db_cpu)||','
      as db_cpu,
      ''||sum(db_time)||',' as db_time,
      ''||sum(app_wait_time)||',' as app_wait_time,
      ''||sum(cluster_wait_time)||',' as cluster_wait_time,
      ''||sum(concurrency_wait_time)||',' as concurrency_wait_time,
      ''||sum(db_block_changes)||',' as db_block_changes,
      ''||sum(execute_count)||',' as execute_count,
      ''||sum(parse_count)||',' as parse_count,
      ''||sum(physical_reads)||',' as physical_reads,
      ''||sum(physical_writes)||',' as physical_writes,
      ''||sum(cursor_cache_hits)||',' as cursor_cache_hits,
      ''||sum(logical_reads)||',' as logical_reads,
      ''||sum(sql_elapsed_time)||',' as sql_elapsed_time,
      ''||sum(io_wait_time)||',' as io_wait_time,
      ''||sum(user_calls)||  ');' as user_calls
      
      insert into table1 values ('2',
      'snip0maptoit',                                                     to_date('01-21-2013','MM-DD-YYYY'), 1761650000,
      31442606371,                              9588122,                                  1315713537,
      2292084380,                               15286062,                                 1393419,
      786196,                                   61929,                                             0,
      682958,                                   34587768,                                 10600605903,
      1466716598,                               6232176);
        • 1. Re: Dynamic SQL
          Robert Angel
          Hi,


          why not use regex_replace to strip the spaces?

          And, million dollar question, why do you need to do this with dynamic sql??


          regards,

          Robert.
          • 2. Re: Dynamic SQL
            DBA112
            I need to schedule it as part of recurring report that queries AWR tables in the database and dynamically prepares insert stmts to populate data in our custom tables.
            • 3. Re: Dynamic SQL
              JustinCave
              DBA112 wrote:
              I need to schedule it as part of recurring report that queries AWR tables in the database and dynamically prepares insert stmts to populate data in our custom tables.
              I'm not sure I understand. The structure of the AWR tables isn't changing and is known at compile time. The structure of your table is, presumably, unchanging and known at compile time. If you are trying to copy data from one static table to another, you would almost always want to use static SQL. I'm hard-pressed to see any reason to prefer dynamic SQL in this case.

              Justin
              • 4. Re: Dynamic SQL
                sb92075
                I am going to restate what I think you are doing & I want you to say whether I correctly state what occurs.

                You are generating AWR reports which takes data out of existing tables & deposits a text file on disk.
                Now you are writing PL/SQL to read the text AWR report file so you can INSERT specific values into custom tables.

                Are the 2 sentences above correct?

                If so, why not use plain SQL to move the data from AWR repository table into your custom tables?