PLS-00302: component 'VARIABLE_SKU_ID' must be declared

Question

I am trying to store result of my query into a variable so that i can use it in another query, but getting errors

create procedure store_validated_sku_id
("variable_sku_id" VARCHAR2)
as
BEGIN

SELECT vsku.SKU_ID INTO  store_validated_sku_id.variable_sku_id 
FROM vs_sku_discont_details_test discontd,vsx_dcs_sku vsku,auto_ship_view bcc 
where discontd.sku_id = vsku.JDA_SKU_ID 
  and bcc.sku_id = vsku.SKU_ID AND vsku.web_eligible = 1 
  and vsku.discontinued =0 and bcc.auto_ship_eligible is null;

END store_validated_sku_id;

/

Getting the following errors in compiler logs:

Error(5,1): PL/SQL: SQL Statement ignored
Error(5,49): PLS-00302: component 'VARIABLE_SKU_ID' must be declared
Error(5,65): PL/SQL: ORA-00904: : invalid identifier

See [this other thread](https://stackoverflow.com/a/6030439/146325) — APC, Mar 23 '19 at 13:53

score 3 · Answer 1 · edited Mar 23 '19 at 17:13

Putting variable_sku_id between double quotes makes the identifier name case-sensitive.

Remove the double quotes, and it should be fine (I've changed your query to use ANSI-92 syntax - putting everything in the FROM clause is outdated old-style Oracle syntax and should be avoided):

create procedure store_validated_sku_id
(variable_sku_id VARCHAR2)
as 
BEGIN

SELECT vsku.SKU_ID INTO  store_validated_sku_id.variable_sku_id 
FROM vs_sku_discont_details_test discontd
JOIN vsx_dcs_sku vsku 
  ON  discontd.sku_id = vsku.JDA_SKU_ID 
JOIN auto_ship_view bcc
  ON bcc.sku_id = vsku.SKU_ID 
 WHERE vsku.web_eligible = 1 
 AND vsku.discontinued = 0 
 AND bcc.auto_ship_eligible is null;

END store_validated_sku_id;

Thanks it worked,i had to make slight modification though – Sanjay Naik Mar 23 '19 at 15:09 — Sanjay Naik, Mar 23 '19 at 15:09

score 2 · Answer 2 · answered Mar 23 '19 at 12:42

2

Yes, your main issue is wrapping your variable(variable_sku_id)'s around with quotes(no matter double or single, both yields problem), you should get rid of them
but also need to convert it to out or in out type, since a in(default) type argument cannot be used as an assignment target.

answered Mar 23 '19 at 12:42

Barbaros Özhan

59,113
10
31
55

Good catch regarding the `out` / `in out` type, I completely missed that one. – Frank Schmitt Mar 23 '19 at 18:56

score 1 · Accepted Answer · edited Mar 23 '19 at 15:42

CREATE OR REPLACE PROCEDURE store_validated_sku_id( variable_sku_id OUT VARCHAR2 ) IS    
BEGIN    
  SELECT vsku.SKU_ID
    INTO variable_sku_id      
    FROM vs_sku_discont_details_test discontd      
    JOIN vsx_dcs_sku vsku      
      ON discontd.sku_id = vsku.JDA_SKU_ID      
    JOIN auto_ship_view bcc      
      ON bcc.sku_id = vsku.SKU_ID      
   WHERE vsku.web_eligible = 1            
     AND vsku.discontinued = 0            
     AND bcc.auto_ship_eligible is null;    
END store_validated_sku_id;

PLS-00302: component 'VARIABLE_SKU_ID' must be declared

3 Answers3