Convert SQL Query to Dynamic SQL

Question

I need to convert my normal SQL query to dynamic sql, can any one please refer the below SQL query and i have posted the error i am facing while executing the dynamic sql

SQL Query:

insert into p_table_name(
select  col001, col002, col003, col004, col005, 
                    col006, col007, col008, col009, col010 AS l_columns_item ,
                    -- more columns (col011 to col300) can be selected here.
  from apex_application_temp_files f, 
       table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_file_name                   => f.filename ) ) p
 where f.name = p_file_name
)

Dynamic SQL Query as Expected.

IF p_table_name = 'DUMMY_TABLE' THEN 
      l_columns_item := 'c001,c002,c003,c004,c005,c006' || ',c007,c008,c009';
END IF; 

l_ddl := 'insert into '||p_table_name||' '||
               'select '||l_columns_item||' '||  
               'from apex_application_temp_files f'||
               '           table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_file_name                   => f.filename ) ) p'
                  'where f.name ='''||p_file_name||'''';
    execute immediate l_ddl;
    RETURN;

Error I am getting:

Error(432,1): PLS-00103: Encountered the symbol " " when expecting one of the following: * & = -     + ; < / > at in is mod remainder not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like like2    like4 likec between || member submultiset 

Answer 1

Missing comma (between tables in the FROM clause) and a space + concatenation sign in front of the WHERE clause.

While working with dynamic SQL, don't execute the statement at once - display it first, make sure it is correct, then execute it.

   l_ddl :=
         'insert into '
      || p_table_name
      || ' '
      || 'select '
      || l_columns_item
      || ' '
      || 'from apex_application_temp_files f, '
      || '           table( apex_data_parser.parse(
                  p_content                     => f.blob_content,
                  p_file_name                   => f.filename ) ) p '
      || 'where f.name ='''
      || p_file_name
      || '''';

   DBMS_OUTPUT.put_line (l_ddl);        --> display it first

The (formatted) result is then

INSERT INTO test
   SELECT c001,
          c002,
          c003,
          c004,
          c005,
          c006,
          c007,
          c008,
          c009
     FROM apex_application_temp_files f,
          TABLE (
             apex_data_parser.parse (p_content    => f.blob_content,
                                     p_file_name  => f.filename)) p
    WHERE f.name = 'file'

which looks OK, but you'll know once you execute it. I can't, don't have your tables.