Board index Oracle

Dynamatic SQL - Where clause

Dynamatic SQL - Where clause

Post by sui.. » Sun, 17 Jan 1999 04:00:00



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,

 
 
 
Top

Dynamatic SQL - Where clause

Post by Ron8 » Wed, 20 Jan 1999 04:00:00


Simply replace your assignment of || p_column_name || .... with a single
variable that you set prior to the assignment on v_statement:

v_whereclause VARCHAR2(2000);
.
.
.
BEGIN

  v_whereclause := p_column_name || '=' || p_column_value

  IF p_column_name2 IS NOT NULL THEN
    v_whereclause := v_whereclause||' AND ' || p_column_name2 .......

HTH

-- Ronald K. Olcott, President
-- Interlude Enterprises
    Innovation and Excellence in Database Applications

 
 
 
Top

1. SQL Join clause to emulate an SQL (where not in) clause

I am wondering how (if it is Possible) to do something like the following query, but doing it using a query and not a (Where not In clause) ..

Select * from SomeLookUpTable where id not in (Select ID from SomeTable)

I am wondering if the same results can be done using a join.

Something like
----------------------------------------------------------------------
Select * from SomeLookupTable lutbl

Left Join

SomeTable stbl

ON

stbl.ID <> lutbl.ID
----------------------------------------------------------------------

obviously this join syntax wont give the required results, rather it will return many records (one record for each record in first table for each record in table 2 except if the ID's match it wont return a record)

is there a way to join it that says

Give me all records in table1 where a matching record is not found in table 2 / but w/o using the first sql statement type with the "not in" clause ..

Remember I want to do this in a join.  I have a vague memory of doing something like this before but can't remember if I am thinking of something else or just cant remember the syntax..

2. Monitor DB Updates with VB App

3. SQL Optimisation , FROM Clause table order, Where clause etc

4. simple select statement

5. Using case stement in a where clause with an in clause

6. FORCE xbase compiler status

7. Conditional WHERE clause (IF/CASE within WHERE clause)

8. What is the fastest ADO record counting method?

9. JOIN condition in FROM clause or WHERE clause?

10. results from SELECT clause used in an IN clause

11. Conditional WHERE clause and ORDER BY clause

12. CASE CLAUSE in WHERE CLAUSE HELP!!!!!

13. Is it possible to use a SELECT clause in a FROM clause


All times are UTC
Board index