Re: PL/SQL Question ...

If you are creating these packages from scripts using SQL*Plus then you can use SQL*Plus variables to define user names in one central place, for example:

global.sql:
define TABLE_OWNER = FC_RTE_A1

package.sql:
@global
create or replace package my_pack as

   g_col1 &&TABLE_OWNER..tablename.col1%TYPE; end;
...

Note the double dot after TABLE_OWNER. You can reference TABLE_OWNER as many times as you like by preceding it by &&.

I hope this is on the right tracks and you are not trying to choose the table owner at run-time. (If you are then you must use dynamic SQL or exit out to a shell script and swap a few synonyms before going back to the package. Changing a synonym will make the package invalid, but the database will automaticlally recompile it when you next execute it. However you cannot make any changes that would invalidate a package _while it is executing_ or it will abort with an error 'existing state of packages has been discarded').

JJ
narenn_at_my-deja.com wrote in message <7kluj4$gv7$1_at_nnrp1.deja.com>...
>I am stuck with this for a while. How do you declare
>something like this with the owner being a variable ?
>
>var-name owner.table.column%type;
>
>I wrote a procedure with above datatype. I don;t want to
>hardcode the table owner name in the script. How do i
>make the owner a variable ?
>
>In other words my problem is this. The procedure i need to
>compile access table's which are not owned by the user id
>i use to compile the procedure but has read permission to
>it. The only way i can access the table is like
>this, owner.table_name. eg : FC_RTE_A1.T_FIKA_KNOWNAFIID
>where FC_RTE_A1 is the owner and T_FIKA_KNOWNASFIID is
>the table name. I don;t want to hard code FC_RTE_A1 ie
>owner of the table because the ownership will change
>between various enviroments i need to compile this script
>in!
>
>Any ideas ? I looked through PL/SQL manual from Oracle. I
>could not find any way to do this ?
>
>Please e-mail me at narendra.nathmal_at_fmr.com if you
>have any suggestions.
>
>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Jun 23 1999 - 18:27:52 CDT