Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Arrow Unanswered: Execute Immediate Problem ????

    Hi all ,
    Can any one tell me why this piece of code is showing errors ?
    I am using oracle 8i and have a table named sms_dispatcher;
    the datatypes of the fields are as declared below the declare statement.


    declare
    msg_id varchar2(40);
    exp_date number(38);
    dest_addr varchar2(100);
    l_counter number(38);
    send_date number(38);
    fs_path varchar2(100);
    msg varchar2(100);
    l_status number(38);
    origin_addr varchar2(100);
    charged_party number(38);
    cm_time number(38);

    code_text varchar2(500);
    begin
    for i in 1 .. 10000
    loop
    msg_id := i;
    exp_date := 1064015375;
    dest_addr := '03138302110';
    l_counter := 5;
    send_date := '1063972175';
    fs_path := 'abcd';
    msg := 'X-Mms-Message-Type';
    l_status := 1;
    origin_addr := 'anonymous';
    charged_party := 1;
    cm_time := 1;

    code_text := 'insert into sms_dispatcher values (' || msg_id || ',' || exp_date || ',' || dest_addr || ',' || l_counter || ',' || send_date || ',' || fs_path || ',' || msg || ',' || l_status || ',' || origin_addr || ',' || charged_party || ',' || cm_time || ')';


    execute immediate (code_text);
    end loop;
    commit;
    end;
    /



    Reagards and thanks in advance.
    Sandy

  2. #2
    Join Date
    Sep 2003
    Posts
    20
    plz its urgent

  3. #3
    Join Date
    Aug 2003
    Posts
    41
    You didnt mention the error you are getting.


    If you can try the same code from SqlPlus you can do this.

    Just above your execute immediate line, add
    dbms_output.put_line(code_text);

    In SqlPlus, type SET SERVEROUTPUT ON
    and execute your code.

    This should print your code_text variable. ie. the command you are trying to execute. You can copy and paste this in SqlPlus prompt and see where your command has gone wrong.

    -Sunil.

  4. #4
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Execute Immediate Problem ????

    If there are any charactor fields you should concat the quotation mark (') at either side of the variable.

    eg: ||variable1||''','''||variable2''','||vabiable3||,

    In the above example varible 1 and 2 are getting inserted to charactor fields while variable 3 is numeric or date.

    To see whether the string is correct you can use dbms_output.put_line().

    Cheers,
    Suren.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It looks like youve missed out the single quotes for all your varchar2 variables i.e.

    You need to make sure you put sinle quotes around your varchar2 variables. To do this within a string you have to put two single quotes (i.e. equivalent to escaping a char in C using \). NOTE in the example below whereever you see '' it is two single quotes NOT double quotes.

    Alan

    if you have

    dest_addr := 'ABC';

    then

    code_text := 'insert into sms_dispatcher values (''' || dest_addr ||''')'

    so the actual insert statment looks like

    insert into sms_dispatcher values ('ABC')

    NOT like

    insert into sms_dispatcher values (ABC)

    as you are doing currently

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •