Home » SQL & PL/SQL » SQL & PL/SQL » reset sequence / insert values (merged)
reset sequence / insert values (merged) [message #376785] Thu, 18 December 2008 09:44 Go to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Good afternoon!

I need to use a sequence in my program but it comes up with this error."ENCOUNTERED THE SYMBOL RESET_SEQUENCE when expecting :=,;(" PLS00103
This is the sequence:
CREATE OR REPLACE PROCEDURE APP.APP_TMP_FVENDAS_REJEITADOS ( evento IN NUMBER) IS
/*<TOAD_FILE_CHUNK>*/
BEGIN     
      IF evento = 1 THEN           
           EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS'; 
             EXEC reset_sequence ('FVENDAS_SEQ');
   
            --SELECT FVENDAS_SEQ.NEXTVAL from dual   
            INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC) 
            SELECT FVENDAS_SEQ.NEXTVAL, NIF, TC1, TC2, NIC 
            FROM APP_TMP_FVENDAS_WORK WHERE TRANSACTION_DT >= sysdate-5;
                        
      END IF;   
     
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERRO NO PROCESSO APP.APP_TMP_FVENDAS_REJEITADOS: ' || TO_CHAR(SQLCODE) || '-' || SQLERRM);
        app_log('[ERROR](APP.APP_TMP_FVENDAS_REJEITADOS): ' || TO_CHAR(SQLCODE) || '-' || SQLERRM); 
END;
/

CREATE or REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as    
    curr_val INTEGER;
BEGIN
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
    EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by -'||curr_val;
    EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
    EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1'; 
END reset_sequence;
/


Please do tell me what's wrong with this?

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 18 December 2008 13:38] by Moderator

Report message to a moderator

