Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: priv to user types

    Hell @All

    I try to create a type like:
    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



    I do not know why.


    when I logged in as sys, it works ! So I checked to diffent privelegs: A lot

    I do all grants to type, which I found for user sys: Bit it does'nt work.

    Does anyone know, how to fix my problem, whithout grant all priv. from sys to user ?

    Thanks a lot for a ansere.

    Josef
    DB: oracle 10.2g ( 10.0.2.0.4 )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> CREATE OR REPLACE TYPE StringArrayType AS TABLE of VARCHAR2(64);
      2  /
    
    Type created.
    
    SQL> desc stringArraytype
     stringArraytype TABLE OF VARCHAR2(64)
    It works for me 10gR2
    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
    May 2009
    Posts
    5

    Which user did you used

    thanks for your working.

    Which user did you used ?
    as sys, it works fine, but Aplication-user does not work.
    Josef

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I always operate using my own DBADMIN schema to avoid SYS or SYSTEM.
    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
    May 2009
    Posts
    5

    this works

    But we created a Appliction-User with a limit of privilegs.
    So no user is able to change the db

    Josef

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So no user is able to change the db
    Then why are you trying to CREATE object & changes DB with a user who should not be doing so?
    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.

  7. #7
    Join Date
    May 2009
    Posts
    5

    user

    Oh, sorry,
    my english is to bad...


    the create a user for our application, using the database local and remote.
    this user have limited privilegs.

    It's working fine.
    now we start to update our application to new function.
    There we are going to use array's for invoking stored-Procedure with no fix number of arguments.
    The called Sored procedure will check the arguments

    So I try to create a a new type. It works fine.

    Use the type, we got the problems.
    so we try to check describe, because, it's a internal function of oracle.

    And it does'nt work. So our application is not the reason to our problems.
    So we started to look for the fix.

    The user, which I try, is the application user. the user is allowed to create table and so on, but not to administer the database.

    is my description so ok to understand ?
    Josef

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >There we are going to use array's for invoking stored-Procedure with no fix number of arguments.
    >The called Sored procedure will check the arguments
    You are working hard to build an application that won't scale & be very challenging to properly test to provide solid Quality Assurance.

    Using DESCRIBE as a means for sanity testing is questionable.
    DESC works only for SQL*Plus & is a non-standard (& buggy) function.

    Please provide small coding example as to how the planned concept would actually operate within the application.

    You report problem, but when we can not see the code or actual errors it is difficult to provide reasonable alternatives or suggestions.

    use CUT & PASTE so we can see exactly what you are seeing.
    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.

  9. #9
    Join Date
    May 2009
    Posts
    5

    script

    Hello anacent,


    thanks for your support.

    here is a short testing scrpit, which fails too.
    Our application, which fails, is a combination Java and PLSQL Stored-Procedure, which need more software to work.
    So we build for testing a smal script....
    these script should work on DB 9.2 as user sys, not as normal user

    I'm going to test it on DB 10.2...


    Thanks for help
    Josef

    ************************************************** ********************************


    PROMPT Typ anlegen ...
    CREATE OR REPLACE
    TYPE StringArrayType AS TABLE of VARCHAR2(64)
    /

    PROMPT Struktur des Types ...
    desc StringArrayType;

    DECLARE
    myStrings StringArrayType := StringArrayType();
    BEGIN
    /*
    || Die Methode EXTEND fügt Element(e) an eine Collection an.
    || Ohne Parameter aufgerufen, hängt EXTEND ein einziges Nullelement an.
    */
    myStrings.EXTEND;
    myStrings(1):= 'HALLO';
    /*
    || EXTEND(n) fügt n Nullelement an.
    */
    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;
    /

    ************************************************** ***************************************

Posting Permissions

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