Hi. In this lesson, we understand how to deal with index by table, which is also known as associative array. It's a kind of array more popularly known as index by table. And also we talk about what is dynamic SQL? So what is the difference between static SQL and dynamic SQL? And when do we need to use dynamic SQL?

Let's get started. Index by table is a collection of homogeneous values, which is generally known as array in other programming languages. In the context of PL SQL, we call it as an associative array. You can also call it as a kind of dictionary. Because it's a set of keys and values. You have to first create a new data type You need to mention that it is a table type.

And you need to mention two things, what is the type of the element you want to have and then what type of index you want to have. Interestingly, the index need not be a number. That is typically the case again in other languages, but here index can be either a number, you can mention it as binary integer or PL SQL int user. Or you can also mention that as a VAR character to that means the index is a string. Let us see an example. Here I'm creating a table type, and I call it as the phone's type.

And it is a table and it is going to have a valid character to have 50. These are the elements of the array and the index of that is going to be this So I don't want index to be a number, it can as well be if you want. But in this case, I want index to be a string. So once you declare a new type called fonts type in this way as a table, which can store strings and index it by strings. Now you need to create a variable of this type, the name of the variable is friends phones. So this is the variable.

In other words, this is the array we want to use. And the type of the array is here. This is the type of the array and you can store a few elements into that array in this way. As you can see, the index is a string and the value is a string. And the value can be anything you want. I mean any string up to 50 characters.

So we are Inserting three elements into this, Eric. And we can have any number of elements, we are not talking about any limit for that. So this is a dynamic array in a way, because we are not talking about the size of the array, then how do we access the elements, of course, by the index, it's very similar to any other language. The only new thing is, your index is not always a number. It can be even a string. So I want to take all the phone numbers of Larry, and this is how you give the index.

And then how do we access all the elements? Because if there are numbers like zero to n, we set a loop and we take all of them. But here indexes are not numbers, indexes or names, the strings. So how do we take them in order to take the first index of this array, we use a method called first first is like a function, and it returns the first index, which is a string. So we declare a variable of type string. And we store that into it.

And if it is not now, because subsequently as you keep moving forward, sooner or later this will be null. So I say if it is not null, then display the details, we want to display the index as well as the values have the element with that index. So display index and the values identified by the index. And then comes the next index. We can't just go for next index on our own. We need to ask array where is the next index?

I mean, what is the next index? The next index is another name, and you get it in this way. So this will give you the next index. But if the next index is not available, then it is going to return null, and then your loop stops. So this is the loop we use to display all the elements of the array. Let us switch to SQL Developer.

So here is my array. And then these are the names and then the phone numbers that I want to store. I want to display the element identified or indexed by Larry. And then I would like to get the first index and take all the elements from there till the end. So, if I go and run this program, it's going to display something like this. So you get each and every index followed by the values.

And interestingly, you can move in the opposite direction, you can start from the last element if you want. And you can then say, give me the prior index, not the next index. So we are going from last element, the first element. So let's clear this and run it again. And this time, you will be getting elements coming in the reverse order. So Scott is coming first, and then JSON.

So like this, it's possible to move in any direction you like. There are a few other functions other than what we use, like first, last, next and prior, but that's beyond the scope of this course. But I just want you to know what is an array in PL SQL and how we can make use of it. So it's called index by table or simply associate to a or you can even call it a kind of dictionary. Then generally we execute SQL statements by giving the complete SQL command at the time of writing the program. For example, we give complete select command or complete update, insert, whatever.

But there are occasions where you don't know exactly what is the command you want to execute. For example, you want to execute a select command, but you don't know what table to use. It might be Table A, table B, and you don't know until you run the program. So dynamically based on some condition, during the execution of the program, you need to decide whether to use Table A or table be my B if it is morning you want to use table E. If it is evening, you want to use table B so Table A in the morning Table B in the evening that kind of scenario. So depending on the time of your system, you need to use it or it can be even more complex. So whenever you want to execute a SQL command by constructing the command at runtime, we need to use what is called as dynamic SQL.

And there are two use cases for it. One, you don't know what is the command to be executed. As I mentioned, you might not know the table name or you might not even know which columns you want to access. And the other use case is you have to execute a command in PL SQL block, but that command is not allowed in PL SQL. If you recollect, in PL SQL only select command and DML commands are allowed. You are not allowed to execute a DD And dcl commands.

