Results 1 to 4 of 4

Thread: Creating roles

  1. #1
    Join Date
    Feb 2011

    Angry Unanswered: Creating roles

    Hey forum

    I come to you in search of further enlightenment!

    Currently, I don't have the rights to create a role in the conventional way (university Oracle server admin haven't been kind enough to give us that )

    Basically, I'm trying to run the following script, so that I can customise access to a particular table:

    DECLARE v_role VARCHAR(6)
    SELECT role_name FROM user_security AS :v_role;
    IF v_role IN (‘Lead_officer')
    GRANT all ON pl_reported_crime
    ElsIF role IN ('Officer')
    GRANT READ ON pl_reported_crime;
    End IF;

    It's giving me the error message:

    ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior The symbol "begin" was substituted for "SELECT" to continue. ORA-06550: line 2, column 37: PLS-00103: Encountered the symbol "AS" when expecting one of the following: . , @ ; for <an identifier> <a double-quoted delimited-identifier> group having in

    Any suggestions?

    I'm fairly new to PL/SQL, but any help is appreciated. It's probably a fairly obvious mistake that I've overlooked as well :P

  2. #2
    Join Date
    Feb 2011
    SQL for user_security table

    ( "USER_ID" VARCHAR2(8),

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    - some of your statements miss delimiters;
    - SELECT statement syntax is incorrect;
    - GRANT statement syntax is incorrect;
    - I don't think you can embed DCL (GRANT) statements - use EXECUTE IMMEDIATE instead;

  4. #4
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    when doing SELECT inside of PL/SQL additional details are required to be factored into it.

    1) what should be done with the result set?
    2) if/when only a single row is returned, then the INTO clause can be included into SELECT
    3) when 2 or more rows are returned then some sort of LOOP is required to process each row 1 at a time

    Ask Tom Home
    above contains MANY fine coding examples which can be found doing KEYWORD search
    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.

Tags for this Thread

Posting Permissions

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