Home » SQL & PL/SQL » SQL & PL/SQL » What am I doing wrong here... (10G )
What am I doing wrong here... [message #563523] Tue, 14 August 2012 11:09 Go to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
I am a relative novice at PL SQL... That said... What I would like to do is reset my sequence to 10,000 (or 10,001 is fine as well).

I'm having issues executing the proc. Also, I couldn't figure out how to insert directly from a grouped insert statement into a variable... so I used a cursor with only one record, to do this... there's probably a far easier way to do this, but I didn't know it.

I' getting this as my error message:

Quote:
ORA-06550: line 1, column 42:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.


Any help would be greatly appreciated!

CREATE OR REPLACE 
PROCEDURE S00050_RESET_POINVASS_SEQUENCE
    AS
        v_SEQUENCE_NAME  VARCHAR2 (50) := 'POINVASS_ID_SEQ';
        v_STATEMENT VARCHAR2(2000);
        v_MAX_POINVASS_ID NUMBER;
               

    CURSOR cv_RESET_SEQUENCE_cursor IS
        SELECT MAX(POINVASS_ID) MAX_POINVASS_ID
        FROM P0100_PO_INV_ASSETS;


    BEGIN
        OPEN cv_RESET_SEQUENCE_cursor;    

            LOOP
                FETCH cv_RESET_SEQUENCE_cursor 
                INTO v_MAX_POINVASS_ID;
                
            EXIT WHEN cv_RESET_SEQUENCE_cursor%NOTFOUND;
            
            END LOOP;            
                  
            
            v_STATEMENT := 'ALTER SEQUENCE ' || v_SEQUENCE_NAME ||' INCREMENT BY -' || TO_CHAR(v_MAX_POINVASS_ID, '99,999');  
            
            EXECUTE IMMEDIATE v_STATEMENT;            
            
            v_STATEMENT := 'SELECT '|| v_SEQUENCE_NAME ||'.NEXTVAL FROM dual';
            
            EXECUTE IMMEDIATE v_STATEMENT; --This should bring the sequence to zero
            
            v_STATEMENT := 'ALTER SEQUENCE ' || v_SEQUENCE_NAME ||' INCREMENT BY 10000';  
            
            EXECUTE IMMEDIATE v_STATEMENT; 

            v_STATEMENT := 'SELECT '|| v_SEQUENCE_NAME ||'.NEXTVAL FROM dual';
            
            EXECUTE IMMEDIATE v_STATEMENT; --This should bring the sequence to 10000

            v_STATEMENT := 'ALTER SEQUENCE ' || v_SEQUENCE_NAME ||' INCREMENT BY 1';    

            EXECUTE IMMEDIATE v_STATEMENT; --This should make the next NEXTVAL = 10,001
        
           
    END S00050_RESET_POINVASS_SEQUENCE;
END;

/  


Thanks for taking the time to look at this,
-Gary
Re: What am I doing wrong here... [message #563524 is a reply to message #563523] Tue, 14 August 2012 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 22540
Registered: January 2009
Senior Member
CREATE OR replace PROCEDURE S00050_reset_poinvass_sequence 
AS 
  v_sequence_name VARCHAR2 (50) := 'POINVASS_ID_SEQ'; 
  v_statement     VARCHAR2(2000); 
BEGIN 
    v_statement := 'DROP SEQUENCE POINVASS_ID_SEQ'; 

    EXECUTE IMMEDIATE v_statement; 

    v_statement := 'CREATE SEQUENCE POINVASS_ID_SEQ START WITH 10000'; 

    EXECUTE IMMEDIATE v_statement; 
END s00050_reset_poinvass_sequence; 

/ 
Re: What am I doing wrong here... [message #563525 is a reply to message #563524] Tue, 14 August 2012 11:33 Go to previous messageGo to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
I tried the above code, but it's giving me an insufficient privileges error... even though this is all within my own schema and I am the only one creating objects here.

Quote:
ORA-01031: insufficient privileges
ORA-06512: at "GBLACK.S00050_RESET_POINVASS_SEQUENCE", line 12
ORA-06512: at line 1
Re: What am I doing wrong here... [message #563526 is a reply to message #563525] Tue, 14 August 2012 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 22540
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


SQL> show user
USER is "SYS"
SQL> GRANT CREATE SEQUENCE TO GBLACK;
SQL> EXEC S00050_reset_poinvass_sequence;
Re: What am I doing wrong here... [message #563528 is a reply to message #563526] Tue, 14 August 2012 12:04 Go to previous messageGo to next message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
I read the guidlines, I'm not really sure what I didn't do in my post, please advise. Just FYI, I am running all this from TOAD and not an SQL Prompt... if that makes any difference in reagrds to the guidlines.

I tried to run Show User... I get invalid SQL Statement. As far as: GRANT CREATE SEQUENCE TO GBLACK goes, I am not a DBA so I can't run this either, without the insufficient privileges error message.

I don't understand why I can run a create sequence script directly (meaning if I just run this: CREATE SEQUENCE GBLACK.POINVASS_ID_SEQ START WITH 10000, it works fine), but don't have rights to do so when I execute the stored procedure.

Re: What am I doing wrong here... [message #563531 is a reply to message #563528] Tue, 14 August 2012 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 22540
Registered: January 2009
Senior Member
privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
unless & until GRANT CREATE SEQUENCE TO GBLACK is issued successfully, nothing more can be done.
Re: What am I doing wrong here... [message #563532 is a reply to message #563528] Tue, 14 August 2012 12:11 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
It's the old "privileges granted through roles do not translate to stored procedures." You must have the privilege granted directly to you by a SYS or another user with DBA rights.
Re: What am I doing wrong here... [message #563533 is a reply to message #563528] Tue, 14 August 2012 12:11 Go to previous message
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
Junior Member
OK I found the answer to the privs issue here:

http://stackoverflow.com/questions/996198/execute-immediate-within-a-stored-procedure-keeps-giving-insufficient-priviliges

CREATE OR REPLACE PROCEDURE usp_dropTable(schema VARCHAR, tblToDrop VARCHAR)  AUTHID CURRENT_USER IS BEGIN... 


Thanks for the help! Much appreicated.
Previous Topic: Updating table with sequentially changing column name
Next Topic: Exception at Update Trigger
Goto Forum:
  


Current Time: Wed Jul 30 23:20:34 CDT 2014

Total time taken to generate the page: 0.15441 seconds