Solved

Need help on sql

Posted on 2014-10-31
3
360 Views
Last Modified: 2014-11-03
Hi ,

I have an column called comments in COMMENTS table which is an CLOB column, I need to change the column data type from CLOB to VARCHAR2. Below is the code

DROP TABLE COMM_INTERIM;

CREATE TABLE COMM_INTERIM
   (      COMMENTARY_ID NUMBER ,
      SUBJECT VARCHAR2(400 CHAR) ,
      COMMENTS varchar2(4000 char),
      CREATED_BY NUMBER ,
      LAST_MODIFIED_DATE DATE,
      CREATED_DATE DATE ,
      LAST_MODIFIED_BY NUMBER ,
      VISIBILITY_TYPE NUMBER ,
      DEAL_GROUP_ID NUMBER ,
      LOCK_VERSION NUMBER ,
      VISIBILITY_RESOURCE_ID NUMBER ,
      DEAL_ID NUMBER,
      TRANCHE_ID NUMBER,
      ORGANIZATION1_ID NUMBER,
      ORGANIZATION1_MEMBER_ID NUMBER,
      ORGANIZATION2_ID NUMBER,
      ORGANIZATION2_MEMBER_ID NUMBER,
      ORGANIZATION3_ID NUMBER,
      ORGANIZATION3_MEMBER_ID NUMBER,
      EXTERNAL_ID VARCHAR2(100 CHAR),
      LEVEL_OF_ACCESS_ID NUMBER
   );


 Declare
col_mapping varchar2 (2000);
BEGIN
--  map all the columns in the interim table to the original table
col_mapping :=

                'COMMENTARY_ID COMMENTARY_ID , ' ||
    'SUBJECT SUBJECT , ' ||
    'DBMS_LOB.SUBSTR(COMMENTS,1,4000) COMMENTS , '||
    'CREATED_BY CREATED_BY, LAST_MODIFIED_DATE LAST_MODIFIED_DATE,'
    ||'CREATED_DATE CREATED_DATE , LAST_MODIFIED_BY LAST_MODIFIED_BY ,VISIBILITY_TYPE VISIBILITY_TYPE ,'    
    ||'DEAL_GROUP_ID DEAL_GROUP_ID,LOCK_VERSION LOCK_VERSION,VISIBILITY_RESOURCE_ID VISIBILITY_RESOURCE_ID,'||
    'DEAL_ID DEAL_ID,TRANCHE_ID TRANCHE_ID,ORGANIZATION1_ID ORGANIZATION1_ID,ORGANIZATION1_MEMBER_ID ORGANIZATION1_MEMBER_ID,'
    ||'ORGANIZATION2_ID ORGANIZATION2_ID,ORGANIZATION2_MEMBER_ID ORGANIZATION2_MEMBER_ID,ORGANIZATION3_ID ORGANIZATION3_ID,'
    ||'ORGANIZATION3_MEMBER_ID ORGANIZATION3_MEMBER_ID,EXTERNAL_ID EXTERNAL_ID,LEVEL_OF_ACCESS_ID LEVEL_OF_ACCESS_ID';
   
               
dbms_redefinition.start_redef_table('SCOTT', 'COMMENTS', 'COMM_INTERIM', col_mapping);
END;
/


declare
error_count pls_integer := 0;
BEGIN
  dbms_redefinition.copy_table_dependents('SCOTT', 'COMMENTS', 'COMM_INTERIM',
                                          1, true,true,true,false,
                                          error_count);

  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/


exec  dbms_redefinition.finish_redef_table('SCOTT', 'COMMENTS', 'COMM_INTERIM');
/

After running the above script file geting the below error, any help is really appreciated.

Table created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1809
ORA-06512: at line 4
0
Comment
Question by:sam_2012
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40416291
It is here:
exec  dbms_redefinition.finish_redef_table('SCOTT', 'COMMENTS', 'COMM_INTERIM');
 /

Remove the '/' on the next line.

You only need a trailing '/' on pl/sql blocks.

The EXEC isn't a pl/sql block.

So the '/' is executing the last command in the buffer which was the exec.  Since the finish had already happened, there is nothing to finish the second time.
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40416345
Just as an FYI, EXEC is really to be thought of as a macro.  If you issue:

exec  dbms_redefinition.finish_redef_table('SCOTT', 'COMMENTS', 'COMM_INTERIM');

What actually is run is:

begin dbms_redefinition.finish_redef_table('SCOTT', 'COMMENTS', 'COMM_INTERIM'); end;
/
0
 

Author Closing Comment

by:sam_2012
ID: 40420060
awesome
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question