Stored procedure created but unable to fetch the data when I execute it-CodePudding

When executing the procedure I'm getting this error:

Error starting at line : 146 in command - BEGIN dw_dart.sample_count; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DW_DART.SAMPLE_COUNT' must be declared

ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error.
*Action:

This is my code:

CREATE PROCEDURE dw_dart.sample_count
AS  
BEGIN  
    WITH today AS
    ( 
        SELECT DISTINCT
            source_desc, 
            src_dim_id, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            current_date - 1 AS P_datetime, 
            current_date AS T_datetime ,
            rec_count 
        FROM
            dw_dart.tab_total_last_updated
        WHERE
            to_char(Dw_insert_datetime, 'mm-dd-yy') = to_char(current_date, 'mm-dd-yy')
     ),
     yesterday AS
     (
         SELECT
             src_dim_id, 
             category, 
             source_desc, 
             src_update_datetime, 
             Dw_insert_datetime,
             src_insert_datetime,
             current_date AS T_datetime ,
             rec_count 
         FROM
             dw_dart.tab_total_last_updated
         WHERE 
             TO_CHAR (Dw_insert_datetime, 'mm-dd-yy') =
                 CASE
                     WHEN TO_CHAR (CURRENT_DATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'MONDAY   '
                         THEN TO_CHAR (CURRENT_DATE - 3, 'mm-dd-yy')
                         ELSE TO_CHAR (CURRENT_DATE - 1, 'mm-dd-yy')
                 END 
    ),
    diff AS
    (
        SELECT
            t.src_dim_id, 
            t.source_desc, 
            t.src_update_datetime, 
            t.Dw_insert_datetime,
            t.src_insert_datetime,
            t.P_datetime, 
            t.T_datetime ,
            t.rec_count AS t_rec_count, 
            y.Dw_insert_datetime AS Y_DW_insert_datetime, 
            y.rec_count AS p_rec_count 
        FROM
            today t 
        LEFT JOIN
            yesterday y ON y.source_desc = t.source_desc 
    ),       
    final_query1 AS
    (
        SELECT
            src_dim_id, 
            source_desc, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            P_datetime, 
            T_datetime,
            p_rec_count,
            t_rec_count,
            t_rec_count - p_rec_count AS difference 
        FROM
            diff),--Difference between today's count and previous day count
    final_query2 AS
    (
        SELECT
            src_dim_id, 
            'Daily' as frequency, 
            source_desc, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            P_datetime, 
            T_datetime,
            p_rec_count,
            t_rec_count,
            difference, 
            CASE 
                WHEN difference <> 0 THEN 'pass' ELSE 'fail' 
            END AS status 
        FROM
            final_query1
    )
    SELECT * 
    FROM final_query2
END;

EXECUTE dw_dart.sample_count;

CodePudding user response：

That won't work.

A PL/SQL procedure's SELECT statement (that's select * from final_query2) requires an INTO clause, and there's none in your code.

Besides, you're missing the statement terminator (semi-colon) in the same line.

So, if procedure actually was created, you'd get (with simplified procedure code)

SQL> create procedure sample_count as
  2  begin
  3    with final_query2 as
  4      (select 1 from dual)
  5    select * from final_query2     --> missing semi-colon; missing INTO clause
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> exec sample_count
BEGIN sample_count; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.SAMPLE_COUNT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

Finally, as of error you got: procedure wasn't created so Oracle doesn't see it.

Simplified, again:

SQL> exec non_existing_proc;
BEGIN non_existing_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'NON_EXISTING_PROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

After you fix what I already said, use CREATE OR REPLACE procedure because without REPLACE you'll first have to drop it.

CodePudding user response：

Change your procedure to return a cursor:

CREATE PROCEDURE dw_dart.sample_count (
  o_cursor OUT SYS_REFCURSOR
)
AS  
BEGIN
  OPEN o_cursor FOR
  with today as (
    select distinct
           source_desc, 
           src_dim_id, 
           src_update_datetime, 
           Dw_insert_datetime, 
           src_insert_datetime,
           current_date-1 as P_datetime, 
           current_date as T_datetime ,
           rec_count 
    from   dw_dart.tab_total_last_updated
    where  Dw_insert_datetime >= TRUNC(current_date)
    and    Dw_insert_datetime <  TRUNC(current_date)   1
  ),
  yesterday as (
    select src_dim_id, 
           -- category, -- Unused
           source_desc, 
           src_update_datetime, 
           Dw_insert_datetime,
           src_insert_datetime,
           current_date as T_datetime ,
           rec_count 
    from   dw_dart.tab_total_last_updated
    WHERE  Dw_insert_datetime >=
             current_date
             - CASE TRUNC(current_date) - TRUNC(current_date, 'IW')
               WHEN 0 THEN 3 -- Monday
               WHEN 6 THEN 2 -- Sunday
                      ELSE 1 -- Other days
               END
    AND    Dw_insert_datetime <
             current_date
             - CASE TRUNC(current_date) - TRUNC(current_date, 'IW')
               WHEN 0 THEN 2 -- Monday
               WHEN 6 THEN 1 -- Sunday
                      ELSE 0 -- Other days
               END
  )
  select t.src_dim_id, 
         'Daily' AS frequency,
         t.source_desc, 
         t.src_update_datetime, 
         t.Dw_insert_datetime,
         t.src_insert_datetime,
         t.P_datetime, 
         t.T_datetime ,
         t.rec_count as t_rec_count, 
         -- y.Dw_insert_datetime as Y_DW_insert_datetime, -- Unused
         y.rec_count as p_rec_count,
         t.rec_count - y.rec_count AS difference,
         case when t.rec_count - y.rec_count <> 0 then 'pass' else 'fail' end
           as status
  from   today t 
         left join yesterday y
         on y.source_desc=t.source_desc;
end;
/

Then, if you are using SQL Developer declare a cursor variable, execute the procedure into that cursor and then print the cursor:

VARIABLE cur REFCURSOR;
EXECUTE dw_dart.sample_count(:cur);
PRINT cur;