Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Red face Unanswered: Can I update 20 Fields in a table by Stored Procedure

    Hai Friends,
    Iam trying to update a 20 fields table with a stored procedure and passing 20 parametres in the Procedure.But the problem is with Varchar2 data type.because whenever we pass value to a column we have to pass in the single quote for varchar2 & date data types.there Iam getting problem.Can you please ?? and can we pass so many fields as parametres in Oracle stored Procedures to update a database table with this values ??
    Venu

  2. #2
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Can I update 20 Fields in a table by Stored Procedure

    There should not be any problem as such. You can direcrly assign the input parameters to the update statements. Only requirement is the input parameters should be of the same datatype as the fields of the table. The requirement of putting quotations do not arrise unless you are putting a sincle hard coded value to a field.

    Cheers,
    Suren.

  3. #3
    Join Date
    Sep 2003
    Location
    India
    Posts
    3

    Re: Can I update 20 Fields in a table by Stored Procedure

    Originally posted by mulupuri999
    Hai Friends,
    Iam trying to update a 20 fields table with a stored procedure and passing 20 parametres in the Procedure.But the problem is with Varchar2 data type.because whenever we pass value to a column we have to pass in the single quote for varchar2 & date data types.there Iam getting problem.Can you please ?? and can we pass so many fields as parametres in Oracle stored Procedures to update a database table with this values ??
    Venu
    wat error u r getting ?

  4. #4
    Join Date
    Nov 2003
    Posts
    5

    Re: Can I update 20 Fields in a table by Stored Procedure

    Originally posted by Amardeep
    wat error u r getting ?
    Hi,


    There will be no problem at all update 20 fields of a table at a time with store procedure, only need to be careful is to pass parameters with proper data types.

    Cheers,

    Sunil Sharma

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Re: Can I update 20 Fields in a table by Stored Procedure

    Hi,

    U will be passing the values thro' parameters only, So there won't be any issue with Varchar2 & date datatypes. Take care that actual parameter matches the formal Parameters with datatype & position.
    SATHISH .

  6. #6
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    If you use bind variables, you don't have single quote problems:

    SQL> create procedure testolo (p in varchar2) as
    2 begin
    3 dbms_output.put_line ('p='||p);
    4 end;
    5 /

    Procedure created.

    SQL> set serveroutput on size 100000
    SQL> variable p varchar2(100);
    SQL> exec :p := 'When eating at Mario''s, we ...';

    PL/SQL procedure successfully completed.

    SQL> print p

    P
    ----------------------------------------------
    When eating at Mario's, we ...

    So you see that the bind variable p contains a double quote.

    SQL> exec testolo(:p);
    p=When eating at Mario's, we ...

    PL/SQL procedure successfully completed.

    No single quote problem.

    I'm not aware of max number for the parameters, must be well above 200 if it happens to be one.

    HTH
    Al

Posting Permissions

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