On Oct 25, 1:37 pm, 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)
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
--
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
