Use Ant to insert data to Oracle

Source: Internet
Author: User
[1] Remove the statements in the script including commit, "setserveroutoff", "setserverouton", "setescapeon", and "exec". [2] Remove"

[1] Remove the statements in the script including commit, "setserverout off", "set serverout on", "set escape on", and "exec". [2] Remove "/

[1] Remove the statements in the script including commit, "setserverout off", "set serverout on", "set escape on", and "exec ".

[2] Remove "/"

[3] it is best to use "separator" + "line feed" for statement separators, for example:

Alter table xxx ADD (temp_advtoolbar CLOB )! UPDATE xxx SET temp_advtoolbar = advtoolbar!

[4] determine whether the data exists before inserting configuration data

When we want to insert a configuration data in the WCMConfig table, we need to first determine whether the configuration parameter exists. Note that the code is "end ;!" End, such:

Declare v_count number (10 );

Begin

SELECT count (*) into v_count FROM xxx WHERE CKey = 'kms _ UPLOAD_FILE_MAX_SIZE ';

If (v_count <= 0 or v_count is null) then

Insert into xxx (configid, ctype, ckey, cvalue, cdesc) select max (configid) + 6291456, 'kms _ UPLOAD_FILE_MAX_SIZE ', '123 ', 'The maximum file size that can be uploaded during batch upload, in k' from xxx;

Update yyyset nextid = 0 where tablename = 'xxx ';

End if;

End ;!

[5] to add a column to a table, you must first determine whether the Column exists.

When we want to add the LEAFFLAG column to the xxx table, we need to first determine whether the xxx table already has the LEAFFLAG column. Note that the code is "end ;!" The Code is as follows:

-- Add the field 2011.10.12 by liwei for whether to allow the creation of sub-scenarios (that is, whether it is a leaf node)

Declare v_count number (10 );

Begin

SELECT count (*) into v_count FROM cols

WHERE table_name = 'xxx' and column_name = 'leafflag ';

If (v_count <= 0 or v_count is null) then

Execute immediate ('alter table xxx add LeafFlag number default 0 not null ');

End if;

End ;!

Note:

When we need to use the begin end statement block, only DML (data operation language, such as insert, delete, update, and select) can be used in the begin statement block ), for example, add the configuration parameter code:

Declare v_count number (10 );

Begin

SELECT count (*) into v_count FROM xxx WHERE CKey = 'kms _ UPLOAD_FILE_MAX_SIZE ';

If (v_count <= 0 or v_count is null) then

Insert into xxx (configid, ctype, ckey, cvalue, cdesc) select max (configid) + 6291456, 'kms _ UPLOAD_FILE_MAX_SIZE ', '123 ', 'The maximum file size that can be uploaded during batch upload, in k' from wcmconfig;

Update yyy set nextid = 0 where tablename = 'xxx ';

End if;

End ;!

When adding columns, we may use the alter operation to add a column to a table. In this case, we need to use dynamic SQL (that is, execute immediate) for execution. Because the begin end statement block can only execute DML language, if you want to execute DDL (alter, create, and so on) language, you need to use dynamic SQL. For example:

Declare v_count number (10 );

Begin

SELECT count (*) into v_count FROM cols

WHERE table_name = 'xxx' and column_name = 'leafflag ';

If (v_count <= 0 or v_count is null) then

Execute immediate ('alter table xxx add LeafFlag number default 0 not null ');

End if;

End ;!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.