问题描述:

I tried to create an Oracle function where table name, column name and where string are dynamic parameters:

CREATE OR REPLACE FUNCTION MYSCHEMA.myFunctionName(

tableName in nvarchar2,

columnName in nvarchar2,

whereStr in nvarchar2)

RETURN nvarchar2

IS nActive nvarchar2(2000);

BEGIN

declare

querystr nvarchar2(2000) ;

result nvarchar2(2000);

begin

querystr :='

select listagg('+columnName+','+','+') within group (order by '+columnName+')

from '+tableName+' where 1 = 1 '+whereStr+';';

EXECUTE IMMEDIATE querystr

INTO result;

nActive := result;

RETURN ( nActive );

end;

END ;

/

But it gives me error "Warning: compiled but with compilation errors".

What am I doing wrong?

网友答案:
  1. For concatenate strings in Oracle use || not +
  2. You don't need ; at the end of execute immediate query string
  3. You need to escape ' using ''.

... as @Aleksej said

  1. Execute immediate need query string in CHAR or VARCHAR2;
  2. listagg return either raw or VARCHAR2

CREATE OR REPLACE FUNCTION MYSCHEMA.myFunctionName( 
  tableName in varchar2,
  columnName in varchar2,
  whereStr in varchar2)
RETURN varchar2
BEGIN
  declare 
  querystr varchar2(2000) ;
  result varchar2(2000);
  begin
    querystr :='
    select  listagg('|| columnName || ', '','') within group (order by ' ||columnName ||')
    from ' || tableName || ' where  1 = 1 ' || whereStr;
    EXECUTE IMMEDIATE querystr INTO result;
    return result;
  end;
END ;
/
网友答案:

You need to change the type of your variables, considering that

  • "The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2" (documentation)
  • EXECUTE needs a VARCHAR2: "It must be of type CHAR or VARCHAR2, not NCHAR or NVARCHAR2"

So:

declare 
  querystr varchar2(2000) ;
  result VARCHAR2(32767);
begin
  ...
相关阅读:
Top