Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: procedure not working

    Hi,

    iam unable to run the procedure, the description is here in this procedure iam calling a function called get_role, here it will return a value to the procedure, but i didnt get the return value , when i fired the query the result is coming, so can any one help in this one, here iam sending all the code, to you.

    This is the procedure and here iam calling get_role function , and after returning the role iam assigning this role to that current user.

    CREATE OR REPLACE PROCEDURE authorization
    authid current_user
    as
    cmdstr varchar2(100);
    begin
    --
    -- Set the roles
    --

    cmdstr := get_role;
    dbms_session.set_role(cmdstr);
    end;
    /

    CREATE OR REPLACE FUNCTION get_role
    return varchar2
    as
    ex_user_name varchar2(30) := user;
    cmdstr varchar2(200);
    dummy char;
    begin

    --
    -- Make sure the function is being call from application server
    --
    select 'X' into dummy
    from ias_servers
    where host_name = sys_context('USERENV','HOST')
    and ip_address = sys_context('USERENV','IP_ADDRESS')
    and os_owner = sys_context('USERENV','OS_USER');

    --
    -- Get the role and password
    -- We are assuming the application uses only one role
    --
    select role_name || ' identified by ' || role_password
    into cmdstr
    from application_roles
    where role_name = ( select role_name
    from application_user_roles
    where user_name = ex_user_name
    and rownum = 1 );
    return ( cmdstr );
    exception
    when NO_DATA_FOUND
    then
    return ( null );
    end;
    /


    the procedure is compiled but when i am trying to run the procedure iam getting the following error

    ERROR at line 1:
    ORA-01937: missing or invalid role name
    ORA-06512: at "SYS.DBMS_SESSION", line 120
    ORA-06512: at "PMA_ADMIN.AUTHORIZATION", line 12
    ORA-06512: at line 1
    can any one help me, because iam struggling with the above error.

    Thank you.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would put a line in the function
    dbms_output.put_line(cmdstr);
    after you finish loading the variable to see exactly what the variable is.

    execute the function from sqlplus, but
    sql> set serveroutput on size 1000000;

    first

    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    You might hit an exception in the get_role function. Then
    when you run dbms.set_role(null) you will hit ORA-01937.

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Question

    authid current_user


    From which user you are calling the procedure and in which user the procedure and function are created ?
    Regards,

    Rushi

Posting Permissions

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