[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post

In this article, we will see how to use the RANK analytical function in an Oracle database and how it helps us in an analysis.
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

In this article, we will see how to use Order by Clause in Oracle with simple examples.
0
An introductory discussion about how to use Partition by Clause in Oracle.
0
ORA-12547: TNS:lost contact, "sqlplus / as sysdba"
1

Expert Comment

by:vishal srivastava
Comment Utility
Hi Suri ,

Great Read ! The representation and explanation is very helpful  . Thanks for the blog .
Keep it up !!  :)

- Vishal.
0
This is about the change in the value setting for the rp_filter parameter in Linux.
0
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
0
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
0
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
0
Data center
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
1
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
2
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
0
SQL Scripts
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
2
SQL Command Tool
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.
0
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
2
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Comment Utility
Good Article Voted Yes!!
0
LVL 17

Author Comment

by:Swadhin Ray
Comment Utility
thanks Yashwant for your vote.

regards,
Sloba
0
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
1

Expert Comment

by:alsatham hussain
Comment Utility
i can easily understand.

Thanks
0
Meta characters are the characters in a programming language that are interpreted by the programming language to have specific meanings within the language itself.
0
A short introduction on Oracle Apex: 
Oracle Application Express is mainly used to develop Web based applications with SQL and PL/SQL. The Web-based applications can be developed using a web browser which can easily be deployed for any desktop or mobile applications. There are various version that are been released by Oracle. In recent release on May 2015 they released APEX version 5. Oracle recommends that Oracle APEX to be used on latest web browsers available to have a good experience.

Below are the few listed browsers that oracle suggests:
  • Mozilla Firefox 35
  • Google Chrome 40
  • Apple Safari 7
  • Microsoft Internet Explorer 9
 
We can also use the earlier versions of the web browsers by using the legacy themes.

To access full documentation on Oracle APEX, you can open the link from by clicking on the link here

In this article we will see how we can upgrade the existing APEX version to Oracle Application Express Release 5.0.1 version. You can also see my other articles on upgrading APEX version from version 3 to 4 on 11g.
 
Here my DB version is 11g Release 11.2.0.1.0 where my current APEX version is 4.2.6.00.03:
 
Connected to Oracle Database 11g Release 11.2.0.1.0 
Connected as SYS


SQL> SELECT 

Open in new window

0
The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. http://www.linuxmint.com. Once you open the link you will see a download option at the right side of the page, go to the download page from the menu :

pic-2.pngYou can download 32 bit or 63 bit image and I am using 32 bit as my system is with 32 bit OS . I have downloaded and kept it on my specific directory where I can map to my Virtual Box while installing it.
Now open the VMBox and click on New to create virtual machine and map the ISO image to proceed with installing.  Once you set up all the necessary settings on memory space and disk space you will see the screen like below and then you can click on start button to start the installation.

Follow below steps shown with screen shot to start the virtual machine creation: 
2015-07-05-19-21-31-.jpgCick on New and proceed next :
2015-07-05-19-19-08-Oracle-VM-VirtualBoxGive any name to the virtual machine and select the type and version , click Next:
 
2015-07-05-19-19-26-.jpgSelect the memory size you want the VM to have -  I am using 1GB of RAM i.e. 1024 MB approximately. 

2015-07-05-19-19-46-Oracle-VM-VirtualBox
Click on Create button.
2015-07-05-19-20-52-.jpgClick Next. 

2015-07-05-19-21-00-Oracle-VM-VirtualBoxI am trying all default setting for installing the OS but you can choose as per your need. 
2015-07-05-19-21-18-.jpgHere we can set the size of the virtual hard drive . Once filled click on Create button. 

2015-07-05-19-21-31-.jpg Now set the ISO file which was downloaded from Linux Mint website. 
sloba-000006.jpg
1
Elementary operating system is a very pretty, high speed, lightweight Linux operating system for 32 and 64-bit x86 computers. This OS is built on an Ubuntu core. To install elementary OS we need to download the ISO file from its website i.e. https://elementary.io/
pic-1.pngIf you scroll down on the website you will see like below, to download click on Download button as shown below:

pic-2.png
Now you can choose 32 0r 64 bit ISO file:

