Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2003
    Posts
    53

    Unanswered: append string into sql select statement

    hi, i wanna append a string into a select statement. the example is as below:

    result varchar2(100);
    (some code to get result, e.g. "ID=abc OR ID=def;")

    SELECT * FROM student_table
    WHERE "result";

    i wanna put the result string to the statement above so that i can get the data i want. how can i do that? i tried some ways to put it but failes, getting an error. pls help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXECUTE IMMEDIATE will do what you want

  3. #3
    Join Date
    Nov 2003
    Posts
    53
    post immediate?
    can someone give me the example how to write using that?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit http://tahiti.oracle.com & RTFM yourself!
    If you don't know how/where to look up the syntax for any/every SQL command, you have no business being close to an Oracle DB.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Dynamic SQL is great for this type of thing ... or EXECUTE IMMEDIATE, but as anacedent says ... Read 1st, then trial and error until you get the feel of it and see what it can do...

  6. #6
    Join Date
    Nov 2003
    Posts
    53
    RTFM?? im sure its non-oracle related matter...
    anyway i have look into these actually but still have some error...but it seems some "GURU' lacks patient towards some new guys trying to learn oracle *sigh*...but nevermind there are some who are less ignorant about what they know...

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What is the error and what is the code

  8. #8
    Join Date
    Nov 2003
    Posts
    53
    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "RPT812ORA8.PKGE_STAGE_EISUM", line 216
    ORA-06512: at "RPT812ORA8.PKGE_STAGE_EISUM", line 130
    ORA-06512: at line 1

    my string is actually: mid='abc' OR mid='def'............. OR mid='zes';
    so can be caused by the ' sign?

    thanks for ur help and attention

  9. #9
    Join Date
    Nov 2003
    Posts
    53
    my statement:

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM EMT_LOG WHERE ' || result;

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here are some excerpts from 1 of my procedures I wrote ...
    You have to have a variable that you load you SQL statement into...
    This is using dynamic SQL. Same basic principle for EXECUTE IMMEDIATE... You build a VALID SQL statement and load it into a variable, then execute the variable ...
    ...
    ...
    ...

    stmt1 long;
    cur_n1 int;
    c1 int;
    stmt2 long;
    cur_n2 int;
    c2 int;
    dummy int := 2;
    v_size int := 1000000;
    v_offset int := 1;
    ...
    ...
    BEGIN
    ...
    ...
    STMT1 := 'SELECT '||c_column_updated||'
    FROM '||c_table_name||'
    WHERE '||c_key_columns;

    cur_n1 := dbms_sql.open_cursor;
    dbms_sql.parse(cur_n1,stmt1,dummy);

    dbms_sql.define_column_raw(cur_n1,v_offset,V_BITS, v_size);
    c1 := dbms_sql.execute(cur_n1);

    IF DBMS_SQL.FETCH_ROWS(cur_n1)>0
    THEN
    dbms_sql.column_value_raw(cur_n1,v_offset,v_bits);
    ....
    ....
    STMT2 := 'update '||v_table_name||'
    set '||c_column_updated||' = '||''''||converted_column||''''||'
    WHERE '||c_key_columns;

    cur_n2 := dbms_sql.open_cursor;
    dbms_sql.parse(cur_n2,stmt2,dummy);
    c2 := dbms_sql.execute(cur_n2);
    dbms_sql.close_cursor(cur_n2);
    END IF;

    dbms_sql.close_cursor(cur_n1);
    ...
    ...

    HTH
    Gregg

  11. #11
    Join Date
    Nov 2003
    Posts
    53
    oh now its a lot clearer and i think i know what i should do. thanks for your help gbrabham! sorry for all inconvenience caused because i'm really new in this thing...thanks all for helping.

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You might look at the concatenation of quotes since your result is
    a character string .... ie, ... NOTE here ... You have to get CARRIED away with the quotes !!!


    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM EMT_LOG WHERE id =' ||''''||result||''''||' or id = '||''''||result2||''''

    HTH and hang in there ... Remember .. It could always be worst ... It could be Monday !!!! ..... um .... Nevermind ...

    Gregg

  13. #13
    Join Date
    Nov 2003
    Posts
    53

    got it!

    yup i got it man, all thanks to u gbrabham! owe u 1...

    ----------------------------------------------------------------
    asking is a process of learning...

  14. #14
    Join Date
    Jan 2012
    Posts
    3

    Concat issue in Execute Immediate

    Quote Originally Posted by gbrabham View Post
    You might look at the concatenation of quotes since your result is
    a character string .... ie, ... NOTE here ... You have to get CARRIED away with the quotes !!!


    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM EMT_LOG WHERE id =' ||''''||result||''''||' or id = '||''''||result2||''''

    HTH and hang in there ... Remember .. It could always be worst ... It could be Monday !!!! ..... um .... Nevermind ...

    Gregg

    Hello Gregg

    I have this concatination issue in my SP, I have tried this logic of your in my SP but didnt work

    I have a in parameter(sWhereClause) in my proc having value ID = '1'
    And I am using this parameter(sWhereClause) in REGEXP_SUBSTR to extract numeric value out of this parameter and execute immediate as follows;

    sQry := 'select regexp_substr('||sWhereClause||',''[-0-9-]+'') from dual';
    Execute Immediate sQry Into sID;

    but when I execute 'Execute Immediate' the string value of parameter is not replaced properly and throws error of "Missing right parenthesis"

    Any help would be appreciated.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >sQry := 'select regexp_substr('||sWhereClause||',''[-0-9-]+'') from dual';
    >Execute Immediate sQry Into sID;
    not as above but do as below
    sQry := 'select regexp_substr('||sWhereClause||',''[-0-9-]+'') from dual';
    DBMS_OUTPUT.PUT_LINE(sQry);

    then post results back here
    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.

Posting Permissions

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