But what if I want to execute a CREATE TABLE command or a drop table because these are all DDL commands, they are not allowed by default. In such cases, again, we resort to dynamic SQL. You can do it in two ways, you can do it either by using a DBMS SQL package, or you can do that using some commands provided by PL SQL. And those commands enable you to ambered these dynamic SQL commands within PL SQL block. Then we call it as native dynamic SQL, which was actually a new feature introduced in in Oracle, a nine and I mean nine I okay prior to that we had to use only something like a DBMS underscore SQL, but it is more convenient to use native dynamic SQL. So let us see how we do that.

So here is a small PL SQL block executing two SQL commands dynamically execute immediate is the command we need to use. And you need to give a SQL command as a string and this will be executed at runtime. And the first case is dropped table and you can't directly execute drop table because it's a DDL command. DDL commands are not supported by PL SQL. But through dynamic SQL, I can overcome that limitation. In the second case, I know I want to delete but I don't know exactly which table because the table name is made off this word followed by the column Got an ear.

If current year is 2019, I'm taking the table salaries underscore 2019. But if the same program is run in 2020, it should be salaries underscore 2020. So I want the command to change depending on the current year. In such cases, also we use dynamic SQL. So execute immediate is the command we use to execute any SQL command, and you got to give SQL command as the string. Of course, we use concatenation to put pieces together.

But concatenation can be a little tricky business at times. So you have to be careful that you concatenate pieces correctly. So this is an example and furthermore, it is possible for you to also use parents meters in dynamic SQL. Now, this is where I want to delete the rows from salaries underscore the current year, but I want to delete only a few rows where salary is less than whatever is given there. So this is where I gave what is called as parameter. Colon followed by name is called parameter.

And also when you're concatenating Be very careful, you have to give extra spaces. Otherwise, your table name and this WHERE clause will be placed very ad just gently making it an invalid statement. For example, if you say something like salaries underscore 2019, and then if you don't give this space, you will get the next one concatenated like this So this whole thing will be treated as the table name. And that is going to be an invalid statement. Because there is no such statement, there is no such table. So we need to get rid of that error by adding a space here.

It's okay if you give more spaces, but it's not okay, if you don't give space there. So not giving a space here will throw error. Be careful about that. So this is where I have a command. And I need to replace this with a value. And that's exactly what this using is doing.

It allows you to provide parameters to the SQL command. So here I'm saying Execute the command that is in cmd. That's a variable and I want to pass this parameter two days. So whatever is passed To this procedure that is pass it two days as the parameter that means, if I call this procedure with a value of 5000 now the 5000 will be replacing this parameter. So, salary will be replaced by 5000 this parameter is replaced by 5000. So, this is just an another possibility with dynamic SQL, I mean dynamic SQL with using class, then, what do you do if you want to retrieve the data?

Because dynamic SQL for DML commands and DDL commands is different from dynamic SQL for queries because queries retrieve the data. But how do we take the data? Well, there are two different scenarios. If the query is retrieving only one row from the table, then it's simple. And we are seeing a scenario like that. I want to execute a query and I know it is going to retrieve one row and one column.

So it is going to get me just one row and one column. That is the count. So I want to copy this into the variable called C and t. So when you're guaranteed one row, you can have multiple columns if you like. But what is restricted here is the number of rows you can retrieve. Only one row is retrievable here, and if you try to retrieve more than one row, you know, we can't copy multiple rows into one variable that will fail. So the first scenario is a query with only one row.

That means single row query and we need to use along with execute immediate the into class and give variables so that the result is copied into one or more variables given here. Of course, Optionally, you can also use parameters and replace parameters with values. But what if you want to execute a multi row query? What if you want to retrieve a query that is retrieving multiple rows, then you need to do it in this way. We need to bring a few new things to start with, you need to create a cursor variable and use open statement with slightly different syntax. And you need to create a cursor which is called as a weakly typed cursor means generally the cursor knows what exactly is the query.

It is associated with you know what columns are there in that query. But in this context, the cursor is a very flexible and we call that as weakly typed cursor. An example would make the whole process much simpler. Look at this starting with a new data type, this is how we create a new type and the new type is called as EMP cursor type. And we say it is of type reference cursor. This is what we need to create.

