0

I trying to create table in PL/SQL, the problem is the at DEFAULT ' '.

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||' (STORE VARCHAR2(4) DEFAULT ' ' NOT NULL)';

END CREATE_TABLE ;

Error:

   PLS-00103: Encountered the symbol " NOT NULL)" 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 || bulk member
  SUBMULTISET_
  The symbol "*" was substituted for " NOT NULL)" to continue.

But i can add the default if i do it in sql tools. How can i add the default value in pl/sql?

CREATE TABLE Inventory
(
  STORE VARCHAR2(4) DEFAULT ' ' NOT NULL,
)
0

Single quotes inside single quotes need to be duplicated: default '' '' not null

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||
                    ' (STORE VARCHAR2(4) DEFAULT '' '' NOT NULL)';

END CREATE_TABLE ;

See the manual for details and alternative ways of doing that:

Text Literals | docs.oracle.com

3
  • or use the (in my opinion) much clearer q'[some text]' method. That way you can just use quotes any way you like within the square brackets. Apr 22 '16 at 7:36
  • @BriteSponge: that's what I was referring to with "alternative ways" Apr 22 '16 at 7:36
  • I just find that method so useful I thought I'd mention it explicitly. Apr 22 '16 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.