Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Unanswered: Error using Execute Immediate

    Hi


    I have run the folllowing procedure.. When i run this proc, error is generated:

    UN_HANDELED ORA-00900: invalid SQL statement

    Could any one correct it?

    create or replace procedure populatePeopleDept(lno in integer default 1)
    is
    fUSER varchar2(30):= 'ottuser2' ;
    fPWD varchar2(10):= 'ottuser2';
    fDB varchar2(10):= '@ottold' ;
    sql_stmt varchar2(1000):='';
    begin

    sql_stmt:= 'COPY FROM '||fUSER||'/'||fPWD||fDB||' REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    execute immediate sql_stmt;

    exception when no_data_found then
    dbms_output.put_line('NO DATA FOUND'||sqlerrm);
    when others then
    dbms_output.put_line('UN_HANDELED '||sqlerrm);
    end;

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    COPY is SQL*Plus only. Use the create table as select ... for this purpose
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    33
    Hi evan

    Could u explain me in detail what to do......
    Last edited by kondaoracle; 10-01-03 at 17:55.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    your problem is that COPY can only be used in SQLPLUS and not in PL/SQL, sorry.

    Another way to copy a table and it's data is to use the command
    'create table tabname as select col_list from table'.

    You can do that in PL/SQL using execute immediate.

    But you'll want to know if the table already exists, so you'll have to check all_tables or dba_tables.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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