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
… Read the rest “How to add partitions to an existing table in Oracle database”
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”
Oracle DECODE statement failing on RHEL but works on HPUX
I’ve isolated a very specific code piece that works on our HP-UX Oracle 11.2 environment, but fails on our RHEL 7.1 Oracle 11.2 environment. Any pointers as to why this would happen?
Everything else (except PSU level) is generally the … Read the rest “Oracle DECODE statement failing on RHEL but works on HPUX”
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)
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
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
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”
APEX sum different fields on a form
I want to create a page item display only that sums 2 other page itens on a form.
P1_A is a number field with a mask of currency.
P1_B is a display only field based on a database query that returns a single … Read the rest “APEX sum different fields on a form”
Splitting string into multiple rows in Oracle 8i
I have a table consisting a list of names separated by comma. My goal is to separated them by rooms.
Room | Name
room1 | Anne,Amy
room2 | Ben,Bryan
Room | Name
room1 | Anne
room1 | Amy
room2 | … Read the rest “Splitting string into multiple rows in Oracle 8i”
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,
HOUSE RENT DEDUCTION
But i … Read the rest “how to group aggregate xml elements inside another parent xml tag in pl sql”