I need to create a stored procedure in my schema, using Toad. However, it gives me the error "Insufficient privileges." Again, if I connect as System and prefix the schema name to the table (schema.tablename), it says the table does not exist. Please help.
In order to create a stored procedure in your schema, you need to have the CREATE PROCEDURE system privilege. This can be verified by querying DBA_SYS_PRIVS for your user. Furthermore, if the stored procedure references objects, the user that owns the stored procedure must have the appropriate object privileges as well. For instance, if the stored procedure inserts a row of data into the SCOTT.EMP table, the user that owns the stored procedure must have been granted INSERT on this table. And the grant cannot be through a role. You will need to grant this object privilege directly to the user.
Dig Deeper on Oracle stored procedures
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.