OraFAQ Forum: SQL & PL/SQL » error creating external via dbms_utility.exec_ddl

Home » SQL & PL/SQL » SQL & PL/SQL » error creating external via dbms_utility.exec_ddl_statement@myDB (Oracle 11.2.9.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

error creating external via dbms_utility.exec_ddl_statement@myDB [message #650443]

Mon, 25 April 2016 09:50

gunderj
Messages: 17
Registered: April 2016
Location: California

Junior Member

I am having an error when try to create external table to a remote instance (same box) which appears to be related to a quoting issue on the location name which require single quotes. I have made sure that the create table does work fine without the db_link. When I add in the db_link oracle is requiring me to use dbms_utility.exec_ddl_statement. I have tried with and without execute immediate but both are giving me the same error about the location. Below I paste in the source code and then the error. Thanks so much is someone can advise me on this.

declare
begin
dbms_utility.exec_ddl_statement@DEV('create table
DEMO_MORE.RN_CONFIG_RULE_ext3 organization external(type oracle_datapump default
directory ext_tbl_dir_DEV location
('my_location')) as select *
from DEMO_MORE.RN_CONFIG_RULE');
end;
/

SQL> @q
('my_location')) as select *
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00103: Encountered the symbol "MY_LOCATION" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol ", was inserted before "MY_LOCATION" to continue.

SQL>

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650444 is a reply to message #650443]

Mon, 25 April 2016 09:53

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Assuming the correct version, I'd use the "q notation" for your statement. The problem is the ' marks.

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650445 is a reply to message #650444]

Mon, 25 April 2016 09:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Can you actually do DDL across DBLINK?

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+q+quote

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650447 is a reply to message #650444]

Mon, 25 April 2016 10:12

gunderj
Messages: 17
Registered: April 2016
Location: California

Junior Member

Thanks. What is the q notation?

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650448 is a reply to message #650447]

Mon, 25 April 2016 10:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Try the google search BS posted above

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650450 is a reply to message #650448]

Mon, 25 April 2016 10:23

gunderj
Messages: 17
Registered: April 2016
Location: California

Junior Member

Thanks the qw notation is to use two single quotes, (''my_location'') got past the issue.

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650451 is a reply to message #650450]

Mon, 25 April 2016 11:10

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

No that isn't the q notation, that's the other way of making it work.

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650479 is a reply to message #650443]

Tue, 26 April 2016 06:14

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Why would you even need to do this? Creating tables (normal or external, doesn't matter) should be a one-time thing, not something done at run-time or constantly repeated. Why don't you just connect to the database where you want the external table and issue the DDL directly?

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650486 is a reply to message #650479]

Tue, 26 April 2016 12:08

Bill B
Messages: 1971
Registered: December 2004

Senior Member

In every version I have used, you can't use DDL over a database link. It's just not allowed. Only INSERT/DELETE/ or update allowed. What a hideous security hole it would be if you could do that.

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650489 is a reply to message #650486]

Tue, 26 April 2016 13:54

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can do it using dbms_utility.exec_ddl_statement:

SQL> exec dbms_utility.exec_ddl_statement@mika('create table xxx (id int)')

PL/SQL procedure successfully completed.

SQL> desc xxx@mika
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ID                                        NUMBER(38)

SQL> exec dbms_utility.exec_ddl_statement@mika('drop table xxx')

PL/SQL procedure successfully completed.

SQL> desc xxx@mika
ERROR:
ORA-04043: object xxx does not exist
ORA-02063: preceding line from MIKA

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650542 is a reply to message #650489]

Wed, 27 April 2016 06:38

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Michel Cadot wrote on Tue, 26 April 2016 13:54

You can do it using dbms_utility.exec_ddl_statement:

Others will disagree, but I feel this is a case of 'just because something CAN be done doesn't mean it SHOULD be done.'

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650544 is a reply to message #650542]

Wed, 27 April 2016 06:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I did never encounter a case where this must be used so I tend to agree. Smile

Report message to a moderator

Re: error creating external via dbms_utility.exec_ddl_statement@myDB [message #650705 is a reply to message #650544]

Sat, 30 April 2016 06:58

gunderj
Messages: 17
Registered: April 2016
Location: California

Junior Member

We do have the need for running DDL over db_link - although a bit unusual, we have a job to "reverse evolve" a schema, then "forward evolve" the schema with various parameters for multiple instances. This provides test case content for our schema tracking tool. We use code generation so it can be run for any schema in order to cover various schema definitions.

For our external table test case this code creates the table in the local instance...

create table
my_schema.table_one_ext organization external(type oracle_datapump default
directory ext_tbl_dir_DEV location
('''my_location''')) as select *
from source_schema.table_one');

After saving this code in a variable we first replace the location's three single quotes to two single quotes via the q notation...
s_sql := replace(s_sql,q'[''']',q'['']');

Now this code is used with dbms_utility.exec_ddl_statement for the remote instances...

dbms_utility.exec_ddl_statement@DEV('create table
my_schema.RN_CONFIG_RULE_ext organization external(type oracle_datapump default
directory ext_tbl_dir_DEV location
(''my_location'')) as select *
from source_schema.table_one');

Thanks again for all the help.

Report message to a moderator

Previous Topic:	Trigger to prevent delete based on SQL statement
Next Topic:	call a web service in a function in plsql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:10:33 CDT 2024