pic-3.png
pic-4.pngNow open your virtual machine box to create our new VM.
pic-6.pngClick on New button to start:
pic-7.png
Provide the name of the VM you want to create, I used as “elementaryOS”.  Now click on next button and proceed further. 
pic-8.pngSelect the memory size and click on next.

pic-9.pngClick on create.

pic-10.pngClick next.

pic-11.pngpic-12.pngKeep as default and click next and then choose the directory where you want the VM to be installed. Finally click on create button. 

pic-13.pngNow click on the setting gear icon to map the iso file. 


pic-14.png
Now “Ok” button.  After all the basic settings start the VM to install the OS. 

pic-15.png
pic-1.pngNow click on “Install elementary”.

pic-2.pngClick on continue. 

pic-3.pngClick Install Now and proceed with default selection including location as below. 

pic-4.pngSelect the Keyboard type you want to choose I am going with the default option with US English. 

pic-5.pngpic-6.png
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

With the help of flashback queries we have the option to look back in time to a certain state of a database, for example say we did some operation yesterday at 10 AM and today we want to go back to the same state where we started yesterday. So using Oracle flashback queries we have the ability to go back into time to a certain state of a database.

This can be achieved by using the system change number (SCN) or using a timestamp. We also have the option to select a range of system change number or timestamp values that will help us to go back to the point of time. This option will only help us to see the past stage of the database, not the future.

How this is possible is the first question that we can think about. This is possible as because all the data in databases are stored in data files and the redo log file is more essential here; in other words we are doing a rollback.  

Syntax of flashback queries:
 
SELECT … 
FROM [schema.]table [alias]
[
	  AS OF { SCN | TIMESTAMP } expr
	| VERSIONS BETWEEN { SCN | TIMESTAMP}
		{ expr | MINVALUE } AND { expr | MAXVALUE } 
]
[ WHERE … ] 
[ GROUP BY … ] 
[ ORDER BY … ]

Open in new window

AS OF” will take you to back in time to a previous state of a table against a time or system change number.  For example we want to see the records in a table 10 minutes ago then we can use the “AS OF" condition with the SCN or timestamp with additional conditions, if required, like “where” / ”group by” / “order by” . The “VERSIONS BETWEEN
0
I got a requirement to export the table data into CSV file. The major issue was like the column values from the table needed to be transposed and used as a header on the flat file. The challenge was that the column values were more than 30 characters and can reach up to 4000 characters.
 
I am using the sample table to simulate the issue and to check how we can resolve this problem.
 
Here is my sample table:

pic-4.JPG
Create the sample table:
 
create table MY_SURVEY_RESP
(
SURVEY_REQUEST_NAME VARCHAR2(100),
QUESTION_NAME	VARCHAR2(4000),
RESPONDENT	VARCHAR2(100),
RESPONSE  VARCHAR2(100)
);

Open in new window


To insert some sample data let's execute the below PLSQL block:
 
BEGIN
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Compliance narrative form'
                ,'USER-1'
                ,'');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q2'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
                ,'R1 Evidence List'
                ,'USER-1'
                ,'Registered an evidence');
        INSERT INTO MY_SURVEY_RESP
        VALUES
                ('Q1'
            

Open in new window

0
LVL 74

Expert Comment

by:sdstuber
Comment Utility
For reader reference:

Here's the question referenced above where slobaray asked how to do it.
http://www.experts-exchange.com/Database/Oracle/Q_28680635.html
0
LVL 17

Author Comment

by:Swadhin Ray
Comment Utility
Thanks @ sdstuber : I provided the link to the question on the article including the information.

Regards,
Sloba
0

Introduction: When a new description on subject like subqueries is read, with various types and each with unique features, often readers try to make a mental picture of the process. Such a mental picture as a point of reference becomes very helpful when one attempts to apply the techniques to solve a problem. This is why the old saying "One Picture is Worth a Thousand Words" holds true. 


I have prepared the content in this article with visual presentation followed by descriptions and samples to make the path much easier for those interested in having a good understanding of the world of subqueries and retention of techniques to apply them later. Your comment below and/or indicating if this article was helpful is very much appreciated and could be used to improve my future work.


You may download Northwind.bak to restore in your local SQL Server from https://northwinddatabase.codeplex.com to practice or test most of the examples included in this article.  Also, consider printing figure 1 below to keep it handy for reference as you are reading the article.


0x.pngIn T-SQL we can use the results returned from one query in another. The embedded or referencedsubquery (inner query) returns required data to enable the outer query to perform more complex operations compared to a simple select clause. Below is the list of different subqueries discussed later in detail:


1) Self-contained subqueries (embedded).

