Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Using %TYPE isn't working

    I was trying to keep my datatypes in sync on a PACKAGE I'm building, but I'm having trouble using %TYPE. Neither of the following assignments (for the variables that are commented out) is allowing me to compile the PACKAGE BODY.

    Is this not possible, or am I doing something wrong?

    Code:
    create or replace package mypg
    as
        type wk_stack_row is record (object_name varchar2(30));
        type wk_stack_tabtype is table of wk_stack_row index by binary_integer;
                                
    end mypg;
    
    create or replace package body mypg
    as
      --anotherval wk_stack_row.object_name%TYPE; 
      --anotherval wk_stack_tabtype.object_name%TYPE; 
    end mypg;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of SELECT * from v$version.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    OS name & version: Red Hat Enterprise Linux AS release 4
    Code:
    BANNER                                                          
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bi
    PL/SQL Release 10.1.0.4.0 - Production                          
    CORE	10.1.0.4.0	Production                                      
    TNS for Linux: Version 10.1.0.4.0 - Production                  
    NLSRTL Version 10.1.0.4.0 - Production                          
    
    5 rows selected.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The Manuals do not mention such a combination & all my tests failed.
    I'd conclude that %TYPE (%ROWTYPE) do not work on/with TYPE datatype
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    "The %TYPE attribute provides the datatype of a variable or database column." Type definition is not a variable or a database column.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you consider something like this?
    Code:
    SQL> create or replace package mypg
      2  as
      3    type wk_stack_row is record (object_name varchar2(30));
      4    type wk_stack_tabtype is table of wk_stack_row index by binary_integer;
      5  end mypg;
      6  /
    
    Package created.
    
    SQL> create or replace package body mypg
      2  as
      3    anotherval wk_stack_row;
      4    anotherval wk_stack_tabtype;
      5  end mypg;
      6  /
    
    Package body created.
    
    SQL>

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    We're doing that when we want the entire record. But there are some cases where I want to check the current value in the array, with a locally-stored variable. It's the locally stored variable that I want created in the same TYPE as that in the RECORD.
    --=cf

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes
    in the same TYPE as that in the RECORD.
    Is that what you mean?
    Code:
    anotherval wk_stack_row.object_name%TYPE
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    It is what I was trying, yes. That is what I have in the sample code at the top, which doesn't compile (when you take those comments out).

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, I guess you need to instantiate a variable of your custom type, then determine its %TYPE attribute.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    I agree that you're right on target, but, I don't think PL/SQL works that way. Or at least, I can't get it to work that way. You can only create a variable of the same %ROWTYPE as the entire TYPE, rather a variable which shares the same %TYPE as one of it's components.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •