0
INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits(hostChg,vDate,refDoc,docChg))
VALUES('P002','491221019V','Dulani Perera','3-FEB-49',
     phone_arr_ty(0112233211),hospVisitsNt_ty_tbl(hospVisits_ty(75.00,'25-MAY-06',
     (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2342111322),550.00)),
     hospVisitsNt_ty_tbl(hospVisits_ty(90.00,'29-MAY-06',(SELECT REF(d) FROM DoctorTbl d
           WHERE d.regNo=2344114344),300.00)
     )
);/

When I'm trying to run above query it will generate following error.

Error starting at line : 65 in command -
INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits(hostChg,vDate,refDoc,docChg)) VALUES('P002','491221019V','Dulani Perera','3-FEB-49', phone_arr_ty(0112233211),
hospVisitsNt_ty_tbl(hospVisits_ty(75.00,'25-MAY-06',(SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2342111322),550.00)),
hospVisitsNt_ty_tbl(hospVisits_ty(90.00,'29-MAY-06',(SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2344114344),300.00))
)
Error at Command Line : 65 Column : 58
Error report -
SQL Error: ORA-00917: missing comma
00917. 00000 -  "missing comma"
*Cause:    
*Action:

I think there shouldn't be issue in commas. How to solve this ?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Welcome to Stack Overflow. Please always provide source code, error messages and other textual information as inline plain text rather than pictures. Text is searchable, reusable and normally easier to read. – Álvaro González Sep 20 '16 at 16:56
  • "line 65"? How can that short query have 65 lines? – Marc B Sep 20 '16 at 17:00
  • It would also be helpful to show the object definition you're trying to use, both for the table you're inserting into, and the nested table type you seem to be trying to use. – Alex Poole Sep 20 '16 at 17:01
  • @MarcB - that's just how SQL Developer reports an error when you run a script - it gives the script line number the command is on, rather than (as SQL\*Plus would show) line 1 of that statement. We just can't see the rest of the script (which presumably creates the types and tables...) – Alex Poole Sep 20 '16 at 17:42
  • 1
    Unrelated, but: you do not need `;` **and** `/` for DML statements. In fact this will result in an error. For details see: http://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695 –  Sep 20 '16 at 17:54
  • @a_horse_with_no_name - SQL Developer (which the question was originally tagged with) seems to ignore the `/` with that construct; it doesn't error (or try to insert twice). But in general agree (of course!) – Alex Poole Sep 20 '16 at 18:07

1 Answers1

0

The error is being raised at the opening parenthesis of the nest table type:

INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits(hostChg, ...
                                                         ^

Your PatientTbl has a nested table column. You seem to be trying to specify the details of that table's object type inside the insert statement, which isn't correct.

Assuming that nest table column is called hospVisits you would just do:

INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits)
VALUES('P002','491221019V','Dulani Perera','3-FEB-49',
     phone_arr_ty(0112233211),hospVisitsNt_ty_tbl(hospVisits_ty(75.00,'25-MAY-06',
     (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2342111322),550.00)),
     hospVisitsNt_ty_tbl(hospVisits_ty(90.00,'29-MAY-06',(SELECT REF(d) FROM DoctorTbl d
           WHERE d.regNo=2344114344),300.00)
     )
);

But your nested table construction is wrong too; you are trying to use two separate hospVisitsNt_ty_tbl with a single hospVisits_ty in each, rather than a single hospVisitsNt_ty_tbl with multiple hospVisits_ty:

INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits)
VALUES('P002','491221019V','Dulani Perera',date '1949-02-03',
  phone_arr_ty(0112233211),
  hospVisitsNt_ty_tbl(
    hospVisits_ty(75.00, date '2006-05-25',
      (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2342111322),550.00),
    hospVisits_ty(90.00,date '2006-05-29',
      (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2344114344),300.00)
  )
);

I've also switched to using ANSI date literals, on the assumption (and hope) that those columns are actually dates not strings. You were relying on NLS parameters, which you don't usually have control over, and implicit date conversion is not a good idea as it can fail when other people run your code.

You will get unexpected results even in your own environment because you're using 2-digit years; the string '3-FEB-49' would be implicit converted to 2049, not 1949 (which is looks like you would expect):

alter session set nls_date_format = 'DD-MON-RR';
select to_char(to_date('3-FEB-49'), 'YYYY-MM-DD') from dual;

TO_CHAR(TO
----------
2049-02-03

And that's if your defaut model has RR, RRRR or YY; if it happens to have YYYY then you'd get 0049-02-03.

Object build and insertion tested with some made-up data types, as you haven't provided yours:

create type doctor_ty as object (regNo number)
/
create table DoctorTbl of doctor_ty
/
create type hospVisits_ty as object (hostChg number,vDate date,
  refDoc ref doctor_ty,docChg number)
/
show errors
create type hospVisitsNt_ty_tbl as table of hospVisits_ty
/
create type phone_arr_ty as object (num number)
/
create table PatientTbl(sysID varchar2(4),id varchar2(12),name varchar2(30),dob date,
  phone phone_arr_ty,hospVisits hospVisitsNt_ty_tbl)
nested table hospVisits store as hospVisitsNt return as locator;
/

INSERT INTO PatientTbl(sysID,id,name,dob,phone,hospVisits)
VALUES('P002','491221019V','Dulani Perera',date '1949-02-03',
  phone_arr_ty(0112233211),
  hospVisitsNt_ty_tbl(
    hospVisits_ty(75.00, date '2006-05-25',
      (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2342111322),550.00),
    hospVisits_ty(90.00,date '2006-05-29',
      (SELECT REF(d) FROM DoctorTbl d WHERE d.regNo=2344114344),300.00)
  )
);

1 row inserted.
Alex Poole
  • 183,384
  • 11
  • 179
  • 318