CODEWITHSUNDEEP
oracle-apex
aforz
aforz

Reputation: 49

I can not create a table in oracle apex

I am trying to create the following table on the apex website but it gives me an error .I tested this in SQL plus and it worked perfectly.

    CREATE TABLE job_grade
   (Grade_level varchar(2) not null,
    lowest_sal number not null,
    highest_sal number not null);


INSERT ALL
    INTO job_grade
    VALUES ('A', 0, 1000)
    INTO job_grade
    VALUES ('B', 1001, 2000)
    INTO job_grade
    VALUES ('C', 2001, 3000)
    INTO job_grade
    VALUES ('D', 3001, 4000)
    INTO job_grade
    VALUES ('E', 4001, 5000)
SELECT * FROM DUAL;

output:

   ORA-00911: invalid character
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 673
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 659
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1829


4.     highest_sal number not null);
5. INSERT ALL
6.     INTO job_grade
7.     VALUES ('A', 0, 1000)
8.     INTO job_grade

Upvotes: 0

Views: 464

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

Do as @Koen said.

Alternatively, run a single CTAS which "combines" CREATE TABLE and INSERT INTO:

SQL> create table job_grade (grade_level, lowest_sal, highest_sal) as
  2    select 'A',    0, 1000 from dual union all
  3    select 'B', 1001, 2000 from dual union all
  4    select 'C', 2001, 3000 from dual union all
  5    select 'D', 3001, 4000 from dual union all
  6    select 'E', 4001, 5000 from dual;

Table created.

SQL>

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18565

The issue is with the SQL Workshop in apex. This does NOT work like sqlplus or sqldeveloper. If you click "run" apex will try and run everything as a single statement and this fails if there are multiple statements.

But don't worry, there is a simple workaround. Highlight the statement with the mouse and then click run. That will run only the highlighted statement. So in this case, highlight the CREATE TABLE statement and run it, then highlight the INSERT ALL statement and run it.

For running multiple statements, use "SQL Script" instead.

Upvotes: 3

Related Questions