PLS-00382: expression is of wrong type

Question

I have a procedure defined as:

create or replace
PACKAGE BODY PKG_BG_PRVDR_SCOPE_GROUP 
AS

g_return_code           NUMBER := 99;
g_return_text           VARCHAR2(500) := '';
g_return_desc           VARCHAR2(500) := '';
c_return_length         CONSTANT NUMBER := 500;
g_revision_frozen       NUMBER    := -459;
g_timestamp_error       NUMBER := -86;

c_invalid_row_count     CONSTANT VARCHAR2(100) := 'database integrity check failed';

revision_frozen         EXCEPTION;
duplicate_item          EXCEPTION;
invalid_row_count       EXCEPTION;
invalid_update_ts       EXCEPTION;


PROCEDURE update_bg_provider_scope_group
  (
    p_region_id              IN    bg_prvdr_scope_group.region_id%TYPE,
    p_revision_id            IN    bg_prvdr_scope_group.revision_id%TYPE,
    p_bg_revision_id         IN    bg_prvdr_scope_group.bg_revision_id%TYPE,
    p_classification_id      IN    bg_prvdr_scope_group.classification_id%TYPE,
    p_facility_id            IN    bg_prvdr_scope_group.facility_id%TYPE,
    p_user                   IN    bg_prvdr_scope_group.create_user_id%TYPE,
    p_current_timestamp      IN    bg_prvdr_scope_group.create_ts%TYPE,
    p_group_name             IN    bg_prvdr_scope_group.group_nm%TYPE,
    p_description            IN    bg_prvdr_scope_group.description%TYPE,
    p_prvdr_scope_grp_id     IN    bg_prvdr_scope_group.provider_group_id%TYPE,
    p_ora_rowscn             IN    NUMBER,
    p_cur_out                OUT   g_cursor
  )
  IS
        lv_count_name               NUMBER(10)     := 0;
    lv_count                    NUMBER(10)     := 0;
    lv_last_update_user_id      VARCHAR2(30)     := '';
    lv_user_first_and_last_nm   VARCHAR2(100)     := '';
    lv_current_ts               TIMESTAMP(6) WITH TIME ZONE;
    lv_ora_row_scn              NUMBER;
    lv_error_desc               VARCHAR2(300) := NULL;
  BEGIN
      DBMS_OUTPUT.PUT_LINE('1');

When executing I m getting error as : Connecting to the database SA_Sandbox4. ORA-06550: line 25, column 19: PLS-00382: expression is of wrong type ORA-06550: line 25, column 3: PL/SQL: Statement ignored Process exited. Disconnecting from the database SA_Sandbox4.

Its not entering the Begin...

executing with:

DECLARE
  P_REGION_ID NUMBER;
  P_REVISION_ID NUMBER;
  P_BG_REVISION_ID NUMBER;
  P_CLASSIFICATION_ID NUMBER;
  P_FACILITY_ID VARCHAR2(10);
  P_USER VARCHAR2(30);
  P_CURRENT_TIMESTAMP TIMESTAMP;
  P_GROUP_NAME VARCHAR2(50);
  P_DESCRIPTION VARCHAR2(255);
  P_PRVDR_SCOPE_GRP_ID NUMBER;
  P_ORA_ROWSCN NUMBER;
  P_CUR_OUT SA_SANDBOX4.PKG_BG_PRVDR_SCOPE_GROUP.g_cursor;
BEGIN
  P_REGION_ID := 51;
  P_REVISION_ID := 1;
  P_BG_REVISION_ID := 1;
  P_CLASSIFICATION_ID := 1;
  P_FACILITY_ID := 'GIL';
  P_USER := 'a12345';
  P_CURRENT_TIMESTAMP := localtimestamp;
  P_GROUP_NAME := 'modificationtest';
  P_DESCRIPTION := 'modified successfully';
  P_PRVDR_SCOPE_GRP_ID := 42;
  P_ORA_ROWSCN := localtimestamp;

  PKG_BG_PRVDR_SCOPE_GROUP.UPDATE_BG_PROVIDER_SCOPE_GROUP(
    P_REGION_ID => P_REGION_ID,
    P_REVISION_ID => P_REVISION_ID,
    P_BG_REVISION_ID => P_BG_REVISION_ID,
    P_CLASSIFICATION_ID => P_CLASSIFICATION_ID,
    P_FACILITY_ID => P_FACILITY_ID,
    P_USER => P_USER,
    P_CURRENT_TIMESTAMP => P_CURRENT_TIMESTAMP,
    P_GROUP_NAME => P_GROUP_NAME,
    P_DESCRIPTION => P_DESCRIPTION,
    P_PRVDR_SCOPE_GRP_ID => P_PRVDR_SCOPE_GRP_ID,
    P_ORA_ROWSCN => P_ORA_ROWSCN,
    P_CUR_OUT => P_CUR_OUT
  );
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('P_CUR_OUT = ' || P_CUR_OUT);
*/ 
  :P_CUR_OUT := P_CUR_OUT; --<-- Cursor
END;

Answer 1

Line 25 of your anonymous block appears to be

P_ORA_ROWSCN := localtimestamp;

P_ORA_ROWSCN is defined as a NUMBER. localtimestamp returns a TIMESTAMP. There is no implicit conversion from a TIMESTAMP to a NUMBER so the conversion fails. It's not obvious to me what you want to use to initialize P_ORA_ROWSCN. Normally, I'd guess that should be coming from the ORA_ROWSCN pseudocolumn of a particular row from a particular table. But maybe you want dbms_flashback.get_system_change_number? Or maybe you're using ROWSCN but don't really mean the Oracle system change number (SCN).

Incidentally, the package definition you posted won't compile because it's using a type g_cursor that is not defined in the package. Maybe you removed that when you posted the package definition? Because your anonymous block seems to expect that type to be declared in that package.