This discussion is archived
10 Replies Latest reply: Dec 4, 2012 2:44 AM by 914264 RSS

PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared

914264 Newbie
Currently Being Moderated
Hi all, when I copy a procedure from oracle and then paste it in the TimesTen to recreate the procedure,I get the following exception:
PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared.
So the type 'TABLETYPE_VARCHAR2' is available in oracle while not in TimesTen.
Is there approach to solve this problem or some links to list the incompatibility between oracle and TimesTen about PL/SQL?


Thanks
  • 1. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Hi SuoNayi,

    When you are creating the PL/SQL code in TimesTen you should create all dependent objects in TimesTen as well. In your case, for successful compilation you should create the "TABLETYPE_VARCHAR2" type, but be careful not all PL/SQL types work in TimesTen.
    Documentation says:

    Note the following non-support of data types:

    - PL/SQL data type categories: PL/SQL in TimesTen does not support Internet data types (XMLType, URIType, HttpURIType) or "Any" data types (AnyType, AnyData, AnyDataSet).

    - PL/SQL scalar data types: TimesTen does not support the PL/SQL data types TIMESTAMP WITH [LOCAL] TIME ZONE and UROWID.

    - TimesTen PL/SQL does not support the TimesTen type TT_DECIMAL.

    Hope it helps.

    Best regards,
    Gennady
  • 2. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    914264 Newbie
    Currently Being Moderated
    Gennady,thanks for your information.
    It seems that TABLETYPE_VARCHAR2 is one data type in oracle while not available in timesten.
    In order to migrate the procedure from oracle to timesten gracefully,
    I want to know if there is a substitute or solution for this data type.
  • 3. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Dear SuoNayi,

    Could you please post your PL/SQL Oracle DB code (procedure, TABLETYPE_VARCHAR2 type) which you would like to migrate to TimesTen?

    PS. Please be aware that not ALL PLSQL code which is working fine in Oracle DB could be migrated to TimesTen. Please have a look on restrictions in documentation (http://ocpdba.net/doc/db/11.2/timesten.112/e13076/plsqldiffs.htm)

    Best regards,
    Gena
  • 4. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    914264 Newbie
    Currently Being Moderated
    Hi Gena, I'm afraid that 'tabletype_varchar2' is a user type declared in a standalone stored function.
    I will make sure for this tomorrow morning and give a feedback.
    Thanks for your quick response.

    Good night.
  • 5. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    I'm afraid that 'tabletype_varchar2' is a user type declared in a standalone stored function.
    In this case, you probably need to create this standalone stored function in TimesTen. Anyway, find out it tomorrow.

    Best regards,
    Gena
  • 6. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    914264 Newbie
    Currently Being Moderated
    Hi Gena,I have found two custom types and a custom stored function, they are the following:
    ------------------------------------------------------------------------------------------------------------
    CREATE OR REPLACE TYPE TABLETYPE_NUMBER AS TABLE OF number
    CREATE OR REPLACE TYPE TABLETYPE_VARCHAR2 AS TABLE OF varchar2(255)
    ------------------------------------------------------------------------------------------------------------
    create or replace function f_str_split
    (var_source_string in varchar2,
    var_separator in varchar2,
    var_number in number:=0)
    return TABLETYPE_VARCHAR2 is
    v_source_string varchar2(2000);
    v_separator_position integer;--分割符位置
    v_source_length integer;--要分割的字符串的长度
    v_separator_length integer;--分割符的长度
    v_result TABLETYPE_VARCHAR2:=TABLETYPE_VARCHAR2();
    v_subset varchar2(2000);
    v_count integer:=0;
    begin
    if trim(var_separator) is null then
    raise_application_error(-20001,'必须输入分割符!');
    end if;
    v_source_string:=trim(var_source_string);
    --判断分割符的位置
    v_separator_position:=instr(v_source_string,var_separator);
    --分隔符的长度
    v_separator_length:=length(var_separator);
    --源分割字符串的长度
    v_source_length:=length(var_source_string);
    --源字符串最后必须有分隔符
    if substr(v_source_string,v_source_length-v_separator_length+1,v_separator_length)<>var_separator then
    raise_application_error(-20002,''''||v_source_string||'''字符串最后必须有分隔符!');
    end if;
    while v_separator_position>0 loop
    --截取分割后的字符串
    v_subset:=substr(v_source_string,1,v_separator_position-1);
    if trim(v_subset) is not null then
    v_result.extend;--数组添加一个空下标
    v_result(v_result.last):=v_subset;--为数组最后一位赋值
    end if;
    v_source_string:=substr(v_source_string,v_separator_position+v_separator_length);
    v_separator_position:=instr(v_source_string,var_separator,1);
    v_count:=v_count+1;
    if var_number=v_count then
    v_result.delete;
    v_result.extend;--数组添加一个空下标
    v_result(v_result.last):=v_subset;--为数组最后一位赋值
    return(v_result);
    end if;
    end loop;
    return(v_result);
    end f_str_split;
    ------------------------------------------------------------------------------------------------------------
    I know timesten does not support custom object types, but the procedures use the two types and the function always.
    How to solve this problem?


    Thanks
  • 7. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Hi SuoNayi ,

    In your case I would recommend to create a package with these types. For example:
    create or replace package p_types is
       type tabletype_number is table of number;
       type tabletype_varchar2 is table of varchar2(255);
     end p_types;
    /
    Package created.
    and after that create the function, like the following:
    create or replace function f_str_split (var_source_string in varchar2,
                                            var_separator in varchar2,
                                            var_number in number:=0)
    return p_types.tabletype_varchar2 is
        v_source_string varchar2(2000);
        v_separator_position integer;--?????
        v_source_length integer;--??????????
        v_separator_length integer;--??????
        v_result p_types.tabletype_varchar2:= p_types.tabletype_varchar2();
        v_subset varchar2(2000);
        v_count integer:=0;
    begin
        if trim(var_separator) is null then
           raise_application_error(-20001,'???????!');
        end if;
        v_source_string:=trim(var_source_string);
        --????????
        v_separator_position:=instr(v_source_string,var_separator);
        --??????
        v_separator_length:=length(var_separator);
        --?????????
        v_source_length:=length(var_source_string);
        --????????????
        if substr(v_source_string,v_source_length-v_separator_length+1,v_separator_length) = var_separator then
            raise_application_error(-20002,''''||v_source_string||'''???????????!');
        end if;
        while v_separator_position>0 loop
            --?????????
            v_subset:=substr(v_source_string,1,v_separator_position-1);
            if trim(v_subset) is not null then
                v_result.extend;--?????????
                v_result(v_result.last):=v_subset;--?????????
            end if;
            v_source_string:=substr(v_source_string,v_separator_position+v_separator_length);
            v_separator_position:=instr(v_source_string,var_separator,1);
            v_count:=v_count+1;
            if var_number=v_count then
               v_result.delete;
               v_result.extend;--?????????
               v_result(v_result.last):=v_subset;--?????????
               return(v_result);
            end if;
        end loop;
        return(v_result);
    end f_str_split;
    /
    It compiles well in TimesTen and it works.
    Command> declare
                     v_t p_types.tabletype_varchar2:= p_types.tabletype_varchar2();
                   begin
                      v_t := f_str_split('dhsgd,sdfsd,sdsfsf', ',');
                      dbms_output.put_line(v_t.count);
                  end;
                   /
    2
    
    PL/SQL procedure successfully completed.
    Hope it helps.

    Best regards,
    Gena

    Edited by: Gennady Sigalaev on 04-Dec-2012 02:11
  • 8. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    914264 Newbie
    Currently Being Moderated
    Gena, it seems that package in timesten is a substitute for custom types in oracle, right?
    I will try this solution and give you a feedback.


    Thanks a lot.
  • 9. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    Gennady Sigalaev Journeyer
    Currently Being Moderated
    Gena, it seems that package in timesten is a substitute for custom types in oracle, right?
    TimesTen doesn't support the object types, but we can use types into packages, it is kind of "workaround" I think.
    Basically, the packages are not replacing custom types (there are two different PLSQL objects with different purpose), but in this case we can do it.

    Best regards,
    Gena
  • 10. Re: PLS-00201: identifier 'TABLETYPE_VARCHAR2' must be declared
    914264 Newbie
    Currently Being Moderated
    Gena,compilation is passed!

    Thank you very much.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points