7 Replies Latest reply: Mar 26, 2012 9:38 AM by Laurent Schneider RSS

    SQL Developer 1.5.1 - warning messages generated by CREATE TABLE

    667579
      Hi,

      Have an issue with a CREATE TABLE statement - it works correctly, but generates a warning message when used in SQL Developer (1.2 or 1.5.1). Full test case below:

      Setup:
      drop table samplenames;
      drop table customers;
      drop table phones;
      drop table customers_phone;
      drop sequence primkey;

      create table samplenames
      (name VARCHAR2(10));

      insert into samplenames values ('dan');
      insert into samplenames values ('joe');
      insert into samplenames values ('bob');
      insert into samplenames values ('sam');
      insert into samplenames values ('weslington');

      insert into samplenames values ('sue');
      insert into samplenames values ('ann');
      insert into samplenames values ('mary');
      insert into samplenames values ('pam');
      insert into samplenames values ('lucy');

      create sequence primkey
      start with 1000000
      increment by 1;

      create table customers as
      select primkey.nextval as cust_id,
      tmp1.name || tmp2.name as first_name,
      tmp3.name || tmp4.name || tmp5.name as last_name
      from samplenames tmp1,
      samplenames tmp2,
      samplenames tmp3,
      samplenames tmp4,
      samplenames tmp5;

      CREATE TABLE PHONES AS
      SELECT cust_id, 'H' as phn_loc, trunc(dbms_random.value(100,999)) as area_cde,
      trunc(dbms_random.value(1000000,9999999)) as phn_num
      FROM customers;

      INSERT INTO PHONES
      SELECT cust_id, 'B' as phn_loc, trunc(dbms_random.value(100,999)) as area_cde,
      trunc(dbms_random.value(1000000,9999999)) as phn_num
      FROM customers;

      --randomly delete ~10% of records to make sure nulls are handled correctly.
      delete from phones
      where MOD(area_cde + phn_num, 10) = 0;

      create table statement (there are legacy reasons for why this is written the way it is):
      CREATE TABLE customers_phone NOLOGGING AS
      SELECT cst.*,
      piv.HOME_PHONE,
      piv.WORK_PHONE
      FROM (SELECT cust_id,
      MAX(decode(phn_loc, 'H', '(' || area_cde || ') ' ||
      substr(phn_num,1,3) || '-' || substr(phn_num,4,4), NULL)) AS HOME_PHONE,
      MAX(decode(phn_loc, 'B', '(' || area_cde || ') ' ||
      substr(phn_num,1,3) || '-' || substr(phn_num,4,4), NULL)) AS WORK_PHONE
      FROM phones phn
      WHERE phn_loc IN ('H', 'B')
      AND cust_id IS NOT NULL
      AND EXISTS (SELECT NULL
      FROM customers
      WHERE cust_id = phn.cust_id)
      GROUP BY cust_id) piv,
      customers cst
      WHERE cst.cust_id = piv.cust_id (+)

      Warning message output:
      "Error starting at line 1 in command:
      CREATE TABLE customers_phone NOLOGGING AS
      SELECT cst.*,
      piv.HOME_PHONE,
      piv.WORK_PHONE
      FROM (SELECT cust_id,
      MAX(decode(phn_loc, 'H', '(' || area_cde || ') ' || substr(phn_num,1,3) || '-' || substr(phn_num,4,4), NULL)) AS HOME_PHONE,
      MAX(decode(phn_loc, 'B', '(' || area_cde || ') ' || substr(phn_num,1,3) || '-' || substr(phn_num,4,4), NULL)) AS WORK_PHONE
      FROM phones phn
      WHERE phn_loc IN ('H', 'B')
      AND cust_id IS NOT NULL
      AND EXISTS (SELECT NULL
      FROM customers
      WHERE cust_id = phn.cust_id)
      GROUP BY cust_id) piv,
      customers cst
      WHERE cst.cust_id = piv.cust_id (+)
      Error report:
      SQL Command: CREATE TABLE
      Failed: Warning: execution completed with warning"

      I am on 10.2.0.3. The CREATE TABLE always completes successfully, but the warning bugs me, and I have had no success tracking it down since there is no associated numberr.

      Anyone have any ideas?