Solved

Need help on sql

Posted on 2014-10-31
3
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 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