Create Oracle function with variables and if condition

Question

I have a hard time looking for correct syntax to create a function with variables and if condition.

create or replace
FUNCTION createURL( IDName IN varchar2, IDValue IN number )
  RETURN VARCHAR2
IS
  ApplicationURL VARCHAR2(100); 
  AppplicationParm VARCHAR2(255);
  DBName VARCHAR(100);

BEGIN
  select sys_context('USERENV','DB_NAME') AS Instance into DBName FROM DUAL;

  IF DBName = 'WAMDEV' THEN ApplicationURL := 'http://srpwam10:080/maxi';
      ELSIF  DBName ='WAMDEVPJ' THEN ApplicationURL := 'http://srpwam10:080/maxi';
      ELSIF  DBName = 'WAMTST' THEN ApplicationURL := 'http://wamtest/maxi';
      ELSIF  DBName = 'WAMTSTPJ' THEN ApplicationURL := 'http://wamtest/maximo';
      ELSIF  DBName = 'WAMQA' THEN ApplicationURL := 'http://wamqa/maxi';
      ELSIF  DBName = 'WAMQAPJ'  THEN ApplicationURL := 'http://wamqa/maximo';
      ELSE  DBName := 'WAMP'; ApplicationURL := 'http://wam/maxi';
    END IF ;
 IDN := IDName;
  IF IDN = 'workorderid' THEN AppplicationParm :=  '/ui/?event=loadapp' || CHR(38) || 'value=wotrack' || CHR(38) || 'uniqueid=' || TO_CHAR(IDValue);
      ELSIF IDN = 'assetuid' THEN AppplicationParm :=  '/ui/?event=loadapp' || CHR(38) || 'value=asset' || CHR(38) || 'uniqueid=' || TO_CHAR(IDValue);
      ELSIF IDN = 'locationsid' THEN AppplicationParm :=  '/ui/?event=loadapp' || CHR(38) || 'value=location' || CHR(38) || 'uniqueid=' || TO_CHAR(IDValue);
      ELSE AppplicationParm :=   '/ui/?event=loadapp' || CHR(38) || 'value=sr' || CHR(38) || 'uniqueid=' || TO_CHAR(IDValue);
     END IF;

  RETURN 'javascript:void(window.open(''' ||  ApplicationURL || ApplicationParm || ''',''_blank''))';
END;

I'm not sure if the code is correct and what's the problem? I can not creat the function. Your help will be great appreciated.

You haven't actually asked a question. Does your code generate an error? If so, what error? One thing that leaps out is: IDN := IDName; - you haven't defined a variable called IDN. However, you could just use IDNAME directly in the comparisons, rather than creating a variable just to do the comparisons. E.g. if idname = ... elsif idname = ... — Boneist
– Boneist, Commented Jun 20, 2016 at 16:02

Mottor · Accepted Answer · 2016-06-21 07:16:00Z

And AppplicationParm is some times with 2 p and some times with 3. Get SQLDeveloper and he will show you all errors.

Here is with case:

CREATE OR REPLACE FUNCTION createURL (IDName IN VARCHAR2, IDValue IN NUMBER)
   RETURN VARCHAR2
IS
   ApplicationURL     VARCHAR2 (100);
   ApplicationParm   VARCHAR2 (255);
   DBName             VARCHAR (100);
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'DB_NAME') AS Instance INTO DBName FROM DUAL;
   ApplicationURL := case when DBName = 'WAMDEV' OR DBName = 'WAMDEVPJ' then 'http://srpwam10:9080/maximo' 
                          when DBName = 'WAMTST' OR DBName = 'WAMTSTPJ' then 'http://srpwam10:9080/maximo'
                          when DBName = 'WAMQA' OR DBName = 'WAMQAPJ' then 'http://srpwam10:9080/maximo'
                          else 'http://wam/maximo' 
                     end;
   ApplicationParm := case  IDName
                         when 'workorderid' THEN '/ui/?event=loadapp' || CHR (38) || 'value=wotrack' || CHR (38) || 'uniqueid=' || TO_CHAR (IDValue)
                         when 'assetuid' THEN '/ui/?event=loadapp' || CHR (38) || 'value=asset' || CHR (38) || 'uniqueid=' || TO_CHAR (IDValue)
                         when 'locationsid' THEN '/ui/?event=loadapp' || CHR (38) || 'value=location' || CHR (38) || 'uniqueid=' || TO_CHAR (IDValue)
                         else '/ui/?event=loadapp' || CHR (38) || 'value=sr' || CHR (38) || 'uniqueid=' || TO_CHAR (IDValue)
                      end;
   RETURN 'javascript:void(window.open(''' || ApplicationURL || ApplicationParm || ''',''_blank''))';
END;

Vinicius de Castro · Accepted Answer · 2016-06-20 16:04:28Z

2

I tried to compile your code and there is the error

Compilation errors for FUNCTION EPF.CREATEAMPURL

Error: PLS-00201: identifier 'IDN' must be declared
Line: 19
Text: IDN := IDName;

So you didn't declare the variable IDN

answered Jun 20, 2016 at 16:04

Vinicius de Castro

1941 silver badge9 bronze badges

Collectives™ on Stack Overflow

Create Oracle function with variables and if condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related