Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error. (Oracle 4.0)
ORA-06502: PL/SQL: numeric or value error. [message #666385] Wed, 01 November 2017 15:35 Go to next message
WhoAmI
Messages: 4
Registered: November 2017
Junior Member
Hello,

One of my procedure is resulting in an error as "ORA-06502: PL/SQL: numeric or value error."

create or replace PROCEDURE "P_DATA_TRANSMIT" 
is
    cursor z is SELECT * FROM V_DATA_TRANSMIT;
    z_var   z%rowtype;
    
    l_string VARCHAR2(22767);
    position NUMBER(15);
    k NUMBER(10);
    sql_length NUMBER(10);
    sql_query VARCHAR2(22767);
    query_string VARCHAR2(4000);
    table_name VARCHAR2(4000);
    new_line VARCHAR2(10);
    
BEGIN
  l_string:=lpad(' ',22000,' ');
  execute immediate 'TRUNCATE TABLE DATA_TRANSMIT DROP STORAGE';
  execute immediate 'commit';
  new_line:=chr(13)||chr(13);
  
  open z; fetch z into z_var;
  while z%found loop
    l_string:=z_var.MOD_SQL;
    sql_query:=l_string;
    
    sql_length:=length(sql_query);
    for i in 1..sql_length loop
      if substr(sql_query,k,4)='FROM' or substr(sql_query,k,4)='JOIN' then
        
        table_name:=substr(sql_query,(k+5),instr(substr(sql_query,(k+5)),' ')-1);
        if length(table_name)>2 then
          query_string:='INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE,CUSTOMER_ID) VALUES (:1,:2,:3,:4)';
          
          BEGIN
            execute immediate query_string using z_var.OBJECT_NAME,table_name,z_var.BATCH_TABLE,z_var.CUSTOMER_ID;
            execute immediate 'commit';
           EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line(z_var.CUSTOMER_ID);
          END;
        end if;
      end if;
    end loop;
  fetch z into z_var;
  end loop;
  close z;
  BEGIN
    execute immediate 'INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE) SELECT TABLE_TO,TABLE_FROM,PROC_NAME FROM PROC_TRANSMIT';
    execute immediate 'commit';
  END;
END P_DATA_TRANSMIT;


Can you please help me out am trying to figure it out.
Re: ORA-06502: PL/SQL: numeric or value error. [message #666386 is a reply to message #666385] Wed, 01 November 2017 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

The EXCEPTION handlers is a FATAL bug. It should be removed, deleted, & never used anywhere ever again.
After it is removed, then you'll know the specific line which produces the error.

http://www.orafaq.com/wiki/WHEN_OTHERS

>execute immediate 'INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE) SELECT TABLE_TO,TABLE_FROM,PROC_NAME FROM PROC_TRANSMIT';
> execute immediate 'commit';

There is nothing dynamic about the lines above.
Just do as below.
INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE) SELECT TABLE_TO,TABLE_FROM,PROC_NAME FROM PROC_TRANSMIT;
commit;

BTW - Do you really want to INSERT every row from PROC_TRANSMIT into DATA_TRANSMIT?
Re: ORA-06502: PL/SQL: numeric or value error. [message #666387 is a reply to message #666386] Wed, 01 November 2017 16:03 Go to previous messageGo to next message
WhoAmI
Messages: 4
Registered: November 2017
Junior Member
Thanks for your warm welcome to this forum and for the guidelines.

Yes, I want every row to be inserted from from PROC_TRANSMIT into DATA_TRANSMIT.

When I run the code, I got the errors in the below piece of code


l_string:=z_var.MOD_SQL;
sql_query:=l_string;
Re: ORA-06502: PL/SQL: numeric or value error. [message #666388 is a reply to message #666387] Wed, 01 November 2017 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
What "z_var.MOD_SQL"?
Re: ORA-06502: PL/SQL: numeric or value error. [message #666389 is a reply to message #666388] Wed, 01 November 2017 16:20 Go to previous messageGo to next message
WhoAmI
Messages: 4
Registered: November 2017
Junior Member
MOD_SQL is the column name in V_DATA_TRANSMIT
Re: ORA-06502: PL/SQL: numeric or value error. [message #666390 is a reply to message #666389] Thu, 02 November 2017 02:27 Go to previous messageGo to next message
Littlefoot
Messages: 21240
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is its datatype? Even better, post result of DESC V_DATA_TRANSMIT.
Re: ORA-06502: PL/SQL: numeric or value error. [message #666406 is a reply to message #666390] Thu, 02 November 2017 14:58 Go to previous messageGo to next message
WhoAmI
Messages: 4
Registered: November 2017
Junior Member
The datatype of MOD_SQL is CLOB. It consists of queries

[Updated on: Thu, 02 November 2017 14:59]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error. [message #666407 is a reply to message #666406] Thu, 02 November 2017 15:49 Go to previous messageGo to next message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
WhoAmI wrote on Thu, 02 November 2017 12:58
The datatype of MOD_SQL is CLOB. It consists of queries
You can't assign CLOB to VARCHAR2 variable.

Any "design" that stores SQL or SQL clause in a table column was implemented by an amateur or a fool.
Re: ORA-06502: PL/SQL: numeric or value error. [message #666412 is a reply to message #666407] Fri, 03 November 2017 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 12929
Registered: September 2008
Location: Rainy Manchester
Senior Member
This code is a mess.

You've got variables that serve no useful purpose - l_string - you assign z_var.MOD_SQL to it then assign it to sql_query and then never use it again. Might as well assign sql_query to z_var.MOD_SQL directly. And the initial assignment of l_string to spaces is completely pointless - you overwrite that value before checking the variable again.
And you don't need sql_query either - you can just use z_var.MOD_SQL through out.
You've got variables you use despite never assigning a value to them - k - It's always null. And substr(<something>, null) will always return null. Looks like you should be using i instead of k.
As already pointed out the exception handler is really stupid - knowing the customer_id is far less important than knowing what the error was.
There's nothing remotely dynamic about your insert statments. Changing the values between executions (and that's the only thing that changes) is what normal static inserts do all the time. And commit is obviously not dynamic. Only the truncate needs to be dynamic.
You don't need a commit after truncate - truncate is DDL not DML and so auto-commits.
A max length varchar2 is 32767 not 22767, but CLOB can be longer than that so you need to be using clob variables not max length varchar2.
new_line and position aren't being used anywhere.
Also intermittent commits are unnecessary, one at the end should be all that's needed.
Finally - a for loop would be better than the while loop you are using.

[Updated on: Fri, 03 November 2017 04:59]

Report message to a moderator

Re: ORA-06502: PL/SQL: numeric or value error. [message #666418 is a reply to message #666412] Fri, 03 November 2017 07:35 Go to previous message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
Storing SQL in table columns is to software what using Entity, Attribute, Value data model is to data storage.
Previous Topic: to_char date conversion not working
Next Topic: TABLE PARTITIONING
Goto Forum:
  


Current Time: Thu Nov 23 21:50:07 CST 2017

Total time taken to generate the page: 0.01373 seconds