Discussions
Categories
- 382.3K All Categories
- 2.1K Data
- 208 Big Data Appliance
- 1.9K Data Science
- 447.9K Databases
- 220.9K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 522 MySQL Community Space
- 465 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 490 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.1K SQL & PL/SQL
- 21K SQL Developer
- 293.2K Development
- 7 Developer Projects
- 128 Programming Languages
- 289.9K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 144 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 198 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 260 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 226 Portuguese
procedure access from one schema to another
Dear All,
I have created one procedure in SCOTT schema. just i need to see that same procedure information to TEST schema. how its possible.
if im using SELECT DBMS_METADATA.GET_DDL('PROCEDURE','PROC_NAME','SCOTT') FROM DUAL in scott schema mean i can see that full procedure information,
like wise i need to see in TEST schema mean what command i need to use.
Thanks ALL...
Answers
-
Run the same thing from test user. Just make sure you have access to the procedure in scott from your test user.
-
Yeah I tried that one but its through the error like
ORA-31603: object "scott.PROCEDURE_NAME" 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
-
Hi,
1) in the error message pasted, scott is lower case, the parameter may be case sensitive -- so try the same thing with upper-case 'SCOTT'
2) be sure you spell everything correctly -- e.g. in one place in the post you typed PRCEDURE instead of PROCEDURE
3) if still getting an error, this could be due to lack of privileges (or because some privs are granted via a role, which leads to problems with PL/SQL code). Experiment with grants (try granting EXECUTE on the given procedure, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE -- just don't forget to revoke the privileges when done).
Best regards,
Nikolay
-
Show us the code that you executed
-
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','SCOTT.TEST_PROCEDURE','SCOTT') FROM DUAL
-
842086 wrote: SELECT DBMS_METADATA.GET_DDL('PROCEDURE','SCOTT.TEST_PROCEDURE','SCOTT') FROM DUALJust pass the procedure name. Like this
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',TEST_PROCEDURE','SCOTT') FROM DUAL
-
Yeah i tried that also but still im getting same error
-
What is that error? From TEST user run the below code and tell what is the output you get.
select * from all_objects where object_name = 'TEST_PROCEDURE' and owner = 'SCOTT';
-
Yeah Karthick thanks ... I run this mean i can get owner,object_name,subject_name.created_date details and all.
how can i see the particular procedure full information like
CREATE OR REPLACE PROCEDURE "SCOTT"."TEST_PROCEDURE" AS
BEGIN
DBMS_OUTPUT.PUT_LINE('TEST_GRANTS');
END;
-
before executing try to give permission to the procedure to test schema using grant and then try.