Home » SQL & PL/SQL » SQL & PL/SQL » oracle error (merged)
oracle error (merged) [message #343083] Tue, 26 August 2008 04:15 Go to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
Hi,
I am getting the error when i created the following procedure

create or replace procedure abc(n number)
is
dummy number;
word varchar2(20);
begin
dummy:=n;
select to_char(to_date(dummy,'J'),'JSP') into word from dual;
end;

error
non numeric character was found where numeric was expected

however if I use n in place of dummy in select query it execute.
Whts the reason
oracle error [message #343085 is a reply to message #343083] Tue, 26 August 2008 04:17 Go to previous messageGo to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
Hi,
I am getting the error when i created the following procedure

create or replace procedure abc(n number)
is
dummy number;
word varchar2(20);
begin
dummy:=n;
select to_char(to_date(dummy,'J'),'JSP') into word from dual;
end;

error
non numeric character was found where numeric was expected

however if I use n in place of dummy in select query it execute.
Whts the reason
Re: oracle error [message #343086 is a reply to message #343083] Tue, 26 August 2008 04:20 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Don't used Oracle reserved word as a variable.



SQL> select dummy from dual;

D
-
X


X- numeric?? Hope you have got the answer




Regards,
Oli
Re: oracle error [message #343091 is a reply to message #343083] Tue, 26 August 2008 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's odd - I get the same effect.
PL/SQL procedure successfully completed.

SQL> create or replace procedure test_0067(p_num in number) is
  2    dummy   number;
  3    word    varchar2(20);
  4  begin
  5    dummy:=p_num;
  6    begin
  7      select to_char(to_date(dummy,'J'),'JSP') into word from dual;
  8    exception
  9      when others then
 10         raise_application_error(-20001,p_num||' - '||sqlerrm);
 11    end;    
 12     
 13    dbms_output.put_line(word||' - '||to_char(p_num));
 14  end;
 15  /

Procedure created.

SQL> 
SQL> exec test_0067(1);
BEGIN test_0067(1); END;

*
ERROR at line 1:
ORA-20001: 1 - ORA-01858: a non-numeric character was found where a nume
expected
ORA-06512: at "DEV.TEST_0067", line 10
ORA-06512: at line 1


SQL> 
SQL> create or replace procedure test_0067(p_num in number) is
  2    dummy   number;
  3    word    varchar2(20);
  4  begin
  5    dummy:=p_num;
  6    begin
  7      select to_char(to_date(p_num,'J'),'JSP') into word from dual;
  8    exception
  9      when others then
 10         raise_application_error(-20001,p_num||' - '||sqlerrm);
 11    end;    
 12     
 13    dbms_output.put_line(word||' - '||to_char(p_num));
 14  end;
 15  /

Procedure created.

SQL> 
SQL> exec test_0067(1);
ONE - 1

PL/SQL procedure successfully completed.

Additionally, if you just remove the SELECT from the code, it works:
SQL> 
SQL> create or replace procedure test_0067(p_num in number) is
  2    dummy   number;
  3    word    varchar2(20);
  4  begin
  5    dummy:=p_num;
  6    begin
  7      word := to_char(to_date(dummy,'J'),'JSP');
  8    exception
  9      when others then
 10         raise_application_error(-20001,p_num||' - '||sqlerrm);
 11    end;    
 12     
 13    dbms_output.put_line(word||' - '||to_char(p_num));
 14  end;
 15  /

Procedure created.

SQL> 
SQL> exec test_0067(1);
ONE - 1

PL/SQL procedure successfully completed.

SQL> 


{fixed missing CODE tag}

[Updated on: Tue, 26 August 2008 04:39]

Report message to a moderator

Re: oracle error [message #343092 is a reply to message #343091] Tue, 26 August 2008 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good spot Olivia - I totally missed that.
Re: oracle error [message #343095 is a reply to message #343092] Tue, 26 August 2008 04:53 Go to previous messageGo to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
thanks all.........
Re: oracle error [message #343096 is a reply to message #343085] Tue, 26 August 2008 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do not multi post.

Thread closed
Re: oracle error [message #343100 is a reply to message #343083] Tue, 26 August 2008 05:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Its because: Dummy is a reserved word. When you use it in SELECT..FROM DUAL

It will return 'X' which is a character datatype.




SQL> SELECT DUMMY FROM DUAL;

D
-
X



SQL> /
select to_char(to_date('X','J'),'JSP')  from dual
                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Quote:

word := to_char(to_date(dummy,'J'),'JSP')



Of course, you can!


Regards,
Oli







Re: oracle error [message #343102 is a reply to message #343092] Tue, 26 August 2008 05:05 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Tue, 26 August 2008 04:40
Good spot Olivia - I totally missed that.



Thanks... Razz
Re: oracle error [message #343106 is a reply to message #343100] Tue, 26 August 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Its because: Dummy is a reserved word.

No it is because DUMMY is the column of the table you select:
SQL> create table t (col varchar2(10));

Table created.

SQL> insert into t values ('X');

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace procedure test_0067(p_num in number) is
  2    COL     number;
  3    word    varchar2(20);
  4  begin
  5    COL := p_num;
  6    begin
  7      select to_char(to_date(COL,'J'),'JSP') into word from T;
  8      exception
  9        when others then
 10          raise_application_error(-20001,p_num||' - '||sqlerrm);
 11      end;    
 12      
 13    dbms_output.put_line(word||' - '||to_char(p_num));
 14  end;
 15  /

Procedure created.

SQL> exec test_0067(1);
BEGIN test_0067(1); END;

*
ERROR at line 1:
ORA-20001: 1 - ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MICHEL.TEST_0067", line 10
ORA-06512: at line 1

Regards
Michel
Re: oracle error [message #343110 is a reply to message #343106] Tue, 26 August 2008 05:32 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for pointing out the mistake.
Sorry, DUMMY is not a Reseved word.But you can't use Dummy as an variable if you are using in SELECT ... FROM DUAL..Its a pseudo column (Dummy)

It will give 'X' only as I stated earlier. Which is a type of varchar.

select to_char(to_date('X','J'),'JSP')  from dual
                       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


  1* CREATE TABLE T ( DUMMY VARCHAR2(1))
SQL> /

Table created.



SQL> CREATE TABLE Q ( INSERT VARCHAR2(3));
CREATE TABLE Q ( INSERT VARCHAR2(3))
                 *
ERROR at line 1:
ORA-00904: : invalid identifier



Moreover,
Its not wise to use something as a variable in a select clause which is a again a column in that table. It will treat it as a column rather than a variable.

Regards,
Oli

[Updated on: Tue, 26 August 2008 05:36]

Report message to a moderator

Previous Topic: UTI_FILE: Error
Next Topic: multiple grouping required vertically from single table
Goto Forum:
  


Current Time: Fri Dec 09 17:20:37 CST 2016

Total time taken to generate the page: 0.24552 seconds