3 Replies Latest reply on May 19, 2017 4:58 PM by Mike Kutz

    Is possible to use DOMAIN in Apex Express SQL?

    Ricardo Lavezzi

      I read in the book "Teach Yourself SQL in 10 Minutes":

       

      Domains

       

      A domain is a set of valid data types that can be used. A domain is associated with a data type, so only certain data is accepted. After you create a domain, you can add constraints to the domain. Constraints work in conjunction with data types, allowing you to further specify acceptable data for a field. The domain is used like the user-defined type.

      You can create a domain as follows:

      CREATE DOMAIN MONEY_D AS NUMBER(8,2);
      

       

      You can add constraints to your domain as follows:

      ALTER DOMAIN MONEY_D
      ADD CONSTRAINT MONEY_CON1
      CHECK (VALUE > 5);
      

       

      You can reference the domain as follows:

      CREATE TABLE EMP_PAY
      (EMP_ID        NUMBER(9),
      EMP_NAME      VARCHAR2(30),
      PAY_RATE      MONEY_D);
      

       

       

       

      The book says that it works in Oracle and MS MySQL, but when I try to use it, I get

       

      SQL> CREATE DOMAIN This_Domain AS NUMBER(1);
      CREATE DOMAIN This_Domain AS NUMBER(1)
             *
      ERROR at line 1:
      ORA-00901: invalid CREATE command
      

       

       

      Do even the command DOMAIN exists in SQL express?

        • 1. Re: Is possible to use DOMAIN in Apex Express SQL?
          Mike Kutz

          "SQL Express" is a Microsoft Product.

          You are probably using Oracle Express Edition.  This is usually stated simply as XE.

           

          No.  Oracle does not have a CREATE DOMAIN syntax.

          If you want to manage your database using "domains", start with SQL*Developer Data Modeler.

           

          But, in PL/SQL, you can have a SUBTYPE that does ALMOST same thing. (no CHECK constraints and it is only valid in PL/SQL)

          declare
              SUBTYPE money_d NUMBER(8,2) NOT NULL;
          begin
              null;
          end;
          /
          

           

          MK

           

           

          List of a few CREATE statements found in Oracle Documentation:

           

           

          MK

          1 person found this helpful
          • 2. Re: Is possible to use DOMAIN in Apex Express SQL?
            Ricardo Lavezzi

            How do you find the documentation specific for APEX?

             

            When I search, for example `Oracle Documentation create type´, I get PL/SQL or MySQL pages, which are not compatible with APEX.

             

            Help links inside APEX lead to 404 'Page not found'errors.

            • 3. Re: Is possible to use DOMAIN in Apex Express SQL?
              Mike Kutz

              Stupid answer:  search for "Oracle APEX" 

               

              long answer:

              APEX is not a database.  It is an application development platform.

              APEX requires an Oracle database and only an Oracle database.

              The CREATE TYPE is an SQL (language) statement for creating User Defined Types [UDTs] within the database.

               

              The APEX engine only understands the basic data types ( varchar2, date, number, blob, clob )

               

              I recommend staying away from UDTs until you have learned how to use them within an Oracle database.

               

              Because APEX runs on top of a database, you really need to understand how a database works.

              Eventually, you'll need to understand how (specifically) the Oracle database works.  (all databases are different)

               

              I'd start here: https://docs.oracle.com/database/121/TDDDG/toc.htm

              and here: https://docs.oracle.com/database/121/CNCPT/toc.htm

               

              For general database schema design (CREATE TABLE),

              • start by learning about "third normal design".
              • understanding about "Transactions" is also important.
              • before you do any major work, you should learn about "code repositories".
                • your "CREATE TABLE" scripts should be stored in a Code Repository system

               

              My $0.02

               

              MK

              1 person found this helpful