problem with select..into inside a proc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: problem with select..into inside a proc

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy problem with select..into inside a proc

    Here is my proc --


    CREATE OR REPLACE PROCEDURE test_proc
    (i_tblname IN varchar2,
    i_whereClause IN varchar2,
    i_orderbyClause IN varchar2,
    i_ID IN integer) AS
    v_result VARCHAR2(100);
    v_sqlstmt VARCHAR2(1000);
    BEGIN
    -- table MyTest(testID integer not null PK, testName varchar2(100))
    v_sqlstmt := 'SELECT testName INTO v_result FROM myTest WHERE testID = i_ID';
    IF i_WhereClause is not null THEN
    v_sqlstmt := v_sqlstmt + i_WhereClause;
    END IF;

    IF i_OrderByClause is not null THEN
    v_sqlstmt := v_sqlstmt + i_orderbyClause;
    END IF;

    EXECUTE IMMEDIATE v_sqlstmt;
    dbms_output.put_line('testName is : ' || v_result);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
    END;
    /


    The procedure will be created successfully, but if I tried to run it, it will keep giving me the following error

    ORA-00905: MISSING KEYWORD

    I know it must be caused by the INTO keyword inside the SELECT statement. But I do need to get the result back. My guess is I would need to use certain package from Oracle (say, DBMS_SQL etc) to solve this, is that right?

    Any hint is greatly appreciated!

  2. #2
    Join Date
    Mar 2001
    Location
    AL
    Posts
    18
    Try changing it to:

    v_sqlstmt := 'SELECT testNameFROM myTest WHERE testID = i_ID';

    and then

    EXECUTE IMMEDIATE v_sqlstmt into v_result;



    The EXECUTE IMMEDIATE statement runs the sql stmt exactly as you would outside of the proc, so it doesn't recognize the INTO keyword.



    JDF

  3. #3
    Join Date
    May 2001
    Posts
    285

    Thumbs up

    Thanks, that worked!!!

    BTW -- I was able to do 'select..into..from..where..' outside the proc, so I'm not quite sure about your last statement. So why it's not being recognized inside the proc?

    Originally posted by JDF
    Try changing it to:

    v_sqlstmt := 'SELECT testNameFROM myTest WHERE testID = i_ID';

    and then

    EXECUTE IMMEDIATE v_sqlstmt into v_result;



    The EXECUTE IMMEDIATE statement runs the sql stmt exactly as you would outside of the proc, so it doesn't recognize the INTO keyword.



    JDF

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142


    Does this work with varchar2?
    v_sqlstmt := v_sqlstmt + i_WhereClause;
    (is it new in some version?)

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by elaine3839
    BTW -- I was able to do 'select..into..from..where..' outside the proc, so I'm not quite sure about your last statement. So why it's not being recognized inside the proc?
    Just the syntax of the language. I guess that within the execute immediate clause the sql engine does not have visibility of the variable.

    By the way, I'd personally prefer dropping the "IF" blocks. They aren't required, and the code will be easier to support and a tiny bit faster ...
    Code:
    BEGIN
       v_sqlstmt := 'SELECT testName FROM myTest WHERE testID = i_ID';
    
       Execute Immediate
          v_sqlstmt||i_WhereClause||i_orderbyClause
          INTO v_result ;
    
       dbms_output.put_line('testName is : ' || v_result);
    END;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I think you'll want some spaces in that concatenation (which is why I was acting dumb about +).

  7. #7
    Join Date
    May 2001
    Posts
    285


    Sorry, the '+' is a typo, it should really be '||'.

    Originally posted by DaPi
    I think you'll want some spaces in that concatenation (which is why I was acting dumb about +).

  8. #8
    Join Date
    Mar 2001
    Location
    AL
    Posts
    18
    The EXECUTE IMMEDIATE statement runs the sql stmt exactly as you would outside of the proc, so it doesn't recognize the INTO keyword.

    Sorry that was first thing this morning, I was still having a hard time communicating....


    I meant that the EXECUTE IMMEDIATE basically runs the statment outside of the PLSQL block. So the "into variable" syntax is not valid.

    You'll get the same error if you log into SQL*Plus and run:
    select sysdate into v_result from dual;

Posting Permissions

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


Click Here to Expand Forum to Full Width