Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Dynamatic SQL - Where clause
Can anyone show me a good way to use dynamatic SQL if I have more than 1
condition in the WHERE clause, please?
How can I make it more generic to handle the WHERE clause, I don't want to use DBMS_SQL.BIND_VARIABLE().
CREATE OR REPLACE PROCEDURE do_delete(
p_table_name IN VARCHAR2, p_column_name IN VARCHAR2, p_column_value IN VARCHAR2) IS v_theCursor INTEGER; v_statement VARCHAR2(8192); v_status INTEGER;
BEGIN
v_statement := 'begin delete from ' || p_table_name ||DBMS_SQL.PARSE (v_theCursor, v_statement, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE (v_theCursor, ':v_value', p_column_value); v_status := DBMS_SQL.EXECUTE (v_theCursor); DBMS_SQL.CLOSE_CURSOR(v_theCursor);
' where ' || p_column_name || ' = :v_value; ' ||
' end;';
v_theCursor := DBMS_SQL.OPEN_CURSOR;
EXCEPTION
WHEN OTHERS THEN
IF (DBMS_SQL.IS_OPEN (v_theCursor)) THEN
DBMS_SQL.CLOSE_CURSOR(v_theCursor);
END IF;
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
RAISE;
END do_delete;
/
--
Best regards,
Received on Sat Jan 16 1999 - 00:22:22 CST