Results 1 to 4 of 4

Thread: select query

  1. #1
    Join Date
    Aug 2013
    Posts
    4

    Unanswered: select query

    Hi,

    I am having a table as temp_customers with columns like personid etc. while i execute the below procedure I receive the error as "invalid relational operator" in the query.

    CREATE OR REPLACE procedure customer_detail
    IS

    v_refresh varchar2(500);
    CURSOR get_org IS
    SELECT cst_ref
    FROM temp_org1;

    begin

    for x1_rec in get_org
    loop
    v_refresh:= 'insert into temp_table (select PERSONID from temp_customers where'|| x1_rec.cst_ref ||'is not null)';
    execute immediate v_refresh;
    end loop;
    end;
    /

    could you please help me

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
    Then print the variable before passing it to EXECUTE IMMEDIATE.
    COPY the statement & PASTE into sqlplus to validate its correctness.


    I suspect the problem is due to lack of space characters before & after "x1_rec.cst_ref "
    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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by mathina View Post
    Hi,

    I am having a table . . .
    . . . E t c . . .
    begin

    for x1_rec in get_org
    loop
    v_refresh:= 'insert into temp_table (select PERSONID from temp_customers where'|| x1_rec.cst_ref ||'is not null)';
    execute immediate v_refresh;
    end loop;
    end;
    /
    You may need spaces here:
    Code:
    . . .
    --- - - V - here and here - - V
        where'|| x1_rec.cst_ref ||'is not null)';

    PS: If you do what anacedent suggests, you would spot it.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2013
    Posts
    4
    Thanks a lot. The space was the problem

Posting Permissions

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