Toad World® Forums

Creating script in Schema Browser of user

While connected as sys to a Oracle database and using the schema browser under Toad for ORacle12.6.0.53 I tried to generate a script of a user and nothing happens. If you highlight a user in the schema browser and look at the script tab on the right nothing appears. This all worked in Oracle for Toad 12.1.1… I also found this stopped functioning in 12.5…

Are the scripts still being created for other object types? If so, maybe there is some SQL error happening in the background. Turn on spool SQL to see, and paste the output here.

If other object types are broken too, try shutting down Toad and then look in your User Files folder for a file called ToadActions.dat. Rename it to ToadActions_backup or something, and then try the script again in Toad. I haven’t seen it much in recent versions, but in older versions, if this file got corrupt, it would prevent certain things in Toad from working.


Session: SYS@CMSDEV_DBS

Timestamp: 16:30:04.981

Select * from DBA_users u

where U.USERNAME = :OneObjectName

order by username

:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’


Session: SYS@CMSDEV_DBS

Timestamp: 16:30:05.008

select name,

case

when spare4 is not null and password is not null then spare4 || ‘;’ || password

when spare4 is not null then spare4

else password

end password

from sys.user$

where type# = 1

and NAME = :OneObjectName

:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’


Session: SYS@CMSDEV_DBS

Timestamp: 16:30:05.015

Select o.object_type, pp.*

from dba_objects o,

(select owner, privilege, table_name, null column_name, grantee, grantable

from sys.DBA_TAB_PRIVS

where owner = :OneObjectName

UNION ALL

select owner, privilege, table_name, column_name, grantee, grantable

from sys.DBA_COL_PRIVS

where owner = :OneObjectName

) pp

where o.owner = pp.owner

and o.object_name = pp.table_name

and o.object_type in (‘TABLE’, ‘VIEW’, ‘SEQUENCE’, ‘PACKAGE’, ‘PROCEDURE’, ‘FUNCTION’, ‘DIRECTORY’, ‘LIBRARY’, ‘TYPE’, ‘OPERATOR’, ‘INDEXTYPE’, ‘EDITION’, ‘JOB’, ‘PROGRAM’, ‘SCHEDULE’)

order by pp.owner, pp.table_name, pp.grantee, pp.grantable, pp.privilege

:OneObjectName(VARCHAR[7],IN)=‘ABISWAS’

Other objects in schema browser seem to work ok. Only users seem to be failing.

Oh, I see what’s going on. You have your script tab set to show you the privileges on that user’s object to other users.

To switch it to show the script to create the user, click the first toolbar button on the script tab. Then, in the dialog that appears, click on the “Users” tab, and uncheck “Grants on the user’s objects to other users/roles”.

The “create user” and “grants on user’s objects” scripts don’t make sense to show together because if the script creates the user, then the user won’t have any objects to grant yet.

Hi John,

Thanks . this worked fine perfect.