Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38

    Cool Unanswered: Declaring a Table as a Variable??? UPDATE

    I have a great question that I need help on. I am setting up a form that will allow me to change a the status (column) of a record. The form will have two text items, one to insert the table you would like to update, and the other being what status you'd like to make it (A or E). My problem is, Oracle won't allow the table in a UPDATE statement to be a variable.
    EXAMPLE:
    DECLARE
    temp_table VARCHAR2(50);

    BEGIN
    temp_table := :UPDATE_STATUS.table;

    UPDATE rec_status
    SET status = 'A'
    FROM temp_table
    WHERE rec_status is null;

    END;

    How can I use I make the table in the UPDATE statement a variable. Right now, it gives me an error saying I need to declare temp_table, and I did.
    Thank you for your help.
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://download-west.oracle.com/docs...8a.htm#2067717

    FWIW - Depending upon what you are really trying to do, you might need to use
    EXECUTE IMMEDIATE

    When all else fails, RTFM.
    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.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You might want to use dynamic SQL.

    Try something like this:

    Code:
    BEGIN
     execute immediate 'UPDATE :1
                                     SET status = ''A''
                                 WHERE rec_status is null'
     using :UPDATE_STATUS.table;
    END;
    But I wonder, why are you hand coding the status (A) when you say you would be getting it from a text item? you should check that up...

  4. #4
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38

    Wrong code

    I wrote the UPDATE Statement wrong:

    UPDATE temp_table
    SET rec_status = 'A'
    WHERE rec_status is null;


    That is my bad. The Oracle link didn't help too much. I really need to know how to declare a variable that will be used as the TABLE in the UPDATE statement.

    Thank you for your help.
    Todd
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by JMartinez
    You might want to use dynamic SQL.

    Try something like this:

    Code:
    BEGIN
     execute immediate 'UPDATE :1
                                     SET status = ''A''
                                 WHERE rec_status is null'
     using :UPDATE_STATUS.table;
    END;
    But I wonder, why are you hand coding the status (A) when you say you would be getting it from a text item? you should check that up...
    Actually the correct code should be:
    Code:
    BEGIN
     execute immediate 'UPDATE '||:UPDATE_STATUS.table
                            ||'      SET status = ''A''
                                 WHERE rec_status is null';
    END;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38

    Error

    I used that code and when I compiled it the error said, 'This feature isn't supported by client-side programs." It was focused on the 'execute immediate' command.
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by tfarino
    I used that code and when I compiled it the error said, 'This feature isn't supported by client-side programs." It was focused on the 'execute immediate' command.
    What client side program are you using?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38
    Oracle Forms 10g? Would it work once I place the form in the system?
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

  9. #9
    Join Date
    Feb 2004
    Posts
    108
    Hmm ... needs to to tested on 10 Forms.
    BTW, Oracle forms is always one step behind the DB !!
    Better have a DB procedure to perform this task.

  10. #10
    Join Date
    Jan 2004
    Location
    Las Vegas, NV USA
    Posts
    38
    I will give that a try. I'll get back to you after I write the procedure.

    Thank you
    Todd Farino
    Timet Corporation
    todd.farino@timet.com

Posting Permissions

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