This discussion is archived
2 Replies Latest reply: Jan 19, 2013 12:24 PM by 985912 RSS

Inserting values in a table from a view report in Application Express

985912 Newbie
Currently Being Moderated
Hello.

I have several tables with the sql bellow:

Employee
CREATE TABLE "Employee"
(     "Employee_ID" NUMBER,
"Name" VARCHAR2(12) CONSTRAINT "NAME_EMPLOYEE_NN" NOT NULL ENABLE,
"Surname" VARCHAR2(10) CONSTRAINT "SURNAME_EMPLOYEE_NN" NOT NULL ENABLE,
"Address" VARCHAR2(50) CONSTRAINT "ADDRESS_EMPLOYEE_NN" NOT NULL ENABLE,
"Telephone" VARCHAR2(10),
"Personal_ID_Number" VARCHAR2(13),
"Date_of_Employment" VARCHAR2(10) CONSTRAINT "DATA_NN" NOT NULL ENABLE,
"Salary" NUMBER(6,0),
CONSTRAINT "Employee_ID_con" PRIMARY KEY ("Employee_ID") ENABLE,
CONSTRAINT "Employee_CON" UNIQUE ("Personal_ID_Number", "Telephone") ENABLE
)
/


Patient
CREATE TABLE "PATIENT"
(     "Patient_ID" NUMBER,
     "Name_Patient" VARCHAR2(13) CONSTRAINT "NAME_PAT_NN" NOT NULL ENABLE,
     "Surname_Patient" VARCHAR2(10) CONSTRAINT "SURNAME_PAT_NN" NOT NULL ENABLE,
     "ADSRESS_PATIENT" VARCHAR2(50),
     "TELEPHONE_PATIENT" VARCHAR2(10) CONSTRAINT "TEL_PAT_NN" NOT NULL ENABLE,
     "EMPLOYEE_ID" NUMBER CONSTRAINT "DENTIST_PAT_NN" NOT NULL DISABLE,
     CONSTRAINT "PK_PATIENT" PRIMARY KEY ("PATIENT_ID") ENABLE,
     CONSTRAINT "PACIENT_CON" UNIQUE ("TELEFON_PACIENT") ENABLE
)
/
ALTER TABLE "PATIENT" ADD CONSTRAINT "PATIENT_CON3" FOREIGN KEY ("Employee_ID")
     REFERENCES "Dentist" ("Employee_ID") ON DELETE SET NULL ENABLE
/

Appointment

CREATE TABLE "Appointment"
(     "Appointment_ID" NUMBER,
     "Employee_ID" NUMBER CONSTRAINT "NN_Dentist_Appointment" NOT NULL ENABLE,
     "Patient_ID" NUMBER CONSTRAINT "NN_PATIENT_Appointment" NOT NULL ENABLE,
     "Who_Made" NUMBER,
     "Who_Changed" NUMBER,
     "Who_Canceled" NUMBER,
     "Content" VARCHAR2(1000),
     "DATE" DATE,
     CONSTRAINT "PK_Appointment" PRIMARY KEY ("Appointment_ID") ENABLE
)
/
ALTER TABLE "Appointment" ADD CONSTRAINT "Appointment_CON" FOREIGN KEY ("Patient_ID")
     REFERENCES "PATIENT" ("Patient_ID") ENABLE
/
ALTER TABLE "Appointment" ADD FOREIGN KEY ("Employee_ID")
     REFERENCES "Dentist" ("Employee_ID") ENABLE
/
ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Made")
     REFERENCES "Employee" ("Employee_ID") ENABLE
/
ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Changed")
     REFERENCES "Employee" ("Employee_ID") ENABLE
/
ALTER TABLE "Appointment" ADD FOREIGN KEY ("Who_Canceled")
     REFERENCES "Employee" ("Employee_ID") ENABLE
/

Dentist

CREATE TABLE "Dentist"
(     "Employee_ID" NUMBER,
"Qualifications" VARCHAR2(50),
CONSTRAINT "RB_VRAB_STOMATOLOG_PK" PRIMARY KEY ("Employee_ID") ENABLE
)
/
ALTER TABLE "Employee_ID" ADD CONSTRAINT "Dentist_CON" FOREIGN KEY ("Employee_ID")
REFERENCES "Employee" ("Employee_ID") ON DELETE CASCADE ENABLE
/


