CODEWITHSUNDEEP
oracle-databaseplsql
icerabbit
icerabbit

Reputation: 89

Stored Procedure + Call Not working

My Stored Procedure compiles, however; when I try to call it i get the following error: 

Encountered the symbol "IS" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table long double ref
char time timestamp interval date binary national character
nchar
The symbol "IS" was ignored.
ORA-06550: line 2, column 48:
PLS-00103: Encountered the symbol "," when expecting one of the following:

:= ( ; not null range default character
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

My Stored Procedure is as follows: 

create or replace procedure p_xml
(v_utc_offset in XML_HOURS_LOAD.UTCOFFSET%type
, v_data_date in XML_HOURS_LOAD.DATA_DATE%type
, v_data_type in XML_HOURS_LOAD.DATA_TYPE%type
, v_posted_value in XML_HOURS_LOAD.POSTEDVALUE%type
, v_utc_value in XML_HOURS_LOAD.UTCVALUE%type
, v_hour in XML_HOURS_LOAD.HOUR%type
, v_data_code in XML_HOURS_LOAD.DATA_CODE%type
)
AS
BEGIN
  if v_utc_offset >= 4 THEN
  INSERT INTO xml_hours_Load (UTCOffset, Data_date, Data_Type, PostedValue, UTCValue, Hour, Data_Code)
  VALUES(v_utc_offset, v_data_date, v_data_type, v_posted_value, v_utc_value, v_hour, v_data_code);  
  COMMIT;
 END IF;
END;

How I am calling it (and what is returning the error listed above) is as follows:

DECLARE
  v_utc_offset is XML_HOURS_LOAD.UTCOFFSET%type,
  v_data_date is XML_HOURS_LOAD.DATA_DATE%type,
  v_data_type is  XML_HOURS_LOAD.DATA_TYPE%type,
  v_posted_value is  XML_HOURS_LOAD.POSTEDVALUE%type,
  v_utc_value is  XML_HOURS_LOAD.UTCVALUE%type,
  v_hour is  XML_HOURS_LOAD.HOUR%type,
  v_data_code is XML_HOURS_LOAD.DATA_CODE%type;
CURSOR cXmlHoursLoadCursor is (SELECT utcoffset, data_date, data_type, postedvalue, utcvalue, hour, data_code
                             from xml_hours_load);
BEGIN
  FOR v in cXmlHoursLoadCursor LOOP
p_xml(v.utcoffset, v.data_date, v.data_type, v.postedvalue, v.utcvalue, v.hour, v.data_code);
COMMIT;
  END LOOP;
END;

Thanks in Advance!

Upvotes: 0

Views: 107

Answers (1)

kfinity
kfinity

Reputation: 9091

In your anonymous block, the CURSOR is the only variable you should declare using the IS keyword. Remove it from the others.

DECLARE
  v_utc_offset XML_HOURS_LOAD.UTCOFFSET%type;
  v_data_date XML_HOURS_LOAD.DATA_DATE%type;
  v_data_type XML_HOURS_LOAD.DATA_TYPE%type;
  v_posted_value XML_HOURS_LOAD.POSTEDVALUE%type;
  v_utc_value XML_HOURS_LOAD.UTCVALUE%type;
  v_hour XML_HOURS_LOAD.HOUR%type;
  v_data_code XML_HOURS_LOAD.DATA_CODE%type;
CURSOR cXmlHoursLoadCursor is (SELECT utcoffset, data_date, data_type, postedvalue, utcvalue, hour, data_code
                         from xml_hours_load);
BEGIN
  FOR v in cXmlHoursLoadCursor LOOP
p_xml(v.utcoffset, v.data_date, v.data_type, v.postedvalue, v.utcvalue, v.hour, v.data_code);
COMMIT;
  END LOOP;
END;

Upvotes: 3

Related Questions