This discussion is archived
11 Replies Latest reply: Jun 3, 2013 11:55 PM by jariola RSS

Create external table for csv import from dynamic action

Skirnir Newbie
Currently Being Moderated
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
    MarkWagoner Newbie
    Currently Being Moderated
    Did you try placing your code in an anonymous block (BEGIN ... END)?
  • 2. Re: Create external table for csv import from dynamic action
    Skirnir Newbie
    Currently Being Moderated
    Yes, the same error.
  • 3. Re: Create external table for csv import from dynamic action
    Roel Oracle ACE Director
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    It might help when you show you're modified / actual code ...
  • 7. Re: Create external table for csv import from dynamic action
    Mike Kutz Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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