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

    Unanswered: Export and Import

    Hi

    I m using Oracle 9.2.0 on Windows NT.

    I have two tables (RemoteA, RemoteB) on Remote Server which is at Different Location.

    I have to import these two tables data into my Local Server Tables(LocalA, LocalB). This importing should be done daily at 5.00am automatically.


    Any Ideas...

  2. #2
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    You can do that using OEM.
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  3. #3
    Join Date
    Sep 2003
    Posts
    33

    PLS-00201: identifier 'OTTUSER.TBLADDRESSPEOPLE' must be declared

    Hi

    I have following query:

    create or replace procedure myProc

    as

    begin

    select PF.DEPARTMENTID, PF.DEPTNAME FROM OTTUSER.TBLDEPARTMENT@ottdev PF, OTTUSER2.TBLDEPARTMENT@ottdev OTT
    where PF.DEPARTMENTID= OTT.DEPARTMENTID
    AND (NVL(PF.DEPTNAME, ' ') <> NVL(OTT.DEPARTNAME, ' '))
    AND PF.DEPARTMENTID = '10';

    end;


    When i execute this procedure following error:

    PLS-00201: identifier 'OTTUSER.TBLADDRESSPEOPLE' must be declared


    Can any body refine the procedure?

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

    First of all, when selecting inside a procedure you have to put into something. So it should be select kol into local variable from table.
    Then, you have to be aware of the fact that this select can only handle one record. So when the select returns more then one row, the procedure will raise an exception.

    Besides it all, i do not see the use of 'OTTUSER.TBLADDRESSPEOPLE' in your procedure. What is the code ?

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Posts
    33
    Hi Evan,

    My Code returns only one record.

    SQL> create or replace procedure proc
    2 as
    3 no number;
    4 begin
    5 select count(*) into no from ottuser.TBLDEPARTMENT@OTTDEV;
    6 dbms_output.put_line('sorry');
    7 end;
    8 /

    Warning: Procedure created with compilation errors.

    SQL> show err;
    Errors for PROCEDURE PROC:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/4 PL/SQL: SQL Statement ignored
    5/33 PLS-00201: identifier 'OTTUSER.TBLDEPARTMENT' must be declared



    cud u please rectify it...

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

    is '@OTTDEV' a database link ? Does the creatorof the procedure have access to the remote database ? And to the remote table ?
    Edwin van Hattem
    OCP DBA / System analyst

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

    Actually at present OTTDEV is a schema ...In this two users exists OTTUSER and OTTUSER2.

    I have a table tblDepartment in OTTUSER and OTTUSER2 of OTTDEV schema.

    In my procedure i am selecting the data from tblDepartment of OTTUSER @ OTTDEV.

    For that when i use following procedure syntax, gives the error:
    Hope u cud understand now.....If any clarifications, query me....

    I wanna execute this procedure by using ottuser.tbldepartment@ottdev.
    No DBLINKS specified. But when i execute 'select count(*) from ottuser.tbldepartment' gives the no of rows...but when i put in procedure it wont?

    is there any restriction of not to use this in pl/sql


    create or replace procedure proc
    as
    begin
    select count(*) from ottuser.TBLDEPARTMENT@OTTDEV;
    dbms_output.put_line('sorry');
    end;
    /

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

    try dropping the part @ottdev
    Edwin van Hattem
    OCP DBA / System analyst

  9. #9
    Join Date
    Sep 2003
    Posts
    33
    same error

  10. #10
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    I don't know if you understand the pl/sql concept or you're trying some real hi-fi stuff here.

    dot notation is to refer an object in a schema. Schema is a user who owns (some) object(s).

    so if I do....

    Code:
    
    appdev@NICK817.TARRY.LOCAL> create or replace procedure test
      2  as
      3  nr number;
      4  begin
      5  select count(*) into nr from scott.emp;
      6  dbms_output.put_line('Aw cmon it aint that hard');
      7  end;
      8  /
    
    Procedure created.

    So I call scott(schema= because he owns object)and then query his table. I don't see the relevence of @OTTDEV... I have a feeling that you're moving from sql server towards oracle...
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  11. #11
    Join Date
    Sep 2003
    Posts
    33
    Thanx tarry


    My Problem was solved.

  12. #12
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    Great! Thanx for sharing.
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

Posting Permissions

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