Re: RESET SEQUENCE [message #376788 is a reply to message #376785] Thu, 18 December 2008 09:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To be honest I am not sure want you are trying to achieve. But for your error remove the exec from your anonymous block.

Next time if you could format your post it will be much appreciated.

Regards

Raj

[Edit: ] Please don't multipost.

[Updated on: Thu, 18 December 2008 09:51]

Report message to a moderator

Re: RESET SEQUENCE [message #376791 is a reply to message #376785] Thu, 18 December 2008 09:55 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Hey raj im really sorry for my inexperience in this forum, ill try to get better in terms of posting Razz

But let me tell you you'r post was very nice...it worked...removed the EXEC and it worker...


THANKS A LOT... Cool
INSERT Values [message #376796 is a reply to message #376785] Thu, 18 December 2008 10:10 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
IF evento = 1 THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS';

reset_sequence ('FVENDAS_SEQ');

INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC)
SELECT FVENDAS_SEQ.NEXTVAL, NIF, TC1, TC2, NIC
FROM APP_TMP_FVENDAS_WORK WHERE TRANSACTION_DT >= sysdate-5;

END IF;

What happens with this is that it cleans the table APP_TMP_FVENDAS and it doesn't inserts any values from table APP_TMP_FVENDAS_WORK. What's wrong with this block?

Oliveira
Re: INSERT Values [message #376798 is a reply to message #376796] Thu, 18 December 2008 10:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How many more posts are you planning to create ?

Regards

Raj
Re: INSERT Values [message #376803 is a reply to message #376796] Thu, 18 December 2008 10:34 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Im sorry im having problems with the network. and it gives a problem when a submit, but it does submit and thats why it open 3 posts..really sorry Confused
Re: INSERT Values [message #376808 is a reply to message #376796] Thu, 18 December 2008 11:09 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
But aside from the issues before.. Sad

Can somebody tell me why is it that its not inserting values in the table that i want?

Oliveira
Re: INSERT Values [message #376809 is a reply to message #376796] Thu, 18 December 2008 11:27 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Hello once again|
IF evento = 1 THEN
SET TRANSACTION USE ROLLBACK SEGMENT RBS17;
EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS';
reset_sequence ('FVENDAS_SEQ');
INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC)
SELECT FVENDAS_SEQ.NEXTVAL, NIF, TC1, TC2, NIC
FROM APP_TMP_FVENDAS_WORK;
COMMIT;
END IF;

I post once again the code, because this is not inserting values from one table to the other?maybe its not entering in that piece of code...i really dont know Sad Please Help

Oliveira
Re: INSERT Values [message #376814 is a reply to message #376809] Thu, 18 December 2008 12:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Please supply the COMPLETE code, not just a snippet.
Re: INSERT Values [message #376817 is a reply to message #376809] Thu, 18 December 2008 12:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oliveiraum wrote on Thu, 18 December 2008 18:27
maybe its not entering in that piece of code...i really dont know Sad

Learn how to debug. Add log-messages throughout your code to track if it enters (for example) this IF-THEN construct.
Re: INSERT Values [message #377351 is a reply to message #376809] Mon, 22 December 2008 08:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The logic behind tyour code works - see example below.

Possible causes of your problem:
1) There is no data in the table APP_TMP_FVENDAS_WORK

2) Your code is aising an exception somewhere and the exception is being buried by a WHEN OTHERS THEN null; exception handles

3) Anything else, as you've not posted nearly enough information.



create table test_051 (col_1 varchar2(20));

create table test_052 (col_1 varchar2(20));

insert into test_051 select 'T_51 Row '||level from dual connect by level <= 10;

insert into test_052 select 'T_52 Row '||level from dual connect by level <= 10;

commit;

begin

  execute immediate 'TRUNCATE TABLE test_051';
  
  insert into test_051 (col_1) select col_1 from test_052;
  commit;
end;
/

select * from test_051;
Re: RESET SEQUENCE [message #377578 is a reply to message #376785] Tue, 23 December 2008 10:30 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
Hello again!

SET TRANSACTION USE ROLLBACK SEGMENT RBS17;
EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS';
reset_sequence ('FVENDAS_SEQ');
INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC)
SELECT FVENDAS_SEQ.NEXTVAL(), NIF, TC1, TC2, NIC
FROM APP_TMP_FVENDAS_WORK;
COMMIT;

With this piece of code i'm really not getting values from APP_TMP_FVENDAS_WORK to APP_TMP_FVENDAS, and in the end i don´t have any values in the 2 table, and it did had before the insert...this procedure is called trough a shell script...i don't understand why i'n note getting values...
i'm sorry for asking again but can somebody help me?

Re: RESET SEQUENCE [message #377586 is a reply to message #377578] Tue, 23 December 2008 11:05 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read the forum guide. Learn how to use code tags (It's really REALLY easy). Use them from now on.

Either you've got no records in APP_TMP_FVENDAS_WORK,
or you're code is erroring out and the shell script is hiding/not reporting the error,
or some other code is deleting the records you've just inserted.

If you run that code in sqlplus and query APP_TMP_FVENDAS immediately afterwards what happens?
Re: INSERT Values [message #377588 is a reply to message #376796] Tue, 23 December 2008 11:20 Go to previous messageGo to next message
oliveiraum
Messages: 16
Registered: November 2008
Junior Member
CREATE OR REPLACE PROCEDURE APP.APP_TMP_FVENDAS_REJEITADOS ( evento IN NUMBER) IS

/*<TOAD_FILE_CHUNK>*/
BEGIN     
      IF evento = 1 THEN
           SET TRANSACTION USE ROLLBACK SEGMENT RBS17;
           EXECUTE IMMEDIATE 'TRUNCATE TABLE APP_TMP_FVENDAS'; 
           reset_sequence ('FVENDAS_SEQ');
           INSERT INTO APP_TMP_FVENDAS(ID,NIF, TC1, TC2, NIC) 
           SELECT FVENDAS_SEQ.NEXTVAL(), NIF, TC1, TC2, NIC 
           FROM APP_TMP_FVENDAS_WORK; 
           COMMIT;     
      END IF;   
      
      IF evento = 2 THEN
         DELETE FROM APP_TMP_FVENDAS_WORK P WHERE EXISTS
               (    
                 SELECT NIF FROM APP_TMP_FVENDAS T where PROCESSED_FLG = 'Y' AND P.NIF = T.NIF);       
       COMMIT;
      END IF;
      ROLLBACK;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERRO NO PROCESSO APP.APP_TMP_FVENDAS_REJEITADOS: ' || TO_CHAR(SQLCODE) || '-' || SQLERRM);
        app_log('[ERROR](APP.APP_TMP_FVENDAS_REJEITADOS): ' || TO_CHAR(SQLCODE) || '-' || SQLERRM); 
END APP_TMP_FVENDAS_REJEITADOS;
/

Here it is the entire code....please do help me...


[Edit MC: add code tags]

[Updated on: Tue, 23 December 2008 11:45] by Moderator

Report message to a moderator

Re: INSERT Values [message #377589 is a reply to message #377588] Tue, 23 December 2008 11:46 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Now use SQL*Plus and show us the problem.

Regards
Michel
Previous Topic: CAN I INSERT DATA INTO THE TABLE USING NO DATA FOUND EXCEPTION
Next Topic: Instance lockup on GRANT to table
Goto Forum:
  


Current Time: Sat Dec 03 01:26:22 CST 2016

Total time taken to generate the page: 0.08644 seconds