Oracle ошибка ora 06550

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?

asked Jul 10, 2012 at 12:19

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»).

answered Jul 10, 2012 at 12:38

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

There is no declaration of v_state.

DECLARE
    L_DATE_1 DATE :='2018-09-01';
    L_DATE_2 DATE :='2018-11-12';
    begin
    SELECT C."APPCM_PHOTO_CASE_MATERIAL_ID" ID,
           C.APPFE_EVENT_ID ev_id 
    FROM table_1 C
WHERE C.t_appcm_violation_date between L_DATE_1 and L_DATE_2

ошибка Error report —
ORA-06550: Строка 5, столбец 1:
PLS-00428: в этом предложении SELECT ожидается фраза INTO

Что хочет от меня, если я уже идентифицировал переменную?

    with period as (
        select
            to_date('2018-09-01','yyyy-mm-dd') L_DATE_1,
            to_date('2018-11-12','yyyy-mm-dd') L_DATE_2
        from
            dual
    )
 SELECT C.APPCM_PHOTO_CASE_MATERIAL_ID ID,
        C.APPFE_EVENT_ID ev_id 
 FROM period,table_1 C
 WHERE C.t_appcm_violation_date between L_DATE_1 and L_DATE_2

Вот, так я получил желаемое без ошибок. Как сделать аналогию через объявление локальной переменой ?

PLS-00428: в этом предложении SELECT ожидается фраза INTO

Сообщение об ошибке однозначно указывает, что надо объявить локальную переменную для результата и добавить её в запрос: SELECT ... INTO local_result FROM ....

Но в вопросе запрос явно вернёт не одну строку, поэтому тут нужен курсор и цикл.
Лучше неявный курсор, который неявно объявит переменную для результата в теле цикла.
Как-то так:

DECLARE
    L_DATE_1 DATE :='2018-09-01';
    L_DATE_2 DATE :='2018-11-12';
BEGIN
    for rec in (
        SELECT 
           C."APPCM_PHOTO_CASE_MATERIAL_ID" ID, C.APPFE_EVENT_ID ev_id
        FROM table_1 C
        WHERE C.t_appcm_violation_date between L_DATE_1 and L_DATE_2
        ) loop
        dbms_output.put_line ('fetched row:'||rec.id||','||rec.ev_id);
    end loop; 
END;

ответ дан 15 ноя 2018 в 9:48