problem with select..into inside a proc

[elaine3839]

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!

[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

[elaine3839]

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

[DaPi]

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

[slimdave]

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 ...

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;

[DaPi]

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

[elaine3839]

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 +).

[JDF]

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;