Hi Tom,
I would like to get the table names dropped and created in the
following code.

SQL> begin
       execute immediate 'drop table a cascade constraints';
       
       -- I would like to have a statement that says that
       -- so and so table got dropped

       -- Can we use trigger specific ora_dict_obj_name event attribute function here???
       
       execute immediate 'create table b(x int,y varchar2(30))';
       
        -- Here also I want to have..

Any workaround for this requirement?

Please do reply.
Bye!!

 

I am trying to automate the recreation of development database from production data using export/import. Below is the part where I drop all users before importing data into development.

#!/sbin/sh
ORACLE_HOME=/usr/oracle; export ORACLE_HOME
ORACLE_SID=testdb; export ORACLE_SID 

$ORACLE_HOME/bin/sqlplus /nolog << EOF > /usr/local/oracle/${ORACLE_SID}/recreate-database.log
connect / as sysdba
set serveroutput on
begin
for x in ( select username from dba_users where username not in ( 'SYS', 'SYSTEM','OUTLN','DBSNMP' ))
loop
execute immediate 'drop user x.username cascade';
end loop;
end;
/
exit
EOF

But it comes out with something like:

SQL> SQL> SQL> Connected.
SQL> SQL> 2 3 4 5 6 7 8 9 User: PERFSTAT
drop user PERFSTAT
begin
*
ERROR at line 1:
ORA-01936: cannot specify owner when creating users or roles
ORA-06512: at line 6

A "drop user <username> cascade" from command line works fine. But I couldn't figure out why this ORA-01936 when doing the same from an EXECUTE IMMEDIATE. I would appreciate if you can let me know what I am missing. 

Thanks
Anandhi

 

Hi Tom,

My database is 10.2.0.4, OS is solaris 10.5. My objective is to handle unicode data dynamically. 
My database character set is WE8ISO8859P1 and national character set is AL16UTF16.

I have one table which was created using following staement
create table deba ( name nvarchar2(2000) );
insert into deba values ('Àðò¸ì Ðîäèí');
commit;

1) I tried to use "execute immediate" for variable nvarchar2. It does not work. Does "execute immediate" support variable of nvarchar2 ?

2) I am trying to use dbms_sql as it supports national character set data. If you see followings , then you can see , that 1st pl/sql unit is working but not the 2nd one.  I am not sure why . Could you please let me know why ?
   
SQL> select * from deba;

NAME
--------------------------------------------------------------------------------
Àðò¸ì Ðîäèí

SQL> DECLARE  
  2    stmt_str nvarchar2(2000);
  3    cur_hdl number;
  4    rows_processed number;
  5    lname nvarchar2(2000);
  6    d number;
  7  BEGIN
  8  cur_hdl := dbms_sql.open_cursor; -- open cursor 
  9  stmt_str := 'INSERT INTO DEBA VALUES (''Àðò¸ì Ðîäèí'')';
 10  dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native); 
 11  
 12  -- describe defines 
 13  --dbms_sql.define_column(cur_hdl, 1, lname, 2000); 
 14  
 15  rows_processed := dbms_sql.execute(cur_hdl); -- execute 
 16  
 17  /*LOOP
 18      d := dbms_sql.fetch_rows(cur_hdl);
 19      dbms_sql.column_value(cur_hdl, 1, lname);
 20   dbms_output.put_line(lname);
 21      exit ;--when d != 10;
 22  END LOOP;*/
 23    
 24  dbms_sql.close_cursor(cur_hdl); -- close cursor
 25  END;
 26  /

PL/SQL procedure successfully completed.                -->>>>> EXECUTING FINE WITH UNICODE DATA

SQL> select * from deba;

NAME
--------------------------------------------------------------------------------
Àðò¸ì Ðîäèí
Àðò¸ì Ðîäèí

SQL> rollback;

Rollback complete.

SQL> select * from deba;

NAME
--------------------------------------------------------------------------------
Àðò¸ì Ðîäèí

SQL>    

Now I am changing the above with one select statement which is "SELECT name FROM deba WHERE where name like '%ðò%'". Please see below :

SQL> select * from deba;

NAME
--------------------------------------------------------------------------------
Àðò¸ì Ðîäèí

SQL> DECLARE  
  2    stmt_str nvarchar2(2000);
  3    cur_hdl number;
  4    rows_processed number;
  5    lname nvarchar2(2000);
  6    d number;
  7  BEGIN
  8  cur_hdl := dbms_sql.open_cursor; -- open cursor 
  9  stmt_str := 'SELECT name FROM deba WHERE where name like ''%ðò%''';
 10  dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native); 
 11  
 12  -- describe defines 
 13  --dbms_sql.define_column(cur_hdl, 1, lname, 2000); 
 14  
 15  rows_processed := dbms_sql.execute(cur_hdl); -- execute 
 16  
 17  /*LOOP
 18      d := dbms_sql.fetch_rows(cur_hdl);
 19      dbms_sql.column_value(cur_hdl, 1, lname);
 20   dbms_output.put_line(lname);
 21      exit ;--when d != 10;
 22  END LOOP;*/
 23    
 24  dbms_sql.close_cursor(cur_hdl); -- close cursor
 25  END;
 26  /
DECLARE
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 10


SQL> 

I have just changed only the sql , but its is unable to parse. Could you please let me know why ?

Thanks
Deba