- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 989 Articles for Software & Coding
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
757 Views
Problem Statement:You want to find and remove duplicates from a table in Oracle.Solution: We can use Oracle’s internal ROWID value for uniquely identifying rows in a table. The sample syntax to acheive this would like below.delete from table where rowid in (... query here ...)To demonstrate the usage, we will begin by creating sample data.Example-- table with tennis player rankings DROP TABLE atp_stats; CREATE TABLE atp_stats ( player_rank NUMBER NOT NULL, player_name VARCHAR2(100) NOT NULL, time_range TIMESTAMP(6)); -- sample records INSERT INTO atp_stats VALUES (1, 'ROGER FEDERER', CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (2, 'RAFAEL NADAL', ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
2K+ Views
Problem Statement:You want to validate the syntax of a SQL before execution.Solution: The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. We can leverage DBMS_SQL.PARSE function to validate the syntax.We will first define a function to accept the SQL statement as the parameter and parse the SQL statement./* * --------------------------------------------------------------------------- * Function : check_syntax * Input : sql statement * Output : Number * --------------------------------------------------------------------------- */ FUNCTION check_syntax ( p_query IN CLOB ) ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
173 Views
Problem Statement:You want to find out subtotals for all combinations of the dimensions specified in Oracle.Solution:The CUBE function will generate subtotals for all combinations of the dimensions specified. If “n” is the number of columns listed in the CUBE, there will be 2n subtotal combinations.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type VARCHAR2(100) NOT NULL, titles NUMBER NOT NULL);Example-- insert ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
3K+ Views
Problem Statement:You want to find out totals, subtotals and a grand total in Oracle.Solution:Oracle ROLLUP function performs grouping at multiple levels, using a right to left method of rolling up through intermediate levels to any grand total. To demonstrate the ROLLUP function we will create a table to hold tennis player along with the ATP tour titles and Grandslam titles acheived by the player.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
6K+ Views
Problem Statement: You want to find and replace a string in Oracle.Solution:Function: TRANSLATESyntax: TRANSLATE(expr, from_string, to_string)TRANSLATE funtion in oracle allows you to make many single character, one on one substitutions in a single operation.However, the to_string and from_string values must not be empty, if an empty string is passed to TRANSLATE Function, Oracle Database interprets the empty string as null and returns null./* replace all occurances of b with j in below string */ SELECT 'back and bill ' AS original_string , TRANSLATE('back and bill', 'b', 'j') AS replaced_string FROM dual;Outputback and bill jack and ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
193 Views
Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.SQL:/* Function - Example to show if the row is parent of any other rows or not Tables Used - students Data - Documented below */ SELECT student_id, level, CASE WHEN connect_by_isleaf = 0 THEN 'Yes' ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
418 Views
Problem Statement: You need to traverse the hierarchy data from top to bottom but doesn’t want a particular branch in the output.Solution: We will look at couple of examples for this problem statement.Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
579 Views
Problem Statement: You need to traverse the hierarchy data from top to bottom and sort the rows at the same level in a hierarchy.Solution: The usual ORDER BY clause will not sort the rows at the same hierarchy level. We need to use SIBLINGS with in the ORDER BY Clause.Additionally, Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can ... Read More
![Kiran P](https://www.tutorialspoint.com/assets/profiles/270934/profile/60_284346-1604569411.jpg)
636 Views
Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.Solution:Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have ... Read More
![Chirag Nagrekar](https://www.tutorialspoint.com/assets/profiles/163503/profile/60_109370-1581658197.png)
486 Views
To set the line breakpoint in the script, we can use the Set-PSBreakpoint command with -Line parameter and need to give the path of the script on which the Line breakpoint needs to set.Consider we have the script below which retrieves the value up to 99, starting from 1, We will set the Line Breakpoint at line number 3 so we will use the below command. Here the script name is WhieScript.ps1 and stored at C:\temp.Set-PSBreakpoint C:\temp\WhileScript.ps1 -Line 3Once you run the above command, you will get the output with the details as shown below.ID Script Line Command Variable ... Read More