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 On Tue, Oct 26, 2010 at 11:12 AM, Dan H <dan.he...@gmail.com> wrote: > 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 > -- 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
