How to add partitions to an existing table in Oracle database

When the data in the table becomes bigger, we’ll need partitioning in order to make our SQL command faster. But the thing is we already have data in that table. These queries will help you do that with just one execution step.

--create new partitioned table from old table
CREATE 
Read the rest “How to add partitions to an existing table in Oracle database”

Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value

This article guides you to write Oracle PL/SQL command for creating new table as select from other tables/views with partitioning and column default value.

First of all, this is the syntax of a simple CTAS:

CREATE TABLE TBLA AS SELECT * FROM TBLB;

Please note that in the example above, … Read the rest “Oracle: “CREATE TABLE AS SELECT” (CTAS) with PARTITIONING and Column Default Value”

SQL insert into select from – insert the id instead of the data

SQL insert into select from – insert the id instead of the data

I need to populate my fact table with data from lds_placement table. I have selected the records and here is what it looks like:
fk1_account_id | fk3_job_role_id | salary | no_of_placements | YEAR
———————————————————————
10 | 3 … Read the rest “SQL insert into select from – insert the id instead of the data”

SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)

SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)

Apologies in advance for the length of the question:
I am working on a query to display data that will pull from different tables depending on the facility that is requesting the data.
We maintain many statistics from … Read the rest “SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins)”

ORA-12899 value too large error while trying to drop a NUMBER column

ORA-12899 value too large error while trying to drop a NUMBER column

I am trying to drop a column from a table in Oracle using a statement like this:
ALTER TABLE “MY_TABLE” DROP COLUMN “ENABLED”;

but I am getting this error:
ORA-12899: value too large for column “MY_TABLE”.”ENABLED” (actual: 184, … Read the rest “ORA-12899 value too large error while trying to drop a NUMBER column”

select value from same row using case on table oracle

select value from same row using case on table oracle

I have a table like that in oracle:

Then I want to select customer_type and my new column named ‘customer_id’. If customer_type is ‘CORPORATE’ then ‘customer_id’ column will filled with shipper_id else if customer_type is ‘RETAIL’ the column (‘customer_id’) will … Read the rest “select value from same row using case on table oracle”

how to group aggregate xml elements inside another parent xml tag in pl sql

how to group aggregate xml elements inside another parent xml tag in pl sql

i have following types of xml data stored in oracle database,
Updates

123
2016
1 BASIC PAY 440000 ASSOCIATION SUBSCRIPTION 240 TELEPHONE ALLOWANCE 800 HOUSE RENT DEDUCTION 2587.5 MEDICAL ALLOWANCE 700 GAS BILL 450

But i … Read the rest “how to group aggregate xml elements inside another parent xml tag in pl sql”