Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

When I use "dbms_lob.append",column'value is null(empty value) form table,"dbms_lob.append"can't app

MadFrogSep 18 2016 — edited Sep 18 2016

Hello everyone!

I am Chinese,my english is not good.I have a problem about "When I use "dbms_lob.append",column'value is null form table,"dbms_lob.append" can't append behind string".

I try to use nvl() function to resolve the problem,but it dosen't work。Please help me,thank you!

My code is:

CREATE OR REPLACE

PROCEDURE "PR_ECOS_CUSTBACK_HEAD_QUERY" (

V_PAGE_NO IN VARCHAR2,

 V\_FUN\_ID    IN VARCHAR2,

V_OUT_RETURN OUT VARCHAR2,

V_OUT_JSON OUT CLOB

)

IS

V_TOTAL VARCHAR(100);

V_OU_MSG VARCHAR(3000);

V_NUM NUMBER;--查询"ECOS_OP_CUSTFEEDBACK_HEAD"表的全部记录数

BEGIN

V_OUT_RETURN := '';

dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB

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}');

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;@@

NOTE'value is null like this:

2016-09-18_142336.png

Because the note'value is null,the V_OUT_JSON'content is :

V_OUT_JSON = {"total":1,"rows": [{"ROW_ID":1,"LINKORDERNUMBER":100000001,"CUSTOMERID":200000001,

"RECEIVERNAME":"李四","TELEPHONE":12345678901,"STORENAME":"上海店","REGION":"开发区",

"RECEIVEADDRESS":"天地广场8号楼110单元119号","ORDERAMOUNT":999999,"ORDERDATE":"01-SEP-16",

"SENDWAREHOUSE":"上海店","EVENTLEVEL":"高","INTERNALSERVER":"王五","COSTAMOUNT":9999,

"BACKFREIGHT":"9","BACKADDRESS":"天地广场8号楼110单元119号","RECORDSTATUS":"未处理",

"CREATEDATE":"09-SEP-16","CREATERID":9000000001,"MODIFYDATE":"10-SEP-16",

"MODIFIERID":900000002,"DELETEFLAG":0,"Note":"

You can see the last word Note'behind is nothing ,the right V_OUT_JSON should be:

V_OUT_JSON = {"total":1,"rows": [{"ROW_ID":1,"LINKORDERNUMBER":100000001,"CUSTOMERID":200000001,

"RECEIVERNAME":"李四","TELEPHONE":12345678901,"STORENAME":"上海店","REGION":"开发区",

"RECEIVEADDRESS":"天地广场8号楼110单元119号","ORDERAMOUNT":999999,"ORDERDATE":"01-SEP-16",

"SENDWAREHOUSE":"上海店","EVENTLEVEL":"高","INTERNALSERVER":"王五","COSTAMOUNT":9999,"BACKFREIGHT":"9","BACKADDRESS":"天地广场8号楼110单元119号",

"RECORDSTATUS":"未处理","CREATEDATE":"09-SEP-16","CREATERID":9000000001,"MODIFYDATE":"10-SEP-16","MODIFIERID":900000002,

"DELETEFLAG":0,"Note":""}],

"footer": null,

"columns": null,

"frozenColumns": null,

"AttachRemark": null,

"Attach": null}

When I change the code,it doesn't work,change like this:

dbms_lob.append(V_OUT_JSON, to_char(item.NOTE)); => dbms_lob.append(V_OUT_JSON, nvl(to_char(item.NOTE),''));

Can you tell me how to modify the code "dbms_lob.append(V_OUT_JSON, to_char(item.NOTE)); ",can make the V_OUT_JSON right?
This post has been answered by MadFrog on Sep 18 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 16 2016
Added on Sep 18 2016
2 comments
663 views