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

    Thumbs up Unanswered: ORA-06502: PL/SQL: numeric or value error: character to number

    Hi
    My Procedure looks like this.
    I am getting the error:
    ORA-06502: PL/SQL: numeric or value error: character to number

    at i:='copy........';


    And when i use @ ie scott/tiger@ottdev , @ behaviour is different.
    Could any one help me..


    Thanx in advance

    konda



    procedure populatePeopleDept(lno in integer default 1)
    is
    fUSER varchar2(30):= 'ottuser' ;
    fPWD varchar2(10):= 'ottuser';
    fDB varchar2(10):= '@ottdev' ;

    sUSER varchar2(30):= 'ottuser2' ;
    sPWD varchar2(10):= 'ottuser2';
    sDB varchar2(10):= '@ottold';
    i number default 0;

    begin
    DBMS_OUTPUT.ENABLE(1000000);

    i := 'COPY FROM sUSER||'/'||sPWD||sDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    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
    Aug 2002
    Posts
    3

    Re: ORA-06502: PL/SQL: numeric or value error: character to number

    Looks like you declared variable "i" as a number data type and trying to assign a string to it !!.




    Originally posted by kondaoracle
    Hi
    My Procedure looks like this.
    I am getting the error:
    ORA-06502: PL/SQL: numeric or value error: character to number

    at i:='copy........';


    And when i use @ ie scott/tiger@ottdev , @ behaviour is different.
    Could any one help me..


    Thanx in advance

    konda



    procedure populatePeopleDept(lno in integer default 1)
    is
    fUSER varchar2(30):= 'ottuser' ;
    fPWD varchar2(10):= 'ottuser';
    fDB varchar2(10):= '@ottdev' ;

    sUSER varchar2(30):= 'ottuser2' ;
    sPWD varchar2(10):= 'ottuser2';
    sDB varchar2(10):= '@ottold';
    i number default 0;

    begin
    DBMS_OUTPUT.ENABLE(1000000);

    i := 'COPY FROM sUSER||'/'||sPWD||sDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    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;

  3. #3
    Join Date
    Sep 2003
    Posts
    33

    Re: ORA-06502: PL/SQL: numeric or value error: character to number

    Thanks for ur idea!!

    But even if I declare variable 'i' and assign the copy command to it, the same error...

    I think the pbm is with the COPY command usage in procedure.

    Send a sample procedure which uses COPY command.

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

    look at the string like you posted it:
    i := 'COPY FROM sUSER||'/'||sPWD||sDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    I think it should be:
    i := 'COPY FROM '||sUSER||'/'||sPWD||'sDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    Also, besides creating the string nothing happens in the procedure.Maybe you forgot to add execute immediate ?

    Your question about the @ i don't understand.

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

  5. #5
    Join Date
    Sep 2003
    Location
    Hanoi- Vietnam
    Posts
    27

    Re: ORA-06502: PL/SQL: numeric or value error: character to number

    Why do you try with clear command. For example:

    i := 'COPY FROM ottuser2/ottuser2@ottold sDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    Cheer !
    Originally posted by kondaoracle
    Thanks for ur idea!!

    But even if I declare variable 'i' and assign the copy command to it, the same error...

    I think the pbm is with the COPY command usage in procedure.

    Send a sample procedure which uses COPY command.

  6. #6
    Join Date
    Sep 2003
    Posts
    33
    I tried like this

    i := 'COPY FROM ottuser2/ottuser2@ottold REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

    No errors..

    but the content is not getting copied to tbldepartment1.

    I want to execute COPY FROM ......USING..... Clause from a procedure...

    Any ideas!

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

    What was the complete code you executed ? Did you issue the execute immediate statement ?
    Was the table created or did it already or still exist ?
    Edwin van Hattem
    OCP DBA / System analyst

  8. #8
    Join Date
    Sep 2003
    Posts
    33
    Hi evan,

    I tried the following code:

    create or replace procedure populatePeopleDept(lno in integer default 1)
    is

    fUSER varchar2(30):= 'ottuser' ;
    fPWD varchar2(10):= 'ottuser';
    fDB varchar2(10):= '@ottdev' ;
    i varchar2(1000):='0';

    begin
    DBMS_OUTPUT.ENABLE(1000000);

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

    dbms_output.put_line('i '||i);

    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;--end of function
    /

    When i execute using 'exec populatePeopleDept' following message:

    i COPY FROM fUSER/fPWD fDB REPLACE TBLDEPARTMENT1 USING select
    DEPARTMENTID,DEPTNAME from tbldepartment1

    How to execute it?
    i didnt give any execute immediate statement...

    give ur comments pls.

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

    Your code is not correct ! It shows the names of the variables as values instead of showing the values of the variables (just look at the output generated by the procedure which contains the names of the variables and not ottuser/ottuser@ottdev)

    Change the line :
    i:= 'COPY FROM fUSER/fPWD fDB REPLACE TBLDEPARTMENT1 USING select DEPARTMENTID,DEPTNAME from tbldepartment1';

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

    To execute the statement simpley add the line :

    execute immediate i;

    Hope that helpes
    Edwin van Hattem
    OCP DBA / System analyst

  10. #10
    Join Date
    Sep 2003
    Posts
    33
    Thanx for ur reply

    But i am getting error:

    UN_HANDELED ORA-00900: invalid SQL statement

  11. #11
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    i'm sorry, but the COPY command seems to be SQL*Plus only, so it cannot be used in PL/SQL.
    You will have to use the 'create table as select... to do this.
    Be sure to grant create table to the user executing the procedure directly.

    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
  •