Home » RDBMS Server » Server Administration » Dynamic SQL
Dynamic SQL [message #370359] Thu, 02 September 1999 13:06
bernard
Messages: 3
Registered: September 1999
Junior Member
When I run the following script, I get an error message. I am not sure why???

ERROR MESSAGE:
Warning: Procedure created with compilation errors.

begin bernard; end;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object BATIODB.BERNARD is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Procedure dropped.

SCRIPT:
create or replace procedure bernard as
begin
declare
cursor1 number;
ret_val 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(*) 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;
/
set serveroutput on;
execute bernard;
drop procedure bernard;
exit;
Previous Topic: Launch a package
Next Topic: Stored Function returning a big String!!!
Goto Forum:
  


Current Time: Thu Mar 28 05:00:11 CDT 2024