| PL/SQL Dynamic Injection [message #200028] |
Fri, 27 October 2006 08:28  |
cherr
Messages: 2
Registered: October 2006
|
Junior Member |
|
|
I am trying to store PL/SQL code in a table, na_emails.
Running all of the below:
============================================================
create table na_emails
(
msg_body long not null
)
/
create table na_recipients
(
email_addr varchar2(60) not null,
email_sent date,
first_name varchar2(60) not null,
last_name varchar2(60) not null,
constraint na_recipients_ea_pk primary key (email_addr)
)
/
insert into na_recipients (email_addr, first_name, last_name)
values ('cherr@vt.edu','Christian C.','Herr');
insert into na_emails (msg_body) values
('''<p>Dear '' || recipient_v.first_name || '' '' || recipient_v.last_name || '',</p>''');
commit;
/
DECLARE
recipient_v na_recipients%ROWTYPE;
email_v na_emails%ROWTYPE;
body_v long;
cursor r1 is
select *
from na_recipients
where email_addr = 'cherr@vt.edu';
cursor e1 is
select *
from na_emails;
BEGIN
open e1;
fetch e1 into email_v;
close e1;
open r1;
loop
fetch r1 into recipient_v;
exit when r1%NOTFOUND;
body_v := '<p>Dear ' || recipient_v.first_name || ' ' || recipient_v.last_name || ',</p>';
dbms_output.put_line(body_v);
dbms_output.put_line(email_v.msg_body);
end loop;
close r1;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
/
============================================================
You will get this:
<p>Dear Christian C. Herr,</p>
'<p>Dear ' || recipient_v.first_name || ' ' || recipient_v.last_name || ',</p>'
If you:
select msg_body
from na_emails
MSG_BODY
-------------------------------------------------------------------------------------------
'<p>Dear ' || recipient_v.first_name || ' ' || recipient_v.last_name || ',</p>'
I am trying to get the bottom output line here to look like the top. The goal is to be able to
have dbms_output.put_line(email_v.msg_body) interpret the ' || recipient_v.first_name || ' code.
I am thinking this is possible (somehow!). Any ideas? Does this make sense?
Running Oracle 8i, soon to move to 10g.
[Updated on: Fri, 27 October 2006 08:32] Report message to a moderator |
|
|
|
| Re: PL/SQL Dynamic Injection [message #200040 is a reply to message #200028] |
Fri, 27 October 2006 11:02  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
One easy way it tocreate palceholder in the template then just replace at runtime:
insert into na_emails (msg_body) values
('''<p>Dear ##FirstName## ##LastName##,</p>''');
then in your code:
email_v := replace(email_v, '##FirstName##', recipient_v.first_name);
email_v := replace(email_v, '##LastName##', recipient_v.last_name);
|
|
|
|
| Re: PL/SQL Dynamic Injection [message #200048 is a reply to message #200040] |
Fri, 27 October 2006 11:59 |
cherr
Messages: 2
Registered: October 2006
|
Junior Member |
|
|
|
That is true, it will work, but if I were to change the msg_body to include another field, I would have to recode the procedure; the goal being to have the procedure always stay the same and be able to simply change the data in the msg_body field.
|
|
|
|
| Re: PL/SQL Dynamic Injection [message #200062 is a reply to message #200048] |
Fri, 27 October 2006 13:55 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
SQL> create table tmp1 (col1 varchar2(100));
Table created.
SQL> --we don't want v3, v4 to be used - so just trim them down to null
SQL> insert into tmp1 values
2 ('''Hello ''||:v1||''-''||:v2||substr(:v3, 1, 0)||substr(:v4, 1, 0)||'', today is ''||sysdate');
1 row created.
SQL> set serveroutput on
SQL> DECLARE
2 -- these simulate your record variables
3 v_fnam VARCHAR2 (20) := 'Joe';
4 v_lnam VARCHAR2 (20) := 'Blogs';
5 v_later1 VARCHAR2 (20) := 'XX';
6 v_later2 VARCHAR2 (20) := 'YY';
7
8 v_out VARCHAR2 (100);
9 v_sql VARCHAR2 (100);
10 BEGIN
11 SELECT col1 INTO v_sql FROM tmp1 WHERE ROWNUM = 1;
12 --dbms_output.put_line('select '||v_sql||' from dual');
13 EXECUTE IMMEDIATE 'select ' || v_sql || ' from dual'
14 INTO v_out
15 USING v_fnam, v_lnam, v_later1, v_later2;
16 DBMS_OUTPUT.put_line (v_out);
17 END;
18
19 /
Hello Joe-Blogs, today is 27-OCT-06
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
