oracle apex 18.2 pl/sql函数主体返回sql查询检查页面项目值 [英] oracle apex 18.2 pl/sql function body returning sql query checking page item value

问题描述

我正在创建一个Interacitve报告pl/sql function body returning sql query 我有一个页面项目:P1_DIVISION_ID作为参数传递，并且检查项目的值不为null，如下所示:

I am Creating a interacitve repor pl/sql function body returning sql query I have a page item :P1_DIVISION_ID to pass as parameter as well as check item value is not null as show below

declare
lv_query varchar2(4000);
begin
IF :P1_DIVISION_ID IS NOT NULL THEN
select 'select DIVISION,
       CUSTOMER_ID,
       PARTY_NAME,
       ACCOUNT_NUMBER,
       ORG_ID,
       OU_NAME,
       AGING_1_30,
       AGING_31_60,
       AGING_61_90,
       ABOVE_90,
       CURRENT_BALANCE,
       PAST_DUE,
       WEBSITE_STATUS,
       BLOCK_DATE,
       BLOCK_REASON,
       TOTAL_NUM_LOGIN,
       CP_LAST_PAY_DT,
       CP_LAST_AMT,
       CP_LAST_PAY_MODE,
       CP_AGE,
       CP_STATUS,
       CP_DATE,
       CP_DEFF,
       CP_UNBILL,
       CP_PHONE,
       CP_EMAIL,
       CP_ACCT_MGR,
       FU_ASSIGN,
       CP_LTR_SENT_DATE,
       CP_LTR_TYPE,
       CP_COMMENTS,
       COMMENTS
  from XX_CUSTOMER_AGING_V WHERE DIVISION '||:P1_DIVISION_ID INTO lv_query
FROM DUAL;
END IF;


RETURN lv_query;


end;

但是当我验证查询时，它会向我显示以下错误

BUT when i validate the query it show me the below errors

ORA-20999: WWV_FLOW_EXEC.NULL_QUERY_RETURNED_BY_FUNCTION

处理错误所需的建议

推荐答案

1. P1_DIVISION_ID时的查询是什么样的?该函数返回空值，因为您没有指定一个值，因此返回错误.

1. What is the query like when P1_DIVISION_ID? This function is returning a null because you didn't specify one, and hence the error.

请勿执行select ... into lv_query from dual.只需将字符串分配给变量.

Don't do select ... into lv_query from dual. Just assign the string to the variable.

您的功能执行不了什么，为什么还要这样做呢?为什么不只是对源进行SQL查询呢?

Your function isn't doing much so why are you even doing this? Why not just a SQL query for the source?

这篇关于oracle apex 18.2 pl/sql函数主体返回sql查询检查页面项目值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！