Forum Stats

  • 3,784,156 Users
  • 2,254,897 Discussions
  • 7,880,713 Comments

Discussions

Categories

procedure access from one schema to another

2»

Answers

  • User_ISH63
    User_ISH63 Member Posts: 50 Blue Ribbon

    Yeah I Already Given Grants

    · Share on TwitterShare on Facebook
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Not sure what is wrong. Everything looks ok to me. Just this simple query should executed from TEST schema should get what you want

    SELECT DBMS_METADATA.GET_DDL('PROCEDURE',TEST_PROCEDURE','SCOTT') FROM DUAL

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Aug 2, 2013 10:51AM 
    842086 wrote:

Yeah i tried that also but still im getting same error

    You could NOT possibly have tried a copy of what Karthick posted since the code he posted has a SYNTAX error; missing a quote.

    SELECT DBMS_METADATA.GET_DDL('PROCEDURE',TEST_PROCEDURE','SCOTT') FROM DUAL

    Don't TELL us what you tried.

    SHOW us what you tried. Cut & Paste an EXACT copy of what statement you executed and an EXACT copy of the result that you get.

    · Share on TwitterShare on Facebook
  • User_ISH63
    User_ISH63 Member Posts: 50 Blue Ribbon

    Hi,

         I Tried the Query (SELECT DBMS_METADATA.GET_DDL('PROCEDURE','TEST_PROCEDURE','SCOTT') FROM DUAL) in SCOTT schema i got the output as like as Eg :1.1. If I tried the same query in TEST schema mean i got error like as Eg: 1.2.

    I need the output like as Eg 1.1 while running Query in TEST Schema

    Eg 1.2

    ORA-31603: object "TEST_PROCEDURE" of type PROCEDURE not found in schema "SCOTT"
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
    ORA-06512: at "SYS.DBMS_METADATA", line 3912
    ORA-06512: at "SYS.DBMS_METADATA", line 5678
    ORA-06512: at line 1
    31603. 00000 -  "object \"%s\" of type %s not found in schema \"%s\""
    *Cause:    The specified object was not found in the database.
    *Action:   Correct the object specification and try the call again.

    Eg :1.1

      

    DBMS_METADATA.GET_DDL('PROCEDURE','TEST_PROCEDURE','SCOTT')

           CREATE OR REPLACE PROCEDURE   "SCOTT"."TEST_PROCEDURE" AS
         BEGIN
         DBMS_OUTPUT.PUT_LINE('TEST_GRANTS');
         END;
         /
        

    DBMS_METADATA.GET_DDL('PROCEDURE','TEST_PROCEDURE','SCOTT')


      CREATE OR REPLACE PROCEDURE "SCOTT"."TEST_PROCEDURE" AS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('TEST_GRANTS');
    END;
    /

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown 
         I Tried the Query (SELECT DBMS_METADATA.GET_DDL('PROCEDURE','TEST_PROCEDURE','SCOTT') FROM DUAL) in SCOTT schema i got the output as like as Eg :1.1. If I tried the same query in TEST schema mean i got error like as Eg: 1.2.
I need the output like as Eg 1.1 while running Query in TEST Schema

    That is the correct behaviour if user TEST is not a privileged user.

    See the docs for DBMS_METADATA in the Packages and Types reference

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_metada.htm

    Security Model

    The object views of the Oracle metadata model implement security as follows:

  Nonprivileged users can see the metadata of only their own objects.

    You either need to make TEST a privileged user or you need to have a privilege user create a function to return the results that you want.

    · Share on TwitterShare on Facebook
  • Mohan1312
    Mohan1312 Member Posts: 69
    edited Aug 8, 2013 3:26AM

    Hi,

    try this

    select t from

    (select line,'Create or replace '||TEXT t from (select Line,TEXT from all_source where type = 'PROCEDURE' and name = 'PROCEDURE_NAME' and Owner = 'SCOTT') where Line = 1

    union

    select line,TEXT t from (select Line ,TEXT from all_source where type = 'PROCEDURE' and name = 'PROCEDURE_NAME' and Owner = 'SCOTT') where Line != 1 )

    order by line;

    Regards,

    Mohan.

    · Share on TwitterShare on Facebook
  • Raunaq
    Raunaq Member Posts: 458

    You can also query in DBA_SOURCE

    · Share on TwitterShare on Facebook
  • Ario
    Ario Member Posts: 6

    Hi,

    I have slo faced the same problem.This is because of missing priviledges. I

    Please do this to resolve.

    1.grant SELECT_CATALOG_ROLE to TEST;  -- You can do this from SYSTEM or any user having DBA role

    2.log out from TEST if you are already logged in. Then log in again.

    3.select dbms_metadata.GET_DDL('PROCEDURE','TEST_PROCEDURE','SCOTT') FROM DUAL;

    · Share on TwitterShare on Facebook
  • Ario
    Ario Member Posts: 6

    Did you try which was suggested.

    Please let me know the results.

    · Share on TwitterShare on Facebook
  • User_ISH63
    User_ISH63 Member Posts: 50 Blue Ribbon

    Hi Mohan,

    Thank You This Query has been worked Perfectly. Thank You So Much

    · Share on TwitterShare on Facebook
This discussion has been closed.