Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Exceute Immediate Error

    I m tring my hand on Dynamic SQL for the first time. Can some1 in simple terms explain the meaning of Execute Immediate. I tried 1 code which was

    declare
    l_depnam varchar2(20) := 'testing';
    l_loc varchar2(10) := 'Dubai';
    begin
    execute immediate 'insert into dept values (:1, :2, :3)'
    using 100, l_depnam, l_loc;
    commit;
    end;
    /

    However I got the error :

    execute immediate 'insert into dept values (:1, :2, :3)'
    *
    ERROR at line 5:
    ORA-06550: line 5, column 10:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
    := . ( @ % ;


    Can anybody explain y at the first place are we using Dynamic SQL like this.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    go to http://asktom.oracle.com
    and do a keyword lookup on
    dynamic SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can try following...


    declare
    l_depnam varchar2(20) := 'testing';
    l_loc varchar2(10) := 'Dubai';
    l_num NUMBER (3) := 100
    begin
    execute immediate 'insert into dept values (:1, :2, :3)'
    using l_num, l_depnam, l_loc;
    commit;
    end;
    /

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    can anybody help

    Error when I tried ur code.

    ERROR at line 9:
    ORA-06550: line 9, column 9:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
    := . ( @ % ;

    end;
    /


    May be Execute Immediate is not supported in the Version I m working in .I am using Oracle8i and My SQL*PLUS version is 8.0.4.0.0.

    So wats the problem

  5. #5
    Join Date
    Jan 2004
    Posts
    492

    Re: can anybody help

    Originally posted by varun_751980
    Error when I tried ur code.

    ERROR at line 9:
    ORA-06550: line 9, column 9:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
    := . ( @ % ;

    end;
    /


    May be Execute Immediate is not supported in the Version I m working in .I am using Oracle8i and My SQL*PLUS version is 8.0.4.0.0.

    So wats the problem
    Do a little debugging yourself - there is a missing semicolon ; after the l_num variable declaration. I assume if you are trying Dynamic SQL you should at least have a primitive understanding of PL/SQL!

  6. #6
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Re: can anybody help

    I missed the semicol while pasting the code. It is there in code which was executed . still I am getting the above error.

    Any help ???????

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess that you'd like to insert data into "scott.dept" table. If so, on my Oracle 8.1.7 column dept.deptno is a number(2), while you try to insert "100" into it. Is it number(3) on Oracle 8i?

    Other than this, your code written in the very first post here works properly.

  8. #8
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by Littlefoot
    I guess that you'd like to insert data into "scott.dept" table. If so, on my Oracle 8.1.7 column dept.deptno is a number(2), while you try to insert "100" into it. Is it number(3) on Oracle 8i?

    Other than this, your code written in the very first post here works properly.
    it works fine with me.....

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: can anybody help

    Originally posted by varun_751980
    May be Execute Immediate is not supported in the Version I m working in .I am using Oracle8i and My SQL*PLUS version is 8.0.4.0.0.

    So wats the problem
    That is probably it: 8.0.4.0.0 predates 8i and probably doesn't support EXECUTE IMMEDIATE. If you have 8i server you should also have the 8i client.

Posting Permissions

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