Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Dynamic Injection
PL/SQL Dynamic Injection [message #200028] Fri, 27 October 2006 08:28 Go to next message
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&nbsp;' || 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: insert two records
Next Topic: sysout
Goto Forum:
  


Current Time: Wed Dec 07 14:22:12 CST 2016

Total time taken to generate the page: 0.05195 seconds