Get SYS Objects Access?
Oct 26, 2010What is the minimum access level ( or grant) needed be able to alter user defined (non sys or system) Stored Procedure in residing in SYS schema.
Example - User A need to alter SP sys.myStoredProc.
ADVERTISEMENT
SQL & PL/SQL :: Access Attributes Of Objects Of Nested Table In Member Function Of Oracle Database
Nov 17, 2011I have created two types and a list of the first type:
create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/
I have now just created the two types as follows:
create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/
Ok, I've created the tables (I don't know if it's necessary to point out my problem)
CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/
And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:
Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS
[Code]....
But I don't get the right way, it doesn't work
SQL & PL/SQL :: DBA User Access To Other User Objects
Dec 6, 2010when a dba user'AA' try to access other user'BB' object it gives error pls-00201 identifier 'BB.function_name' must be declared. However the procedure of BB user are being accessible.
View 17 Replies View RelatedApplication Express :: ORA-24247 / Network Access Denied By Access Control List
Apr 5, 2013When I invoke SOAP Web-Service using APEX_WEB_SERVICE.MAKE_REQUEST, then I'm able to get response from web-service. However all German character are replaced by JUNK data. However data is coming fine when I test web-services using SOAP UI.
I tried to invoke web-service using UTL_HTTP. However when I use UTL_HTTP, then I'm getting following error.
ORA-24247: network access denied by access control list
<li> Why German characters are replaced by Junk data while invoking web-service from APEX, while it's working fine from SOAP UI
<li> When I can access web-service successfully using APEX_WEB_SERVICE, then why it's throwing ORA-24247 error when I call using UTL_HTTP?
DB: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
APEX: 4.0.2.00.07
Web-Server: EPG
SELECT * FROM NLS_DATABASE_PARAMETERS;
[code]...
ORA-24247 / Network Access Denied By Access Control List (ACL)
Dec 12, 2012I am having oracle database version 11.2.0.3.0 standard edition, where one of my users requirement is that he wanted to send mails from oracle database but he is getting below error
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "HF_REPORTING.SEND_MAIL", line 12
ORA-06512: at line 18
is it possible to send mails from oracle database in STANDARD Edition?
SQL & PL/SQL :: ORA-24247 / Network Access Denied By Access Control List (ACL)
Nov 15, 2011While Running the proc,it's raising the below error.
Error report:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "LISTER_SHIFT.SOLN_GENERIC_SENDMAIL", line 33
ORA-06512: at line 1
[code]....
PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full
Oct 5, 2013Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
SQL & PL/SQL :: Roles Given To Objects
Feb 27, 2011I have granted execute, select, insert, update, delete privilege on objects to roles. Now i want to check status. we have around 2000 objects.
Require out put should be like this.
Object_name Object_Type Role_Granted
----------- ----------- ------------
Table1 Table ABC_ROLE
Table2 Table CDE_ROLE
PROCE1 PROCEDURE PROC_ROLE
Func1 FUNCTION FUN_ROLE
SQL & PL/SQL :: Constraints On Objects?
Mar 5, 2012Ive created a scenario to what im trying to achieve here so ignore how its set-up attribute wise.
CREATE TYPE object_obj AS OBJECT (
obj_id NUMBER,
NAME VARCHAR2(20),
age NUMBER) NOT FINAL;
/
CREATE TYPE object_ext UNDER object_obj (
course_name VARCHAR2(20));
/
CREATE TYPE object_ext2 UNDER object_obj (
location VARCHAR2(20);
/
CREATE TABLE object_tab OF object_obj(obj_id PRIMARY KEY);
/
Now for the course_name i need to make sure it can only be on of these three values ('Science','Math','English'). I can't find a way to apply this constraint without making a table. But then that creates another issue of how to insert the data from the main supertype (object_obj) and i only want the object_tab table and view the subtypes via a select query.
PL/SQL :: Backup All DB Objects
Jun 19, 2013I need a script to generate the source of every db object to an sql file with object name as file name, if it is a table then that_table.sql if view then that_view.sql like that, but my schema has around 2k objects. i am using toad but as the number of objects are more i am not able to generate source script files for all objects.
View 3 Replies View RelatedPL/SQL :: What Are Objects Using PGA Memory
Feb 5, 20131)collections uses pga memory ,does globle tem table used in sp also uses PGA memory.
2)and does type of table of object type uses pga memory.
Moving Objects From One Tablespace To Another?
Feb 4, 2013I need to prepare script to move all objects from one tbs to another tbs. Should I move all the objects individually using "alter table" Command. I got all the objects information using "DBA_SEGMENTS" view.
I have more number of tables,indexes in that tablespaces.
I can not use exp for tablespace backup.
SQL & PL/SQL :: View To See Invalid Objects
Apr 20, 2011i want create view to select all invalid objects in database.So i create this one:
CREATE OR REPLACE FORCE VIEW INVALID_OBJECTS_DETAILS
(
DETAILS
)
AS
SELECT DISTINCT a.owner || ', ' || a.object_name
FROM dba_objects a, dba_source b
WHERE a.owner = b.owner
AND a.object_name = b.name
AND a.object_type = b.TYPE
AND a.status != 'VALID'
AND b.text NOT LIKE '%@%';
But I want only select invalid objects without a database link .
SQL & PL/SQL :: Find Dependent Objects?
Feb 9, 2011Say i have a Procedure or package or any other stored subprogram, I need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause
View 4 Replies View RelatedSQL & PL/SQL :: What May Be Disadvantages Of Schema Objects
Jul 13, 2010I was wondering whether we all think of disadvantages or is it we always love the concept and use them..so recently i started out to think what may be the disadvantages of schema objects.
For example Packages has lot of advantages like Information hiding, Scope of varaibles declared in spec, dependency crisis etc, then i read somewhere that if a package is referenced then the entire package will be downloaded in the memory.
So my question is what if the package is very large would it accomdate in the memory without any leaks , i am assuming that its like LEAST RECENTLY USED mechanism. i have a question for you about disadvantages
1. Packages
2. Cursors
3. Collections, includes Associative arrays, VARRAY, nested table
4. materialized views
5. views
6. indexes
7. having more than 100 columns in a table need to spilt it or to keep in the same table.
any object can have its own disadvantages.
SQL & PL/SQL :: Compile Invalid Objects
Jul 16, 2012I am Modifying a table structure, so dependent objects(triggers,packages etc) are getting invalid. So i thought of compiling those invalid objects which are related with the modified table. I used below query to get the invalid objects,
select obj.object_name,obj.object_type from user_objects obj,all_dependencies dep
where referenced_name='DEPT' and obj.object_name=dep.name and dep.owner='SCOTT' and obj.status='INVALID'
Q1)What is wrong with this query, sometimes it works sometime it doesn't.?
Q2)All_dependencies : does this view show all dependent object on a table even the Invalid one's?
Q3) Is there any alternative to find the dependent invalid objects or even dependent object on a table ?
SQL & PL/SQL :: Recreate Objects Of One Schema Into Another
Apr 8, 2010I got the below error while executing the procedure. What the procedure doing is recreate the objects of one schema into another. After recreating some objects it throws the error.
code is...
1 CREATE OR REPLACE PROCEDURE SCHEMA_IMPORT6
2 AUTHID CURRENT_USER
3 AS
4 V_Objid NUMBER;
5 V_Xml XMLTYPE;
6 V_Xml_Ind XMLTYPE;
[code]....
SQL & PL/SQL :: Objects Containing Expression Cannot Be Used As Assignment
Apr 4, 2012I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...
CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT (
strName VARCHAR2(100),
--
CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT,
--
MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2,
MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2)
) NOT FINAL;
[code]....
How can I do this parentObject.getChildObject().setChildFunction()?
SQL & PL/SQL :: Query On Invalid Objects
Jul 10, 2013Quote:by default the job runs once every 24 hours.
When you see LAST_ANALYZED being days, weeks, or months in the past do not be alarmed.
If/when the data in a table does not change, then the statistics do not need to change.
Oracle collects new statistics when enough of the data (about 10%) has changed.
Like above statement for DBMS_STATS job, is there a automatic job that runs every day to compile invalid objects for schema or we need to compile it manually ?
PL/SQL :: How To Export All The Objects To Ddl File
Nov 15, 2012how to export all the objects to ddl file.
View 11 Replies View RelatedObjects :: During Compilation - Says Does Not Exist?
Mar 21, 2013I listed invalid object, but when compile object, say that not exist.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' AND OBJECT_NAME ='PCK_PIR_NFEL_MILLENIUM' AND OWNER='PIRAMIDE'
OBJECT_NAME OBJECT_TYPE
--------------------------------- --------------------
PCK_PIR_NFEL_MILLENIUM PACKAGE
SQL> ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE;
ALTER PACKAGE PCK_PIR_NFEL_MILLENIUM COMPILE
*
ERROR at line 1:
ORA-04043: object PCK_PIR_NFEL_MILLENIUM does not exist
SQL & PL/SQL :: How To List Database Objects
Jul 21, 2010Is there a SQL that gives the list database objects (tables/views/packages/functions etc.) where a particular column in a table is used?
Example:
CREATE TABLE XYZ (ID NUMBER);
I want to know all the objects that used the column ID in table XYZ either in select statement or in a condition.
SQL & PL/SQL :: Dependencies Between Objects In Database?
Dec 3, 2012Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.
Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.
Find Objects Containing A Specified Character Pattern
Mar 21, 2011Is there a method or a tool out there that can do a search through an Oracle Schema to find objects ( tables, fields, stored procedures, etc) containing a specified character pattern ? For example : I would like to return all of the tables that contain fields containing the character string "ABC"
View 1 Replies View RelatedHow To Move Database Objects From One Schema To Another
Sep 6, 2011I wanted to move all db objects from one schema to another schema.
View 4 Replies View RelatedMigrate MySQL 5.0 Objects To Oracle 11g
Jan 23, 2013I`m migrating a mysql 5.0 database to Oracle 11g. I used Oracle's Sql developer and with a workaround i could convert tables, constraints, indexes, some triggers and created sequences.
My question is, what happen with stored procedures and functions, they didn't pass. I've been searching and i found here hey have to be rewritten. Is there any way to convert those objects ? I can't afford to do it manually because the mysql database has almost 400 of them and they aren't small.
Type Objects And Bulk Loads
Apr 19, 2013I am trying for a solution to automate a process of bulk inserts. I have attached the script file.
Attached File(s)
blk_test_script.txt ( 1.45K )
Number of downloads: 6
Returning A Table Without Creating Objects?
May 4, 2010I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....
How do I return the table (referenced by a cursor) without creating objects?
SQL & PL/SQL :: Invalid Objects In SYSMAN Schema
Jul 19, 2012Recently I migrated our Oracle to new machine using exp/imp on schema basis. After import finished I had tons of invalid objects in database. I ran utlrp.sql script and lots of them got validated. Than I recompiled manually in EM those are left but two invalid objects (MGMT_JOB_UI description and body) in SYSMAN schema gave error while recompiling.
Now when I click on any scheduled jobs to edit it or view its schedule, EM throw following error:
X Error
jobType - jobType page property expected
I think its related to that invalid package. The errors while compiling the specification are as follow:
Line # = 50 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 65 Column # = 9 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared
Line # = 88 Column # = 1 Error Text = PL/SQL: Declaration ignored
Line # = 107 Column # = 9 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared
The Package specification is as below,
AS---------------------------------------------------------------------------------- type definitionTYPE CURSOR_TYPE IS REF CURSOR;-- Get the targets for this step, if any-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN SMP_EMD_STRING_ARRAY;-- Get the targets for this step, if any, as a comma separated string-- if p_return_display_names is false, return the target's internal nameFUNCTION get_step_targets_str ( p_step_id NUMBER, p_return_display_names BOOLEAN DEFAULT true ) RETURN VARCHAR2;-- Get the parameters for this job, filter as specified for this jobtypePROCEDURE get_visible_params ( p_job_id RAW, p_exec_id RAW, p_params_out OUT CURSOR_TYPE);-- Get the URI for this uri_use-- see emSDK/job/dtd/UriSource.java for uri_use constantsFUNCTION get_display_uri ( p_job_type IN VARCHAR2, p_uri_use IN
[code]....
SQL & PL/SQL :: Get The Distinct Elements Of A Collection Of Objects
Oct 7, 2010I have a collection of objects built in a PL/SQL program.
I want to keep only the distinct elements of this collection for the rest of the program.
I failed to find the correct way to do it
For instance (the actual object is far more complex containing object attributes itself), with the following types:
drop type t1b
/
drop type t1a
/
create or replace type t1a as object (
v1 integer,
v2 integer
)
/
create or replace type t1b as table of t1a
/
I have the following variable:
v t1b := t1b(t1a(1,2),t1a(1,2));
And I want to get only one "t1a(1,2)" in my collection.
My first idea (actually the second one but it does not matter) was to use DISTINCT:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select distinct t1a(v1,v2) bulk collect into v from table(v);
5 dbms_output.put_line(v.count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4
As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).
The next idea was to use multiset operators:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select v multiset intersect distinct v into v from dual;
5 dbms_output.put_line(v.count);
6 end;
7 /
1
PL/SQL procedure successfully completed.
This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.