COPY command
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: COPY command

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    Can I use Copy command for inserting rows into a table with the Insert into table select Query.

    My Select Query has bind variables values of which are passed in D2K reports.

    Can I then use COPY command in D2K report in Before Report trigger?

    What is the advantage of COPY command over direct Insert?

    Also, if I can have good examples of COPY command.

    TIA.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think COPY is a SQL*Plus command and probably couldn't be used in PL/SQL.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131

    Smile

    Jeff (My Hero) is once again correct.

    This is an SQL* command and can be used to insert data via a select statement, but I do not know is it will work in PL/SQL either.

    I had to prepare a small doc on the SQL* COPY command for our developers since they can not use import/export to move their data. One of the major benefits with COPY is that you can set the commit level via ARRAYSIZE and COPYCOMMIT.

    I'll E-mail you the doc if you like.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Mr.Hanky
    Jeff (My Hero) is once again correct.


    Two days ago you're ready to shoot me, and now I'm your hero?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131

    Talking

    Hmm, I could blame it on short term memory loss and the 80's.

    I could mention that lots of "heros" get shot by admiring fans

    Or I could just mention that I am always prepared for a good ol' fashioned drive by as any DBA should be, no?

    With much love and admiration
    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Mr. Hanky,


    Pl. send me the docs you have on sshah1001@hotmail.com.

    Thanks,

    Sam
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Aug 2001
    Posts
    134

    Mr.Hanky , I'll E-mail you the doc if you like.

    Please Cc to my address mahmoodshaikh70@hotmail.com
    Thanx

  8. #8
    Join Date
    Aug 2001
    Posts
    184
    me too!

    ocpwannabe@yahoo.com
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  9. #9
    Join Date
    Feb 2001
    Posts
    82
    can you count me 2 anthony_k_velarde@yahoo.com

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131
    Let's just do this the easy way shall we.



    SQL* Plus
    Copy Command


    You must be connected to one of the databases involved. It doesn’t matter which, but you must be connected to either the database containing the data or the database to which you want to copy the data.


    SET ARRAY[SIZE] May be abbreviated to array.
    This parameter sets the number of rows fetched at one time. The default is 15. The allowed range is from 1 to 5000.

    SET COPYC[OMMIT] May be abbreviated to copyc.
    The copycommit setting works in conjunction with the arraysize setting. The arraysize setting controls the number of rows in a batch. The copycommit setting controls how many batches are copied before committing. The number or rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT.

    The default is 0 this means that by default COPY will only commit after the copy command is finished. This is a potential rollback disaster, please adjust this accordingly. You would not want to forget this on a very large table.

    Example
    SET ARRAYSIZE 15
    SET COPYCOMMIT 10
    COPY TO my_id@database name-
    INSERT my_id.table_name-
    USING SELECT * from table_name;

    Since the arraysize setting is 15 and the copycommit setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).

    COPY
    The COPY command allows you to use SQL* Plus as a conduit for transferring data between two Oracle databases:

    COPY {FROM connection | TO connection}
    {APPEND | CREATE | INSERT | REPLACE}
    destination_table [(column_list)]
    USING select_statement

    Note; When using the COPY command In SQL*Plus you must code a hyphen (-) after each line to continue to the next line. The last line will end with a semi-colon ";"

    COPY is the command.

    FROM/TO
    If you are connected to the source database , use the TO option to specify the destination database. If you are connected to the target database, use the FROM option to specify the source of the data.

    Connection
    Is the login information to use when connection to the other database.
    Your_id@database name

    APP[END]
    Causes SQL*Plus to insert the copied rows into the destination table, creating it first if necessary.

    CRE[ATE]
    Causes SQL*Plus to copy the data only if the destination table is a new table.
    If the destination table already exists, the COPY command will abort.

    INSERT
    Causes SQL*Plus to insert the copied rows into the destination table only if it already exists. If the destination table is a new table, the COPY command will abort.

    REP[LACE]
    Causes SQL*Plus to drop the destination table if it currently exists. A new table is then created, and the data is copied.

    Destination_table
    Is the name of the table to which you want to copy.

    Column_list
    Specifies column names to use when the COPY command creates a new destination table. This is a comma delimited list, and the number of column names must match the number of columns in the SELECT statement.

    Select_statement
    Is a SELECT statement that returns the data you want to COPY.


    * Excerpts from Oracle SQL*Plus the definitive guide by Jonathen Gennick,
    O’Reilly Press.



    [Edited by Mr.Hanky on 01-18-2002 at 08:52 AM]
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width