2) Correlated subqueries (embedded).

3) Derived Table Expressions (embedded).

4) Common Table Expression, CTE (referenced).

5) Views and Inline Table-Valued Functions (referenced).


To reference a Table Expression (or an encapsulated named query ) that is executed prior to its use at referenced points in an outer query and/ or in an intermediate table expression. The major advantage of using a named table expression is to facilitate recursive operation and to be referenced more than once in a query operation.


 

Figure01.png Figure 1: Subqueries at a glance.



* Inline table-valued FUNCTIONS return a table data type, the other types of functions not related to the discussion here are scalar FUNCTIONS and Built-in functions. Scalar functions return a single data value and Built-in functions come with SQL Server to facilitate various operations.

01x.pngA self-contained subquery embedded in an outer query is a stand-alone query with no dependencies to its outer query. You can highlight such subquery to test run it independently for QC purpose.

02x.pngA scalar subquery, by definition, returns a single value to be used in its outer query either as predicate in the outer query's WHERE clause or included directly in its outer query's SELECT clause as an aliased column. 


-- Problem statement: Show product names for ContactName ='Peter Wilson'.
                                        
                                        SELECT ProductID
                                             , ProductName 
                                        FROM dbo.Products
                                        WHERE SupplierID = (SELECT SupplierID                -- scalar subquery in WHERE clause, example 1.1
                                                            FROM dbo.Suppliers
                                        	            Where ContactName ='Peter Wilson');
                                        
                                        -- Problem statement: Display all products with first contact name from Suppliers table. Not very
                                        -- practicle, but it shows how to include a self-containe independent subquery in a SELECT clause.
                                        
                                        SELECT ProductID
                                             , ProductName
                                             , (SELECT Top(1) ContactName                    -- scalar subquery in SELECT clause, example 1.2
                                                FROM dbo.Suppliers) AS FirstContactName 
                                        FROM dbo.Products;


If a self-contained scalar subquery returns an empty set, its outer query sees it as a null mark. And, if such a subquery by mistake coded returning multiple values will produce the following error upon running the entire query (Outer + Subquery): 


Msg 512, Level 16, State 1, Line 23

Subquery returned more than 1 value...


Note: The new and acceptable terminolgy is null or null mark. The term null value, often used, is not correct because by definition null is neither a value nor can have a value.

03x.pngThe multiple value self-contained query is used where a multi-valued result is expected, for example when using the IN predicate. As an example, the following query uses a multi-valued subquery to return products supplied by suppliers from UK. The inner query returns supplierid for suppliers from UK. You can use ... WHERE supplierid NOT IN (SELECT supplierid... 0 to return all others but UK if required.


-- Problem statement: Show product names not associated with ContactName ='Peter Wilson'.
                                        
                                        SELECT ProductID
                                             , ProductName
                                        FROM dbo.Products 
                                        WHERE SupplierID IN (SELECT SupplierID               -- Multi-value subquery in WHERE clause, example 1.3
                                                             FROM dbo.Suppliers
                                                             WHERE CompanyName <> 'Peter Wilson') 
                                        
                                        -- There is no practical application for multi-value subquery in SELECT clause.

04x.pngCorrelated subqueries, unlike self-contained subqueries discussed above, this subquery references a column in the outer query. Thus we cannot select a correlated subquery to test run it. Both outer and inner query must run at once.

05x.pngSame table correlation: Both outer and inner query refer to different instances of the same table.


-- Problem statement:  Write a query to return products with the maximum unit price in each category.
                                        
                                        SELECT ProductName
                                             , UnitPrice
                                        	 , CategoryID
                                        FROM dbo.Products AS t1                              -- Same table correlation subquery, example 2.1
                                        WHERE unitprice =
                                        (SELECT MAX(unitprice)
                                        FROM dbo.Products AS t2
                                        WHERE t2.categoryid = t1.categoryid);

To produce the expected results, the same table (dbo.Products) is aliased as t1 and t2 to produce two instance of the same table to be used independent of each other in the outer and inner query .

06x.pngDifferent table correlation: The outer query (Customers) and the inner query (Orders) refer to instances of two different tables.


-- Problem statement: Write a query to return CompanyName for OrderDate '1997-01-16'.
                                        
                                        SELECT CompanyName                                   
                                        FROM dbo.Customers AS C                              -- Different tables correlation subquery, example 2.2
                                        WHERE EXISTS    -- exist here returns true or false 
                                        (SELECT * FROM dbo.Orders AS O
                                         WHERE O.CustomerID = C.CustomerID 
                                               AND O.OrderDate = '1997-01-16');
                                        
                                        -- ... WHERE NOT EXISTS(SELECT * FROM …    <-- use this when required

08x.pngTable Expressions are named queries. You a) write an inner query that returns a relational result set, b) name it, and c) query it from an outer query. T-SQL supports four forms of table expressions:

