Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Dynamatic SQL - Where clause

Dynamatic SQL - Where clause

From: <suisum_at_ecn.ab.ca>
Date: 16 Jan 99 06:22:22 GMT
Message-ID: <36a0301e.0@ecn.ab.ca>


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, Received on Sat Jan 16 1999 - 00:22:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US