Comments by KaRaMiLo (Top 19 by date)

still not working and when i compile it nothing is wrong but when i run it that message keep coming

i keep getting this error :
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

i can't apply it on update statements

the problem that i am trying to update not to select !

I got some problems :
In those examples there is different websties to search in but in my example it is only 1 website so when he ranks something he use a url pattern which is different because there is multiple website but in my case there is only 1 pattern so the results will remain the same !!
do you got me ?

i am stuck because oracle portal don't support multipart request ( file upload )

i don't want to do this i already added the site to search for , i want to rank the results

well i am using google search api with in my website and i want it to search my website only ( i've already done this ) but when the user use the example above i want the result to ranked.

i do know the user name and password but i don't know how to use them within java code !

here is what am doing , but i want to allow dynamic sql upon UPDATE ! :


DELIMITER //
CREATE TRIGGER update_prefix BEFORE UPDATE ON prefix_info FOR EACH ROW
BEGIN
SET @curr_number=0,@num_record=0,@temp=1,@cond=0;
SELECT count(subscriber_id)curr_numb INTO @num_records FROM account_info ;

while @temp <= @num_record do


if @temp <= 1 then
SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id = account_info.sub_prefix_id limit 1;
UPDATE account_info SET account_info.subscriber_id = concat(NEW.prefix_value,@curr_number) WHERE NEW.prefix_id = account_info.sub_prefix_id ;
SET @temp=@temp+1;
end if;

if @temp > 1 then
SET @cond = @temp-1 ;
SET @cond = concat(@cond,",1");
SET @cond = concat("SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id=account_info.sub_prefix_id limit ",@cond);
PREPARE stmt FROM @cond;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
UPDATE account_info SET account_info.subscriber_id = concat(NEW.prefix_value,@curr_number) WHERE NEW.prefix_id = account_info.sub_prefix_id ;
SET @temp=@temp+1;
end if;


end while;

END ;//

thnx for ur answer , i know all of that but i want to test this case exactly locally on my pc to do it , it is just an alternative to a solution i already got using stored procedures.

hmm , i know that but this link still don't illustrate Is it ok to use Dynamic SQL with TRIGGERS !!

I am sorry but i really guess that u miss the point .
abt ur reply i already know that but thnx anyway.

now here is the scenario :

i got 2 tables :
1- Account_info : with fields : (foreign Key {Prefix_id to prefix into table} , number , subscriber_number )
there is a trigger for this table in order to get the subscriber number using both prefix id and number as in example :
insert into account_info (prefix_id, number ) values (1,9090909) ; ( will automatically insert the subscriber_number with the value that ID 1 refers to and the rest of the number concat (Value(prefix_id(1),number)

2- Prefix_info : with fields: (primary key prefix_id , prefix_value) , this also got a trigger but this trigger works differently .
this trigger is fired when u try to update the prefix value so it looks for all values in account_info table which got the same prefix_id and it will update the subscriber number according to new values.

here is the second trigger code :
Trigger: before_update
Event: UPDATE
Table: prefix_info
Statement: BEGIN
SET @curr_number=0;
SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id = account_info.sub_prefix_id;
UPDATE account_info SET account_info.subscriber_id = concat(NEW.prefix_value,@curr_number) WHERE NEW.prefix_id = account_info.sub_prefix_id ;
END

You will notice in this statement "SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id = account_info.sub_prefix_id;" that i try to load the number into variable but the query returns more than a single number which cause this error "ERROR 1172 (42000): Result consisted of more than one row" , so i want to enhance that trigger behavior in order to let it update all records not a single one only.

i hope that i make it clear for u know.

and thnx for ur time

will i am not so good in mysql , so i really don't know if this going to make a problem or not . But the item in where clause is the same item which will be updated (is it going to to make a problem or not ?).
after adding index to that item inside where clause i will use the item or the index (i guess the item rite ?)