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
Solved

Need help on sql

Posted on 2014-10-31
3
348 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 76

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 34

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

840 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