Now, this is what we mean by a weak cursor or a weakly typed cursor. Now create a variable of that type. This cursor, this variable which is of cursor is now capable of storing rows that are retrieved by a query. So here is my open AI Open the cursor and I retrieve rows from this query, and you can give any query you like. And whatever rows the query is retrieving, all the rows are copied into this cursor. This is what we mean by a weekly type of cursor.

So we did discuss about cursor handling in the past. There, the cursor was associated with a specific select command with columns and all. But this cursor is very dynamic. Any retrieved rows will be copied into this. So whatever rows this query is retrieving all of them are copied into this cursor, and then onwards, it's almost the same old process. You start fetching row by row until you reach end of the cursor, and then you stop.

But you need to know how many columns you have. Actually retrieving. If you do not know how many columns you're retrieving, then this is not a possibility. Of course, there are more advanced or dynamic SQL methods, which we do not discuss in this course. But if you're curious to know more about what else can be done, and what are the other possibilities with the dynamic SQL, I strongly suggest you read Oracle documentation, you get a lot more details. But this is where we can have multiple rows.

But we can have only fixed a number of columns. So we need to know how many columns are there. So retrieve one row, copy, whatever you're retrieving from that row into this variable, and then stop when you reach end of the cursor. Otherwise, just print it. Now this is a procedure which takes a condition. And whatever condition you gave that will be added to WHERE clause.

Again, do not forget to give a space. Otherwise you will not be getting required space between where and the condition. So, let's execute this. And this is where I created this processor. So let's just recreate the process is exactly same as what we have seen, and procedure is created successfully. Now I can call this procedure in different ways.

So this is where I call the procedure with the condition saying salary greater than 10 by B, I can change it to 15. And let's just go step by step. Let's comment out the rest of the things I'm not interested. So let's go and execute only the first part of the block and you will see employees who are drawing models 15,000 of course, the headings should be changed accordingly. So this is where I get all the employees who are having salary more than 15,000. Now the same thing I can use, I mean display employees.

But this time let's comment out these two because I'm not interested. Now I want to display all it employs it programmers so I can use this. So let us run the same thing and this time it is going to take this as the condition and one very important thing you got to give double single chords to have one single chord. Otherwise it will be treated as end of the string. So when you're planning to pass one chord as parameter, you need to have two single chords and that will make it as one single chord. So So let's go and run this and see what it does.

Let's clear this and run that. And this will give you all it programmers, you can give any complex condition as long as condition is valid, you can pass anything you like. So, the next example is where I want to pass condition like this manager ID is null and salary should be greater than 10,000. So, if you go and run this, you will get employees who made that criteria, that means their manager ID is not null. So there is a manager, they have a manager and their salary is greater than 10,000. But you got to be careful because this can go wrong.

What if I make a small mistake like this, the condition is no longer valid, so your procedure is going to fail. And if you want to showed that in such cases, it is not going to throw any errors, then you need to enhance your procedure with exception handling. So, you have to say if there is any error in this program either in open or in fetch anywhere you want to go to the exception handler and do nothing. So, you can just enhance it with the exception handler. And you can say when others then and you do nothing, okay or you can even erase an error or display error message, but I just want to change my procedure like this, go ahead and run and the procedure is created. Now, go and run this and this time when you try to execute it is not throwing any error.

So, you have to see whether you want the errors to be suppressed like this, are you want to take some action for those errors So this is how we can create a procedure that is taking a condition and then executing the condition with the dynamic SQL. Interestingly, you can enhance it and event take the table name also as another parameter, then you will put the table name in place of employees using, again, dynamic SQL. So it's a powerful concept, but it's not needed in every case. So there are only a few occasions in which you need to go for it. And also you have to remember it is not the fastest of all. So dynamic SQL is generally slower than static SQL.

So unless you really want dynamic SQL always prefer static SQL. So this is relatively an advanced concept. So in this lesson, we talked about one of the collections In PL SQL called as index by table, we understood what is dynamic SQL and how and when we use it. and execute immediate is the command in PL SQL to enable you to execute any statement dynamically constructed. And we have using option to pass parameters. And when it comes to retrieving the data, we have two scenarios, single row query, multi row query and we have seen how multi row query with weakly typed cursors can be used in this context.

So that's about dynamic SQL. That's about how a collection called index by table can be used. So thanks for your time, keep learning. keep growing.