Oracle Database

78K

Solutions

25K

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
This post first appeared at Oracleinaction by Anju Garg (Myself).

I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reasons for duplicating the storage of undo.

First, let’s demonstrate that
  • The Oracle redo contains undo
  • Checkpointing causes
    • Dirty blocks to be written to datafiles
    • Buffers containing undo to be written to undo tablespace
Overview:

Steps
  1. Find out name of the current undo tablespace 
  2. Create an undo tablespace undotbs2
  3. Create a tablespace test_undo
  4. Create a table test_undo_tab in tablespace test_undo
  5. Insert two rows with txt – teststring1, teststring2 in test_undo_tab
  6. Perform manual checkpoint
  7. Switch logs so that new log does not contain string teststring1
  8. Find out name of current redo log
  9. Switch undo tablespace to undotbs2
  10. Update the column value from teststring1 to teststring_uncommitted and do not commit
  11. In another session, update the column value from teststring2 to teststring_committed and commit
  12. Check that both new and old (undo) values have been written to current redo log
  13. Check that undo tablespace does not contain pre update values i.e. teststring1 and teststring2 as undo information is still in buffer cache and checkpoint has not taken place yet
  14. Perform manual checkpoint so that dirty buffers as well as buffers containing undo information are flushed to disk
  15. Check that datafile does contain updated values (both committed and uncommitted)
0
 
LVL 1

Author Comment

by:Anjugarg66
Comment Utility
I have completed the setup for Google authorship.

Kindly do the needful.

Regards
Anju Garg
0
 
LVL 1

Author Comment

by:Anjugarg66
Comment Utility
Hello mlmcc,

    I have made the required change.

Regards
Anju Garg
0
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
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 7

Expert Comment

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

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
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
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 16

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 66

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 77

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
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

This article shows how to upgrade from APEX version 3.2.1.00.10 to 4.2.6.00.03 on Oracle Database 11g Release 11.2.0.1.0.
First of all why we need to upgrade, the default version provided by Oracle doesn't support few of the features which is later version they improved the PDF printing options , Mobile application builder and many more.

The details on the version changes can be retrieved from the below link:
By upgrading the APEX version we are not upgrading the Oracle database, this will remain as it is but all the latest options to build an small application can be done having more features on upgrading the application side. This is not make any significance of performance improvement lastly this supports HTML 5 too and the basic themes are also present, where as we can also configure it. 

By default APEX version 3  comes when we install Oracle database from 10g onwards.  Once we have it installed we need to unlock the "anonymous " user and check if our HTTP port by executing the below SQL :
 
SQL> select dbms_xdb.getHTTPPort from dual;

Open in new window

Now if we see any result then fine else we can set the HTTP port by executing the below code:
 
EXEC dbms_xdb.sethttpport(8082);

Open in new window

Now if we open the URL on our local we will see the below screen for login:

PIC-1.pngInitially the screen looks as like above. To check the version details we can login to our database to verify :

Login as sys user and check:


Open in new window

0
If you are anything like me, everytime you get a new computer or need to do a fresh install of your work computer you immediatly go and re-install SQL Developer on your machine.  You get all your connections setup and you think you are good to go.  Then you try to write your first query. 
 
Select * from my_table where date_column > '2010-05-05'. 

Open in new window


You run the query and you get an error message that the date is in the wrong format.   SQL Developer comes preset with a date format that it wants to use, and it is never the one I want.

Now to fix this you can always use the to_date function provided in pl/sql, but if you are writing a lot of queries that use dates this can become annoying.  It is nice to be able to just plugin what ever date format you always use and have SQL Developer remember this syntax.  The good news is you can do this in SQL Developer.  I always have a hard time finding the exact setting, so here is exactly how you would do it.

In the top menu go to the Tools -> Preferences -> Database -> NLS

SQL-Developer-Date-Step-1.jpg
SQL-Developer-Date-Step-2.jpg
Within the NL set the Date Format, Timestamp Format and the Timestamp TZ Format.  Being from the United States, below are the values I like to use.

Date Format: YYYY-MM-DD HH24:MI:SS
Timestamp Format: YYYY-MM-DD HH24:MI:SSXFF
Timestamp TZ Format: YYYY-MM-DD HH24:MI:SSXFF TZR

Once you hit 'OK', your settings will now be updated.  Now you will be able to write queries with specified dates in the format you wanted to use.  Now you run your query from above again.
 

Open in new window

2
 

Expert Comment

by:santosh shetye
Comment Utility
hey,
     but how to do this using query (manually) in sql developer...
1
 

Expert Comment

by:ajinkya kaspale
Comment Utility
hi,
same doubt like #santoshshetye336
0
I'm working on giving a course in Oracle advanced SQL. One of the topics is the MODEL clause. I needed documentation to explain this clause, so I made an article for this.

This clause allows you to use your data like an array, do calculations on that array and then return the calculated data.  This sounds a lot like working with a spreadsheet.

In this part I I'll be looking at the model clause with a single dimension.
 

