Forum Stats

  • 3,783,352 Users
  • 2,254,762 Discussions
  • 7,880,372 Comments

Discussions

Update stored procedure?

657216
657216 Member Posts: 1
edited Aug 29, 2008 7:53AM in SQL & PL/SQL
Hi there,

I'm a newbie about SQL/PLSQL, and I have a problem while working with Oracle. Can anyone help me to solve below problem?

I have a stored procedure as below:
PROCEDURE updateText(product IN VARCHAR2) IS+
BEGIN+

UPDATE table_name+
SET product_text = product;+
END updateText;+

which input params is:
* product: "product_code || product_description || product_name"
I want the new value to be a combination of "product_code || product_description || product_name" using value on the same record of updated record, and using appropriate value of that record.
However, when I run above stored procedure, the value of table_name.product_text became "product_code || product_description || product_name" for all records in the table :(.

Can you please help me on this? Thanks so much.

Regards,
Lam
Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,645 Red Diamond
    Can you give us some example data for the table and an example of a parameter and what the expected update on the data should look like. It'll help us to understand what you want better.

    Obviously you are missing a WHERE clause in your update statement, but how that should be implemented is anybody's guess (and it would be a guess) at the moment.
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    You dont have a filter criteria on your update. What records you have to update. You must specify them in your WHERE clause of the UPDATE statement. If you dont give that then entire table will be updated for that column.

    Thanks,
    Karthick.
  • 319958
    319958 Member Posts: 162
    yup, missing where clause means you want to update everything

    PROCEDURE updateText(product IN VARCHAR2,product_primaryKey IN table_name.productPrimaryKey%type ) IS
    BEGIN

    UPDATE table_name
    SET product_text = product
    WHERE productPrimaryKey = product_primaryKey
    ;
    END updateText;
  • 113438
    113438 Member Posts: 164
    I think you want something like
    PROCEDURE updateText(p_product_code IN integer) IS
    BEGIN
    UPDATE table_name
    SET product_text = product_code || product_description || product_name
    where product_code=p_product_code;
    commit;
    END updateText;
This discussion has been closed.