a) Derived tables and b) Common table expressions (CTEs) are created and used in code (non-db object).

c) Views and d) Inline table-valued functions are created in code or in SSMS stored as db objects.

09x.pngNon-database-objects are created and used in code, visible only in the scope of the statement that defines them. When out of scope, the table expression is gone and is not usable. See the first example under section 3 below.

10x.pngThe derived table is a subquery in a FROM clause. The FROM clause must have an aliased name. It is called a derived table because the fields in the outer SELECT clause query come from this named derived table. There are Inline and External derived table subqueries:

11x.pngIn an inline single derived table, it is mandatory to give an alias to the table in the FROM clause.

In an external single derived table, it is mandatory to list the field name inside () after the FROM table alias name. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 3.1
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
                                        ProdType  ProdType
                                        B	      x
                                        B	      xx
                                        D	      x
                                        D	      xx
                                        
                                        Select Product, ProdType
                                        from (
                                          select *, count(*) over (partition by Product) cnt from #t
                                        ) d where cnt > 1;
                                        
                                        -- Note: This example will be used later in discussing CTEs.

07ax.pngThe self imposed limitation with derived tables is that we don't use multiple table expressions in a FROM clause whereas with CTEs discussed later we can. Of course, technically speaking, we could nest multiple derived tables as shown below but nesting them in this manner complicates the coding and makes it prone to syntax errors thus rarely used. 


-- Multiple derived tables syntax 
                                        -- (for reasons stated this is possible but not used):
                                        
                                        SELECT f1, f2, ...                       
                                        FROM (SELECT ...  
                                              FROM (SELECT ...  
                                                    FROM Ta 
                                                    WHERE ...) AS Tb
                                              WHERE ...) AS Tc
                                        WHERE ...) As Td (f1, f2, ...);

12x.pngCTE (common table expression):

CTE is like derived table expressions. It's a named table expression that is visible only to the statement that follows it. Like a query against a derived table, CTE involves three main parts:

a) Name the inner query,

b) Name the columns of the inner query (right outside the FROM clause),

c) Name the outer query.


As opposed to derived tables, CTEs are not nested. CTE_1 and CTE_2 etc. are separated by a comma. Now, CTE_2 can reference CTE_1 because it leads VTE_2. And the outer query can reference both CTE_1 and CTE_2.

;WITH <CTE_a>                    -- Single CTE syntax
                                        AS
                                        (
                                        <inner query>
                                        )
                                        <outer query>
                                        ;                 -- Semicolon at the end terminates CTE_a operation.                 
                                        
                                        ;WITH CTE_1 AS (                 -- Multiple CTEs syntax
                                        SELECT ...
                                        FROM Table1),     -- Comma here allows a second CTE start next.
                                        CTE_2 AS (
                                        SELECT ...
                                        FROM CTE_1)
                                        <outer query with references to CTE_1 and/or CTE_2>
                                        ;                 -- Semicolon at the end terminates CTEs operation. 

