I am writing procedure for Oracle DB 10g/11g and i need to assign spesific date value to the variable. Code is below.
DECLARE
date_from DATE := NULL;
DECLARE
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
When I run this, I am getting this error.
Error starting at line : 1 in command -
DECLARE
date_from DATE := NULL;
DECLARE
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
Error report -
ORA-06550: line 3, column 1:
PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:
begin function pragma procedure subtype type <and identifier>
<a double-quoted delimited-identifier> current cursor delete
exist prior
The symbol "begin" was substituted for "DECLARE" to continue.
ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
( begin case declare and exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier><a double-quoted
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usuallz a PL/SQL compilation error.
*Action:
I have T-SQL experience, however very new to PL/SQL in OracleDB. Thanks in advance.
You have used extra declare. This should be ok:
DECLARE
date_from DATE := NULL;
d_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO d_count FROM TABLE_X;
IF d_count = 0 THEN
SELECT MIN(TRX_DATE) INTO date_from FROM TABLE_Y;
ELSE
SELECT MAX(TRX_DATE) INTO date_from FROM TABLE_X;
END IF;
END;
Basic template for scripts:
declare
-- Local variables here
i integer;
begin
-- statements here
end;
Basic Template for procedures:
procedure TEST(Name in out type, Name in out type, ...) is
begin
end TEST;