Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Stored Procedure + Multiple Table insert

    Hi all...I'm a bit of a noob to pl/sql so please excuse my ignorance. I'm sure this is a pretty common task/question in here yet I couldn't really find what I was lookin for when i searched the sql forum..so here goes:

    Basically, I've got to do an insert (from an ASP) page into multiple tables. 4, to be precise. The first table I need to insert data into has a column that has a trigger/seq on it which is the PK. I need to grab that value, and use it in my other 3 inserts for their FK's. I know you can do this using a stored proc...But my knowledge as I said is limited in this area..And I don't want to make a huge ASP mess doing for inserts and passing around the key.

    I'm not so confused as to doing the insert in the stored proc...somethin like this?

    My two main questions are, how do i transition from one insert to another? is it as simple as just adding another statement? Or do I need to do somethin in between and....how do I store the ID Column (the value I need to reinsert)..a bind variable? Below is my code...the parts I didnt know how to code are in []'s..Any direction would be great


    Code:
    CREATE OR REPLACE PACKAGE InsertQueue
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE InsertQueue
    	(
    		
    		QueueName				IN		VARCHAR2,
    		NotifyName				IN		VARCHAR2
    		NotifyContact				IN		VARCHAR2,
    		ITSC_Increment             		IN   		VARCHAR2
    		
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY InsertQueue
    AS
    	PROCEDURE InsertQueue
    	(	
    
    		QueueName				IN		VARCHAR2,
    		NotifyName				IN		VARCHAR2
    		NotifyContact				IN		VARCHAR2,
    		ITSC_Increment             		IN   		VARCHAR2
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
    Insert into Tbl_Queue (Q_Name) VALUES (QueueName)  [GET ID COLUMN]
    Insert into Tbl_Increment (ID, ITSC_Increment) VALUES ([ID COLUMN FROM TBL_QUEUE],ITSC_Increment)
    Insert into Tbl_Last_Query (ID) VALUES ([GET ID COLUMN})
    Insert into Tbl_Notify (Q_Name, Contact_Address, Q_id, Full_Name) VALUES (QueueName, Notify_Contact, [GET ID COLUMN], Notify_Name)
    
    
    END;
    
    
    END;
    /

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You don't need the REF CUR, or any other cursor for this. To return a value from an insert/update use the RETURNING clause.

    Code:
    create or replace function.....
       nId table1.id%type;
    begin
       insert into table1(col1,col2) values ('a','b') returning ID into nID;
       insert into table2(id,col3) values (nId,'c');
    end;
    etc etc
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Looks pretty straight fwd...My only question is about this line

    nId table1.id%type;

    Should it literally be that (aside from the table name) or...is that just a declartation of a variable to hold the ID value?

    thanks again

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    yes, it is just a variable declaration. you could specify it as "nid number". or whatever datatype it should be. using the "%type" guarantees that the variable is always the same datatype as the table column, even if the column is altered.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Dec 2003
    Posts
    148
    hmm...my stored proc is below however I am getting an error associated with the returning into statement....any ideas?

    Code:
    SQL> CREATE OR REPLACE PACKAGE InsertQueue
      2  AS
      3   
      4   PROCEDURE InsertQueue
      5   (
      6    
      7    QueueName    IN  VARCHAR2,
      8    NotifyName    IN  VARCHAR2,
      9    NotifyContact    IN  VARCHAR2,
     10    ITSC_Increment               IN     VARCHAR2,
     11    nID       tbl_queue.id%type
     12    
     13   );
     14  END;
     15  / 
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY InsertQueue
      2  AS
      3   PROCEDURE InsertQueue
      4   ( 
      5  
      6    QueueName    IN  VARCHAR2,
      7    NotifyName    IN  VARCHAR2,
      8    NotifyContact    IN  VARCHAR2,
      9    ITSC_Increment               IN     VARCHAR2,
     10    nID       tbl_queue.id%type
     11   )
     12   AS
     13   BEGIN 
     14  Insert into Tbl_Queue (Q_Name) VALUES (QueueName) returning ID into nID;  
     15  Insert into Tbl_Increment (ID, ITSC_Increment) VALUES (nID,ITSC_Increment);
     16  Insert into Tbl_Last_Query (ID) VALUES (nID);
     17  Insert into Tbl_Notify (Q_Name, Contact_Address, Q_id, Full_Name) VALUES (QueueName, NotifyCont
    act, nID, NotifyName);
     18   END;  
     19  END;
     20  / 
    
    Warning: Package Body created with compilation errors.
    
    SQL> 
    SQL> show err
    Errors for PACKAGE BODY INSERTQUEUE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/69    PLS-00403: expression 'NID' cannot be used as an INTO-target of a
             SELECT/FETCH statement
    
    SQL>

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I am getting an error associated with the returning into statement..
    Error? What error? I don't see any error.
    Are we supposed to guess both the error & the solution?
    If so, the always correct answer is "42".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You are declaring nID in the wrong place, try something like:

    Code:
    CREATE OR REPLACE PACKAGE BODY InsertQueue AS
       PROCEDURE InsertQueue
       ( 
        QueueName IN VARCHAR2,
        NotifyName IN VARCHAR2,
        NotifyContact IN VARCHAR2,
        ITSC_Increment IN VARCHAR2
       )
       IS
          nID tbl_queue.id%type;
       BEGIN 
          Insert into Tbl_Queue (Q_Name) VALUES (QueueName) returning ID into nID;  
          Insert into Tbl_Increment (ID, ITSC_Increment) VALUES (nID,ITSC_Increment);
          Insert into Tbl_Last_Query (ID) VALUES (nID);
          Insert into Tbl_Notify (Q_Name, Contact_Address, Q_id, Full_Name) VALUES (QueueName, NotifyCont
    act, nID, NotifyName);
       END;  
    END;

  8. #8
    Join Date
    Dec 2003
    Posts
    148
    JM,
    Thanks - that worked great.

    Ana,
    I try my best to provide all the info I can when looking for help on BB's...I had the error below my Stored Proc.

Posting Permissions

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