Home » RDBMS Server » Server Administration » Dynamic SQL
Dynamic SQL [message #370362] Fri, 03 September 1999 09:19 Go to next message
bernard
Messages: 3
Registered: September 1999
Junior Member
I get the following error messages when I run this script:
ERROR MESSAGE:
Procedure created.

begin bernard; end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "BATIODB.BERNARD", line 22
ORA-06512: at line 1

SCRIPT:
create or replace procedure bernard as
begin
declare
cursor1 number;
ret_value number;
c_owner dba_segments.owner%TYPE;
c_segment_name dba_segments.segment_name%TYPE;
countme number;
c_test varchar2(50);
cursor dba_segment_crs is
select owner, segment_name
from dba_segments
where owner not in ('SYSTEM','SYS')
and rownum and segment_type = 'TABLE';
begin
cursor1 := dbms_sql.open_cursor;
open dba_segment_crs;
loop
fetch dba_segment_crs into c_owner, c_segment_name;
exit when dba_segment_crs%notfound;
c_test := 'select count(distinct(substr(rowid,1,8))) from '||c_owner||'.'||c_segment_name;
dbms_sql.parse( cursor1, c_test, dbms_sql.v7 );
dbms_sql.define_column( cursor1, 1, countme );
ret_value := dbms_sql.execute_and_fetch(cursor1);
dbms_sql.column_value( cursor1, 1, countme );
dbms_output.put_line( c_test||'---'||countme );
end loop;
dbms_sql.close_cursor( cursor1 );
end;
end;
/
Re: Dynamic SQL [message #370366 is a reply to message #370362] Sun, 05 September 1999 22:13 Go to previous messageGo to next message
Amit Chauhan
Messages: 74
Registered: July 1999
Member
Hi,
I couldnt check the whole proc, I was in a hurry. But I can tell u the reason why this error comes. It comes whe you try to insert a value in a variable which is greater than its size. E.g if variable x is varchar2(3), then this sentence will give the PL/SQL numeric error :
x := 'Amit';

And the other reason is when u try to enter alphanumeric character into a number variable.

Thanks
Amit
Re: Dynamic SQL [message #370367 is a reply to message #370362] Mon, 06 September 1999 04:30 Go to previous message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
You'll kick yourself! -- You've defined c_test as VARCHAR2(50), but the fixed-string part of the SQL statement is 47 characters before you add the owner and table name. If you redefine c_test as, say, VARCHAR2(500) it should work properly.

The result of coding on a friday, I guess.

Previous Topic: Dynamic SQL memory problems
Next Topic: Data Import from MS Server 6.4 to Oracle8?
Goto Forum:
  


Current Time: Thu Mar 28 03:28:30 CDT 2024