Home » SQL & PL/SQL » SQL & PL/SQL » Create External table using Execute Immediate
Create External table using Execute Immediate [message #240991] Mon, 28 May 2007 02:54 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
Can I create an External Table using Execute statement.

I created a directory as :
create directory testdir as  'd:\external';


I am able to create the below External Table using :
 create table exttemp
   (
   empno number(5),
   ename varchar2(50),
   sal number(10)
   )
   organization external
   (
   default directory testdir
   access parameters
   (
   records delimited by newline
   skip 1
   fields terminated by ","
   optionally enclosed by '"'
   )
   location('test.csv')
  )


Now I want to create this External in a PL/SQL block using Execute Immediate.

But this is giving below error:
SQL> begin
  2  execute immediate 'create table exttemp
  3    (
  4    empno number(5),
  5    ename varchar2(50),
  6    sal number(10)
  7    )
  8    organization external
  9    (
 10    default directory testdir
 11    access parameters
 12    (
 13    records delimited by newline
 14    skip 1
 15    fields terminated by ","
 16    optionally enclosed by '"'
 17    )
 18    location('test.csv')
 19   )' 
 20  end;
 21  /
ERROR:
ORA-01740: missing double quote in identifier


Please let me know the way as how can I create the external table in a PL/SQL block.

Regards,
Mona Singh

[Updated on: Mon, 28 May 2007 02:54]

Report message to a moderator

Re: Create External table using Execute Immediate [message #240998 is a reply to message #240991] Mon, 28 May 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When ' are inside a string like here inside the execute immediate string you have to double them:
'test.csv' -> ''test.csv''

Regards
Michel
Re: Create External table using Execute Immediate [message #241003 is a reply to message #240998] Mon, 28 May 2007 03:24 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for your reply Michel. I changed 'test.csv' to "test.csv". But this gives the same error.

  1    begin
  2     execute immediate 'create table exttemp
  3      (
  4      empno number(5),
  5      ename varchar2(50)
  6      sal number(10)
  7      )
  8      organization external
  9      (
 10      default directory testdir
 11      access parameters
 12      (
 13      records delimited by newline
 14      skip 1
 15      fields terminated by ","
 16      optionally enclosed by '"'
 17      )
 18      location("test.csv")
 19     )';
 20*   end;
SQL> /
ERROR:
ORA-01740: missing double quote in identifier


Regards,
Mona
Re: Create External table using Execute Immediate [message #241006 is a reply to message #241003] Mon, 28 May 2007 03:29 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Dont you need

optionally enclosed by ''"''
Re: Create External table using Execute Immediate [message #241007 is a reply to message #241003] Mon, 28 May 2007 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You used double-quotes " instead of double the quotes ''

And you have to do the same thing with '"'.

Regards
Michel

[Updated on: Mon, 28 May 2007 03:58]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241012 is a reply to message #241007] Mon, 28 May 2007 03:46 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have used double quotes only...."".
If I had used single quotes two times instead of double quotes ...it would look like ....''
Actually, it lloks like if single quote is used here....but I have used double quotes only.

[Updated on: Mon, 28 May 2007 03:47]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241014 is a reply to message #241007] Mon, 28 May 2007 03:48 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

declare
v varchar2(1000);
begin
v:='create table exttemp
        (
       empno number(5),
        ename varchar2(50)
       sal number(10)
        )
        organization external
        (
       default directory testdir
       access parameters
       (
       records delimited by newline
       skip 1
       fields terminated by ","
       optionally enclosed by ''"''
       )
       location("test.csv")
      )';
dbms_output.put_line(v);
--execute immediate v;
    end;


output
create table exttemp
        (
       empno number(5),
        ename varchar2(50)
       sal number(10)
        )
        organization external
        (
       default directory testdir
       access parameters
       (
       records delimited by newline
       skip 1
       fields terminated by ","
       optionally enclosed by '"'
       )
       location("test.csv")
      )

i guess you get it now Razz

and added to that . you are missing a comma after ename

begin
  2     execute immediate 'create table exttemp
  3      (
  4      empno number(5),
  5      ename varchar2(50)
  6      sal number(10)
  7      )


regards
shanth.

[Updated on: Mon, 28 May 2007 03:55]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241018 is a reply to message #241014] Mon, 28 May 2007 03:56 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have added the comma, but what else I am missing...as even now I am getting the same error.
Re: Create External table using Execute Immediate [message #241021 is a reply to message #241012] Mon, 28 May 2007 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant you have to use:
''

and not
"

Regards
Michel
Re: Create External table using Execute Immediate [message #241025 is a reply to message #241018] Mon, 28 May 2007 04:00 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

i have edited and updated my previous message.
did you check that and do you recieve the same error. after altering the code as above?


regards
shanth

[Updated on: Mon, 28 May 2007 04:01]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241050 is a reply to message #241025] Mon, 28 May 2007 04:32 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Ya I am getting the same error when try to run the excute statement:
  1  declare
  2  v varchar2(1000);
  3  begin
  4  v:='create table exttemp
  5          (
  6         empno number(5),
  7          ename varchar2(50),
  8         sal number(10)
  9          )
 10          organization external
 11          (
 12         default directory testdir
 13         access parameters
 14         (
 15         records delimited by newline
 16         skip 1
 17         fields terminated by ","
 18         optionally enclosed by ''"''
 19         )
 20         location("test.csv")
 21        )';
 22  --execute immediate v;
 23*     end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  v varchar2(1000);
  3  begin
  4  v:='create table exttemp
  5          (
  6         empno number(5),
  7          ename varchar2(50),
  8         sal number(10)
  9          )
 10          organization external
 11          (
 12         default directory testdir
 13         access parameters
 14         (
 15         records delimited by newline
 16         skip 1
 17         fields terminated by ","
 18         optionally enclosed by ''"''
 19         )
 20         location("test.csv")
 21        )';
 22  execute immediate v;
 23*     end;
SQL> /
declare
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 22
Re: Create External table using Execute Immediate [message #241056 is a reply to message #241050] Mon, 28 May 2007 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First
("test.csv")

should be
(''test.csv'')

Regards
Michel
Re: Create External table using Execute Immediate [message #241058 is a reply to message #241050] Mon, 28 May 2007 04:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you create this table outside of pl/sql?
This saves you the quotes-trouble, plus it allows you to reference it with static sql.
Using dynamic sql to create the external table, like you do, doesn't add anything, since no element of your create statement is dynamic..

the error, by the way is (as Michel said a couple of times):
location(''test.csv'') instead of "test.csv" 


[Edit: hm ,too late. Michel told you yet another time Smile]

[Updated on: Mon, 28 May 2007 04:47]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241066 is a reply to message #241058] Mon, 28 May 2007 04:53 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Michel/Frank,
I am able to create the table now.

Regards,
Mona
Re: Create External table using Execute Immediate [message #241072 is a reply to message #241066] Mon, 28 May 2007 04:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
But again, why use dynamic sql for that?
Re: Create External table using Execute Immediate [message #241078 is a reply to message #240991] Mon, 28 May 2007 05:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Frank,
I have an excel file with two sheets and I need to import it in a oracle table.
First I saved it as .csv which allows me to save only one sheet with one csv file.
So I am now making the demo table exttemp to see how it works.
I have to write a sql file which will be called from a .ksh file. The name of the table and csv file will change so will be sent as parameters to the sql file.

So I am using the external table which will create the table as well as import the data from csv file.
Now I have to work on how to import two sheets of the source excel sheet. I saved the 2 sheets as test.csv and test1.csv.
Now test.csv is imported and now I have to append the other to the same table.

Let me know if I am working in the right direction.
And if this is correct way then how to import the other csv file to this table.

Regards,
Mona
Re: Create External table using Execute Immediate [message #241081 is a reply to message #241078] Mon, 28 May 2007 05:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You could create one external tables, referencing both test.csv and test1.csv. Here is an example by Maaher.
If you have a new set of date, simply replace the csv-files, but leave your table alone.
After replacing the files, you can query the external table again.
Re: Create External table using Execute Immediate [message #241091 is a reply to message #241081] Mon, 28 May 2007 05:38 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Frank,
The code given by Maaher works for miultiple tables and I am able to create the table with both the csv files.
The above table gets created by using test.csv and test1.csv. What in case if I have a new csv file to be imported in the already created table.
Now I do not want to recreate the table that has already imported the two files test.csv and test1.csv. But I want to append only the new csv file test2.csv



  declare
  v varchar2(1000);
  begin
  v:='create table exttemp
          (
         empno number(5),
          ename varchar2(50),
         sal number(10)
          )
          organization external
          (
         default directory testdir
         access parameters
         (
         records delimited by newline
         skip 1
         fields terminated by ","
         optionally enclosed by ''"''
         )
 location(''test.csv'', ''test1.csv'')
       )';
 execute immediate v;
     end;


Please advice.

[Updated on: Mon, 28 May 2007 06:34]

Report message to a moderator

Re: Create External table using Execute Immediate [message #241164 is a reply to message #241091] Mon, 28 May 2007 09:39 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't consider these external tables as 'steady' tables. The idea of external tables is that you import into your actual tables.

Now, you're still using dynamic sql to create the external table. Create one table prior to building code. Do not use pl/sql for it.
Previous Topic: Errbuf and retcode...
Next Topic: Real time SQL queries
Goto Forum:
  


Current Time: Mon Dec 05 14:45:52 CST 2016

Total time taken to generate the page: 0.18741 seconds