SQL & PL/SQL :: Partition Creation Based On Other Table Values

Feb 12, 2013

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);

[Code]..

It is not creating the partitions and even not giving any errors.

View 4 Replies


ADVERTISEMENT

Dynamic Pivot Table (Partition) Creation

Oct 31, 2010

I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.

View 3 Replies View Related

SQL & PL/SQL :: Partition A Existing Table Based On Varchar2 Field?

Oct 13, 2011

I need to partition a existing table based on varchar2 field (which is actaully date value but storing as character in the table). Using below statement for creating table, but getting error.

create table TST_CUST_ARC
(
interact_id NUMBER(10),
extrn_id VARCHAR2(38),
src_cd VARCHAR2(25),
full_nm VARCHAR2(45),
run_control_date VARCHAR2(13)

[code]....

Getting error : ORA-00907: missing right parenthesis

View 5 Replies View Related

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER

I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......

I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........

The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Can Drop Partition Based Indexes From A Particular Partition

Mar 3, 2011

I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?

View 8 Replies View Related

SQL & PL/SQL :: View With Column Based On Row Values Of Another Table

Apr 13, 2011

I have table with values :

PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68

For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :

PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72

basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.

View 2 Replies View Related

Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

PL/SQL :: Updating CSV Values In Oracle Table Based On Condition

Aug 27, 2013

I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.

View 1 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

SQL & PL/SQL :: Sequence Creation For Results (Values)

Jan 25, 2013

How to create the sequence which gives following values

1
0
1
0
1
0

when we excite the following query , it has to give the above results

select seq.nextval from dual

View 5 Replies View Related

SQL & PL/SQL :: Partition Name Based On Data?

Oct 9, 2013

I have a range date partition table month wise which i want to truncate the partition and load for each month data i get from source.

How can i get the partion name based on data value.

e.g

source data

SALES_IDSALES_DT

101/02/2008
101/03/2008
101/04/2008
101/05/2008

target table has same structure as source but has more data but i want to delete only those which is coming from source if it is there in target and then load. so i want to get partition name so that i can truncate and load them.

View 11 Replies View Related

Performance Tuning :: How To Assure Partition Table Is Better Than Non-partition Table

Mar 25, 2011

I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?

I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?

View 11 Replies View Related

SQL & PL/SQL :: Truncate Partition Based On Given Input?

Jun 20, 2013

How to truncate the partition based on the given input. I've below table partitioned on dt field. How can i truncate the whole partition which contain the date 2013/04/30.

CREATE TABLE TST_SUMMARY
(
CUST_ID NUMBER(38) NOT NULL ,

[Code]....

View 21 Replies View Related

SQL & PL/SQL :: Analyze Partition Based On Trading Date

Mar 3, 2011

We have fact tables partitioned based on week.

We receive point of sale data on daily basis .

Some times we will receive data for previous week also.

In current system , we are analyzing only one partition(current partition).

Now , we want to analyze all old partitions also based on incoming old trading date.

how can i approach to find partitions for old data (trading_date s)

View 1 Replies View Related

Performance Tuning :: Select Partition Table With Non-partition Key Condition?

Jun 26, 2010

I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk

when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)

View 3 Replies View Related

PL/SQL :: Oracle Database 11g - Add Partition Based On Amount Of Data To Be Populated

Oct 30, 2013

I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production and TNS for Linux: Version 11.2.0.3.0 - Production.Requirement is to create a script to add a LIST partition to some selected tables in a schema (tables do not have data, they are not partitioned). There are about 300 such tables (can vary) and their names are maintained in a separate table. Example -Existing table

  -CREATE TABLE test_part(id number (2),
name varchar2(20),
audit_userid number (9)); 
Expected table
-CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9))  
PARTITION BY LIST (audit_userid)      (PARTITION p1_audit_userid VALUES (1));

 Ultimate goal is to add more partitions based on the amount of data to be populated.

View 1 Replies View Related

