In article <36a0301e.0_at_ecn.ab.ca>,
suisum_at_ecn.ab.ca () wrote:
> 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 ||
> ' where ' || p_column_name || ' = :v_value; ' ||
> ' end;';
>
> v_theCursor := DBMS_SQL.OPEN_CURSOR;
> 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);
>
> 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,
>
Unfortunately, you have to use bind. If you have a variable in your 'WHERE'
clause, the only way of passing a value to that variable is to bind that
variable. No other way.
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sat Jan 16 1999 - 13:24:44 CST
