Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Location
    India
    Posts
    5

    Unanswered: Dynamically transferdata from table in one tablespace to other using stored procedure

    Hi all

    Being a pl/sql beginner i would hope people out might be help me out.

    Oracle 8 on Win NT 4.0

    What i want to do is pass a varible to the an oracle stored procedure. The variable will hold the tablespace name. I would like to reference to this variable in my insert statement shown below. I would like to copy the all data from one table from one tablespace to a table similar in structure in a different tabblespace. I would like to accomplish by using stored procedure.

    the following is the code i am trying to compile.

    PROCEDURE Test (PRM_TSPN IN VARCHAR2)
    IS
    BEGIN
    'INSERT INTO' || PRM_TSPN|| '.TABLE_NAME (SELECT * FROM TABLE_NAME);
    END;

    but i get the following error

    (1):PLS-00103: Encountered the symbol "|" when expecting one of the following:
    (1):
    (1): := . ( @ % ;
    (2):PLS-00103: Encountered the symbol "END"

    can some please help me. I tried to use the dbms_sql package but to no avail.

    Warm Regards

    Vineeth V. Shetty

  2. #2
    Join Date
    Apr 2003
    Posts
    4
    PROCEDURE Test (PRM_TSPN IN VARCHAR2,tbl_name varchar2)
    IS
    TABLE_NAME varchar2(30):= tbl_name;
    BEGIN
    'INSERT INTO ' || PRM_TSPN|| .'||TABLE_NAME||' (SELECT * FROM '||TABLE_NAME||');'
    END;

Posting Permissions

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