I'm trying to make an application in Oracle Application Express and I've tried making an SQL report using the following SQL query:

CREATE OR REPLACE FORCE VIEW "Appointment_REPORT" ("DOCTOR", "PATIENT", "Content", "Date") AS
select concat(concat(v.name,' '),v.surname) as doctor, concat(concat(pa.name_patient,' '),pa.surname_patient) as pactent, p.content, p.date
from Appointment p, Amployee v, Patient pa
where
v.employee_id=p.employee_id
and p.patient_id=pa.patient_id
/


The SQL works fine and gives me lovely results in my report. However, I don't know how to link the SQL to my actual Appointment table. Deleting, updating or creating new appointments through this view is impossible because I get the following error Unexpected error, unable to find item name at application or page level.

I understand how to make a form on the Appointment table but given how that table contains lots of primary keys (numbers) it's not preferable for the user to have to deal with that.

Can I have some help with linking the two and making it functional?

Thanks in advance.
  • 1. Re: Inserting values in a table from a view report in Application Express
    sb92075 Guru
    Currently Being Moderated
    982909 wrote:
    Hello.

    I have several tables with the sql bellow:
    You are digging yourself a DEEP hole by using double quote marks & Mixed Case table & column names!

    >
    CREATE TABLE "Appointment"
    (     "Appointment_ID" NUMBER,
         "Employee_ID" NUMBER CONSTRAINT "NN_Dentist_Appointment" NOT NULL ENABLE,
         "Patient_ID" NUMBER CONSTRAINT "NN_PATIENT_Appointment" NOT NULL ENABLE,
         "Who_Made" NUMBER,
         "Who_Changed" NUMBER,
         "Who_Canceled" NUMBER,
         "Content" VARCHAR2(1000),
         "DATE" DATE,
    AVOID using Reserved Words like "DATE" as column or table names!

    >
    I'm trying to make an application in Oracle Application Express and I've tried making an SQL report using the following SQL query:

    CREATE OR REPLACE FORCE VIEW "Appointment_REPORT" ("DOCTOR", "PATIENT", "Content", "Date") AS
    select concat(concat(v.name,' '),v.surname) as doctor, concat(concat(pa.name_patient,' '),pa.surname_patient) as pactent, p.content, p.date
    from Appointment p, Amployee v, Patient pa
    where
    v.employee_id=p.employee_id
    and p.patient_id=pa.patient_id
    /


    The SQL works fine and gives me lovely results in my report. However, I don't know how to link the SQL to my actual Appointment table. Deleting, updating or creating new appointments through this view is impossible because I get the following error Unexpected error, unable to find item name at application or page level.

    I understand how to make a form on the Appointment table but given how that table contains lots of primary keys (numbers) it's not preferable for the user to have to deal with that.
    Any table can have only ONE Primary Key.
    Can I have some help with linking the two and
    to which "two" do you refer?
    making it functional?
    Since you have told us what functionality is desired or expected,
    I don't know what might be a solution.
  • 2. Re: Inserting values in a table from a view report in Application Express
    985912 Newbie
    Currently Being Moderated
    1. My original tables aren't in English therefore i copied the code and edited it by translating it but I apologise for not preserving the case, I didn't think it was that relevant for you to understand what my tables and queries are about.

    2. Since the column names aren't in English my column name isn't Date but Datum. Again, a change due to translation.

    3. I understand that tables have one primary key. What I meant was foreign keys, but I typed primary keys because the values Employee_ID and Patient_ID are primary keys in different tables (Employee and Patient respectively).

    4. "The two" are my actual report and the form. Since the report is a result of the given sql query I doubt it's update-able and this is the actual problem.

    5. The functionality I'm looking for is that I see the report according to my SQL query. However if I can't update appointments through the SQL query (the source of the report) and also delete appointments then I can't update the actual table which is necessary for actual change in the report.

    I hope this helps.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points