SQL & PL/SQL :: Alter Table To Create Partition On Non-partition Table?

Jun 19, 2012

Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.

View 1 Replies View Related

SQL & PL/SQL :: Add Range Sub-Partition To Hash Partition Table

Jan 2, 2013

Can I add range sub partition to a hash partition table. Example like this.

CREATE TABLE test
(
test_id VARCHAR2(10 ) ,
test_TYPE VARCHAR2(5) ,
CREATE_DATE date
)
partition by hash (test_id, test_type)
Partitions 3
SUBPARTITION BY RANGE (CREATE_DATE);

When Tried, I am getting syntax error as invalid option.

View 8 Replies View Related

SQL & PL/SQL :: Adding A New Partition To Existing Partition Table

Jun 14, 2011

i want to create a new partition for version 2

existing table is as below

create table test
(
name varchar2(100),
version NUMBER(12)
)

[Code]....

View 15 Replies View Related

SQL & PL/SQL :: Partitioned Table - Inserted Partition Key Does Not Map To Any Partition

Jul 10, 2012

I have two tables in which one is partitioned table with the following details.

CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),

[code].....

When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error

SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.

ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198

[code].....

I also tried to upload the same data using sqlldr.

sqlldr log file contents is as follows:

Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37

[code].....

So some sqlldr bad file contents is as follows.

100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Partition Size In A Partition Table

Jun 8, 2011

How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')

View 3 Replies View Related

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

I am using the below code to update specific sub-partition data using oracle merge statements.

I am getting the sub-partition name and passing this as a string to the sub-partition clause.

The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.

We are using Oracle 11gr2 database.

Below is the code which I am using to populate the data.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Inserts Based On Column Values

Aug 15, 2012

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.

CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100

--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...

View 5 Replies View Related

PL/SQL :: Collect Values Based On The Flag

Oct 18, 2012

I have a scenario, where I need to find the bucket values with the flag as "Y". The problem is i have 5 buckets

Bkt1 Bkt2 Bkt3 Bkt4 Bkt5 Flag
------------------------------------------
1000 500 230 100 677 Y
789 100 122 666 888 N
783 110 142 166 788 N
781 110 112 616 988 Y
709 130 121 661 878 N

Here I need to write a SQL query in Oracle 10g fetching the respective bucket value(for all the 5 buckets) along with the flag as "Y" and more importantly should have the bucket name as well.This is needed as in the UI we need to display the respective bkt value which is "Y" as bold.

So in the final List I need to have the VO which should contain the respective bucket name, respective value and flag as "Y"

Some thing like this I need to get in my final ArrayList in java

Bkt1 1000 Y
Bkt1 781 Y
Bkt2 500 Y
Bkt2 110 Y
Bkt3 230 Y
Bkt3 112 Y
Bkt4 100 Y
Bkt4 616 Y
Bkt5 677 Y
Bkt5 988 Y

how we can do it using the SQL query.

View 16 Replies View Related

How To Assure Partition Table Is Better Than Non-partition Table

Mar 25, 2011

I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?

I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?

View 2 Replies View Related

Insert Rows Based On Text Values?

Mar 22, 2007

I am an Oracle beginner and I am having some trouble with the following insert query.

I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.

INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);

The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"

View 1 Replies View Related

Grabbing 2 Unique Values Based On A Field?

Nov 29, 2012

I have a list sample:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
141138899 MLIQUID02T 368308 CARLENA
5078916 MLIQUID02T 368308 DONNA

[code]....

What I'd like to do is run SQL that can create output where I get 2 records from each Location. Example:

Order#Location VendorName
--------- --------- ------- -------
145646842 MLIQUID02T 368308 JOHNNEISHA
134962284 MLIQUID02T 368308 JERRY
13999694 MLIQUID03T 368308 TINA
175439805 MLIQUID03T 368308 RANDI
4801973 MLIQUID05T 368308 DIANA
55907648 MLIQUID05T 368308 DESIREE

Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved