pl sql insert into within a procedure and dynamic variables

Question

I need some help with PL SQL. I have to insert some data into table. Another application is calling my procedure and caller is passing few details which I also need to insert into my table.

here is the syntax I am struggling with:

PROCEDURE invform_last2orders_item_insert(  p_userId IN NUMBER 
                       ,p_accountId IN  NUMBER
                       ,p_site_Id IN  NUMBER 
                       ,p_return_message OUT VARCHAR2) IS 
Begin 
  insert into mytable
  (p_userId , p_accountId , p_site_Id  , sku, description, 'Cart', 1, unitId) 
  as 
  select sku, description, unitId  
  from mycatalogtable where site_id= p_site_Id  ) ;
End;

Can you help me with syntax? I need to pass three parameters from called in parameter and some values returned from select query. How can I achieve this?

thank you for your help.

Littlefoot · Accepted Answer · 2020-10-19 21:45:45Z

4

That would be something like this; see comments within code:

PROCEDURE invform_last2orders_item_insert
  (  p_userId IN NUMBER 
    ,p_accountId IN  NUMBER
    ,p_site_Id IN  NUMBER 
    ,p_return_message OUT VARCHAR2) 
IS 
Begin 
  insert into mytable
    -- first name all columns you'll be inserting into; I don't know their
    -- names so I just guessed
    (userid, 
     accountid, 
     siteid, 
     sku, 
     description, 
     col1, 
     col2, 
     unitid
    )
    -- if you were to insert only values you got via parameters, you'd use the 
    -- VALUE keyword and insert those values separately. 
    -- As some of them belong to a table, use SELECT statement
    (select p_userid, 
            p_accountid, 
            p_siteid,
            c.sku,
            c.description,
            'Cart',
            1,
            c.unitid
     from mycatalogtable c
     where c.site_id = p_site_Id
    );

  -- I don't know what you are supposed to return; this is just an example
  p_return_message := sql%rowcount || ' row(s) inserted';
End;

answered Oct 19, 2020 at 21:45

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

techie_ns Over a year ago

but my select statement does not have columns p_userid, p_accountid, p_siteid to return. they are being passed into procedure call.

Littlefoot Over a year ago

Columns to be inserted must match the ones selected. It doesn't matter whether they are, actually, parameters passed to the procedure.

Himanshu Kandpal · Accepted Answer · 2020-10-20 01:04:47Z

0

in your select statement you should have the same number of columns as you are inserting into the table, your code should be something like this example,

DECLARE
userid varchar2(20) := 'Jack';
Begin
    INSERT INTO mytable (SELECT userid, SPORT from OLYM.OLYM_SPORTS);
    commit;
end;

answered Oct 20, 2020 at 1:04

Himanshu Kandpal

1,67611 silver badges18 bronze badges

Collectives™ on Stack Overflow

pl sql insert into within a procedure and dynamic variables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related