Home » SQL & PL/SQL » SQL & PL/SQL » SQL Generation (Oracle 10g)
SQL Generation [message #418247] Thu, 13 August 2009 11:57 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi, i am trying to generate a create table partition DLL for exisitng tables .

Every DDL has below lines. I am getting error as it has special character.
error: SP2-0552: Bind variable "00" not declared.

PARTITION COMM_VALUES_ARC2_P2009_Jul_W1 VALUES LESS THAN (TIMESTAMP '2009-07-08 00:00:00');


Can someone pls guide me ? thanks
Re: SQL Generation [message #418254 is a reply to message #418247] Thu, 13 August 2009 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can someone pls guide me ? thanks

You MUST post the whole session not just a small part of a statement.
I bet you have a missing quote somewhere and this is NOT the only error in your script.

Regards
Michel
Re: SQL Generation [message #418359 is a reply to message #418247] Fri, 14 August 2009 06:23 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Below wil be the complete script

select 'CREATE TABLE '||OWNER||'.'||TABLE_NAME||'_2
TABLESPACE TABLESPACE01
LOGGING
PCTFREE 10
TABLESPACE TABLESPACE01
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 8
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
(PARTITION TABLE01_ARC2_P2009_Aug_W1 VALUES LESS THAN (TIMESTAMP '2009-08-08 00:00:00')'
from dba_tables
where owner='SCOTT' and table_name like '%_ARC';

[Updated on: Fri, 14 August 2009 06:24]

Report message to a moderator

Re: SQL Generation [message #418360 is a reply to message #418359] Fri, 14 August 2009 06:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
you need to double quote the string like this in the select statement

PARTITION COMM_VALUES_ARC2_P2009_Jul_W1 VALUES LESS THAN (TIMESTAMP ''2009-07-08 00:00:00'')' from dba_tables
where owner='SCOTT' and table_name like '%_ARC';

[Updated on: Fri, 14 August 2009 06:34]

Report message to a moderator

Re: SQL Generation [message #418362 is a reply to message #418359] Fri, 14 August 2009 06:35 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
You quotes are the problem, try:
select 'CREATE TABLE '||OWNER||'.'||TABLE_NAME||q'!_2
TABLESPACE TABLESPACE01
LOGGING
PCTFREE 10
TABLESPACE TABLESPACE01
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 8
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
(PARTITION TABLE01_ARC2_P2009_Aug_W1 VALUES LESS THAN (TIMESTAMP '2009-08-08 00:00:00')!'
from dba_tables
where owner='SCOTT' and table_name like '%_ARC';

[Updated on: Fri, 14 August 2009 06:36]

Report message to a moderator

Re: SQL Generation [message #418365 is a reply to message #418247] Fri, 14 August 2009 07:05 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks a lot.
Previous Topic: Field Combination query (merged 3)
Next Topic: grant select privelege
Goto Forum:
  


Current Time: Sun Dec 11 08:02:23 CST 2016

Total time taken to generate the page: 0.07523 seconds