• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

how do I sync up sequence value so that the right number is entered into a column

I have a procedure that is executed when a new record is inserted into table A. This procedure then inserts the record into table B and populates an ID column using a sequence that gets the nextval in a trigger. The procedure is trying to copy that ID value back a column in table A.

The procedure was first lookiing for the currval. But because nextval is never called in procedure it doesn't work. So it was changed to get nextval. Only this doesn't work because after the insert into table B the nextval increments by 1. So the number entered into id in table B is never the same as the number we try to update the column in table A with.


Here is the section of the procedure. I omitted insert column names and variable declarations datatypes etc

                                                                         
execute immediate l_insert_stmt;    
EXECUTE IMMEDIATE 'SELECT ' ||targetSchema ||'.seq_bib.nextval FROM dual' INTO iCounter;                                                                                                              
                                                                               
 l_update_stmt := 'UPDATE ' || sourceSchema || '.tableA = ' || iCounter || ' WHEREtableB_ id = ' || stiRecordId;                            
   execute immediate l_update_stmt;          

how can i get the id value for table b entered into table a during the update                                    
                                 
0
sikyala
Asked:
sikyala
  • 6
  • 6
  • 4
2 Solutions
 
sdstuberCommented:
use  seq_bib.currval  in the update  after the next val of the insert


or insert and use the returning clause to extract the value you just inserted.

there is no need to do a select first
0
 
slightwv (䄆 Netminder) Commented:
0
 
sikyalaSenior Database AdministratorAuthor Commented:
using the returning clause sounds like what i want it isn't clear how to write the clause
i would want the value of the id column in table b assigned to icounter variable
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
execute immediate 'nsert into B (col1,col2,col3) values(1,2,3) returning id into :icounter' using out icounter;
0
 
sikyalaSenior Database AdministratorAuthor Commented:
when i add returning etc below insert statement it gives an error

execute immediate l_insert_stmt;
returning id into :icounter' using icounter;

i get the error

54/12   PLS-00103: Encountered the symbol "ID" when expecting one of the
         following:
         := . ( @ % ;

254/20   PLS-00049: bad bind variable 'ICOUNTER'
0
 
sdstuberCommented:
please post all of your code

I just did a small test with this...  

a trigger populates the ID column of mytable

DECLARE
    icounter   NUMBER;
BEGIN
    EXECUTE IMMEDIATE
        'insert into mytable (description,code) values(1,2) returning id into :icounter'
        USING OUT icounter;

    DBMS_OUTPUT.put_line(icounter);
END;
0
 
slightwv (䄆 Netminder) Commented:
add ' returning id into :icounter' to l_insert_statement.

you are also missing 'out':

execute immediate l_insert_stmt using out icounter;
0
 
sdstuberCommented:
also,  looks like you put the RETURNING clause on the outside of the sqlstatement,  it is part of the sql.

and you need to use USING OUT,  not just USING

USING - this is for input parameters
USING OUT - this for output parameters
0
 
sikyalaSenior Database AdministratorAuthor Commented:
well here is the beginning part of the procedure

create or replace PROCEDURE CopyRecord                                          
      (targetSchema IN VARCHAR2, sourceSchema IN VARCHAR2, stiRecordId IN NUMBER)    
                                                                               
      IS                                                                            
                                                                               
         l_insert_stmt           VARCHAR2(4000);                                    
                                                                               
         l_update_stmt           VARCHAR2(4000);                                    
                                                                               
         l_seq_stmt             VARCHAR2(4000);                                      
                                                                               
         l_insert_filePath        VARCHAR2(4000);                                    
                                                                               
         iCounter                NUMBER;                                            
                                                                               
      BEGIN                                                                          
                                                                               
        l_insert_stmt := 'INSERT INTO ';                                            
                                                                               
        l_insert_stmt := l_insert_stmt || targetSchema;                              
                                                                               
        l_insert_stmt := l_insert_stmt || '.TABLE_B                            
                                                                               
      (                                                                              
                                                                               
          field1,                                                                  
                                                                               
          fieldT2,                                                                
                                                                               
          field3                                                    
                                                                                
      )                                                                              
                                                                               
      SELECT                                                                        
                                                                               
          field1,                                                                  
                                                                               
          field2,                                                                
                                                                               
          field3                                                                
      FROM ';                                                                        
                                                                               
      l_insert_stmt := l_insert_stmt || sourceSchema;                                
                                                                               
      l_insert_stmt := l_insert_stmt || '.TABLE_A WHERE id = ' || stiRecordId ;
                                                                               
  <So I enter returning here?>
  returning id into :icounter'
    USING OUT icounter;
                                                                             
                                                                               
      execute immediate l_insert_stmt;  
      
      
      l_update_stmt := 'UPDATE ' || sourceSchema || '.TABLE_A SET RECORD_ID = ' || iCounter || ' WHERE id = ' || stiRecordId;              


It doesn't work when I do it this way how would I change it
0
 
slightwv (䄆 Netminder) Commented:
No.  you have:
...
 l_insert_stmt := l_insert_stmt || '.TABLE_A WHERE id = ' || stiRecordId ;
                                                                               
  <So I enter returning here?>
  returning id into :icounter'
    USING OUT icounter;
...


the "returning if into"  Needs to be part of the string passed to execute immediate.

change to:

 l_insert_stmt := l_insert_stmt || '.TABLE_A WHERE id = ' || stiRecordId || '  returning id into :icounter';

...
 execute immediate l_insert_stmt USING OUT icounter;
0
 
sikyalaSenior Database AdministratorAuthor Commented:
ok
0
 
sikyalaSenior Database AdministratorAuthor Commented:
getting error ORA-00933: SQL command not properly ended

0
 
sdstuberCommented:
"getting error"   doing what?

when you make a change, if that change doesn't work, you need post what your new code is doing so we can see what might be the cause?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
l_insert_stmt := l_insert_stmt || '.TABLE_A WHERE id = ' || stiRecordId || '  returning id into :iCounter';                                                                              
      
execute immediate l_insert_stmt USING OUT icounter;
0
 
sdstuberCommented:
yes - the returning part is correct

no - the insert part is wrong


why are you doing an insert with a WHERE clause?  also,  if you already have the ID,  why do you need try to return it in a different variable?


look at the example I posted above.  Your SQL statement, when fully populated, must look basically like what I did.

use dbms_output and print your sql instead of executing it.  Does the SQL look valid?
0
 
slightwv (䄆 Netminder) Commented:
Please post your table definition and procedure parameters so we can provide a working example.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now