iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 18

“The person who makes a success of living is the one who see his goal steadily and aims for it unswervingly. That is dedication.” Cecil B. DeMille (1881 - 1959)

 

Read first then play the video:

   SQL-VIDEO -How to use Native Dynamic SQL

  

Using Native Dynamic SQL

 

Hands-On Introduction

Your organization wants you to write a stored procedure to pass the table name and get back the number of records that table contains. The SELECT statement must be created dynamically, since you don’t know what table you are getting statistics from. You should write your function so that your client can display the tables’ name, plus the number of records contained each table.

 

Your assignments are:

1- To create a function called “get_total_recs,”

2- To pass a table name as a parameter,

3- To test and query a list of all user tables with their number of rows in the table.

 

Write a PL/SQL procedure using Native Dynamic SQL

Native Dynamic SQL allows an application to run SQL statements whose contents are not known until runtime. The statement is built up as a string by the application and is then passed to the server. Generally dynamic SQL is slower than static SQL so it should not be used unless absolutely necessary. Make sure to check the syntax, since syntax checking and object validation cannot be done until runtime. The only advantage of dynamic SQL is that it allows you to perform DDL commands and also allows you to access objects that will not exist until runtime.

 

Connect to sqlplus as the iself user.
SQL> CONNECT iself/schooling


Set the pagesize to 55 and the linesize to 100.
SQL> SET PAGESIZE 55 LINESIZE 100

Pass a table name as parameter

Write a stored procedure, to pass the table name as a parameter, and get back the number of records that table contains.
SQL>

CREATE OR REPLACE FUNCTION get_total_recs (loc VARCHAR2)
    RETURN NUMBER

IS


    Query_str VARCHAR2(1000);
    Num_of_recs NUMBER;


BEGIN


    Query_str := 'SELECT COUNT(*) FROM ' || loc;
    EXECUTE IMMEDIATE query_str INTO num_of_recs;
    RETURN num_of_recs;


END;
SQL> /
Notice that the native dynamic SQL was used in the stored procedure.


Test the function

Test your function with a single table.
SQL> SELECT get_total_recs('emp') FROM dual
SQL> /


Test your function with multiple tables.
SQL> SELECT table_name as "Table Name",
                get_total_recs(table_name) as "Number of Records"
                FROM user_tables
SQL> /


Drop the get_total_recs function.
SQL> DROP FUNCTION get_total_recs
SQL> /

 

You see things; and you say, 'Why?' But I dream things that never were; and I say, "Why not?" George Bernard Shaw (1856 - 1950), "Back to Methuselah" (1921), part 1

 

Questions:

Q: What is a Native Dynamic SQL statement?

Q: Write a stored procedure to pass the table name and get back the number of records that table contains. The SELECT statement must be created dynamically, since you don’t know what table you are getting statistics from. You should write your function so that your client can display the tables’ name, plus the number of records contained each table.