Oracle Create Function

Question

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?

Use show errors or query the user_errors view to see the actual compilation errors. (SQL Developer also has a pane that shows them). You need to look at unescaped quotes within your dynamic string and the concatentation character (|| not +); but why does your listagg have three arguments? — Alex Poole
– Alex Poole, Commented Apr 18, 2016 at 7:39

Praveen · Accepted Answer · 2016-04-18 07:50:25Z

5

For concatenate strings in Oracle use || not +
You don't need ; at the end of execute immediate query string
You need to escape ' using ''.

... as @Aleksej said

Execute immediate need query string in CHAR or VARCHAR2;
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 ;
/

answered Apr 18, 2016 at 7:50

Praveen

9,4634 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

barış çıracı Over a year ago

Thanks buddy...True query is CREATE OR REPLACE FUNCTION name( tableName in varchar2, columnName in varchar2, whereStr in varchar2) RETURN varchar2 IS nActive varchar2(2000); 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; nActive := result; RETURN ( nActive ); end; END ; /

Aleksej · Accepted Answer · 2016-04-18 07:45:17Z

1

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
  ...

edited Apr 18, 2016 at 7:45

answered Apr 18, 2016 at 7:36

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Collectives™ on Stack Overflow

Oracle Create Function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related