This discussion is archived
4 Replies Latest reply: Jan 22, 2013 2:53 PM by DBA112 RSS

Dynamic SQL

DBA112 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points