Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Thumbs up Unanswered: Problem with a Procedure !

    I don't know what's going on !!!

    I created this procedure and when I execute it, there's an error like:

    SQL> SET SERVEROUTPUT ON SIZE 1000000
    SQL> DECLARE
    2 BEGIN
    3 SPCRE_REINICIA_ANEXOS;
    4 END;
    5 /
    DECLARE
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at "CREDITOS_PRU.SPCRE_REINICIA_ANEXOS", line 15
    ORA-06512: at line 3


    Note: CREDITOS_PRU is the User

    /************************************************** *****
    PROCEDURE SPCRE_REINICIA_ANEXOS IS
    lnum_max_val VARCHAR2(10);

    BEGIN

    SELECT MAX(A)
    INTO lnum_max_val
    FROM (
    SELECT MAX(TO_NUMBER(CDANEXO_CLASIFICA)) A FROM TSCR_CUPO_X_PROPUES
    UNION
    SELECT MAX(TO_NUMBER(CDANEXO_CLASIFICA)) A FROM TSCR_CUPO_X_POLIZA
    )A;

    EXECUTE IMMEDIATE 'DROP SEQUENCE SSCR_ANEXOS';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE SSCR_ANEXOS INCREMENT BY 1 START WITH '||(lnum_max_val+1);

    END SPCRE_REINICIA_ANEXOS;
    ************************************************** *****/

    But when I put everything into a PL-SQL block (in SQL-Plus) like this:
    /************************************************** *****

    DECLARE
    lnum_max_val VARCHAR2(10);

    BEGIN

    SELECT MAX(A)
    INTO lnum_max_val
    FROM (
    SELECT MAX(TO_NUMBER(CDANEXO_CLASIFICA)) A FROM TSCR_CUPO_X_PROPUES
    UNION
    SELECT MAX(TO_NUMBER(CDANEXO_CLASIFICA)) A FROM TSCR_CUPO_X_POLIZA
    )A;

    EXECUTE IMMEDIATE 'DROP SEQUENCE SSCR_ANEXOS';
    EXECUTE IMMEDIATE 'CREATE SEQUENCE SSCR_ANEXOS INCREMENT BY 1 START WITH '||(lnum_max_val+1);
    END;

    ************************************************** *****/
    That works perfectly, The procedure Drops the Sequence perfectly, the problem is creating It.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with a Procedure !

    Stored procedures require privileges to be granted directly to their owner, not via a role.

  3. #3
    Join Date
    Jun 2003
    Posts
    294

    Re: Problem with a Procedure !

    OK
    I'll try !!

    Thanks !!

Posting Permissions

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