• 138
    0

    Describing 3 new useful Create Table command enhancements introduced in Oracle Database 12c. These new features were really required to improve the coding and performance. 1. Assign Default Values To Columns Using Sequence You can now assign sequence number as default value to a particular column in Oracle 12c to generate unique value for every ...
  • 298
    0

    In this tutorial you will learn how to create a local user in Oracle Database 12c and how to give admin rights to that user and how to import an user (schema) exported by Oracle 11g database into Oracle 12c database. Following is the step by step guide for Oracle 12c create pluggable database example. ...
  • 122
    0

    In this tutorial you will learn to how to pass the parameters to SQL Script and use that parameters in SQL statements. The following examples shows how to pass the Character String, Numeric and Date type parameters to SQL script. Suppose we have an SQL script “MyScript.Sql” and the following statements are in it and ...
  • 164
    0

    Whenever we get some database related task to perform some query for analysis purpose or any  other reports related, then we just don’t think much and start writing SQL Query to fetch the data. You simply run a query specifying the condition you want, and the database engine process to get it, but sometimes, you ...
  • 79
    0

    You are working in SQL editor of TOAD and writing a large query or program and suddenly Toad hangs due some abnormal execution or other reason and you have not saved your work then it is a bit worry situation because you want to save your huge query or program in which you was working. ...
  • 113
    0

    Suppose you have a requirement to round any decimal numeric value to nearest 0.25 in Oracle SQL, then you have to find some other way to round because in Oracle there is no such parameters in Round or Trunc function to round a value to nearest 0.25. In my case, I was dealing with time ...
  • 98
    0

    The MODEL clause allows you to access a column in a row like a cell in an array. This gives you the ability to perform calculations in a similar manner to spreadsheet calculations. The following query retrieves the sales amount for each month in 2014 made by employee id 21 for product types 1 and 2 ...
  • 83
    0

    You can reverse a string in SQL by using Reverse function of SQL. It is very simple to use, following is the command to reverse a string: SELECT reverse (‘fox infotech’) FROM DUAL; And if you want to reverse a string manually using PL/SQL block, here is the example: set serveroutput on;DECLARE    v_string   ...
  • 77
    0

    I have given many examples based on HR schema, hence I am providing here the HR schema script. Copy it from below: CREATE TABLE HR.COUNTRIES(  COUNTRY_ID    CHAR(2 BYTE) CONSTRAINT COUNTRY_ID_NN NOT NULL,  COUNTRY_NAME  VARCHAR2(40 BYTE),  REGION_ID     NUMBER,  CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID))ORGANIZATION INDEX; CREATE TABLE HR.DEPARTMENTS(  DEPARTMENT_ID    NUMBER(4),  DEPARTMENT_NAME  VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN ...
  • 77
    0

    You are trying to update particular rows in a table in Oracle and the statement taking too much time and after some time you get the error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. After getting this error you identified the problem, to follow Oracle best practices for application development, use ...
  • 92
    0

    Suppose you have a standard for your database that all foreign key columns must have indexes created for them and  you want to verify that all foreign key columns are indexed or not. The following query will get you the names of foreign key columns for which index have not been created in Oracle: selecta.constraint_name ...
  • 250
    0

    What is Oracle real time backup system? Oracle streams replication is the process which enables Oracle database schema to replicate to another Oracle database. Which can be located to any location. Whenever a transaction occurs in the primary database DML or DDL it will execute immediately to the target database to maintain a live backup ...
  • 85
    0

    Yes, Oracle can have a primary key table containing duplicate values in particular field, but it doesn’t mean that it will allow further duplicate values but it means that it will ignore the existing duplicate values while creating the primary key on that field. Suppose you have a requirement that to keep existing table as ...
  • 85
    0

    Suppose you have a requirement that a particular table in Oracle can not have records greater than 10 and also there is a requirement to handle this issue without trigger. Then there is only one way left from my point of view is Check Constraint for a particular field, for this case you must have ...