13x.pngRecall the example 3.1 (from inline single derived table section) above where it was returning Products with two maximum UnitPrice in each category? Here we essentially producing the same result but using with a CTE. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 4.1
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ProdType =x):
                                           (we can do this easily with a simple WHERE clause but the following is to demo CTE use) 
                                        ProdType  ProdType
                                        B	      xx
                                        D	      xx
                                        
                                        ;With d
                                        as
                                        (
                                        Select row_number()  over(partition by Product order by ProdType) As rn
                                        , Product
                                        , ProdType
                                        From #t)
                                        Select d.Product, d.ProdType
                                        From d
                                        Where d.rn =2;

14x.pngTo have multiple CTEs, end the first with a comma and start the next CET. There is no need to repeat another WITH keyword because the first one will be is not terminated yet. For T-SQL to build temp table #t see http://www.experts-exchange.com/questions/28688390/How-to-query-using-derived-table-and-multi-CTEs.html


Product  ProdType                                    -- Inline single derived table subquery, example 4.2
                                        A	      x
                                        B	      x
                                        B	      xx
                                        C	      x
                                        D	      x
                                        D	      xx
                                        E	      x 
                                        -- Problem statement: Starting with above data, produce the following (ignore single Prod. Types):
                                        ProdType  ProdType
                                        B	      x
                                        B	      xx
                                        D	      x
                                        D	      xx
                                        
                                        ;With d
                                        as
                                        (
                                        Select row_number()  over(partition by Product order by ProdType) As rn
                                        , Product
                                        From #t),
                                        e 
                                        as
                                        (
                                        Select #t.Product, ProdType, d.rn 
                                        From #t inner join d on d.Product = #t.Product
                                        Where d.rn >=2)
                                        Select e.Product, e.ProdType
                                        From e;

15x.pngRecursive CTE: “A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.” This description is from https://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx

 

In earlier versions prior to SQL Server 2005, a recursive query use of temp tables, cursors, and additional coding was required. Here, in the example below an anchor subquery (starting subquery which fires only once) is followed by CTE body subquery with a UNION ALL operator before it. It is possible to have multiple UNION ALL + body subquery combination following the first one all separated by a comma. At least one of the body subquery is required to reference the CTE name defined in the anchor query.

 

In this example, employee 7 is returned by the anchor query before executing the body subquery repeatedly joining previous rows.

-- Problem statement: Write a query to return the management chain from EmploeeID = 7. 
                                        
                                        ;WITH BossCTE                                        -- Recursive CTE, example 4.3
                                        AS             
                                        (
                                        SELECT EmployeeID
                                             , ReportsTo
                                             , firstname
                                             , lastname
                                             , 0 AS distance
                                        FROM dbo.Employees 
                                        WHERE EmployeeID=7
                                        UNION ALL
                                        SELECT M.EmployeeID
                                             , M.ReportsTo
                                             , M.firstname
                                             , M.lastname
                                             , S.distance + 1 AS distance
                                        FROM BossCTE AS S JOIN dbo.Employees AS M
                                        ON S.ReportsTo = M.EmployeeID
                                        ) 
                                        SELECT EmployeeID
                                             , ReportsTo
                                             , firstname
                                             , lastname
                                             , distance
                                        FROM BossCTE
                                        Order By distance DESC;

CTEs and derived tables are created and used in code, visible only in the scope of the statement that defines them. After that statement terminates, the table expression is gone thus is not, as we discussed in the previous sections, derived tables and CTEs are table expressions that are visible only in the scope of the statement that defines them. Hence, derived tables and CTEs are not reusable.

What we have discussed so far is for use in the code being executed after which when it runs out of scope, it is of no use. In the following section we will see how they could be saved as database object for reusability. 

19x.pngDatabase-object(Views and Inline Table-Valued Functions): For reusability, you need to store the definition of the table expression as an object in the database, and for this you can use either views or inline table-valued functions. Because these are objects in the database, you can control access by using permissions. The main difference between views and inline table-valued functions is that the former doesn't accept input parameters and the latter does. As an example, suppose you need to persist the definition of the query with the row number computation from the examples in the previous sections. To achieve this, you create the following view.


17x.pngA view is some saved TSQL to create a virtual table based on the saved definition. The input column names used in views must exist in the other tables in the database. The view, saved object, in the database is reusable whenever needed provided the users have necessary permission to use them. The result set return by views are relational*  (see below on use of Order By clause with relational subqueries). Additionally, unlike functions, views do not accept parameters.

 

--Problem statement: Write a view to show products with the same ListPrice in  more than three products:
                                        
                                        create VIEW dbo.ProductsSameListPriceOverTwoCounts
                                        AS                                                   -- View, example 5.1
                                        select ProductID
                                             , ProductName
                                             , UnitPrice
                                        from (
                                          select *, cnt = count(*) over (partition by unitprice)
                                          from dbo.Products
                                        ) d where cnt > 3;
                                         
                                        -- Test:
                                        select * from ProductsSameListPriceOverTwoCounts
                                        --Returns:
                                        ProductID  ProductName                   UnitPrice
                                        
                                        67     Laughing Lumberjack Lager          14.00
                                        42     Singaporean Hokkien Fried Mee      14.00
                                        34     Sasquatch Ale                      14.00
                                        25     NuNuCa Nuß-Nougat-Creme            14.00
                                        1      Chai                               18.00
                                        35     Steeleye Stout                     18.00
                                        39     Chartreuse verte                   18.00
                                        76     Lakkalikööri                       18.00

* The term relation often is mixed up with term relationship which has to do with joining primary keys and foreign keys. However in standard SQL “… a relation, as originally defined by E. F. Codd, is a set of tuples (d1, d2, ..., dn), where each element dj is a member of Dj, a data domain”. SQL Server, Oracle, MySQL etc. are different flavors of the standard SQL These languages use terms rows and columns in place tuples and domains. As part of a relation defined by standard SQL, relations cannot have Order By clause to sort rows. The exception to use Order By applies in a subquery, the use of ORDER BY with a TOP or OFFSET-FETCH to produce certain results not ordering the data it returns. 


 Inline table-valued functions are like views but accept parameters and you may include Order By clause in its definition. Unlike the previous example where constant 3 was hard coded, with inline table-valued function we can include an input parameters like @RepeadedListPriceCoun to enable user to use different values.


-- Problem statement: Write an Inline table-valued function to show products with the same ListPrice 
                                        -- if equal or over RepeadedListPriceCoun provided:
                                        
                                        Create Function dbo.fnProductsSameListPriceOver(
                                        @RepeadedListPriceCount As Int) Returns Table
                                        AS                                                   -- Inline table-valued function , example 5.2
                                        Return
                                        select ProductID
                                             , ProductName
                                             , UnitPrice
                                        From (
                                          Select *, cnt = Count(*) over (Partition Yy unitprice) 
                                          From dbo.Products
                                        ) d Where cnt >= @RepeadedListPriceCount;
                                        
                                        -- Test:
                                        Select * From fnProductsSameListPriceOver(4);
                                        
                                        -- Returns 25 records:
                                        ProductID  ProductName             UnitPrice
                                        47	Zaanse koeken	                9.50
                                        45	Rogede sild	                9.50
                                        21	Sir Rodney's Scones	       10.00
                                        3	Aniseed Syrup	               10.00
                                        74	Longlife Tofu	               10.00
                                        .
                                        .
                                        56	Gnocchi di nonna Alice	       38.00
                                        12	Queso Manchego La Pastora      38.00
                                        27	Schoggi Schokolade	       43.90
                                        63	Vegie-spread	               43.90

 



Summary: The readers of this article fall in three categories of pro, intermediate, or beginner. If you are a pro, any correction or improvement from will be greately appreciated. For intermediate-level professionals, I hope this presentation is helping to form a complete mental picture and is making easier your work with subqueries by recognizing 5 distinct mode of there applications 1) Self-contained, 2) Correlated, 3) Derived table, 4) CTEs, and 5) View & inline table-valued Functions. My recommendation for the beginner SQL enthusiasts is to read the SQL related questions present at EE (answered or not, without going through solution provided by others, if already answered) to come up with your own solution. This is a good mental gym for your SQL brain muscles. In a matter of a few months you will see how easily you are able to suggest a good solution for a questions which presently maybe a bit mind boggling.


