Problem in UDF

[Prapagar]

Hi all,

Pls help me in UDF,DB is Oracle.
I did the following steps but not able to get in Impromtu.

1)Created this function in DB.

CREATE OR REPLACE function check_numeric(xvar varchar2) return varchar2 as
yn varchar2(1) := 'Y';
nvar number := 0;
begin
select xvar/100 into nvar from dual;
yn := 'Y';
return yn;
Exception
When VALUE_ERROR OR INVALID_NUMBER Then
yn := 'N';
return yn;
end;

2)Created a file cogudfor.sql(as this file not exist in bin folder) and pasted the following code.

DECLARE DATABASE FUNCTION check_numeric(STRING)
RETURNS STRING
FUNCTION NAME check_numeric;

3)Changed the impfunct.ini,

check_numeric= in [Common Database Function List] part and added following script in bottom of the file.

[check_numeric]
label=check_numeric
param=1
return=CH1=CH;
text_String_exp
exp=check_numeric(^1)
tip==Syntax:check_numeric(xvar)
tip1=String expression

4)Changed the orfunct.ini,

check_numeric= in [Database-specific Function List] part and added following script in bottom of the file.

[check_numeric]
label=check_numeric
param=1
return=CH1=CH;
text_String_exp
exp=check_numeric(^1)
tip==Syntax:check_numeric(xvar)
tip1=String expression

and connection thro native mode.

pls tell me where i went wrong.


Thanx,
Prapagar.

 

[kajarekar]

Hi Prapagar,

Did you try a syntax checking utility provided by Cognos. It has been documented in the PDF file for UDFs

... vivek

 

[kajarekar]

Back again prapagar,

I had faced a similar problem in Ver 6 of Impromptu while I was connecting to MS SQL Server 2000 in native mode. I found one knowledgebase document which stated that UDF will not work in native mode. When I changed my connection to ODBC, I started getting that function.

I do not know whether this has been solved in Series 7, but check on this and let me know your findings.

I will try to find that knowledgebase document in the meanwhile for your benefit

..... Vivek

 

[Prapagar]

Thanx alot for ur help Vivek,

I'll let u know, after trying ur ideas.

Prapagar

 

[Prapagar]

Vivek,

able to get the function in Expression Editor,

I changed the following section in impfunct.ini & orfunct.ini,


[check_numeric]
label=check_numeric
param=1
return=CH
1=CH;String_exp
exp=check_numeric(^1)
tip=Syntax:check_numeric(String_exp)
tip1=String expression


so there is no limitation for UDF in Cognos7 version2 for native mode connection.

Thanx for ur help.

Prapagar

 

[kajarekar]

Prapagar

Thanks for you feedback

vivek

 

[mjhian]

I wondered how you added it to the DB!
" Pls help me in UDF,DB is Oracle.
I did the following steps but not able to get in Impromtu.
1)Created this function in DB."

I have the code in "oracle udf.txt" from the example:
create or replace function url (
IN_URL varchar2,
IN_TEXT varchar2 := NULL
) return varchar2
is begin
return '<A HREF="' || IN_URL || '">' ||
nvl(IN_TEXT,IN_URL) || '</A>';
end;
/

But how is this added to the Oracle DB?

Thanks in advance.