Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: How to Select From different schema's table in stored procedure?

    The Sql is

    Select items
    From schema1.Table a, Schema2.Table b
    Where a.item_id = b.itme_id

    In SQL Plus is Run OK!
    But in My StoredProcedure is Wrong!

    I try to write sql in my storedprocedure is

    Select items
    From schema1.Table@ora1 a, Schema2.Table@ora2 a
    Where ....

    But it also wrong

    the procedure's error is "Must Declare identifier schema1.table"
    how to select different schema's table in Stored Procedure?
    Thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    In the procedure, you are using dblinks that you are not using in
    sql... Ensure that you have the appropriate grants on the schemas
    on the different databases.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Thanks a lot!
    I use "Grant Select any table to MyUser" is OK!
    But I want Grant only one Schema's all table to myuser, not all Schema's table, how to do?

    I don't want "grant select on usr1.table1 to usr2"
    and "grant select on usr1.table2 to usr2"
    ...
    "grant select on usr1.tablen to usr2"

  4. #4
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    If you are refering to two table in two schemas in the same database you do not have to use a DB link. Instead you can creat a public or private synonym on the other schema table and use the synonym name in the select clause. The synonym shuold have proper privilages granted to the users who execute the procedure.

    Cheers,
    Suren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •