Home » SQL & PL/SQL » SQL & PL/SQL » Dropping tables in a stored procedure (119)
Dropping tables in a stored procedure [message #655041] Fri, 19 August 2016 07:19 Go to next message
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 #655042 is a reply to message #655041] Fri, 19 August 2016 07:37 Go to previous messageGo to next message
BlackSwan
Messages: 25963
Registered: January 2009
Location: SoCal
Senior Member
BAD, Bad, bad code

use Global Temporary Table instead
Re: Dropping tables in a stored procedure [message #655043 is a reply to message #655041] Fri, 19 August 2016 07:39 Go to previous messageGo to next message
John Watson
Messages: 7366
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Be prepared for people to tell not to do this. Creating objects dynamically is a Bad Thing.
Apart from that, your second EXECUTE IMMEDIATE is in the exceptions clause, so you never get to it unless the DROP failed and raised the exception.
Re: Dropping tables in a stored procedure [message #655044 is a reply to message #655043] Fri, 19 August 2016 08:33 Go to previous messageGo to next message
simonrlee22
Messages: 2
Registered: August 2016
Junior Member
Hi,

THankyou for the responses.

Why is creating objects dynamically a bad thing? Also how do I end the exception so that it then continues with rest of the code.

THank you

Simon
Re: Dropping tables in a stored procedure [message #655045 is a reply to message #655044] Fri, 19 August 2016 09:47 Go to previous messageGo to next message
John Watson
Messages: 7366
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 #655047 is a reply to message #655041] Fri, 19 August 2016 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65463
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition

Quote:
WHEN OTHERS THEN NULL;
This is a bug in your procedure, actually the BIGGEST bug you can ever write in your procedure.
If you want to trap the error when the table does not exist then trap this exception do NOT trap ALL exceptions.
Read WHEN OTHERS.

Re: Dropping tables in a stored procedure [message #655048 is a reply to message #655045] Fri, 19 August 2016 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65463
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
That way, you are guaranteed to end up with an empty table after the procedure call completes.
Or at the beginning, it seems OP wants to empty before inserting and selecting. Smile

@OP,
Can't you achieve what you need using a GLOBAL TEMPORARY TABLE?
Then no create to drop/create, no need to truncate, the table is empty at the beginning of the session/transaction.

Re: Dropping tables in a stored procedure [message #655049 is a reply to message #655048] Fri, 19 August 2016 14:05 Go to previous messageGo to next message
Bill B
Messages: 1729
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 Go to previous message
BlackSwan
Messages: 25963
Registered: January 2009
Location: SoCal
Senior Member
Bill B wrote on Fri, 19 August 2016 12:05
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.
+1!
Previous Topic: Dynamic SQL Learning
Next Topic: connect by prior help
Goto Forum:
  


Current Time: Thu Apr 26 23:55:35 CDT 2018

Total time taken to generate the page: 0.07806 seconds