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.

CodePudding user response：

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;

CodePudding user response：

In your case, only one DECLARE is enough.

Besides (although not an error),

• variable that is declared is NULL by default; you don't have to specify it (that's DATE_FROM)
• d_count is used to accept result of the count function; if nothing is found in table_x, count will be 0 anyway so you don't have to specify d_count's initial value either

So:

DECLARE
   date_from  DATE;
   d_count    NUMBER;
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;