User defined function returns VARCHAR2 at Max size

[Dan H]

I have created a function and only need to return a VARCHAR2 of size
25 but when I use the function in a create table as statement I am
always getting a column width of 4000. Any suggestions?

CREATE OR REPLACE FUNCTION Find_Category (Word IN VARCHAR2)
RETURN VARCHAR2 IS
temp_variable varchar2(25);
BEGIN
temp_variable := case
when InStr(Word, 'PSC')<>0 Then 'PSC'
when InStr(Word, 'ACH')<>0 Then 'RRL'
when InStr(Word, 'FMC')<>0 Then 'RRL'
when InStr(Word, 'PLC')<>0 Then 'RRL'
when InStr(Word, 'RGH')<>0 Then 'RRL'
when InStr(Word, 'SMC')<>0 Then 'HCTL'
when InStr(Word, 'SCHC')<>0 Then 'HCTL'
when InStr(Word, 'AHC')<>0 Then 'HCTL'
when InStr(Word, 'DSC')<>0 Then 'DSC'
else 'Unknown'
end;
temp_variable := substr(temp_variable,1,25);
return temp_variable;

end;


Shouldn't the fact that I have declared the variable as VARCHAR2(25)
limit the return variable? I even tried the substr but I still creates
column of VARCHAR(4000)

[Michael Moore]

Dan,

Your return type is VARCHAR2, not VARCHAR2(25) (which would not be allowed) . So, the fact that your temp_variable is varchar2(25) is irrelevant.  

Not sure what you mean by "getting" as in "getting a column width of 4000". Also, not sure what you mean by "creates column of VARCHAR(4000)". 

What is it that you are doing that shows you "4000"?

Mike

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[Michael Moore]

I think I should clarify. When you return from the Find_category function, the number of characters returned should be the same as the number of characters in the variable "temp_variable". Since the largest possible value you are assigning to temp_variable is "Unknown" (7 characters), the most your calling program should ever see is a length of 7.

----------------------------------------------try this--------------

DECLARE

   v_con   VARCHAR2 (4000);

   FUNCTION con

      RETURN VARCHAR2

   IS

      temp_str   VARCHAR2 (25);

   BEGIN

      temp_str := 'Unknown';

      RETURN temp_str;

   END;

BEGIN

   v_con := con;

   DBMS_OUTPUT.put_line (LENGTH (v_con));

   DBMS_OUTPUT.put_line (v_con);

END;

[Thomas Olszewicki]

Create TABLE, Create View are DDL commands in SQL engine.
SQL engine cannot look into an pl/sql function to determine the length
of return value.
You have to help the SQL engine to establish this value.
Try:
create or replace view v$find_category as
select substr(find_category('PCS'),1,25) FindCat from dual;

HTH
Thomas

[Dan H]

To clarify when I use the function in a create table as select
statement I get a column width of 4000. But I found a way to reduce
this somewhat using a substr statement in the select. Bad? If I don't
use the substring I get a column width of 4000


CREATE table coll_count_2 as

SELECT Count(*) as ORDER_MNEMONIC_Count,
encounter,
substr(Find_Category(coll_loc_name),1,25) as Collection_Loc_Category

On Oct 25, 12:23 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> Dan,
>
> Your return type is VARCHAR2, not VARCHAR2(25) (which would not be allowed)
> . So, the fact that your temp_variable is varchar2(25) is irrelevant.
>
> Not sure what you mean by "getting" as in "getting a column width of 4000".
> Also, not sure what you mean by "creates column of VARCHAR(4000)".
>
> What is it that you are doing that shows you "4000"?
>
> Mike
>

[Michael Moore]

Now I understand.

Try this

create or replace function fmmx

return varchar2

as

begin

return 'xxx';

end;

create table mmt1 as 

select  cast (fmmx as varchar2(30)) mx2 from dual;

Mike

[Dan H]

That worked great.

Thanks for your help. More questions to come.....

On Oct 26, 12:52 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> Now I understand.
>
> Try this
>
> create or replace function fmmx
> return varchar2
> as
> begin
> return 'xxx';
> end;
> create table mmt1 as
> select  cast (fmmx as varchar2(30)) mx2 from dual;
>
> Mike
>

[Aaron G]

Thanks for this!