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 > > On Mon, Oct 25, 2010 at 10:37 AM, Dan H <dan.he...@gmail.com> wrote: > > 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) > > > -- > > 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-PLSQL@googlegroups.com > > To unsubscribe from this group, send email to > > oracle-plsql-unsubscr...@googlegroups.com > > For more options, visit this group at > >http://groups.google.com/group/Oracle-PLSQL?hl=en > > -- 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-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
