Solved

Need help on sql

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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 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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now