CODEWITHSUNDEEP
sqlplsql
unknownsatan
unknownsatan

Reputation: 731

ORA-06550 Error in PL/SQL

Well I am trying to run this script in PL/SQL. But I am constantly getting errors, I tried replacing single quotes with double quotes but no use. 

ACCEPT p_name PROMPT "Enter Customer Name: "
VARIABLE g_output VARCHAR2(200)
DECLARE
   v_street VARCHAR2(30);
   v_city VARCHAR2(20);
   v_prov VARCHAR2(20);
   v_postal VARCHAR2(10);
BEGIN
   SELECT cstreet, ccity, cprov, cpostal
     INTO v_address,v_city,v_state,v_zip
     FROM customer
    WHERE cname = "&p_name";
   :g_output := "&p_name" || " " ||v_street || " " || v_city;
   :g_output := :g_output " " || v_prov || " " || v_postal;
END;
/
PRINT g_output

Error:

Enter Customer Name: Ankur Kaushal
old  10:     WHERE cname = "&p_name";
new  10:     WHERE cname = "Ankur Kaushal";
old  11:    :g_output := "&p_name" || " " ||v_street || " " || v_city;
new  11:    :g_output := "Ankur Kaushal" || " " ||v_street || " " || v_city;
   :g_output := :g_output " " || v_prov || " " || v_postal;
                          *
ERROR at line 12:
ORA-06550: line 12, column 27:
PLS-00103: Encountered the symbol " " when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between || indicator multiset member SUBMULTISET_
The symbol "." was substituted for " " to continue.


Input truncated to 14 characters

G_OUTPUT
--------------------------------------------------------------------------------

Any mistake I am making here?

Upvotes: 0

Views: 13504

Answers (3)

Jegadees
Jegadees

Reputation: 1

 SELECT cstreet, ccity, cprov, cpostal
 INTO v_address, v_city, v_prov, v_zip

There is no declaration of v_state.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Shouldn't the penultimate line be

  :g_output := :g_output || ' ' || v_prov || ' ' || v_postal;

?

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Oracle SQL and PL/SQL uses single quotes ' to delimit strings. Double quotes " are used to signal identifiers (table names, column names...).

Replace all your double quotes by single quotes.

Also note that SQL*Plus is a poor tool to be used as a user interface. There is no way to make your actual code work with names that include quotes ("O'Reilly") except making the user manually enter two single quotes ("O''Reilly").

Upvotes: 1

Related Questions