Home » SQL & PL/SQL » SQL & PL/SQL » Table Partition
Table Partition [message #237684] Tue, 15 May 2007 08:15 Go to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Good Morning.

I try to create a partition table by using dynamic SQL. But it did not work. Please help. I would appreciate that
Here is my code:

DECLARE
   cid	      integer := DBMS_SQL.OPEN_CURSOR;

BEGIN
    loop
	Begin
	      DBMS_SQL.PARSE(cid,'Create table testing (deptno	   varchar2(4),
					                lname	   varchar2(20),
							fname	   varchar2(20),
							ssn	   varchar2(9),
							hire_date  varchar2(4),
							salary	    varchar2(6)
                                                        Partition by list (deptno)
                                                        (
                                                        Partition dept_10 values ('10'),
                                                        Partition dept_20 values ('20'))'), dbms_sql.v7);
		DBMS_SQL.CLOSE_CURSOR(cid);
		dbms_output.put_line('TABLE CREATED');
		goto endhere;
	End;
    End loop;
        <<endhere>>
	null;
END;



Thank you very much.
Re: Table Partition [message #237688 is a reply to message #237684] Tue, 15 May 2007 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Did not work is not a valid Oracle error
2/ Error depends on Oracle version, so post it
3/ If you have ' inside a string then double it
4/ Why using dbms_sql.v7 when you try to execute a DDL that does not exist in v7? Use dbms_sql.native.

Regards
Michel
Re: Table Partition [message #237698 is a reply to message #237684] Tue, 15 May 2007 08:40 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you for your reply.

I have Oracle 10g. And I don't know what you meant: "If you have ' inside a string then double it"
Please tell me more details because I'm so new in Oracle. Thanks much for your help.
Re: Table Partition [message #237702 is a reply to message #237698] Tue, 15 May 2007 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select 'Partition dept_10 values ('10')' from dual;
select 'Partition dept_10 values ('10')' from dual
                                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select 'Partition dept_10 values (''10'')' from dual;
'PARTITIONDEPT_10VALUES(''10'')
-------------------------------
Partition dept_10 values ('10')

1 row selected.

Regards
Michel
Re: Table Partition [message #237739 is a reply to message #237684] Tue, 15 May 2007 09:58 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much. I'm almost there. I got everything ok accept the of missing right parenthesis. I'l figure it out.

I have one more question. Do I need to modify SQL statements in order to access partitioned tables?

Thanks
Re: Table Partition [message #237742 is a reply to message #237739] Tue, 15 May 2007 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No. Partitioning is transparent to SQL queries.

Regards
Michel
Re: Table Partition [message #237780 is a reply to message #237684] Tue, 15 May 2007 12:58 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thank you very much.
Previous Topic: How to format number to a time
Next Topic: FUNCTION problem
Goto Forum:
  


Current Time: Fri Dec 13 06:06:46 CST 2024