Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?
View 2 Replies i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:
1) Do i need to pre-create only schema user or all the users under it.
2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?
i want to give privilege of create trigger,procedures and functions privileges to a user "A"on the schema "B". how can i do it. i've already given select,insert,update,delete privilege to user "A"
how can user "A" create trigger(etc.) on tables of user "B".
How to call stored procedures or functions from a database trigger?
View 1 Replies View RelatedAny ways for Debugging in PLSQL.
Ex:In a package or procedure i have thousands lines of Code how can i Debug.
I'm not sure if this belong to this place. move it to the proper place.
I'm having an annoying problem: (I'm not sure if it is a problem)
- In a package body I'm trying to delete/update/insert/select the contents of a table in other schema.
- The respective synonym exists.
- I had created a role with the respective privileges over the synonym.
- I granted the role to the package's owner.
- I try to compile the package, but it keeps returning compilation errors. (Not table found)
- In standard SQL, I can delete/insert/update/select over the table.
- The only way to compile the package, without errors, is to grant the privileges directly to the package's owner.
Is it supposed to work in this way?
NAME package
---------- --------------
Anand basic Anand pascal Juliana cobol Kamala dbase Mary oracle Mary c Partick c++ Qadir assembly Qadir c Ramesh dbase Remitha c Remitha assembly Revathi pascal Revathi basic
I am unable to locate the asm packages for rhel 5 :2.6.8-128 kernel of 32 bit.
Only 64 bit rpm are availbale on OTN.Can ayone suggeet me the URL for 32 bit>
Get following output using user_source(or other) view?
Package_nameProcedure_nameline_startline_end
ABCXYZ1022
In procedure_name column it could be any procedure, function name or any object name exist in package body.
We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.
I'll provide the timeline of what occurred:
1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.
2.) I ran a "drop package body" statement to drop the package body from MY schema.
3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.
4.) I ran a script to check for any invalid objects under the application user schema. There were none.
5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.
6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.
I also ran utlrp.sql.
I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.
I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.
I am trying to use dbms_jobs in Oracle 8i database.
This is the syntax I am using:
begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;
i have defined the variable jobno as number!
And i am getting the error :
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
I then go and look at the Sys.dbms_job package and see its not compiling
I have this error:
(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored
i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
[code]....
what is the difference using cursor in specifications & body in packages?
View 1 Replies View Relatedwe have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.
now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.
I am really wondering about data loading to the cloud, I've just read the documentation, but regarding the packages, I have more than 100 package and I have to "add to cart" one by one the problem is that the package body is not added by default, so you have to expand the packages one by one then select the package body right click and add to cart. This task is very tedious, is there any other options.”
View 8 Replies View RelatedI'm trying to install 11g Release 2 on a CentOS (4.9) box, but the pre-req check shows that I'm missing the following packages (I had 9 to begin):
gcc-3.4.6 (X86_64)
gcc-c++-3.4.6(X86_64)
libstdc++-devel-3.4.6
Tried to use the yum command "yum install gcc" but get the following error.
Error: cannot find a valid baseurl for repo:update
I think this is due to the "CentOS-Base.repo" file, which I've tried to update by changing the mirror URL's but no success.Then tried to find the rpms online ([URL]...
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: rm -f ntcontab.*
[code]....
I need to figure out which Oracle Database packages are installed on a certain server.
First problem: no Enterprise Manager available. So I need some command line tools or SQL statements.
I've already found some with them I was able to spot RAC, partitioning and so on. But I'm still missing a option/command to spot
- diagnistic pack
- tuning pack
- web logic
- web server
How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies View RelatedI have a table called "Subjects" which lists subjects to match with notations in another table I have created a simple sequence (CREATE sequence subjectid) to created the subject id for the table. But I notice that if there is a skip in the date, the sequence increments automatically when I am not even using it. It even appears to be incrementing even when I am not doing any database activity.
This is not an issue of data integrity, because the values in the subject_id column do not need to be sequential, they just need to be unique. But it really has me curious. I created another table called "keep_track" to keep track of what is happening:
16-NOV-12 2952
16-NOV-12 2953
16-NOV-12 2954
16-NOV-12 2955
16-NOV-12 2956
16-NOV-12 2957
16-NOV-12 2958
16-NOV-12 2959
16-NOV-12 2960
16-NOV-12 2961
16-NOV-12 2962
[Code] ......
I have a procedure in the live environment , which operate in 2 mode.
1 regular load at 10 min
2 nightly
The code is same except they set the last loaded time for both in 2 dfferent rows, so that next time run will get incremental data.
I can trace this procedure on mode 1 that is is scheduled but unable to find how the 2nd one is getting trigger every night. there is no jobs corresponding to that. Is there any way i can find which method is trigger the procedure during night.
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER
INTO v_schema_name
FROM ALL_OBJECTS
WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
A Java program would call a sequence of 5 PLSQL procedures, and they all need to participates the Tranasction defined in Java.
What do I do for the transactions inside PLSQL to ensure all 5 pariticpate in the Java transaction? I suppose I just do not specify any COMMIT in the PLSQL, but that probably would not work as PLSQL would COMMIT by default after exiting the proc?
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS
PROCEDURE proc_1 IS
BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_2*
I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.
I am facing below Oracle error when ever I am changing the contents of the package within it. %ORA-06508: PL/SQL: could not find program unit being called:
The packages and the dependents are recopmiled successfully and there are no invalid objects.The problem is resolved only if the database is reastarted.
What might be the problem?I am using a PL/SQL collection of record datatype in this package.
How can I view the log of a stored procedure after it is called by an application?
View 5 Replies View RelatedIn the below SQL, I am calling a function "listagg_nm(deptno)", and function is to get all the employee name for a particular department with comma separated
output is:
-------------------------------------------------
DEPTNO SAL LISTAGG_NM(DEPTNO)
10 5000 KING,CLARK,MILLER
10 1300 KING,CLARK,MILLER
10 2450 KING,CLARK,MILLER
20 2975 JONES,SCOTT,FORD,SMITH,ADAMS
[code]....
below are the code
select deptno,sal,listagg_nm(deptno) from emp order by deptno;
FUNCTION listagg_nm (p_deptno NUMBER)
RETURN CLOB
IS
v_str CLOB;
[code]....
My doubt/question is for every row in the SQL the function is being called. t means that for 1st row deptno=10, the function is called and return all concatenated name for department 10, then again for 2nd row deptno=10, it again do the same, similarly for all other department.
Can we avoid this, I mean, if function is already returned a concatenated string of ename for deptno=10, then in the next function call it will checks if it is for same department (i.e. deptno=10) then the function will not go into for loop and it will directly return the concatenated string of ename for deptno=10 which we already done for the first row and so on for other department number ( something like storing the value in variable. Is it possible to achive this and does this is good for performance as we are not going into loop inside the function to return the concatenated string of ename for the same deptno everytime ( I have to return more than 10000 char., so i used clob in function and similarly the numbers of row return by SQL is more than 1000)and also how to code this, I tried but unable to achive.
When i am trying to create awr snap shot using
exec dbms_workload_repository.create_snapshot;
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_WORKLOAD_REPOSITORY" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.DBMS_WORKLOAD_REPOSITORY"
ORA-06512: at line 1
I am getting below error when SP (Packaged Procedure) is called from application...
ORA-06508: PL/SQL: could not find program unit being called
After checking DB, i found package to be valid and call to SP giving same error ORA-06588.If i fire alter command to compile the package..
alter package name compile; and then if i try to run the SP in db it runs sucessfully.again after some time ORA-06508 it occurs again. after compiling it starts working fine again.
Body of the procedure :-
begin
indexFields.extend;
indexFields(indexFields.last) := ATTRIBUTE_REC_TYPE(i_attributeName,i_attributeValue);
o_errorcode := '0';
o_errormsg := null;
EXCEPTION
[code]....
sometimes it is working fine, sometimes gives error on compiling it works again.
I have researched this problem and checked my variable sizes over and over again. I have tested the procedure within the Oracle Express environment and it works fine; HOWEVER, when the procedure is called from my C# app it produces the ORA-06502 error.
The stored procedure signature looks like this...
Original - SQL Code
create or replace save_new_project (p_custorgid in number,
p_title in varchar2,
p_AOIName in varchar2,
p_description in varchar2,
p_receiveddate in date,
p_deadlinedate in date,
p_startdate in date,
[code]....
The OracleParameter in my C# app for the last out param is defined as such...
cmd.Parameters.Add(new OracleParameter("p_statustypedescrip", OracleDbType.Varchar2, 30, ParameterDirection.Output));
As I said at the beginning of this post, the procedure works fine in the Oracle environment. So why is it not working by simply calling it from C#? I've tried changing the OracleDbType to CLOB which eliminates the error but it returns a bizarre result. It returns this string, "Oracle.DataAccess.Types.OracleClob".
Since CLOB doesn't really work either I switch back to Varchar2 and specify a size of 5000 (in the database the field I am querying is defined as Varchar(30)). I still get the ORA-06502 error.
I am clueless as to what the problem is. It should work and it does if I run a series of SQL statements in an Oracle SQL Command window. The test that works fine looks like this...
Original - SQL Code
declare
v_projid projects.projectid%type;
v_statustypedescrip projectstatustypes.type%type; /* this is a varchar(30) */
begin
save_new_project(2, 'Some input text goes here', 'More input text', 'And more again','26-APR-2007','26-APR-2007','26-APR-2007','26-APR-2007','users name as inpujt text
[code]....
But calling save_new_project from C# throws ORA-06502. It identifies line 40 of my stored procedure. This is line 40...
Original - SQL Code
p_statustypedescrip := v_statustypedescrip;
p_statustypedescrip := v_statustypedescrip;
I am using oracle 10g and toad 11.5 . i am trying to call an api from an anonymous block . If i recompile the api after adding dbms_output.put_line and then try to execute the anonymous block ,it shows error as "ORA-06508: PL/SQL: could not find program unit being called". However if i end current session and open a new session , then the anonymous block will execute without the error. i am made to reconnect the session everytime i make a change to API. making any configurations in toad or database level.
View 12 Replies View Related