Prerequisites: A running database, data and a user.

To get everything out of this, I installed my very first 12c Oracle enterprise database with DBCA. It's a single instance (non-plugged) enterprise edition 12.1.0.1 database on a Windows Server 2008 machine named TESTU12. As data I used the separate samples download from oracle.com that contains shemas hr, pm, sh, oe, ix and bi. I created user TEST with the DBA role and create session granted. (It's for testing so the DBA role is least troublesome). For testing I'm using SQLplus.
 

The model clause syntax

Syntactically the model clause sits behind the group by clause in the query block:
 
query-block.gifThe model clause itself has the MODEL keyword, three optional parts and the main model section:
main-model.gif
The main model section has two mandatory sections: the model column clause and the model rules clause:
main-model.gifThe model columns clauses has this definition:
model-column-clauses.gifand the model rules clause is like so :
model-rules-clause.gif


Minimal select statement with model clause

2
 
LVL 15

Administrative Comment

by:Eric AKA Netminder
Comment Utility
Geert,

Congratulations! Your article has been Accepted and has been awarded Approved status.

ericpete
Page Editor
0
BRIEF HISTORY

From time to time during my journey through the "IT" realm, I have been faced with the necessity to code a program or module to parse some source text and produce an array containing the tokens, operators and delimiters thereof, the ultimate goal being that of performing some type of analysis of the original sentence.

All started during the dinosaur era when I was tasked with coding a program to translate one flavor of “Business Basic” to another flavor of “Business Basic”. And surely there was the need to code a parsing routine to produce a structure that could be analyzed and cross-referenced in order to automatically produce the equivalent statements in the target syntax.

Some years passed by and during a project to design a relational database we needed to create a metadata catalog from the source COBOL-based legacy application’s record definitions, working-storage areas, CICS screens, programs and other related structures . Consequently a parsing module was required.

More years passed by, until recently I needed to analyze arithmetic formulas using the Shunting Yard algorithm and yet another parsing program was coded to go.

OVERVIEW

According to Wikipedia, the term ”parsing” is used to refer to the formal analysis by a computer of a sentence or string of words and breaking it down into its constituents, resulting in a parse tree showing their syntactic relation …
1
 
LVL 29

Author Comment

by:MikeOM_DBA
Comment Utility
Thanks!
The source code seems to be missing -- added the source code.
0
 
LVL 29

Author Comment

by:MikeOM_DBA
Comment Utility
Corrected "Shunting_Yard" and other typos in the PL/SQL code.

   text2token-pkg.sql
0
You have a query that takes too long and you want help to analyze the execution plan?

Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.

Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.
 

Method 1 - Without Tuning Pack

I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:
 
 
set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Open in new window

 
Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:
 
 
-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Open in new window

 
Finally I get the execution plan to a text file:
 
 
spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
spool off

Open in new window

 
The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.
 

Method 2 - With Tuning Pack


When you have tuning pack, you have access to the great SQL monitoring feature.
 

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
When I add the first two blocks (without parameters) above and below my query and then execute, I receive the set in the below image, with no mention of where the .txt file is, so it's not clear how I should  proceed. Screen-Shot-2016-01-28-at-3.18.13-PM.pngOracle SQL Developer v4.1.2.20.
0
 

Expert Comment

by:Jacob Forest
Comment Utility
You can do it with dbForge Studio for Oracle (https://www.devart.com/dbforge/oracle/studio/). It provides execution plan, step-by-step code execution, breakpoints, watches, a call stack and other useful features.
0
Overview

This past end of year brought us new requirements to download “compliance” files from government entities and the additional burden of maintaining these file updated monthly.

Some of these files are ‘Dbase IV’ (.dbf file format) which made it a manual procedure to load and maintain the data in Oracle tables.

To deal with this situation and be able to automate this process we coded a PL/SQL procedure to transform these “.dbf” files to pipe-delimited files and use as part of the ETL script.

This “ATTACH_DBF_FILE” procedure  provides the means to convert a ‘Dbase IV’ database file to a pipe-delimited file and attach the converted file to an oracle database as an external table.

Perhaps there already exists a Unix utility that does this, but with budget and time constraints we had to code our own.

How it Operates
 

1.

Open the dbf file as BLOB.

2.

Analyze and extract metadata for the structure and fields from the dbf file header:

a.      Field name
b.      Type
c.      Length
d.      Decimals
e.      Auto-Increment

3.

Build and execute DDL to create the external table.

4.

Extract the data from the dbf file and write to pipe delimited file.
Parameters

PROCEDURE Attach_Dbf_File
(
  P_Directory         VARCHAR2
, P_File_Name        VARCHAR2
, P_Schema            VARCHAR2
, P_Delimiter         VARCHAR2    DEFAULT '|'
)

Parameter   Description
3
 
LVL 38

Expert Comment

by:Geert Gruwez
Comment Utility
nice article ... i could have used this 2 decades ago !
0

Oracle Database

78K

Solutions

25K

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.