bz_tr.lst

oracle9i+j2ee开发aq oracle9i+j2ee开发aq oracle9i+j2ee开发aq

SQL> CONNECT system/&master_pass as sysdba;
Connected.
SQL> @@bz_tr_priv.sql &hr_pass
SQL> REM script name: bz_tr_priv.sql
SQL> REM Grants execute on dbms_tranform to bz
SQL> REM Grant select on hr.employees to bz and bz_adm
SQL> REM
SQL> REM
SQL> REM
SQL> REM version: 9.0.1
SQL> define hr_pass = &1
SQL> 
SQL> grant execute on dbms_transform to bz;

Grant succeeded.

SQL> 
SQL> connect hr/&hr_pass
Connected.
SQL> grant select on hr.employees to bz;

Grant succeeded.

SQL> grant select on hr.employees to bz_adm;

Grant succeeded.

SQL> 
SQL> 
SQL> CONNECT bz_adm/&bz_adm_pass
Connected.
SQL> @@bz_tr_mapping.sql
SQL> REM This function verifies if the employee_id is correct and fills in the
SQL> REM missing information
SQL> 
SQL> 
SQL> REM =======================================================
SQL> REM cleanup section
SQL> REM =======================================================
SQL> 
SQL> execute dbms_transform.drop_transformation -
> 	  (schema =>'BZ_ADM',name =>'BZCARDVERIFY');
BEGIN dbms_transform.drop_transformation  	(schema =>'BZ_ADM',name =>'BZCARDVERIFY'); END;

*
ERROR at line 1:
ORA-24185: Transformation BZ_ADM.BZCARDVERIFY, does not exist 
ORA-06512: at "SYS.DBMS_TRANSFORM", line 37 
ORA-06512: at "SYS.DBMS_TRANSFORM", line 229 
ORA-06512: at line 1 


SQL> 
SQL> drop function FnVerifyBzCardOrder;

Function dropped.

SQL> 
SQL> create or replace function FnVerifyBzCardOrder
  2  	     (ord BZ_ADM.BZCARDORDER_TYP)
  3  return BZCARDORDER_TYP is
  4  	     newOrder BZ_ADM.BZCARDORDER_TYP;
  5  BEGIN
  6  	     newOrder := BzCardOrder_typ(null, null, null, 'NORMAL');
  7  	     select employee_id, first_name, last_name into newOrder.employee_id,
  8  		     newOrder.first_name, newOrder.last_name from hr.employees e
  9  	     where (ord.employee_id is null or ord.employee_id=e.employee_id)
 10  	     and (ord.first_name is null or ord.first_name = e.first_name)
 11  	     and (ord.last_name is null or ord.last_name = e.last_name) and rownum<2;
 12  	     exception
 13  		     when NO_DATA_FOUND then
 14  			     newOrder := ord;
 15  	     return newOrder;
 16  END;
 17  /

Function created.

SQL> 
SQL> show errors;
No errors.
SQL> 
SQL> execute dbms_transform.create_transformation ( -
> 	  schema => 'BZ_ADM', name  => 'BZCARDVERIFY', -
> 	  from_schema => 'BZ_ADM', to_schema => 'BZ_ADM', -
> 	  from_type => 'BZCARDORDER_TYP', to_type => 'BZCARDORDER_TYP',-
> 	  transformation => 'BZ_ADM.FNVERIFYBZCARDORDER(SOURCE.USER_DATA)');

PL/SQL procedure successfully completed.

SQL> 
SQL> show errors;
No errors.
SQL> grant execute on FnVerifyBzCardOrder to bz;

Grant succeeded.

SQL> 
SQL> spool off