Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2011
    Posts
    9

    Unanswered: Newbie question:

    I am reading a course in Oracle SQL development, and I have a question I canīt find any solution at.
    My task is:
    Make two object (one table, one sequence) as following desc:

    SQL> desc kund
    Name Null? Type
    ----------------------------------------- -------- ------------
    KNR PRIMARY KEY NOT NULL NUMBER(9)
    FNAMN VARCHAR2(25)
    ENAMN VARCHAR2(30)


    I have no problem to create a table and a sequence, but I canīt get any solution on hove a could make it with two objects.
    Do you guys have any tip?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I canīt get any solution on hove a could make it with two objects.
    I see the words but do not understand what they mean.

    Please clarify what you desire.

    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete 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
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by zecute View Post
    Make two object (one table, one sequence) as following desc:
    Apparently you have already done that.

    but I canīt get any solution on hove a could make it with two objects.
    Tables, Sequences, Views etc. are also called "Objects" in the database (that's why everything shows up in the view ALL_OBJECTS).

    To me this sounds you are simply confusing by the different meanings of the word object and that you are already done with your homework.

  4. #4
    Join Date
    Feb 2011
    Posts
    9
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Release 11.1.0.6.0 - Production
    PL/SQL Release 11.1.0.6.0 - Production
    CORE 11.1.0.6.0 Production
    TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
    NLSRTL Version 11.1.0.6.0 - Production

  5. #5
    Join Date
    Feb 2011
    Posts
    9
    Thanks! of course it is that way.
    But I have another problem that someone can gave me tip about.

    My task is:
    Create a trigger that starts before insert, update or delete at the table kund. The task that the trigger gets is that only the (user) 'BOSS' have the right to make DML operations against the table kund. Make an exception in the trigger that throws when some other user are trying to to do something in the table kund.

    I have made a procedure that the users are going to use when they are updating the table and the procedure is:

    Code:
    CREATE OR REPLACE PROCEDURE do_ny_kund(p_fnamn in kund.fnamn%TYPE, p_enamn in kund.fnamn%TYPE)
    AS
    v_knr NUMBER;
    BEGIN
    INSERT INTO kund (knr, fnamn, enamn)
    VALUES (v_knr, p_fnamn, p_enamn);
    commit;
    end;
    /
    So when the wrong user are trying to execute the do_ny_kund procedure then the trigger are going to execute and throws the exception.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And your question is?

  7. #7
    Join Date
    Feb 2011
    Posts
    9
    My question is:
    Hove can I make this type of trigger that only gets the specifik user 'BOSS' right to make DML operations against the table kund. And if another user are trying to make DML operations then does the trigger throws the exceptions?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by zecute View Post
    My question is:
    Hove can I make this type of trigger that only gets the specifik user 'BOSS' right to make DML operations against the table kund. And if another user are trying to make DML operations then does the trigger throws the exceptions?
    Create a trigger:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2235611

    Retrieve the current user:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions207.htm#i79833

    Check the current user:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref894

    Throw an exception
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref2017

  9. #9
    Join Date
    Feb 2011
    Posts
    9
    Thank you so much for your help!
    The problem is solved.

Posting Permissions

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