Dynamic SQL Query

From: Kevin <kjk_at_hrb.com>
Date: Tue, 21 Jul 1998 09:09:20 -0400
Message-ID: <35B49300.2BA5@hrb.com>

I am trying to create a query on the fly and for some reason I can't get the thing to work. Basically what I have done is created a string that contains exactly what I want my SELECT statement to be. This was done by concatinating conditions into a WHERE clause. Is there some way that I can execute this string? I have tried to use DBMS_SQL to do this and all it does is crash. Such as:

v_CursorID := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(v_CursorID, sql_string, DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_last_name);
DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_emp_no,4);v_Dummy:=
DBMS_SQL.EXECUTE(v_CursorID);

loop
if DBMS_SQL.FETCH_ROWS(v_CursorID)=0 then   EXIT;
end if;
DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_emp_no);

(print values)

end loop;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);

Not sure if there is another better way to do this. Any suggestions?

Thanks in advance...
Kevin Received on Tue Jul 21 1998 - 08:09:20 CDT