There is a utility in dbms_utility (dbmsutil.sql creates this package) which
will resolve a name
I've copied part of the package spec;
procedure name_resolve(name in varchar2, context in number,
schema out varchar2, part1 out varchar2, part2 out varchar2,
dblink out varchar2, part1_type out number, object_number out number);
- Resolve the given name. Do synonym translation if necessary. Do
- authorization checking.
- Input arguments:
- name
- The name of the object. This can be of the form [[a.]b.]c[@d]
- where a,b,c are SQL identifier and d is a dblink. No syntax
- checking is performed on the dblink. If a dblink is specified,
- of the name resolves to something with a dblink, then object
- is not resolved, but the schema, part1, part2 and dblink out
- arguments are filled in. a,b and c may be delimted identifiers,
- and may contain NLS characters (single and multi-byte).
- context
- Not currently used, must be set to 1 for future compatibility.
- Output arguments:
- schema
- The schema of the object. If no schema is specified in 'name'
- then the schema is determined by resolving the name.
- part1
- The first part of the name. The type of this name is specified
- part1_type (synonym, procedure or package).
- part2
- If this is non-null, then this is a procedure name within the
- package indicated by part1.
- dblink
- If this is non-null then a database link was either specified
- as part of 'name' or 'name' was a synonym which resolved to
- something with a database link. In this later case, part1_type
- will indicate a synonym.
- part1_type
- The type of part1 is
- 5 - synonym
- 7 - procedure (top level)
- 8 - function (top level)
- 9 - package
- If a synonym, it means that 'name' is a synonym that translats
- to something with a database link. In this case, if further
- name translation is desired, then you must call the
- dbms_utility.name_resolve procedure on this remote node.
- object_number
- If non-null then 'name' was successfully resolved and this is the
- object number which it resolved to.
- Exceptions:
- All errors are handled by raising exceptions. A wide variety of
- exceptions are possible, based on the various syntax error that
- are possible when specifying object names.
esiyuri_at_my-dejanews.com wrote in message
<6vl17a$5eu$1_at_nnrp1.dejanews.com>...
>In article <6vk7n7$49i$1_at_nnrp1.dejanews.com>,
> jflipse_at_spacestar.com wrote:
>> I am able to truncate tables within a stored procedure by passing the
>> table name (mytable) to a procedure which performs the truncate
>> :
>> :
>> I have no problems when the table name is explicitly called out, but
>> run into problems when the table name is a synonym. I've tried to
>> bind the synonym to a variable and pass this to the procedure...
>
>John,
>
>the basic problem is that you can not use "truncate table" on a synonym.
To
>get around this you could always convert the synonym name into the actual
>table name by checking ALL_SYNONYMS. Bear in mind that there may be
further
>complications if the synonym "points" to another synonym, or a view, or a
>table that is owned by another user.
>
>If the truncate is being done in a stored procedure you will also have to
>ensure that the owner of the procedure has the required privs to truncate
the
>table. Unless the table owned by the same user, these need to be granted
>directly rather than through a role.
>
>--
>Regards
>Yuri McPhedran
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Oct 09 1998 - 08:43:30 CDT
