Home » SQL & PL/SQL » SQL & PL/SQL » Is there any alternative for LIST partition to capture the DEFAULT values? (merged)
icon4.gif  Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #411896] Tue, 07 July 2009 04:55 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Hi,

Consider the below script to create a table with LIST partition.

CREATE TABLE list_part (
deptno NUMBER(10),
deptname VARCHAR2(20),
quarterly_sales NUMBER(10,2),
state VARCHAR2(5))
PARTITION BY LIST (state) (
PARTITION q1_tn VALUES ('TN') ,
PARTITION q1_ka VALUES ('KA') ,
PARTITION q1_ap VALUES ('AP') ,
PARTITION q1_mh VALUES ('MH') ,
PARTITION q1_mp VALUES ('MP') ,
PARTITION q1_default VALUES (DEFAULT) );

INSERT INTO list_part
VALUES (10, 'A', 1000, 'TN');

INSERT INTO list_part
VALUES (20, 'B', 1000, 'KA');

INSERT INTO list_part
VALUES (10, 'A', 1000, 'WA');

COMMIT ;

SELECT *
FROM list_part;

DEPTNO|DEPTNAME|QUARTERLY_SALES|STATE
-------------------------------------
10 A 1000 TN
20 B 1000 KA
10 A 1000 WA

SELECT *
FROM list_part PARTITION (q1_DEFAULT);

DEPTNO|DEPTNAME|QUARTERLY_SALES|STATE
-------------------------------------
10 A 1000 WA



ok.


When I execute the below query,

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name LIKE 'LIST_PART';

result is

PARTITION_NAME |TABLESPACE_NAME| HIGH_VALUE
-------------------------------------------
Q1_TN RCDW_ACC_1M_INX01 'TN'
Q1_KA RCDW_ACC_1M_INX01 'KA'
Q1_AP RCDW_ACC_1M_INX01 'AP'
Q1_MH RCDW_ACC_1M_INX01 'MH'
Q1_MP RCDW_ACC_1M_INX01 'MP'
Q1_DEFAULT RCDW_ACC_1M_INX01 DEFAULT


CREATE TABLE part_info (partition_name VARCHAR(15), high_value VARCHAR(15));


INSERT INTO part_info
VALUES (SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name LIKE 'LIST_PART');

ORA-00936: missing expression

My requirement is to return the partition DEFAULT value with a single qoutes like this 'DEFAULT'.

IS IT POSSIBLE.


regards,
Nataraj.

Is there any alternative for LIST partition to capture the DEFAULT values? [message #411917 is a reply to message #411896] Tue, 07 July 2009 05:23 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Hi,

CREATE TABLE list_part (
deptno NUMBER(10),
deptname VARCHAR2(20),
quarterly_sales NUMBER(10,2),
state VARCHAR2(5))
PARTITION BY LIST (state) (
PARTITION q1_tn VALUES ('TN') ,
PARTITION q1_ka VALUES ('KA') ,
PARTITION q1_ap VALUES ('AP') ,
PARTITION q1_mh VALUES ('MH') ,
PARTITION q1_mp VALUES ('MP') ,
PARTITION q1_default VALUES (DEFAULT) );

Is there any alternative for the above LIST partition to capture the DEFAULT values?

Please advise.

regards,
Nataraj.

Re: Is there any alternative for LIST partition to capture the DEFAULT values? [message #411932 is a reply to message #411917] Tue, 07 July 2009 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
I can't image which one it can be. Do you want another word than DEFAULT?

Regards
Michel
Re: ORA-00936: missing expression [message #411935 is a reply to message #411896] Tue, 07 July 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Is there any alternative for LIST partition to capture the DEFAULT values? [message #411956 is a reply to message #411932] Tue, 07 July 2009 06:10 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

If any record with values other than 'TN' ,'KA' ,'AP' ,'MH' and 'MP' is inserted in to this table then this record should go to Q1_DEFAULT partition.

regards,
Nataraj.

Re: Is there any alternative for LIST partition to capture the DEFAULT values? [message #411958 is a reply to message #411956] Tue, 07 July 2009 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #411981 is a reply to message #411896] Tue, 07 July 2009 06:42 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Pls tell whether there is an alternative way to arrive this.

If any record with values other than 'TN' ,'KA' ,'AP' ,'MH' and 'MP' is inserted in to this table then this record should go to Q1_DEFAULT partition.

regards,
Nataraj.
Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #411987 is a reply to message #411981] Tue, 07 July 2009 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is. What is the problem?

Regards
Michel
Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #412153 is a reply to message #411896] Wed, 08 July 2009 00:48 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Quote:
It is. What is the problem?

Regards
Michel


Now I'm creating another part_info table to put the partition information of this 'LIST_PART' table.


CREATE TABLE part_info (partition_name VARCHAR(15), high_value VARCHAR(15));  
 

INSERT INTO part_info
     VALUES (SELECT partition_name, high_value
              FROM user_tab_partitions
             WHERE table_name LIKE 'LIST_PART');


ORA-00936: missing expression

The error is due to the DEFAULT value from the select query with missing single quotes.

This is my problem.
Pls give me a solution to insert the partition information of this 'LIST_PART' table in to another table.

regards,
Nataraj.



[Updated on: Wed, 08 July 2009 00:53]

Report message to a moderator

Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #412154 is a reply to message #412153] Wed, 08 July 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The error is due to the DEFAULT value from the select query with missing single quotes.

No, the FIRST error is due to your syntax which is not valid.
Have a look at SQL Reference.

Regards
Michel
Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #412164 is a reply to message #412154] Wed, 08 July 2009 01:31 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi Michel
SQL> CREATE TABLE part_info  (partition_name VARCHAR2(30), HIGH_VALUE     LONG);

Table created.
SQL>  INSERT INTO part_info
  2   SELECT partition_name, high_value
  3   FROM user_tab_partitions
  4   WHERE table_name LIKE 'LIST_PART'
  5  /
 SELECT partition_name, high_value
                        *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

Can you please tell me what error I am making?
Re: Is there any alternative for LIST partition to capture the DEFAULT values? (merged) [message #412169 is a reply to message #412164] Wed, 08 July 2009 01:53 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Ok I found out in the same forum
http://www.orafaq.com/forum/t/55560/0/

It can be achieved by Copy from command
SQL>  COPY  from ayush/password@orcl-
>  create test_t -
>  using SELECT  high_value  FROM user_tab_partitions WHERE table_name LIKE 'LIST_PART'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TEST_T created.

   6 rows selected from ayush@orcl.
   6 rows inserted into TEST_T.
   6 rows committed into TEST_T at DEFAULT HOST connection.

SQL> /

HIGH_VALUE
--------------------------------------------------------------------------------
'TN'
'KA'
'AP'
'MH'
'MP'
DEFAULT

6 rows selected.

Previous Topic: Tuning a query
Next Topic: updating based on multiple condition
Goto Forum:
  


Current Time: Sun Dec 04 10:12:38 CST 2016

Total time taken to generate the page: 0.12540 seconds