This discussion is archived
11 Replies Latest reply: Dec 13, 2012 3:02 PM by Justin Cave RSS

drop and recreate table using dynamic sql

choti Newbie
Currently Being Moderated
The following procedure has to drop the table and recreate it. currently i am getting the dbms_output of the query. if i run query alone it is working fine. but through procedure it is not working. Please help me

PROCEdure emp_backup is
sql_txt varchar2(10000);
begin
     begin
     EXECUTE IMMEDIATE 'drop table emp_backup ' ;
     exception
     when others then
     null;

     sql_txt:= 'CREATE TABLE emp_backup as ' ||
                 'select * from emp  '||
                 'where dep_no=10 ' ;
     dbms_output.put_line(sql_txt);
     EXECUTE IMMEDIATE sql_txt;
Exception
when others then
Null;

End;
  • 1. Re: drop and recreate table using dynamic sql
    Warren Tolentino Expert
    Currently Being Moderated
    PROCEdure emp_backup is
    sql_txt varchar2(10000);
    begin
      begin
       EXECUTE IMMEDIATE 'drop table emp_backup ' ;
      exception
        when others then
          null;
      end;  --    <-- missing this end
     
      sql_txt:= 'CREATE TABLE emp_backup as ' ||
                'select * from emp  '||
             'where dep_no=10 ' ;
      dbms_output.put_line(sql_txt);
      EXECUTE IMMEDIATE sql_txt;
      
    Exception
      when others then
       Null;
    End;
  • 2. Re: drop and recreate table using dynamic sql
    choti Newbie
    Currently Being Moderated
    sTILL NOT ABLE TO INSERT
  • 3. Re: drop and recreate table using dynamic sql
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) Remove the exception handler. A WHEN OTHERS THEN NULL exception handler is almost always an error. In this case, your DDL statement is presumably failing but you've told Oracle that you don't care if it fails, you don't want to know what the exception was, you simply want to throw it away. In order to debug the problem, you would need to remove the exception handler and tell us what exception you are getting.

    One possibility is that you have the CREATE TABLE privilege granted to you through a role rather than as a direct grant. Within a definer's rights stored procedure, you can only use privileges that is granted directly to the owner of the procedure. You can't use privileges that are granted through a role. You can confirm that to be the case by disabling roles in your interactive session and running the CREATE TABLE statement. In SQL*Plus, do
    SQL> set role none;
    SQL> create table ...
    If that fails, then the problem is that you only have the CREATE TABLE privilege through a role. You could either ask the DBA to grant you this privilege directly or you could change your stored procedure to be an invoker's rights stored procedure.

    2) It makes little sense to programmatically drop and recreate a table in the first place. It makes even less sense to create a table that simply duplicates a subset of data from an existing table. That's why views and materialized views exist.

    3) It makes little sense to put the CREATE TABLE statement inside the exception handler for the DROP TABLE statement. This means that you are only creating the table if your attempt to drop it fails. If the table exists before you call this procedure, you drop it. If it doesn't exist, you create it. It seems highly unlikely that if it makes sense to do this in the first place that it would make sense for you not to know whether the table exists if the procedure completes successfully.

    4) I'm guessing that you are using 11g since you are allocating a 10,000 byte string that you are passing to EXECUTE IMMEDIATE-- 10g doesn't allow strings that long to be passed to EXECUTE IMMEDIATE. It's always helpful to tell us exactly what version of Oracle you're using rather than making us guess, though, particularly where there are potential differences in behavior.

    Justin
  • 4. Re: drop and recreate table using dynamic sql
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Whenever you're tempted to write dynamic SQL, ask yourself (or someone on this forum) is it's really necessary.
    choti wrote:
    The following procedure has to drop the table and recreate it.
    Why do you think it's necessary in this case? Why do you need to drop the table? Why can't you just TRUNCATE the table to remove all the data? You need dyanmic SQL to do a TURNCATE TABLE in PL/SQL, so that's a good reason.
    At any rate, once you have an empty table, why do you need dynamic SQL to populate it? What's wrong with a regular INSERT statement?
    currently i am getting the dbms_output of the query. if i run query alone it is working fine. but through procedure it is not working. Please help me
    You can help yourself by letting PL/SQL hlep you. Get rid of the EXCEPTION section. All it's doing is keeping you from seeing error messqages that are designed to help you solve the problem.
    PROCEdure emp_backup is
    sql_txt varchar2(10000);
    begin
         begin
         EXECUTE IMMEDIATE 'drop table emp_backup ' ;
         exception
         when others then
         null;
    
         sql_txt:= 'CREATE TABLE emp_backup as ' ||
                     'select * from emp  '||
                     'where dep_no=10 ' ;
         dbms_output.put_line(sql_txt);
         EXECUTE IMMEDIATE sql_txt;
    Exception
    when others then
    Null;
    
    End;
    Does the procedure owner have the necessary privileges? Remember, roles don't count in AUTHID DEFINER stored procedures, such as this one. All the necessary privileges must be granted either to the procedure owner, or to PUBLIC.
  • 5. Re: drop and recreate table using dynamic sql
    choti Newbie
    Currently Being Moderated
    Hi Frank,

    We are building warehouse. So we have full loads and incremental load. Currently i am doing full load. I need to test every time. I am clear on the requirements. My requirement is just drop the table and recreate it.

    i am taking filtered data from source where dept no=10 and put it in one table. We are developing from scratch so its new implementation. Lot of testing required. boss told me to drop table and recreate it.

    we don't want to maintain different files for create table and script. everything should be maintained inside the procedure. As per your suggestion. right if the table doesnot exist it will throw error first time. it make sense truncate the table. But i need to create table as per select statment. for that reason i need to drop the table and create the table again.

    Removing exception means first exception or second one.
  • 6. Re: drop and recreate table using dynamic sql
    choti Newbie
    Currently Being Moderated
    I am getting following error ORA-01031: insufficient privileges

    What is the command to grant the privileges to this
  • 7. Re: drop and recreate table using dynamic sql
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    choti wrote:
    Hi Frank,

    We are building warehouse. So we have full loads and incremental load. Currently i am doing full load. I need to test every time. I am clear on the requirements. My requirement is just drop the table and recreate it.
    Don't be ridic! It seems like you hve trouble distuinguishing a requirement from a possibole way to fulfill a requirement. For example, getting rid of all the old data be a requirement. Dropping the table is one way to meet that requirement, TRUNCATing the table is another, probably better way to do do the same thing.
    i am taking filtered data from source where dept no=10 and put it in one table. We are developing from scratch so its new implementation. Lot of testing required. boss told me to drop table and recreate it.
    If you want to do things your boss's way, ask your boss how to do it. Not everybody on this forum is always going to agree with you boss. Most of the people who answer questions here want to help you find good ways to do your job.
    we don't want to maintain different files for create table and script.
    Why not?
    everything should be maintained inside the procedure. As per your suggestion. right if the table doesnot exist it will throw error first time. it make sense truncate the table. But i need to create table as per select statment. for that reason i need to drop the table and create the table again.

    Removing exception means first exception or second one.
    Both of them. "EXCEPTION WHEN OTHERS THEN NULL;" is kind of like saying, "My daughter is supposed to be home from school by 4:00. If she's not home by 4:00, don't tell me about it, and if you know where she is or why, don't say anything."
  • 8. Re: drop and recreate table using dynamic sql
    Justin Cave Oracle ACE
    Currently Being Moderated
    choti wrote:
    I am getting following error ORA-01031: insufficient privileges

    What is the command to grant the privileges to this
    GRANT CREATE TABLE TO <<user that owns the procedure>>
    Seconding Frank, though, and echoing my earlier comment, dropping and recreating tables does not make sense. It makes sense to truncate a table if you're testing a load. If you're building a data warehouse, it makes sense to drop and recreate a partition if you are re-running a load for a particular date and you have daily partitions. Dropping and recreating a table in a stored procedure is not a reasonable way to test a system (nor is it a reasonable way to deal with the change control issues that will inevitably occur when you need to modify the structure of a table).

    Justin
  • 9. Re: drop and recreate table using dynamic sql
    choti Newbie
    Currently Being Moderated
    Thank you.
  • 10. Re: drop and recreate table using dynamic sql
    choti Newbie
    Currently Being Moderated
    Thanks Frank. I understand the problem
  • 11. Re: drop and recreate table using dynamic sql
    Justin Cave Oracle ACE
    Currently Being Moderated
    "WHEN EXCEPTION THEN NULL;" is kind of like saying, "My daughter is supposed to be home from school by 4:00. If she's not home by 4:00, don't tell me about it, and if you know where she is or why, don't say anything."
    I will have to remember that one! Though when I shamelessly steal it, I will probably choose to use coming home by curfew rather than coming home from school.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points