build dynamic sql on the fly in pl/sql 1

[ddiamond]

In pl/sql, is it possible to execute an sql statement that was built on the fly and stored in a string?

Here is my current code:

IF DBANum = 1 THEN
  UPDATE quote_follow_up_log
  SET dba_name1 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
ELSIF DBANum = 2 THEN
  UPDATE quote_follow_up_log
  SET dba_name2 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
ELSIF DBANum = 3 THEN
  UPDATE quote_follow_up_log
  SET dba_name3 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
ELSIF DBANum = 4 THEN
  UPDATE quote_follow_up_log
  SET dba_name4 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
ELSIF DBANum = 5 THEN
  UPDATE quote_follow_up_log
  SET dba_name5 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
ELSIF DBANum = 6 THEN
  UPDATE quote_follow_up_log
  SET dba_name6 = rec.dba_legal_name
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent';
END IF;

Notice that every update statement is identical except they update fields dbaname1 through dbaname6 depending on the numberic variable DBANum. I'm wondering if there is a cleaner way to handle this.

 

[Dagon]

You need to look at dynamic SQL. The way to do it now is to use the "EXECUTE IMMEDIATE" command. I can't remember if this was available in 8i or not. If it isn't, you have to use a package called DBMS_SQL. There is an Oracle manual called the called something like the "Supplied PL/SQL Packages" manual that has details of how to use this.

 

[SantaMufasa]

ddiamond,

Absolutely...you can build the SQL statement on the fly and execute the statement with this construct:

begin
    execute immediate
 'UPDATE quote_follow_up_log '||
  'SET dba_name'||dbanum||' = rec.dba_legal_name'||
  'WHERE master_number = rec.master_number '||
    'AND master_sequence = rec.master_sequence '||
    'AND status <> ''Sent''';
...
end;
/

Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddiamond]

Thanks Dagon, I will look into it.

 

[lewisp]

BEGIN
  EXECUTE IMMEDIATE 'UPDATE quote_follow_up_log ' ||
                    'SET dba_name' || To_Char(DBANum) ||
                    ' = rec.dba_legal_name ' ||
                    'WHERE master_number = rec.master_number ' ||
                    'AND master_sequence = rec.master_sequence ' ||
                    'AND status <> ''Sent''';
END;

 

[lewisp]

Curse my slow typing fingers!

 

[ddiamond]

Everyone, thanks for the quick replies.

SantaMufasa,

Thanks for the example. This is exactly what I am trying to do. Unfortunately, I think Dagon is correct. It appears that version 8i does support execute immediate, but version 8.0 does not. I am using version 8.0.

- Dan

 

[SantaMufasa]

Dan,

Seems like it's time to hit up the Powers That Be to fork over the dough for an upgrade to Oracle 10g, at least. <smile>

You are missing out on too much nice functionality by using a version that went off support years ago.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

The way I used to get around this back in 8i was to spool the statement to a flat file and then execute the flat file in SQL*Plus.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[lewisp]

Ok, its a bit of a hack, but try this:

UPDATE quote_follow_up_log
  SET dba_name1 = Decode(DBANum,1,rec.dba_legal_name,dba_name1),
      dba_name2 = Decode(DBANum,2,rec.dba_legal_name,dba_name2),
      dba_name3 = Decode(DBANum,3,rec.dba_legal_name,dba_name3),
      dba_name4 = Decode(DBANum,4,rec.dba_legal_name,dba_name4),
      dba_name5 = Decode(DBANum,5,rec.dba_legal_name,dba_name5),
      dba_name6 = Decode(DBANum,6,rec.dba_legal_name,dba_name6)
  WHERE master_number = rec.master_number
    AND master_sequence = rec.master_sequence
    AND status <> 'Sent'

 

[ddiamond]

Once again, thank you all for your prompt replies. I really appriciate it.

Dave,

You are right, we should have upgraded a long time ago, and I am still fighting that battle with corporate. Unfortunately, it isn't a question of money, it's all politics. BTW, I heard that Oracle 11 has been recently released.

BJCooper,

Spooling my sql statements to another file and then executing it is an interesting idea. My weekly script that updates statistics on all indexes uses this approach. Since I'm only dealing with 6 columns in this case, however, I'm not sure I want to resort to that.

Lewisp,

Nice decode statement. Although you still repeat code for all 6 fields, at least it is only one update statement (cleaner than my elsif statements). I may give it a try.

- Dan

 

[ddiamond]

Lewisp,

Your decode statement worked great.

- Dan