OraFAQ Forum: SQL & PL/SQL » How to add whitespace as default value using execute immediate?

Home » SQL & PL/SQL » SQL & PL/SQL » How to add whitespace as default value using execute immediate? (11g 11.2.0.1 , windows server 2008r2 standard)

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

How to add whitespace as default value using execute immediate? [message #614318]

Tue, 20 May 2014 15:28

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Hello,

I am trying to execute the below code trying to add whitespace as default value. Works fine when I don't use the EXECUTE IMMEDIATE statement, but I need to use it because it's in a PL/SQL block for a script I run for releases. Any help is much appreciated. I have also posted the error message I get.

MY CODE:

BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE PRICES ADD (
oid varchar2(38) default lower(sys_guid()) not null,
price_key_0 varchar2(38) default ' ' not null,
price_key_1 varchar2(38) default ' ' not null

)';
END;

END OF MY CODE:

Error report -
ORA-06550: line 4, column 86:
PLS-00103: Encountered the symbol " not null,
pr" when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || multiset bulk
member submultiset
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Report message to a moderator

Re: How to add whitespace as default value using execute immediate? [message #614319 is a reply to message #614318]

Tue, 20 May 2014 15:33

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Please read How to use [code] tags and make your code easier to read You have been asked to do this before.

You need to use the concatenation operator to build up the string.

Report message to a moderator

Re: How to add whitespace as default value using execute immediate? [message #614320 is a reply to message #614319]

Tue, 20 May 2014 15:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://askanantha.blogspot.com/2007/12/q-quote-operator-introduced-in-oracle.html

Report message to a moderator

Re: How to add whitespace as default value using execute immediate? [message #614321 is a reply to message #614319]

Tue, 20 May 2014 15:42

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Thanks John I reposted the code posting let me know if the format is wrong. I'm not sure what the correct syntax is using the concatenation operator. Can you post example of what your talking about using the code below. I tried using different combination using the || operator, but still no luck. Appreciate it.


BEGIN
           EXECUTE IMMEDIATE 'ALTER TABLE PRICES ADD (
                                                      oid varchar2(38) default lower(sys_guid()) not null, 
                                                      price_key_0 varchar2(38) default ' ' not null,
                                                      price_key_1 varchar2(38) default ' ' not null

                                                      )';
END;

Report message to a moderator

Re: How to add whitespace as default value using execute immediate? [message #614322 is a reply to message #614321]

Tue, 20 May 2014 15:56

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Thank you BlackSwan you the man! Here is the code I used and worked!

DECLARE 
  l_str varchar2(2000);

BEGIN

  l_str := 'ALTER TABLE PRICES ADD (
                                    oid varchar2(38) default lower(sys_guid()) not null, 
                                    price_key_0 varchar2(38) default '' ''  not null,
                                    price_key_1 varchar2(38) default '' ''  not null
                                   )';
 EXECUTE IMMEDIATE l_str;
 END;

Report message to a moderator

Re: How to add whitespace as default value using execute immediate? [message #614349 is a reply to message #614322]

Wed, 21 May 2014 02:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Use new quoting mechanism to make the string literal look simpler :

l_str := q'[ALTER TABLE PRICES ADD (
                                    oid varchar2(38) default lower(sys_guid()) not null, 
                                    price_key_0 varchar2(38) default ' '  not null,
                                    price_key_1 varchar2(38) default ' '  not null)
           ]';

Report message to a moderator

Previous Topic:	Looping joins
Next Topic:	Reset Sunday as first start week

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 00:25:52 CDT 2024