oracle PLS-00382: 表达式类型错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22386811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PLS-00382: expression is of wrong type
提问by beetri
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.
执行时我收到错误：连接到数据库 SA_Sandbox4。 ORA-06550：第 25 行，第 19 列：PLS-00382：表达式类型错误 ORA-06550：第 25 行，第 3 列：PL/SQL：忽略语句 进程退出。与数据库 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;
采纳答案by Justin Cave
Line 25 of your anonymous block appears to be
您匿名块的第 25 行似乎是
P_ORA_ROWSCN := localtimestamp;
P_ORA_ROWSCNis defined as a NUMBER. localtimestampreturns a TIMESTAMP. There is no implicit conversion from a TIMESTAMPto a NUMBERso 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_ROWSCNpseudocolumn of a particular row from a particular table. But maybe you want dbms_flashback.get_system_change_number? Or maybe you're using ROWSCNbut don't really mean the Oracle system change number (SCN).
P_ORA_ROWSCN被定义为NUMBER. localtimestamp返回一个TIMESTAMP. 没有从 aTIMESTAMP到 a 的隐式转换，NUMBER因此转换失败。你想用什么来初始化对我来说并不明显P_ORA_ROWSCN。通常，我猜这应该ORA_ROWSCN来自特定表中特定行的伪列。但也许你想要dbms_flashback.get_system_change_number？或者也许您正在使用ROWSCN但并不真正意味着 Oracle 系统更改号 (SCN)。
Incidentally, the package definition you posted won't compile because it's using a type g_cursorthat 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.
顺便说一句，您发布的包定义将无法编译，因为它使用了g_cursor包中未定义的类型。也许你在发布包定义时删除了它？因为您的匿名块似乎希望在该包中声明该类型。
回答by Patrick Hofman
We cannot say on the exact field that is giving you this problem since we don't know your table structure, but I can say this:
由于我们不知道您的表结构，因此我们无法确定给您带来此问题的确切字段，但我可以这样说：
Obviously there is a problem with the data type of one of the fields you are passing into your procedure.
显然，您传递到过程中的字段之一的数据类型存在问题。
What I would suggest to do some work to pass the same type of parameter into the procedure as in your table.
我建议做一些工作来将相同类型的参数传递到表中的过程中。
You define this in your procedure:
你在你的程序中定义它：
p_region_id IN bg_prvdr_scope_group.region_id%TYPE,
but you pass in this variable:
但是你传入这个变量：
P_REGION_ID NUMBER;
change it to
将其更改为
p_region_id bg_prvdr_scope_group.region_id%TYPE
and do this for every parameter / variable.
并对每个参数/变量执行此操作。
