Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error inserting data into customer table

I get this error when I try to insert data into my customer table. The table is below the error:

SQL> exec add_customer('Hill', 'Fabian','Goddy's Chapel Rd FENSVILLE,
AL 35501','4105422188');
ERROR:
ORA-01756: quoted string not properly terminated

SQL>  --Customer procedures:
SQL> CREATE OR REPLACE PROCEDURE add_customer
  2    (p_c_id IN NUMBER, 
  3     p_cus_lname IN OUT VARCHAR2, 
  4     p_cus_f_name IN OUT VARCHAR2,  
  5     p_cus_address IN OUT VARCHAR2,
  6     p_cus_phone_number IN OUT VARCHAR2)
  7  IS
  8  BEGIN
  9    -- add customer
 10    INSERT INTO
customer(c_id,cus_address,cus_phone_number,cus_lname,cus_f_name)
 11    VALUES
 12 
(c_id_sequence.NEXTVAL,p_cus_address,p_cus_phone_number,p_cus_lname,p_cus_f_name;
 13  COMMIT;
 14  END;
 15  /

Procedure created.

Your problem is that the delimiter character for strings is the single quote, and you want to use a single quote in your string. You are trying to insert the string "Goddy's." Unfortunately, the single quote is used to denote the beginning and the end of the string.
exec add_customer('Hill', 'Fabian','Goddy's Chapel Rd FENSVILLE, AL
35501','4105422188');

You have two methods to get around this problem. One, you can tell the SQL parser that what you are sending it is a part of the string, not a delimiter to the string. This can be done by using two single quotes, right next to each other as follows:

exec add_customer('Hill', 'Fabian','Goddy''s Chapel Rd FENSVILLE, AL
35501','4105422188');

See the two single quotes right next to each other? This method will work in just about any application. In SQL*Plus, you can use an escape character to denote that the character after the escape character is not a delimiter. This can be done as follows:

set escape 
exec add_customer('Hill', 'Fabian','Goddy's Chapel Rd FENSVILLE, AL
35501','4105422188');

In this case, I explicitly set the escape character as the back slash. This is also the default escape character for SQL*Plus. In my string, if a single quote immediately follows the escape character it is treated as part of the string.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close