Home » SQL & PL/SQL » SQL & PL/SQL » help in sql (merged 3)
help in sql (merged 3) [message #398325] Thu, 16 April 2009 00:56 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
hi all


i have an application that generates records and it has a unique id

like below

NEW0000000005181
NEW0000000004354
NEW0000000003221

after generation of records corresponding to each id

a table is created like below

NEW_****(where the **** indicates last 4 digits of the unique ids)

i need to write a daily process that will drop the above table once the table is created as that table information is not required any more after tha application completes its processing

Re: help in process [message #398333 is a reply to message #398325] Thu, 16 April 2009 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Bad bad bad design.
Runtime creation and deletion of database objects is not the way to go in Oracle.
Re: help in sql [message #398428 is a reply to message #398325] Thu, 16 April 2009 04:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Congratulations - you've got a really poor application design.

EXECUTE IMMEDIATE is what you want to use for the DROP TABLE command, and DBMS_JOB or DBMS_SCHEDULER are what you need to schedule the job to run every day.
Re: help in sql [message #398430 is a reply to message #398325] Thu, 16 April 2009 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT repost the same question every couple of hours just because you don't like the answer.
And find more useful title.

Regards
Michel

[Updated on: Thu, 16 April 2009 04:24]

Report message to a moderator

Re: help in sql [message #398933 is a reply to message #398430] Sun, 19 April 2009 21:56 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I know this is a bad design but will have to deal with it.

need to delete/drop those tables(NEW_****) on a weekly basis?

Whether i have to write a generic code for this?

like selecting all the NEW_**** from user_objects tables and then droping?

or is there a better way out?
Re: help in sql (merged 3) [message #398935 is a reply to message #398325] Sun, 19 April 2009 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I know this is a bad design but will have to deal with it.
Proceed to do so

>like selecting all the NEW_**** from user_objects tables
or USER_TABLES

Re: help in sql (merged 3) [message #398937 is a reply to message #398935] Sun, 19 April 2009 22:05 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
ok will try

thanks
Re: help in sql (merged 3) [message #398961 is a reply to message #398325] Mon, 20 April 2009 00:53 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
First try to build better schema.
If you cannot then try this

begin
 for cur in (select table_name from user_tables where table_name like 'NEW_%')
 loop
  execute immediate
   'drop table ' || cur.table_name || ' cascade constraints purge';
 end loop;
end;


regards,
Delna
Re: help in sql (merged 3) [message #398962 is a reply to message #398961] Mon, 20 April 2009 00:59 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
create or replace procedure PR_KEY_TAB is

  TYPE newtab IS TABLE OF USER_OBJECTS.OBJECT_NAME%TYPE INDEX BY BINARY_INTEGER;

  v_newtab newtab;

  CURSOR C1 IS
    SELECT OBJECT_NAME
      FROM USER_OBJECTS
     WHERE OBJECT_NAME like 'KEY_TABLE_%' AND
           OBJECT_TYPE = 'TABLE';

BEGIN
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT
      INTO v_newtab LIMIT 1000;
       EXIT WHEN C1%NOTFOUND;
    FOR i IN 1 .. v_newtab.COUNT LOOP
    
      EXECUTE IMMEDIATE 'DROP TABLE ' || v_newtab(i) ||'PURGE';
    END LOOP;

  END LOOP;
  DBMS_OUTPUT.put_line('table droped' || C1%ROWCOUNT);
 v_newtab.DELETE;
 

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line(SQLERRM);
  
end PR_new_TAB;


THIS ISN'T DROPING THE TABLES???
Re: help in sql (merged 3) [message #398966 is a reply to message #398325] Mon, 20 April 2009 01:08 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>THIS ISN'T DROPING THE TABLES???
If you do not know the answer, why should we know.
Re: help in sql (merged 3) [message #398976 is a reply to message #398966] Mon, 20 April 2009 01:58 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Provide space before Purge

||'PURGE'; to ||' PURGE';


Thanks
Trivendra

Re: help in sql (merged 3) [message #398989 is a reply to message #398962] Mon, 20 April 2009 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
IF you hadn't added this sorry excuse for an exception handler on
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line(SQLERRM);
then you'd have seen the errors that it raised.

Alternatively, you could have actualy looked at the DBMS_OUTPUT and seen the errors.
Re: help in sql (merged 3) [message #399011 is a reply to message #398989] Mon, 20 April 2009 05:04 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
am not getting ay error but it isnt dropping the tables even after i put a space???
Re: help in sql (merged 3) [message #399012 is a reply to message #399011] Mon, 20 April 2009 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Remove the WHEN OTHERS clause and run it again.
Re: help in sql (merged 3) [message #399020 is a reply to message #399012] Mon, 20 April 2009 05:20 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
where is the WHEN OTHERS in my code
Re: help in sql (merged 3) [message #399030 is a reply to message #398325] Mon, 20 April 2009 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 12407
Registered: September 2008
Location: Rainy Manchester
Senior Member
@JRowbottom - it's a NO_DATA_FOUND, not particularly helpful I agree but not the problem you think it is.

@swas_fly - what does the dbms_output show?
Re: help in sql (merged 3) [message #399031 is a reply to message #399020] Mon, 20 April 2009 05:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Mea Culpa - I misread your code.

Can you run this piece of SQL in SQL*Plus, and copy/paste the results back here:
SELECT OBJECT_NAME
      FROM USER_OBJECTS
     WHERE OBJECT_NAME like 'KEY_TABLE_%' AND
           OBJECT_TYPE = 'TABLE';

and then run this and copy the results back here:
SET SERVEROUTPUT ON SIZE 200000

BEGIN
  pr_key_tab;
END;
/
Previous Topic: Faster deletion
Next Topic: Export -Import
Goto Forum:
  


Current Time: Tue Dec 06 08:26:45 CST 2016

Total time taken to generate the page: 0.17547 seconds