存储过程成功创建,但是运行时提示ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

非著名魔兽解说 发布于 2016/09/18 10:47
阅读 2K+
收藏 0

@IdleMan 你好,想跟你请教个问题:

存储过程成功创建,但是使用参数执行时提示:[Err] ORA-06577: output parameter not a bind variable

执行存储过程的代码为:call  PR_ECOS_CUSTBACK_HEAD_QUERY ('001','select','','');

错误信息为:

[SQL]call  PR_ECOS_CUSTBACK_HEAD_QUERY ('001','select','','')
[Err] ORA-06577: output parameter not a bind variable

存储过程代码为:

CREATE OR REPLACE 
PROCEDURE "PR_ECOS_CUSTBACK_HEAD_QUERY"  (
													V_PAGE_NO   IN  VARCHAR2,--页面Number
													V_FUN_ID    IN	VARCHAR2,--页面Number,对应的功能方法ID
													V_OUT_RETURN OUT VARCHAR2,--子存储过程的输出参数,传递给主存储过程,主存储过程根据这个返回值判断执行子存储过程是否成功
													V_OUT_JSON   OUT CLOB --生成的json,返回给前端调用者  						
)
 IS
V_TOTAL      VARCHAR(100);
V_OU_MSG     VARCHAR(3000);
V_NUM        NUMBER;--查询"ECOS_OP_CUSTFEEDBACK_HEAD"表的全部记录数
BEGIN

	V_OUT_RETURN := '';

	--IF V_PAGE_NO ='001' AND V_FUN_ID ='select' THEN
		SELECT COUNT(1) into V_NUM   FROM  ECOS_OP_CUSTFEEDBACK_HEAD;
		dbms_lob.append(V_OUT_JSON, '{');
        dbms_lob.append(V_OUT_JSON, '"total":');
        dbms_lob.append(V_OUT_JSON, to_char(V_NUM));
        dbms_lob.append(V_OUT_JSON, ',');
        dbms_lob.append(V_OUT_JSON, '"rows": [');
				for item in(SELECT  
					ROW_ID,
					LINKORDERNUMBER,
					CUSTOMERID,
					RECEIVERNAME,
					TELEPHONE,
					STORENAME,
					REGION,
					RECEIVEADDRESS,
					ORDERAMOUNT,
					ORDERDATE,
					SENDWAREHOUSE,
					EVENTLEVEL,
					INTERNALSERVER,
					COSTAMOUNT,
					BACKFREIGHT,
					BACKADDRESS,
					RECORDSTATUS,
					CREATEDATE,
					CREATERID,
					MODIFYDATE,
					MODIFIERID,
					DELETEFLAG,
					Note
        FROM   ECOS_OP_CUSTFEEDBACK_HEAD ) loop 
				dbms_lob.append(V_OUT_JSON, '{');   

				dbms_lob.append(V_OUT_JSON, '"ROW_ID":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.ROW_ID));    
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"LINKORDERNUMBER":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.LINKORDERNUMBER));    
				dbms_lob.append(V_OUT_JSON, ','); 
				
				dbms_lob.append(V_OUT_JSON, '"CUSTOMERID":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.CUSTOMERID));    
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"RECEIVERNAME":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.RECEIVERNAME));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"TELEPHONE":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.TELEPHONE));   
				dbms_lob.append(V_OUT_JSON, ','); 
				
				dbms_lob.append(V_OUT_JSON, '"STORENAME":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.STORENAME));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"REGION":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.REGION));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');
				
				dbms_lob.append(V_OUT_JSON, '"RECEIVEADDRESS":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.RECEIVEADDRESS));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"ORDERAMOUNT":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.ORDERAMOUNT));   
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"ORDERDATE":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.ORDERDATE));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"SENDWAREHOUSE":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.SENDWAREHOUSE));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"EVENTLEVEL":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.EVENTLEVEL));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"INTERNALSERVER":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.INTERNALSERVER));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');		

				dbms_lob.append(V_OUT_JSON, '"COSTAMOUNT":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.COSTAMOUNT));   
				dbms_lob.append(V_OUT_JSON, ',');	

				dbms_lob.append(V_OUT_JSON, '"BACKFREIGHT":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.BACKFREIGHT));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');
				
				dbms_lob.append(V_OUT_JSON, '"BACKADDRESS":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.BACKADDRESS));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"RECORDSTATUS":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.RECORDSTATUS));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"CREATEDATE":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.CREATEDATE));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"CREATERID":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.CREATERID));   
				dbms_lob.append(V_OUT_JSON, ','); 
	
				dbms_lob.append(V_OUT_JSON, '"MODIFYDATE":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.MODIFYDATE));   
				dbms_lob.append(V_OUT_JSON, '"'); 
				dbms_lob.append(V_OUT_JSON, ',');

				dbms_lob.append(V_OUT_JSON, '"MODIFIERID":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.MODIFIERID));   
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"DELETEFLAG":');     
				dbms_lob.append(V_OUT_JSON, to_char(item.DELETEFLAG));   
				dbms_lob.append(V_OUT_JSON, ','); 

				dbms_lob.append(V_OUT_JSON, '"Note":"');     
				dbms_lob.append(V_OUT_JSON, to_char(item.Note));   
				dbms_lob.append(V_OUT_JSON, '"'); 

				dbms_lob.append(V_OUT_JSON, '},');    
				end loop; 
				SELECT SUBSTR(V_OUT_JSON, 0, length(V_OUT_JSON)-1) into V_OUT_JSON FROM DUAL;
				dbms_lob.append(V_OUT_JSON, '],
					"footer": null,
					"columns": null,
					"frozenColumns": null,
					"AttachRemark": null,
					"Attach": null}'); 


			--COMMIT;查询时不需要COMMIT
			V_OUT_RETURN := 'OK';

	EXCEPTION
    WHEN OTHERS THEN
    V_OUT_RETURN := 'ERROR';
    V_OU_MSG    := '失败,原因是:' || SQLERRM;
    ROLLBACK;
    INSERT INTO SYS_ERROR_LOG
      (ROW_ID, PR_NAME, ERROR_DESC, INSDT)
    VALUES
      (SYS_ERROR_LOG_SEQ.NEXTVAL, 'PR_ECOS_CUSTBACK_HEAD_QUERY', V_OU_MSG, SYSDATE);
    COMMIT;
END PR_ECOS_CUSTBACK_HEAD_QUERY;


如果向下面这样点击右键输入参数执行,则提示:ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275



我在代码中加了一句话dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB

如下图:

然后还是有错误,错误信息为:ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275,现在返回的V_OUT_JSON内容为:

NOTE之后拼接的内容没了,如下图所示:

发现没有拼接上的原因是NOTE字段在数据库是空的,导致后续没有拼接上。我在数据库给NOTE字段加上值后,后续的拼接就都拼上了,请问,怎么处理能让字段是空值也不受影响?


加载中
返回顶部
顶部