grammars-v4 icon indicating copy to clipboard operation
grammars-v4 copied to clipboard

plsql grammar issue

Open SukeshP1995 opened this issue 4 years ago • 2 comments

Trying to parse this kind of input

whenever sqlerror exit sql.sqlcode
whenever oserror exit oscode
set timing on
alter session enable parallel dml;

define v_schema_stg = stg;
define v_schema = prod;
define v_parallel = 8;
define v_role = stg_select_role;


--Sample for Merge :
MERGE INTO &v_schema_stg..member_staging tgt
USING 
(SELECT /*+ parallel(&v_parallel) */  member_id, 
	  first_name, 
	  last_name, 
        rank 
  FROM v_schema.members) stg
ON (tgt.member_id  = stg.member_id)
WHEN MATCHED THEN
    UPDATE /*+ parallel(&v_parallel) */  SET tgt.first_name = stg.first_name, 
               tgt.last_name = stg.last_name, 
               tgt.rank = stg.rank
    WHERE tgt.first_name <> stg.first_name OR 
           tgt.last_name <> stg.last_name OR 
           tgt.rank <> stg.rank 
WHEN NOT MATCHED THEN
    INSERT
 	(tgt.member_id, 
	 tgt.first_name, 
	 tgt.last_name, 
	 tgt.rank
      )  
    VALUES
	(stg.member_id, 
	 stg.first_name, 
      stg.last_name, 
      stg.rank
     );

COMMIT;

getting the following error line 7:24 extraneous input '=' expecting {<EOF>,

please help

If possible, please check by yourself

Even the .. notation and &variable also not working

SukeshP1995 avatar Apr 14 '21 16:04 SukeshP1995

much of SQL*PLUS functionality is not written in the grammar. the part on line 7 that is failing is SQL*PLUS. the & variables are also SQL*PLUS. the .. isn't working because of the & variable

Commodore68 avatar Sep 03 '22 19:09 Commodore68

it could be argued that SQL*PLUS should really get its own grammar.

Commodore68 avatar Sep 03 '22 19:09 Commodore68