Ever had a trouble while you were trying to print a variable, got an ORA-06502 error?
ORA-06502: PL/SQL: numeric or value error string
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
If your variable is a CLOB it might be confusing as it was for me..
However, you ‘ll figure out the reason soon enough: Those string parts that you were collecting are bigger than 32K
And you ‘ll realize that you should tell it what to do.
Here is what I came across to. A big and special thanks to oracle forums, of course :)
PROCEDURE chunk_clob(p_clob CLOB) IS
v_offset PLS_INTEGER DEFAULT 1;
v_clob CLOB := p_clob;
v_chunk := dbms_lob.getchunksize(v_clob);
v_len := dbms_lob.getlength(v_clob);
WHILE v_offset v_chunk
THEN v_size := v_chunk;
ELSE v_size := v_len - (v_offset - 1);
v_buf_size := NULL;
dbms_lob.READ(v_clob, v_size, v_offset, v_buf_size);
v_offset := v_offset + v_size;
– OKAY what is this and what am I going to do with this?
It ‘ll take care of your oversized strings so you get to see what your VAR has in it.
Bir sorgu hazirladiniz ve bu sorgudan donen sonucu periyodik olarak bir tabloya yazmak istiyorsunuz..
Ya da bu bir rapor ve bu rapora bakarak periyodik olarak bir kisi ya da ekibe mail gondermek istiyorsunuz..
APEX icinden nasil mail gonderilecegine dair kucuk bir ornegi su yazimda anlatmaya calismistim.
Simdi ise bunu “otomatik” hale getirmeyi deneyelim.
Farkli yontemler elbette var, ama ben -veri tabaninizin “eski” bir versiyonda olmadigini varsayarak- dogrudan dbms_scheduler uzerinden bir procedure ile ilerleyecegim.
i. Oncelikle sorgumuzu bir procedure haline getirerek objemizi olusturalim. Bunu APEX ‘in etkilesimli menulerine yaptirabilecegimiz gibi SQL konsolunda elle de yapabiliriz.
Sorgunun detaylari bu yazinin konusu degil. Fakat soz gelimi, belirli bir data setini dinamik olarak sececegimiz alicilara gondermek istiyorsak
Bir cursor acabilir,
Sonra bu cursor ‘i loop icinde %NOTFOUND oluncaya kadar fetch ederek aksiyonumuzu aldirabiliriz: mesela her defasinda bir email gondermek gibi.
Schedule etmeye karar verdiginiz islem sisteme periyodik olarak email gonderme isi vermekse ve ornek mail gonderme kismini burada isledigimiz gibi apex_mail.send uzerinden kotariyorsaniz, dikkat etmemiz gereken aslinda belki de tek onemli nokta su:
Hazirladiniz SP icinde BEGIN END arasinda APEX Workspace ID ‘nizi assign emelisiniz..
You published a useful interactive report for your users and now want to let them sending emails through that page.
Here you can find a basic example..
Go to your report page from Application Builder panel
Create a page item button
Pick Defined by Dynamic Action as button action
Right click to your brand new Dynamic Action and select Create.
As you can see above, we have to create this action with some conditions. Such as:
Selection Type: Button
Button: address your button which you ‘ve just created. This item will trigger your email sending process.
Once we completed these steps, we can add a True action. You ‘ll see a button named Add True Action at your Dynamic Action Edit page. You can also create one by choosing from right click menu.
You may want to uncheck Fire On Page Load. Because.. Well we want to trigger it by clicking our button, manually :)
So.. Now we need this PL/SQL code which will take care of sending email via Oracle APEX.
I ‘ll share 2 basic examples with you. One for plain-text emails, one for HTML based ones.
I personally prefer to send mails in HTML format… But it ‘s up to you and depends on your needs..
It is pretty easy, right? Just give the right parameters and a simple sentence as email body. You can always use variables to collect these informations like i did..
If you want to go with HTML format, then you ‘ll need some extra time to create a good-looking and small-sized body. That ‘s all :)
Once you store these codes you ‘ve wrote and apply the changes onto your report page, you can start using your button. It should be sending emails to given address. Click on your button and wait couple of minutes to receive it.
I am not going to remind you not to copy everything from my sample snapshots. You do need to change email addresses etc. :)
Should you want to add some extra features on this dynamic action, you can add another True action(s) as well.
You may need to.. Otherwise someone would be able to click on that button more than necessary and we don ‘t want to spam people :)
Let ‘s add a Confirmation action..
Right Click, Create
Now you ‘ll be prompted to click OK everytime you click on your email button..
O.K. but not enough? Well let ‘s hide the button after it ‘s got clicked.
Now we have a button on our report page
Our authorized report viewers can click on it to send email
We have a confirmation dialog before trigger
Our email send button will be hidden right after our user click on it.