Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: PL/SQL: Copying Tables Question

    I need to copy a table from one schema to an existing table in another schema (same database). I'm using PL/SQL to accomplish this. I tried to use the COPY command, but it doesn't seem like PL/SQL will execute it. I get an invalid SQL error when it hits that line.

    Will I need to code a row by row copy? Or is there another way to do this? I'll be replacing existing data in the destination table from data from the source table.

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You do not provide OS name or version.
    You do not provide Oracle version.
    You do not provide actual code.
    You do not provide actual error.

    Based upon what details do you expect any meaningful answer?

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    Are you not able to do a

    insert into schema1.table
    select * from schema2.table ???

    (Of course provided you have logged in as a user with the appropriate rights to both schemas).

  4. #4
    Join Date
    Mar 2004
    Posts
    14
    Quote Originally Posted by arvindram
    Are you not able to do a

    insert into schema1.table
    select * from schema2.table ???

    (Of course provided you have logged in as a user with the appropriate rights to both schemas).
    DOh! I bet that's all I need to do. All my searching for a fancy copy command and I just needed something simpler. I'm a noob.

    Thanks for the help!

  5. #5
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by anacedent
    You do not provide OS name or version.
    You do not provide Oracle version.
    You do not provide actual code.
    You do not provide actual error.

    Based upon what details do you expect any meaningful answer?

    You're On Your Own (YOYO)!
    C'mon anacedent. I am not known for being nice (ask lower(SS659) and billy)
    but this is kind of enough information to give Nancy some hints.

    Oracle runs pretty much the same on all OS's.
    Oracle version no problem too unless Nancy runs Oracle 7 (for bulk processing)
    We all know there's no such thing as COPY in PL/SQL
    So it matters not what kind of error she is getting.


    Nancy, in a nutshell
    You need to:
    DELETE from schema1.table
    INSERT into schema2.table with subselect from schema1.table.
    You can do the insert in bulk if the table is big.

    But if this is going to be a routine excercise, then your schema2.table should be a materialized view of a schema1.table.
    It's going to be copying itself at whatever periodicity you tell it to. Ain't it sweet.
    My way or the highway. Yeah

  6. #6
    Join Date
    Mar 2004
    Posts
    14
    Quote Originally Posted by Nocopy
    Nancy, in a nutshell
    You need to:
    DELETE from schema1.table
    INSERT into schema2.table with subselect from schema1.table.
    You can do the insert in bulk if the table is big.

    But if this is going to be a routine excercise, then your schema2.table should be a materialized view of a schema1.table.
    It's going to be copying itself at whatever periodicity you tell it to. Ain't it sweet.
    Thanks! I really appreciate the assist. I never fail to learn from the good folks here.

    And now I'll scurry off to read up on Materialized views... which I first read as Maternalized views, but that's something entirely different.
    Last edited by NancyM; 05-07-04 at 19:18.

Posting Permissions

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