11 Replies Latest reply: Jun 4, 2013 1:55 AM by jariola RSS

    Create external table for csv import from dynamic action

    Skirnir
      Hello,

      I try to import some data from a csv to a table. The csv lies on the server and i try to make it to an external table to copy its contents to the master data table.
      I can't even save the code, without this error:
      PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
      ( begin case declare exit for goto if loop mod null pragma raise return select update
      while with <an identifier> <a double-quoted delimited-identifier> <a bind variable>
      << continue close current delete fetch lock insert open rollback savepoint set sql execute
      commit forall merge pipe purge
      It works fine in the SQL Developer.
      CREATE TABLE  "DATA_CSV" 
         (     "C1" VARCHAR2(255), 
           "C2" VARCHAR2(255), 
           "C3" VARCHAR2(255), 
           "C4" VARCHAR2(255), 
           "C5" VARCHAR2(255), 
           "C6" VARCHAR2(255), 
           "C7" VARCHAR2(255), 
           "C8" VARCHAR2(255), 
           "C9" VARCHAR2(255), 
           "C10" VARCHAR2(255), 
           "C11" VARCHAR2(255), 
           "C12" VARCHAR2(255), 
           "C13" VARCHAR2(255), 
           "C14" VARCHAR2(255), 
           "C15" VARCHAR2(255), 
           "C16" VARCHAR2(255), 
           "C17" VARCHAR2(255), 
           "C18" VARCHAR2(255), 
           "C19" VARCHAR2(255), 
           "C20" VARCHAR2(255)
         )
          ORGANIZATION EXTERNAL
          (
            TYPE ORACLE_LOADER
            DEFAULT DIRECTORY FTP_FOLDER
            ACCESS PARAMETERS (
              records delimited BY newline
              fields terminated BY ';'
              optionally enclosed BY '"'
              lrtrim
              missing field VALUES are NULL
            )
          LOCATION ('foo.csv')
          );
      {code}
      
      The server I work on, runs on Apex 4.2.2.
      
      Thanks in advance, for your help
      Skirnir                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
        • 1. Re: Create external table for csv import from dynamic action
          Mark Wagoner
          Did you try placing your code in an anonymous block (BEGIN ... END)?
          • 3. Re: Create external table for csv import from dynamic action
            Roel Hartman
            That's because you are trying to issue a DDL statement (CREATE blablabla). That's not PL/SQL....
            However you can do that using dynamic (PL)SQL with the EXECUTE IMMEDIATE command : see http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems017.htm for examples
            • 4. Re: Create external table for csv import from dynamic action
              Vite DBA
              In PL/SQL, any DDL can not run native. Use "execute immediate".

              Regards
              Andre
              • 5. Re: Create external table for csv import from dynamic action
                Skirnir
                This helps for the create table problem, but i stollt got an error with the external table:
                PLS-00103: Encountered the symbol "EXTERNAL" when expecting one of the following: := . ( @ % ;
                • 6. Re: Create external table for csv import from dynamic action
                  Roel Hartman
                  It might help when you show you're modified / actual code ...
                  • 7. Re: Create external table for csv import from dynamic action
                    Mike Kutz
                    Skirnir wrote:
                    Hello,

                    I try to import some data from a csv to a table. The csv lies on the server and i try to make it to an external table to copy its contents to the master data table.
                    Why not just use a Data Loader page? (I'm sure you have your reasons.)
                    It works fine in the SQL Developer.
                    Than why not make it in SQL Developer and keep it?
                    If you need to change the file name for an external table:
                    alter table myapp_schema.DATA_CSV location ( 'newfilename.csv' )
                    usage note: this is also a DDL (see above notes)
                    security note: this should be ran as the owner of the table. (ie wrap in in a procedure in a package)
                    • 8. Re: Create external table for csv import from dynamic action
                      Skirnir
                      I can't do it via a Data Loader Page, because the data is actually from an FTP-Server. In the sequence, before the process, I get the data via a PLSQL script an put it in a directory. From there it should be read by the given piece of code.

                      Here is the new, modified version:
                      BEGIN
                        EXECUTE IMMEDIATE'
                        CREATE TABLE  "MY_TABLE_CSV" 
                         (     "C1" VARCHAR2(255), 
                           "C2" VARCHAR2(255), 
                           "C3" VARCHAR2(255), 
                           "C4" VARCHAR2(255), 
                           "C5" VARCHAR2(255), 
                           "C6" VARCHAR2(255), 
                           "C7" VARCHAR2(255), 
                           "C8" VARCHAR2(255), 
                           "C9" VARCHAR2(255), 
                           "C10" VARCHAR2(255), 
                           "C11" VARCHAR2(255), 
                           "C12" VARCHAR2(255), 
                           "C13" VARCHAR2(255), 
                           "C14" VARCHAR2(255), 
                           "C15" VARCHAR2(255), 
                           "C16" VARCHAR2(255), 
                           "C17" VARCHAR2(255), 
                           "C18" VARCHAR2(255), 
                           "C19" VARCHAR2(255), 
                           "C20" VARCHAR2(255)
                         )';
                         ORGANIZATION EXTERNAL
                         (
                           TYPE ORACLE_LOADER
                           DEFAULT DIRECTORY SOME_FOLDER
                           ACCESS PARAMETERS (
                             records delimited BY newline
                             fields terminated BY ';'
                             optionally enclosed BY '"'
                             lrtrim
                             missing field VALUES are NULL
                           )
                         LOCATION ('foo.csv')
                         );
                      END;
                      {code}
                      
                      Regards
                      Skirnir                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                      • 9. Re: Create external table for csv import from dynamic action
                        jariola
                        Hi,

                        Whole table creation clause should be inside quotes.
                        Try
                        BEGIN
                          EXECUTE IMMEDIATE q'!
                          CREATE TABLE  "MY_TABLE_CSV" 
                           (     "C1" VARCHAR2(255), 
                             "C2" VARCHAR2(255), 
                             "C3" VARCHAR2(255), 
                             "C4" VARCHAR2(255), 
                             "C5" VARCHAR2(255), 
                             "C6" VARCHAR2(255), 
                             "C7" VARCHAR2(255), 
                             "C8" VARCHAR2(255), 
                             "C9" VARCHAR2(255), 
                             "C10" VARCHAR2(255), 
                             "C11" VARCHAR2(255), 
                             "C12" VARCHAR2(255), 
                             "C13" VARCHAR2(255), 
                             "C14" VARCHAR2(255), 
                             "C15" VARCHAR2(255), 
                             "C16" VARCHAR2(255), 
                             "C17" VARCHAR2(255), 
                             "C18" VARCHAR2(255), 
                             "C19" VARCHAR2(255), 
                             "C20" VARCHAR2(255)
                           )
                           ORGANIZATION EXTERNAL
                           (
                             TYPE ORACLE_LOADER
                             DEFAULT DIRECTORY SOME_FOLDER
                             ACCESS PARAMETERS (
                               records delimited BY newline
                               fields terminated BY ';'
                               optionally enclosed BY '"'
                               lrtrim
                               missing field VALUES are NULL
                             )
                           LOCATION ('foo.csv')
                           )!';
                        END;
                        {code}
                        
                        Regards,
                        Jari
                        -----
                        My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
                        Twitter: http://www.twitter.com/jariolai                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                        • 10. Re: Create external table for csv import from dynamic action
                          Skirnir
                          Thank you! This works fine!

                          I understand what the 'q' stands for, but I dont get the meaning of the exclamation mark.

                          Thank you!
                          Skirnir
                          • 11. Re: Create external table for csv import from dynamic action
                            jariola
                            Hi,

                            This might help
                            http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/02_funds.htm#i38404

                            Regards,
                            Jari
                            -----
                            My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
                            Twitter: http://www.twitter.com/jariolai