| Dropping tables in a stored procedure [message #655041] |
Fri, 19 August 2016 07:19  |
 |
simonrlee22
Messages: 2
Registered: August 2016
|
Junior Member |
|
|
Hi
I'm relatively new to oracle and I'm having trouble writing a stored procedure.
I want to baicly check if a table exists, if it does I want to drop it and create a new one. if it doesn't exist then I should just create a new one.
My code seems to work if the table is not there, but if it is there, it seems to drop it and then end the pricedure.
My code is below:
create or replace PROCEDURE SL_CREATE_ENROLMENT_CURRENT (vDate IN DATE) IS
vFYear VARCHAR2(2);
BEGIN
vFYear:= SL_GET_FUNDING_YEAR(vDate);
EXECUTE IMMEDIATE 'DROP TABLE SL_ENROLMENT_'||vFYear||'';
EXCEPTION
WHEN OTHERS THEN NULL;
EXECUTE IMMEDIATE
'CREATE TABLE SL_ENROLMENT_'||vFYear||' AS
SELECT .........
FROM...........';
END SL_CREATE_ENROLMENT_CURRENT;
Thanks in Advance
Simon
|
|
|
|
|
|
|
|
|
|
| Re: Dropping tables in a stored procedure [message #655045 is a reply to message #655044] |
Fri, 19 August 2016 09:47  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'll let others explain why creating objects dynamically is not best practice. Also, there are better pl/sql programmers here than me to explain syntax, if you can't be bothered to look it up yourself.
But I will give you a solution: replace your DROP TABLE with TRUNCATE TABLE. That way, you are guaranteed to end up with an empty table after the procedure call completes.
|
|
|
|
|
|
|
|
| Re: Dropping tables in a stored procedure [message #655049 is a reply to message #655048] |
Fri, 19 August 2016 14:05 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
If the structure of the table is always the same then it makes no sense to do what you are doing. Make a single table and add a YEAR column to partition the data. You would do something like this
create or replace PROCEDURE SL_CREATE_ENROLMENT_CURRENT (vDate IN DATE) IS
BEGIN
delete from SL_ENROLMENT WHERE ENROLL_YEAR = TRUNC(VDATE,'year');
insert into SL_ENROLMENT(col1,col2,col3,col4,enroll_year)
SELECT col1,col2,col3,col4,TRUNC(VDATE,'year')
FROM...........';
commit;
END SL_CREATE_ENROLMENT_CURRENT;
[Updated on: Fri, 19 August 2016 14:06] Report message to a moderator |
|
|
|
| Re: Dropping tables in a stored procedure [message #655050 is a reply to message #655049] |
Fri, 19 August 2016 14:28 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Bill B wrote on Fri, 19 August 2016 12:05If the structure of the table is always the same then it makes no sense to do what you are doing. Make a single table and add a YEAR column to partition the data.
+1!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
