#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0

    Question Using truncate command inside stored procedures


    Hi All,

    This is my first post here . Here is my question..

    I have created a table in oracle and inserted data into it. Now i created a stored procedure which truncates the table.

    Here is the command

    TRUNCATE TABLE tablename

    I am working on the same schema so i am the owner for all these objects.

    I am getting the following error when i try to compile the stored procedure from TOAD.


    PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

    := . ( @ % ;
    The symbol ":= was inserted before "TABLE" to continue.


    And i am not an expert in oracle, so kindly be as descriptive as possible .

    Thanks for your help in advance.

    Silverdream
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0
    I tried assigning the truncate statement to a string and performed an EXECUTE IMMEDIATE.

    and it works

    Have another question

    I need to copy huge amount of data from table 'a' to table 'b'.
    Is there any quick way to do something like this?

    Thanks

    silverdream
    Last edited by silverdream; February 2nd, 2004 at 04:35 AM.
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    You can copy data from one table to another by using one of following method, I assume that table structure of both tables are identical and thier datatypes as well or atleast should be compatible.

    Method 1:
    ------------
    as long as you are not tryinh to copy long or bfile datatypes and the database is same, the following command suppose to work. <schemaname> is optional, if you are copying data of one table to another in the same schema.

    SQL>insert into table b (select * from schemaname.a)
    /

    Method 2:
    ------------
    If your databses are different then first create the databse link to make use <Method 1>, all restriction will be applied.

    SQL>create public database link scott.com
    2>connect to scott identified by tiger
    3>using 'ORCL'
    /
    If you dont want to make a public database link, omitt the public keyword from the above command.
    now use the <method1> as follows:

    SQL>insert into table b (select * from a@scott.com)

    OR

    SQL>COPY FROM scott/tiger@ORCL -
    TO scott/tiger@ORCL2 -
    REPLACE b -
    USING SELECT * FROM a
    /
    copy command will replace the table if it is already exist and keep the column names and thier data types identical to source table.


    Regards,


    <copy> command create the table in your schema itself, and keep the datatype of each column identical



    SQL>Copy

IMN logo majestic logo threadwatch logo seochat tools logo