Thank you,


Mike Eghtebas, BSME, PE

 





piechartY.pngSkill Highlights:

 

  • Extensive data analysis experience, dynamic reporting and developing automation tools to streamline activities and boost productivity.
  • Expertise designing user friendly interfaces, experienced in T-SQL, ASP.Net, C#, VB.Net, ADO.Net, VBA, Excel Macros, and VBA coding.
  • Others: Git, MySQL, Java, PHP
     






5

Expert Comment

by:Rayne
Comment Utility
Thank you Mike, this really clears a lot of key concepts :)
0
This article started out as an answer to a question asked here on EE on why someone would want to use ETL ("Extract, Transform, Load") tools rather than writing application code or using tools native to the database platforms. My response and this article are written from the perspective of a database guy.

I've seen a few ETL tools like the open source Talend; but the only tool I've actually used in a production environment is Informatica so that tool heavily influences my perception. I never became an expert with Informatica but I got to work with some people that were; and, having survived, I'm glad for the experience.

First a few things that I turned out to LOVE.
 
  1. Self documenting Data Flow. Trying to answer the question "Where did you get this piece of information?" can be difficult (and that's being kind) with scripts, programs, stored procedures and so on. However, tracing through a workflow is easy. Of course, if the processing is complex with lots of data sources and many transformations it may still take awhile to decipher it all; but the flow itself is still essentially just connecting the dots.
  2. Semi-agnostic processing. I absolutely detest platform agnostic development. BUT... tools like Informatica have a nice compromise to them. If you change platforms of your data sources, you must rewrite your source qualifiers, but... after that you're essentially done. So, your inputs can use SQL syntax, stored procedures,
5
LVL 67

Expert Comment

by:Jim Horn
Comment Utility
Excellent article, voted Yes.

A couple of thoughts based on SSIS experience:
Aggregates and sorts are referred to as blocking transformations, where the entire set of data needs to be loaded before a sum/count/sort can be performed, which would decrease a lot of the benefits of running streams of data.
File manipulation is not difficult in SSIS, especially with the third party tool Pragmatic Works' Task Factory, however most large shops I've worked in have a separate FTP/Feed Transport guy/team where that person was in charge of moving/encrypting files.
0
Normally when we create a table we need to specify the datatypes of the columns that are need to be created in Oracle, but here we used to have few limits on Varchar2 , NVarchar , Raw datatypes .... 
For example if I need to store a string having more than 4000 characters in length then I cannot store it on a Varchar2 column , till 11g we used to store in a CLOB  column. If we see realistically I don't think that we really need the clob column here might be we need only 7000 characters space within a column where we can store the data but till 11g we can still utilize it within PLSQL which can handle till 32k. So in short we can say like before Oracle version 12c we were able to use the datatype till 4000 on Varchar2, NVarchar2, Raw Data Types but now in 12c onwards this is been extended till 32767 but internally in 12c this get stores in CLOB column for extended varchar2 columns [This point was suggested by slightwv]. Till 11gR2 we were able to use the 32k datatype on PLSQL but not on table level.  Many time we see that the datatype might not required to be a CLOB but might just need few more characters then 4000. Where we normally use a substring to insert only 4000 characters , which is not not requuired in 12c database by using the extending the string size.
This can be achieved by extending the max_string_size parameter on system level and executing utl32k.sql present on oracle home folder after …
1
LVL 7

Expert Comment

by:Docteur_Z
Comment Utility
Question : Is there any counterpart to activating this feature ?
My idea : why not make it default on all new 12c DBs ?
0
LVL 80

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Question : Is there any counterpart to activating this feature ?

I do not understand what you mean by 'counterpart'.

If you mean any other way to store more than 4000 characters in a varchar2, then no.
If you mean a different way to allow the use, no as well.

The online docs are always a great source of information:
http://docs.oracle.com/database/121/REFRN/refrn10321.htm#REFRN10321

>>My idea : why not make it default on all new 12c DBs ?

CLOBs come with a cost.  The decision to use them must be made by the individual not 'chosen' by Oracle.
0
Web application components that can be published and used on the Internet are called web services. Here we will see how we can use Oracle PLSQL to access web services.

In Oracle we have a package called UTL_HTTP. By using this package we can call web services using a POST method and get a response from it. For this example I am using the  web service available on www.w3schools.com for converting Celsius to Fahrenheit: http://www.w3schools.com/webservices/tempconvert.asmx?op=CelsiusToFahrenheit

We will be sending the POST method using UTL_HTTP package to the above URL for any value as Celsius and then that value gets converted and responds back to use with Fahrenheit value. So the conversion will be happening on the web service.

Let us first check manually using a browser for any values that can be converted. When we open the URL to the service we will see something as shown below:

pic-1.jpgLet's put 10 as input to the parametrer value for Celsius and check what output we are getting:

pic-2.jpgAfter giving the input click on Invoke button to get the response as like below:

pic-3.jpgSo from the above response we can see that 10 Celsius converted as Fahrenheit with a value of 50.
<string xmlns="http://www.w3schols.com/webservices/">50</string>

Open in new window

2

Oracle Database

79K

Solutions

26K

Contributors

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.