Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    42

    Unanswered: Create dynamic Where clause in Oracle SP

    Hi,

    I have a web page where users can search for customers by filling in a form which has the following feilds: -

    Title
    Firstname
    Surname
    Address1
    Address2
    Postcode
    Telephone No

    There is a customer table with the above columns.

    The problem I have is the user can type any combination of the above fields i.e. they might type in the title and address1.

    I need to create a dynamic SQL script such as
    SELECT * FROM CUSTOMER
    WHERE colX = varX

    Can anyone suggest a solution please?

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Create dynamic Where clause in Oracle SP

    Example:

    DECLARE
    A NUMBER;
    CURSOR C1 IS
    SELECT TABLE_NAME, COLUMN_NAME
    FROM USER_TAB_COLUMNS
    WHERE NULLABLE='N' AND TABLE_NAME LIKE 'P_%';

    BEGIN
    FOR i IN C1
    LOOP
    execute immediate
    'SELECT COUNT(*) INTO A FROM '||i.TABLE_NAME||
    ' WHERE '||i.COLUMN_NAME||' IS NOT NULL';
    END LOOP;
    END;
    Joel Pérez

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Create dynamic Where clause in Oracle SP

    EXECUTE IMMEDIATE Statement
    The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or anonymous PL/SQL block. For more information, see Chapter 11.

    http://download-west.oracle.com/docs...ms18.htm#33889

    Native Dynamic SQL
    A happy and gracious flexibility ... --Matthew Arnold

    This chapter shows you how to use native dynamic SQL (dynamic SQL for short), a PL/SQL interface that makes your applications more flexible and versatile. You learn simple ways to write programs that can build and process SQL statements "on the fly" at run time.

    Within PL/SQL, you can execute any kind of SQL statement (even data definition and data control statements) without resorting to cumbersome programmatic approaches. Dynamic SQL blends seamlessly into your programs, making them more efficient, readable, and concise.

    This chapter discusses the following topics:

    What Is Dynamic SQL?
    The Need for Dynamic SQL
    Using the EXECUTE IMMEDIATE Statement
    Using the OPEN-FOR, FETCH, and CLOSE Statements
    Tips and Traps for Dynamic SQL

    http://download-west.oracle.com/docs...dynam.htm#4376
    Joel Pérez

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •