ORA-01436: CONNECT BY loop in user data

            SELECT CASE

                     WHEN CONNECT_BY_ISLEAF = 1 THEN 0

                     WHEN LEVEL = 1 THEN 1

                     ELSE -1

                  END

                     AS status,

                  LEVEL,

                  menu_desc AS title,

                  DECODE (0, NULL, 'fa-bars', 'fa-file') AS icon,

                  child_node AS VALUE,

                  menu_desc AS tooltip,

                  APEX_UTIL.prepare_url (

                        'f?p='

                     || :app_id

                     || ':'

                     || page_no

                     || ':'

                     || :app_session

                     || ':::::')

                     AS link

             FROM (SELECT mennam AS menu_desc,

                          progcod AS parent_node,

                          modcod AS child_node,

                          progmid AS page_no

                     FROM menumst)

       START WITH child_node = 0

       CONNECT BY PRIOR parent_node = child_node

ORDER SIBLINGS BY parent_node;

I have faced ORA-01436: CONNECT BY loop in user data error

Various use of CASE statement

Q. Which query return different value?

1. SELECT CASE 0 WHEN 0 THEN 'A' ELSE 'B' END

FROM DUAL;

2. SELECT CASE WHEN 0 = 0 THEN 'A' ELSE 'B' END

FROM DUAL;

3. SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END

FROM DUAL;

4. SELECT CASE WHEN NULL IS NULL THEN 'A' ELSE 'B' END

FROM DUAL;

Class 1 Practice

Practice 1

1.            Connect database in SQL*Plus session using the user ID and password.

2.            SQL*Plus commands access the database (Select one).
                                a. True
                                b. False

3.            The following SELECT statement executes successfully:
SELECT last_name, job_id, salary AS Sal FROM    employees;
                                a. True
                                b. False

4.            The following SELECT statement executes successfully:
SELECT *
FROM   job_grades;
                                a. True
                                b. False

5.            There are four coding errors in this statement. Can you identify them?
SELECT  employee_id, last_name sal x 12              ANNUAL SALARY
FROM   employees;

6.            Show the structure of the DEPARTMENTS table. Select all data from the table.

7.            Show the structure of the EMPLOYEES table. Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Save your SQL statement to a file named lab1_7.sql.

8.            Run your query in the file lab1_7.sql.

9.            Create a query to display unique job codes from the EMPLOYEES table.

If you have time, complete the following exercises:

10.          Copy the statement from lab1_7.sql into the SQL*Plus Edit window. Name the column headings Emp #, Employee, Job, and Hire Date, respectively. Run your query again.

11.          Display the last name concatenated with the job ID, separated by a comma and space, and name the column Employee and Title.

 

If you want an extra challenge, complete the following exercise:

12.          Create a query to display all the data from the EMPLOYEES table. Separate each column by a comma. Name the column THE_OUTPUT.

Home

Welcome to our blog. Our mission to learn and solve Oracle related problem. 

Introduction to Oracle

Oracle is a product from Oracle Corporation that provides a relational database management system. Oracle’s RDBMS supports any kind of data models, and has different product editions such as Standard Edition, Enterprise Edition, Express Edition, and Personal Edition, among which the user gets to choose the database system depending on their need. Oracle products are scalable and secure, with high performance ability, compared to other databases available in the market.

What is Oracle?

Its database is also known as simply Oracle also. It is a multi-model relational database management system, mainly designed for enterprise grid computing and data warehousing. It is one of the first choices for enterprises for cost-effective solutions for their applications and data management. It supports SQL as a query language to interact with the database.

Currently, its database comes in five different editions based on the features available.

Standard Edition One: It is suitable for single-server or highly branched business applications with limited features.

Standard Edition: It delivers all facilities provided in Standard Edition One. In addition, it provides larger machine support and Oracle Real Application clustering service.

Enterprise Edition: This edition is packed with features like security, performance, scalability, and availability, required for highly-critical applications in which online transaction processing is involved.

Express Edition: It is an entry-level edition that is free to download, install, manage, develop and deploy.

Personal Edition: It comes with the same features of Enterprise edition except Oracle Real Application Clustering.

Features of Oracle

An Oracle database offers the following features to meet the requirements of powerful database management:

1. Scalability and Performance
2. Availability
3. Backup and Recovery
4. Security

1.       Scalability and Performance: Features like Real Application Clustering and Portability make an Oracle database scalable according to the usage. In a multiuser database, it is required to control data consistency and concurrency which are contemplated by Oracle.

2.       Availability: Real-time applications require high data availability. High performing computing environments are configured to provide all-time data availability. Data is available during the time of planned or unplanned downtime and failures.

3.       Backup and Recovery: Its layout complete recovery features to recover data from almost all kinds of failures. In case of failure, the database needs to be recovered within no time for high availability. Unaffected parts of data are available while the affected ones are getting recovered.

4.       Security: Securing the data is always the top priority. Oracle provides mechanisms to control data access and usage. Implementing authorization and editing user actions can prevent unauthorized access and allow distinct access to the users.

Importance of Oracle

It is among the oldest companies which provide database management solutions. The company has always focused on Enterprise requirements and acknowledged the latest technology trends. That’s why its products are always embellished with new features. For instance, the latest Oracle database 19c is available on Oracle Cloud as well. Oracle offers users to choose from the different database editions which suit their needs in order to provide a cost-effective solution.

Benefits

We have talked about the features of its database. These features give Oracle an edge over other competitors. Now, we will look at the advantages of Oracle.

1.       Performance: It has methodologies and principles to achieve high performance. We can implement performance tuning in its database to retrieve and alter data faster, in order to improve query execution time and hence application operations.

2.       Multiple Databases: Its database supports managing multiple database instances on a single server. Instance Caging method is provided by Oracle to manage CPU allocations on a server running the database instances. Instance caging works with the database resource manager to manage services over multiple instances.

3.       Editions: As we discussed above, about the different editions which are offered by Oracle, it gives benefit to the users to purchase edition as per their application requirements. They can seamlessly update the edition if their requirements change in the future. If you want to learn and do some hands-on Oracle, you can download and install the express edition database which is absolutely free.

4.       Clusters: It uses Real Application Clusters to provide a high data availability system. The database with RAC has benefits over traditional database servers:

• Scaling the database over multiple instances.
• Load balancing
• Data redundancy and availability
• Flexible to increase processing capacity

5.       Failure Recovery: RMAN (Recovery Manager) is the feature of an Oracle DB which recovers or restores the database files during downtime and outages. It supports online, archived backups and continuous archiving. Users can also SQL* PLUS for recovery, called as user-managed recovery, which is supported by it. There is an export utility available in the database to add user-managed backups.

6.       PL/SQL: The database supports PL/SQL extension for procedural programming.

Why do We Use Oracle?

It is a database management software product. A database contains an organized collection of information. A database management system is not only used for storing the data but to effectively manage it and provides high performance, authorized access and failure recovery features. It provides a software solution that is easy to use and manage database operations, from Personal to Enterprise level applications.

Conclusion

We can conclude that Oracle is powerful database server management software that can serve the requirements of Enterprise level and small level applications as well. It includes almost all the features required to support modern applications and therefore, is widely used.

Please comment for continuing the mission. I try to answer any type question.