The SUBSTR function is used to extract characters from a word or sentence.
For Example, I have run into many situations were I need to apply different programming logic based on characters in a string.
Suppose I have an employee table with an employee_id and employee_name (First, Last, middle) and I want to store the middle name in my database as middle initial only. So I only need the first character of the middle name.
I can simply do this by using a SUBSTR on the middle name field.
I find the MINUS operator very handy and use it a lot to compare tables and find out missing things.
For example suppose you want to compare Table1 with Table2.
If the two tables have the same layouts (same column names and data content), you can simply do this
This query will compare each record in Table 1 to a record in table 2.
The result returned will be records in table 1 that are not in table 2.
you can reverse the MINUS order to get records in table 2 that are not in table 1
Typically in a database you have many users.
The owner shcema that owns the objects, usually decides the privileges given to other users on the objects it owns.
Lets call the schema owner "OWNER" and suppose we have another user in the database called "READER".
For the user "READER" to be able to view the data in a table owned by "OWNER", the owner has to grant the reader select priviliges.
In many cases you might need to compare data between tables across different databases. For example your test instance and production instance.
You can easily create a database link between the 2 instances and be able to access the production data from the test database.
If you want to see how much space your tables are taking in the database, log in to the shcema and execute this SQL.
The Above query tells you the table name, the table space it is residing in and the size in MB.
If you have a table partitoned on a certain column and on one of your loads you try to update that column on already existing records. You might run into this oracle error
The above error was caused because when you updated the record, it now belongs to a different partition but since row movement between partitions is not enabled on the table, you got that error.
|Peoplesoft Web PIA installation||PeopleSoft Technical||03/15/2017 - 2:20am|
|Populate Multiple Grids on a same Page with diff output based on conditions.||PeopleSoft Technical||03/04/2017 - 1:35pm|
|Updating a row using Peoplecode - Component Interface.||PeopleSoft Technical||02/21/2017 - 11:26pm|
|To load CSV file data into staging table without using file layout||PeopleSoft Technical||01/15/2017 - 8:31am|