-
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!
-
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
-
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
-
Does this work with varchar2?
v_sqlstmt := v_sqlstmt + i_WhereClause;
(is it new in some version?)
-
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;
-
I think you'll want some spaces in that concatenation (which is why I was acting dumb about +).
-
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|