1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Create type privelegs

Discussion in 'SQL PL/SQL' started by jr1, May 29, 2009.

  1. jr1

    jr1 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello @All



    DB: Ora 10.2.0.4

    I'm new on this forum, but I got a problem on my DB.
    I tried to create a user type:

    CREATE OR REPLACE TYPE StringArrayType AS TABLE of VARCHAR2(64);
    answere from DB:
    type created


    next check the type:
    describe StringArrayType;
    answere from DB:
    Error: object StringArrayType does not exist



    doing the same as user sys/sysdba -- it works. So I think, it should be a problem of privelegs, but I do not find the problem.




    We build a test-script, which works fine on DB Ora9
    but not on 10


    DECLARE
    myStrings StringArrayType := StringArrayType();
    BEGIN
    myStrings.EXTEND;
    myStrings(1):= 'HALLO';
    --
    myStrings.EXTEND(2);
    myStrings(2):= 'Mr.';
    myStrings(3):= 'Oracle';
    --
    FOR element IN 1..myStrings.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(element)||'-tes Element => ' ||myStrings(element));
    END LOOP;
    END;
    /





    Please can you give me some Information about my problems ?

    Thanks a lot

    Josef
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    HI Josef, I don't see any problem with your code. I logged into Oracle 10.2.0.1 as system and did this

    Code (Text):

    SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 29 17:32:57 2009

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> CREATE OR REPLACE TYPE stringarraytype AS TABLE OF VARCHAR2 (64);
      2  /

    Type created.

    SQL> set serveroutput on;
    SQL> DECLARE
      2     mystrings   stringarraytype := stringarraytype ();
      3  BEGIN
      4     mystrings.EXTEND;
      5     mystrings (1) := 'HALLO';
      6  --
      7     mystrings.EXTEND (2);
      8     mystrings (2) := 'Mr.';
      9     mystrings (3) := 'Oracle';
     10  
     11  --
     12     FOR ELEMENT IN 1 .. mystrings.COUNT
     13     LOOP
     14        DBMS_OUTPUT.put_line (   TO_CHAR (ELEMENT)
     15                              || '-tes Element => '
     16                              || mystrings (ELEMENT)
     17                             );
     18     END LOOP;
     19  END;
     20  /
    1-tes Element => HALLO
    2-tes Element => Mr.
    3-tes Element => Oracle

    PL/SQL procedure successfully completed.
     
    SO no problem whatsoever.