I had posted this question earlier but did not get any reply - may be I was not very clear with my query.

We are on UDB V8.1 ( on AIX)
Is it possible for me to create a stored proc (A) in schema X and access
tables/views in schema Y ? How do I do this ? Because currently I am building the SP from within the development center where I can only specify the schema to which the SP should belong.

I am not doing the bind step explicitly.

Can someone tell me what are the steps to be followed in such a case ?
Also if there is a way to specify the function path while building the SQL stored proc.

Any help is greatly appreciated.

All you need to do is place the schema as the prefix to the table or view you are calling in the stored proc. ie. TEST.table_a.

You can also create synonyms (called aliases) for the tables under the schema for which the stored proc is associated.

Thanks for your suggestion.

I think I was not very clear with my query.
My objective was to keep the code independent of the schema and then to bind the code against objects in the schema by specifying the schema name as we can do while binding C/COBOL programs.

So is it possible not to explicitly code the schema but specifying it while building the SQL stored procedures ?

Hi,

I don't think you can do that.

But if you script your STPs in SQL and use unqualified object names you can deploy them with an initial SET CURRENT SCHEMA <whatever>, which would do the binding to that schema behind the scenes.

Johann

Hello

To me, rather than exploring how to access tables/views defined in another schema, I would explore how you can possibly execute SP from another schema, being in a particular schema.

I don't think inter-schema execution of SP is possible. When you try do a CALL to an SP in schema Y, being in a SP in schema X, I don't think the runtime would automatically attach schema Y.

To my knowledge, an SP call within an SP can only inherit SCHEMA with which the calling SP is executed.

If my understanding of your problem is not correct, let me know





------------------------------------------------------------------------------

thanks jsander. I have not yet found a solution to my problem and it would seem that it is not possible to bind SQL procs explicitly to objects belonging to a different schema.

As to the solution suggested - if I do a SET CURRENT schema this will mean that the SP will be placed in the schema specified in the SET CURRENT SCHEMA and it will access objects belonging to the same schema.

For k raman -
I was referring to a scenario where I want my SQL stored proc to be in schema X and access objects ( views/tables) belonging to schema Y without explicitly qualifying these with the schema name within the code.
For C/Cobol pgms we can do this where in the bind step we can give the qualifier explicitly.

I agree with the our friend who suggested to use SET CURRENT SCHEMA.. I think it is doable in that way....

-------------------------------------------------------------------------

But obviously if you do a SET CURRENT SCHEMA X. Then the SP and the objects accessed will all belong to schema X. That is , it will not serve the original purpose of having the SP in one schema and the objects referenced within it ( tables/views etc.) in a different schema