Results 1 to 6 of 6

Thread: Dynamic SQL in Oracle database

  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Dynamic SQL in Oracle database

    Hello guy !

    I need to know about the dynamic SQL.What about this concept and what is the use. I need to know the implementation of dunamic SQL with their respective examples and all.Is it being used with application or any data management purpose.Please tell me the as soon as possible

    Thanks.

  2. #2
    Join Date
    Apr 2008
    Posts
    1,948

    Dynamic SQL in Oracle database

    Dynamic SQL :

    Dynamic SQL enables you to code which is referenced to the SQL statement whose full text is not known until run time.The Static SQL statements not always change from execution to execution.There are some limitations with static SQL that's why dynamic SQL comes to overcome this.

    Dynamic SQL programs makes enable to handle updation and changes in data definitions without recompiling.This facility makes dynamic SQL much more reliable than static SQL.

  3. #3
    Join Date
    May 2008
    Posts
    2,012

    Causes to use Dynamic SQL in Oracle database

    Causes to use Dynamic SQL:

    There are so many thing arises to use the dynamic SQL when the static SQL can't be performed according to your desired result and In case when you are aware of exact SQL statements that must be executed by a PL/SQL procedure.These are dependent on processing work done by the program.

    The following section shows you some scenario to use the dynamic SQL and solution of typical problems using dynamic SQL.

    1- Executing DDL and SCL Statements in PL/SQL
    2- Executing Dynamic Queries
    3- Referencing Database Objects that Do Not Exist at Compilation
    4- Optimizing Execution Dynamically
    5- Executing Dynamic PL/SQL Blocks
    6- Performing Dynamic Operations Using Invoker-Rights

    And much cases where we use the dynamic SQL.

  4. #4
    Join Date
    Apr 2008
    Posts
    2,005

    Executing DDL and SCL Statements in PL/SQL in Oracle database

    Executing DDL and SCL Statements in PL/SQL :

    In case of the execution of PL/SQL program,you can execute the following types of statements with the dynamic SQL.

    • DDL(Data definition language) such as CREATE,DROP, GRANT and REVOKE
    • SCL (Session control language) such as ALTER SESSION and SET ROLE


    Type statements can be used and one thing more,you can use only TABLE clause with the SELECT statement.

  5. #5
    Join Date
    May 2008
    Posts
    2,297

    Executing Dynamic Queries in Oracle database

    Executing Dynamic Queries :

    You can develop the application using dynamic SQL which will facilitate you to execute the dynamic queries.whose full text is not known at run time.

    There are so many applications uses dynamic queries like :
    1- Choose query search or sorting criteria at runtime.
    2- Allows users to input or select optimizer hints at execution time
    3- Query on a data storage where the data definitions of tables are constantly updating.
    4- Query against a data storage where new tables objects are created randomly.

  6. #6
    Join Date
    Oct 2005
    Posts
    2,393

    Optimizing Execution Dynamically in Oracle database

    Optimizing Execution Dynamically :

    You can apply the usage of dynamic SQL to make the SQL statement in such a way which optimizes the execution by combining the hints into a SQL statement behaving dynamically and it will show you the changes in the hints upon your current statistics without recompilation of database.

    The following procedure uses a variable named My_hint which allow to the users to pass a hint option with the SELECT statement :
    Code:
    CREATE OR REPLACE PROCEDURE employees_query
          (my_hint VARCHAR2) AS
       TYPE cursor_tp IS REF CURSOR;
       c cursor_tp;
    BEGIN
       OPEN c FOR 'SELECT ' || my_hint ||
          ' emp_id, emp_name, salary, job_id  FROM employee WHERE emp_id = 9889';
          END;

Similar Threads

  1. Statpack in oracle database
    By Reegan in forum Networking & Security
    Replies: 5
    Last Post: 16-02-2010, 08:00 PM
  2. Cursor in oracle database
    By Ainsley in forum Software Development
    Replies: 5
    Last Post: 11-02-2010, 06:24 PM
  3. Subqueries in oracle database
    By Landan in forum Software Development
    Replies: 4
    Last Post: 09-02-2010, 05:57 PM
  4. Packages in oracle database
    By Garlands in forum Software Development
    Replies: 3
    Last Post: 30-01-2010, 02:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,635,551.00832 seconds with 17 queries