Hello,

ACCEPT p_name PROMPT 'Please enter the Patient Name :'
ACCEPT p_address PROMPT 'Please enter the address :'
ACCEPT p_sex PROMPT 'Please enter the Sex :'
ACCEPT p_age PROMPT 'Please enter the age :'
ACCEPT p_contact PROMPT 'Please enter the contact no:'
ACCEPT p_first_vis PROMPT 'Please enter the First Visit Date :'
ACCEPT p_last_vis PROMPT 'Please enter the Last Visit Date :'
INSERT INTO patient(no,name,address,sex,age,contact_no,first_visit,last_visit)
VALUES (patient_no.NEXTVAL,UPPER('&name'),'&p_address','&p_sex',&age,&contact,TO_DATE('&first_vis'),TO_DATE('&last_vis'))
/

I am getting :

ACCEPT p_name PROMPT 'Please enter the Patient Name :'
*
ERROR at line 1:
ORA-00900: invalid SQL statement

After I run the run and enter the value for the prompt .


Please advise.


Thanks





Andy

Not sure if it makes a difference, but try double quoting the "prompt" string instead of single quoting it.

I will try this on one of our test instances here in a bit

hello,

no luck.I have double quote the prompt but the error message is still the same.


Andy

ok, it seems this needs to be in a script file for some reason. Weird.

I am able to get it to run just fine by placing all the SQL statements in a file and then running it:

ie in SQL*Plus

start c:\testfile.sql

and it works.

Hello Protollix,

Yup.I have tested in sql script file and as you said, it works.

Does any one have any idea of this?:confused:


Thanks anyway,Protollix



Andy

Hello Protollix,

Need some further advice,

I need to update the some of the column in the table.It will be asking for user input and update the necessary column base on the user input :

UPDATE doctor
SET (doctor.&doctor_contact,INITCAP('doctor.&doctor_department'))
WHERE name= UPPER('&doctor_name')
/


When I run the script, I am getting :


Enter value for doctor_contact: 0124526598
Enter value for doctor_department: General
old 2: SET (&doctor_contact,INITCAP('&doctor_department'))
new 2: SET (0124526598,INITCAP('General'))
Enter value for doctor_name: Ahmad
old 3: WHERE name= UPPER('&doctor_name')
new 3: WHERE name= UPPER('Ahmad')
SET (0124526598,INITCAP('General'))
*
ERROR at line 2:
ORA-01747: invalid user.table.column, table.column, or column specification


Please advice,thanks



Andy

the syntax for updating a column:

UPDATE table_name
SET column_name = new_value
WHERE where_condition

give that format a try

Hello,

I am trying to update the values in the column based on the user input rather than through the sql script directly.

Any best way to achieve this?



Andy

Ok, I figure out the ways,

UPDATE doctor
SET contact_no=&doctor_contact,department=INITCAP('&doctor_department')
WHERE name= UPPER('&doctor_name')
/


Thanks for the time .




Andy