Home » SQL & PL/SQL » SQL & PL/SQL » Passing partition name using a variable in insert statement. (Oracle 10g)
Passing partition name using a variable in insert statement. [message #400853] Wed, 29 April 2009 23:48 Go to next message
nawshot
Messages: 12
Registered: May 2007
Junior Member
Hi,

I want to insert data into a table in a partition by passing partition name through a variable.

When I run the below procedure I am getting error as "partition does not exist" but when I checked about the partitions details it is there .

CREATE OR REPLACE procedure emis_stock_f_archive_insert IS

v_partition_name varchar2(50);

BEGIN
-- ouput of the below select will be "APR_2009_CES"
select TO_CHAR (current_month_closing_date, 'MON')||'_'||TO_CHAR (current_month_closing_date, 'yyyy')||'_'||'CES') INTO v_partition_name from rdw.rdw_current_process_dates_v ;

INSERT INTO ARCHIVE_STOCK_F SUBPARTITION(v_partition_name)
SELECT * FROM DW_OWNER.rdw_test_STOCK_F SUBPARTITION(v_partition_name);

COMMIT;

END;
/

regards,
naw
Re: Passing partition name using a variable in insert statement. [message #400854 is a reply to message #400853] Wed, 29 April 2009 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

Please, please, please Read & Follow Posting Guidelines above.

Go to the section labeled "Practice" & do as directed.

Privileges acquired via ROLE do not apply within PL/SQL procedures.
Re: Passing partition name using a variable in insert statement. [message #400855 is a reply to message #400853] Thu, 30 April 2009 00:32 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a variable as partition name.
You have to use dynamic SQL.

Regards
Michel
Previous Topic: Problem related to view and long running query
Next Topic: Global Variable
Goto Forum:
  


Current Time: Sun Dec 04 14:44:25 CST 2016

Total time taken to generate the page: 0.08030 seconds