1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to avoide the error ORA-00900!!

Discussion in 'SQL PL/SQL' started by many_yammy, May 19, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    what is the problem in this piece of code:


    CREATE OR REPLACE PROCEDURE RESET_SEQ (P_SEQ_NAME IN VARCHAR2, P_VAL IN NUMBER)
    IS
    L_VAL NUMBER;

    BEGIN

    EXECUTE IMMEDIATE
    'SELECT' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

    IF (P_VAL - L_VAL - 1) != 0 THEN
    EXECUTE IMMEDIATE
    'ALTER SEQUENCE' || P_SEQ_NAME || 'INCREMENT BY' || (P_VAL - L_VAL - 1) || 'MINVALUE 0';
    END IF;

    EXECUTE IMMEDIATE
    'SELECT' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

    EXECUTE IMMEDIATE
    'ALTER SEQUENCE' || P_SEQ_NAME || 'INCREMENT BY 1';

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Sequence does not exist');
    END;
    /



    that I receive the error:

    Error starting at line : 29 in command -
    exec reset_seq ('surface_Area_seq', 1)
    Error report -
    ORA-00900: invalid SQL statement
    ORA-06512: at "ANDREAS.RESET_SEQ", line 7
    ORA-06512: at line 1
    00900. 00000 - "invalid SQL statement"



    I appreciate any help in advance.
    Many
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Check your code....

    Simple example :


    Code (SQL):


    CREATE OR REPLACE PROCEDURE RESET_SEQ (P_SEQ_NAME IN VARCHAR2, P_VAL IN NUMBER)
    IS
        L_VAL NUMBER;
        Z     NUMBER;
    BEGIN

    EXECUTE IMMEDIATE 'SELECT ' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

    Z:=(P_VAL - L_VAL - 1);

    IF Z != 0 THEN
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || P_SEQ_NAME || ' INCREMENT BY ' || Z || ' MINVALUE 0';
    END IF;

    EXECUTE IMMEDIATE  'SELECT ' || P_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO L_VAL;

    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || P_SEQ_NAME || ' INCREMENT BY 1';

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Sequence does not exist');
    END;
    /
    EXEC  RESET_SEQ('surface_Area_seq',1);


     
     
  3. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    what would be the problem?

    I think the code is correct.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    you didn't use separators:
    between 'SELECT' and P_SEQ_NAME,
    between 'ALTER SEQUENCE' and P_SEQ_NAME
    etc.
     
    many_yammy likes this.
  5. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Oh my God.

    you're right.

    Thanks.