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

    Unanswered: stored procedures dynamic where clause

    Hi,

    can anyone send some examples of stored procedures having the dynamic where clause and executed in Oracle SQL.

    how to check the functionaluty of dynamic where clause

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

    Re: stored procedures dynamic where clause

    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;


    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/doc...ems18.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/doc..._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
  •