-1

I'm trying to execute this query, but I'm getting ORA-00911: invalid character. It appears as if the error is ASCII characters in front of my table name.

CREATE TABLE #RMDist (
  Rebate_ID VarChar(MAX), 
  Rebate_Desc VarChar(MAX), 
  Sponsor_ID VarChar(MAX), 
  Sponsor_Desc VarChar(MAX), 
  GLPeriod INT, 
  DocType INT, 
  GLAccount VarChar(MAX),
  BusinessEntity INT, 
  TotalAmount Decimal (15,2)
)
Stacey Greer
  • 1
  • The table name `#RMDist` with leading hash could be a problem. – Tim Biegeleisen Jul 18 '22 at 17:37
  • Does this answer your question? [What is the equivalent of varchar(max) in Oracle?](https://stackoverflow.com/questions/414817/what-is-the-equivalent-of-varcharmax-in-oracle) – MT0 Jul 18 '22 at 17:37
  • Where [in the manual](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483) did you find `varchar(max)`? –  Jul 18 '22 at 17:48
  • Note that using special characters, or quoted object names like `"#RMDist"` is not considered best practice for Oracle. Object names are unquoted and thus case-insensitive by default, and contain only alpha-numeric characters, underscores, and possibly dollar signs ($). Quoting object names in DDL forces case-sensitivity into the object name and forces the use of quotes whenever the object is referenced anywhere else. – pmdba Jul 18 '22 at 17:56

1 Answers1

0
  • VarChar(MAX) is not valid in Oracle, use VARCHAR2(4000) or CLOB.
  • If you want to use the identifier #RMDist then you need to use a quoted identifier (everywhere it is going to be used).

Like this:

CREATE TABLE "#RMDist" (
  Rebate_ID      CLOB,
  Rebate_Desc    CLOB,
  Sponsor_ID     CLOB,
  Sponsor_Desc   CLOB,
  GLPeriod       INT,
  DocType        INT,
  GLAccount      CLOB,
  BusinessEntity INT,
  TotalAmount    Decimal (15,2)
)

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • I would say "do not use" for such identifiers, because the one who absolutely sure they need such names (for the reason) should already know how to achieve this. – astentx Jul 18 '22 at 18:16
  • I really appreciate this feedback, I changed VarChar(Max) to Varchar2(4000). I'm now getting an error of 00922. 00000 - "missing or invalid option" – Stacey Greer Jul 18 '22 at 19:13
  • CREATE TABLE "#RMDist" (RebateID VARCHAR2(4000), RebateDesc VARCHAR2(4000), SponsorID VARCHAR2(4000), Sponsor_Desc VARCHAR2(4000), GLPeriod INT, DocType INT, GLAccount VARCHAR2(4000), BusinessEntity INT, TotalAmount Decimal(15,2)) – Stacey Greer Jul 18 '22 at 19:13
  • "#RMDist" - not great, avoid this if you can, it will only cause you grief for the lifetime of your project going forward. – thatjeffsmith Jul 18 '22 at 19:16
  • @thatjeffsmith should I just removed it altogether? or structure it another way? – Stacey Greer Jul 18 '22 at 19:26
  • create table rmdist / CREATE TABLE RMDIST would be preferable, don't use object names requiring quotes b/c of special characters, spaces, reserved names, or preserving CaSe – thatjeffsmith Jul 18 '22 at 19:30