Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.
Select Username from User_Users
Will any of the below variables return the trigger owner when used inside the trigger code
sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')
consider the trigger below,
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
I am having some issues in creating the following trigger. It gets created but with compilation errors.
create or replace trigger min_max_update_trg
after insert on tidlrrep
referencing new as n
for each row
declare
bltMaxSize number;
repMaxSize number;
[code].....
when i tried without the comparison if repMaxSize < bltMaxSize then it got created successfully.
I am trying to call procedure inside trigger.. but i get error ora-04098 ..
create table emp_hstry
as
select * from emp
where
1= 2 ;
create or replace procedure emp_del_hstry(v_empno NUMBER ,
v_ename VARCHAR2,
v_job VARCHAR2)
is
insert into emp_hstry (empno,ename,job)
values (v_empno,v_ename,v_job);
COMMIT;
end;
create or replace trigger emp_del_hstry1
after insert or delete
on emp
for each row
begin
if deleting then
emp_del_hstry(:old.empno,:old.ename,:old.job);
end if;
end;
delete from emp
where
empno = '7369'
AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '
end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.
The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by (and as far as I understand, supposed to be used with) SYS user. what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users?
CREATE TABLE TBL_LTEST
(
ID NUMBER,
MYPO NUMBER
);
alter table tbl_ltest add constraint pk_tbl_ltest primary key (id, mypo);
I want the data in this table to be like this:
ID MYPO
1000 401
1000 402
1000 403
2000 401
2000 402
2000 403
Rule of MYPO column population:
1. It should always start with 401 (I can handle that with Oracle check constraint that mypo should be > 401)
2. For each Id, the first insert has to be 401, then it should add one from there within the Id
(something like, mypo is a sequence within the ID.)
I am trying to populate mypo inside the trigger in this way:
CREATE OR REPLACE TRIGGER trigTBL_LTEST
BEFORE INSERT OR UPDATE
ON TBL_LTEST
REFERENCING NEW AS NEW OLD AS OLD
[code]...
I need to find out the exact DML statement which invoked a particular trigger inside a trigger body.
View 7 Replies View RelatedI hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:
Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))
Step# 2 manipulated the tables:
Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:
CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......
Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')
I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.
Im using the following oracle database.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
I have one problem in trigger execution. I have a small plsql block in trigger and, I want to execute it as a dynamic way. but it is giving the error. Please find the trigger code. Here my intension is that, the column name used in trigger should be dynamic. In future, if I want to switch the column name, I have to do without modification in trigger.
The error im getting is "ORA-01008: not all variables bound".
CREATE OR REPLACE TRIGGER ETM_AR_IU
AFTER UPDATE ON
EXTERNAL_MAPPING
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
[code]...
Is their any query to find cpu usage & memory usage of all the queries currently running on DB?
View 5 Replies View Relatedi tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.
View 1 Replies View RelatedWhich of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
I am trying to implement a Jtree with Context Menu. When Context Menu is implemented the nodes wont expand or collapse.This happens with Sample Trees Packaged application for Project Documentation tree.
View 0 Replies View RelatedI'm trying to find out if I can create a context sensitive menu in Oracle Forms 10g. So far I haven't found much in the way of info. where I can find some sample code or a demo I can learn from?
The idea being that when a user right clicks an item they are then presented with relevent options for that item.
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
View 1 Replies View RelatedWe have code that occasionally errors with ORA-12151, with tracing indicating some funk surrounding OCI calls to OCIPing(), OCIBreak(), and OCIReset(). What the code is trying to do here is release a connection from a pool (our implementation, not OCIPool) and before releasing it trying to make sure nothing is actively running on the connection etc. The environment is created in THREADED mode (ie not non-blocking).
Reading the documentation its not really clear to me if OCIBreak() and OCIReset() are really intended to be called while in THREADED mode.
Is it a bad idea to call break/reset as part of a general connection-reset routine?
The basic control flow (minus error handling etc) is:
<pre>
reset() {
int rc = OCIBreak(connection_, err_);
rc = OCIReset(connection_, err_);
if (OCIPing(connection_, err_, OCI_DEFAULT) != OCI_SUCCESS) {
return errors;
}
return 0;
}
</pre>
I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:
CREATE TABLE SUBMISSION
( SUBMISSION_ID NUMBER(10) NOT NULL,
SUBMISSION_NAME VARCHAR2(100) NOT NULL
);
CREATE TABLE ADVISOR_TYPE
[code]...
I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward. I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum.
How do I create Items context help editable report or report & form app?
what is its table?
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
when to use || NULL oerator or what is the meaning of this statement when it is used with the column name.
and A.RtmtCd = B.RtmtCd|| NULL
or
A.RtmtCd||NULL IS NOT NULL
I have one procedure which is executing daily two times(i.e morning run and evening run) . In morning run it's executing around 150 mins and evening run executing around 25 mins.
It's happening for all procedures executing in longer run in morning. There is NO LOCKS, I have checked LOCKS while executing morning run. I suspects CPU usage in morning run.
How to check CPU usage ? AND also each session how much utilizing CPU. I'm using oracle 8i database, I know it's older version but my company is using oracle 8i. So i have to look in this database version only.
I have a table "tl" which is partitioned--say 30 partition and for each partition there is a seperate view like as follows
create view view_t130 as select * from tl partition (p30);
create view view_t129 as select * from t1 partition (p29);
.
.
.
create view view_t101 as select * from t1 partition (p01);
my question is how to use hints on this table if your are using view to access the data from internal table.
Normal structure is if i don't wrong:-
index( <<view name|view alia name>> <<table name|table alias name>> name of index)
Consider my case
select * from view_t130 where <index_column> --not picking up index
i want to give expicit index hint.so i used the same structure that i specified above but it didn't work.
select /*+ index( view_t130 t1 <index_name) */ * from view_t130 where <index_name>
how to give explicit index hint..but one constraint is i cannot give any alias names for internal tables because those(view structure) are generated by predefined scripts..so it's not possible to change it.
I had a question on BFILENAME() :
first,
create or replace directory UTL_FILE_DIR as 'k:lob_test';
GRANT READ,WRITE ON DIRECTORY UTL_FILE_DIR TO user1;
and then ,I login use user : user1
create or replace package body p_utl_mail_attach_raw as
procedure send_single_user_attach_raw(........)
fil BFILE;
filenm VARCHAR2(50) := 'aaa.jpg'
begin
fil := BFILENAME('UTL_FILE_DIR', filenm);
end ;
this result it show :
UTL_FILE_DIR/aaa.jpg, exists=F, length=0, open=F
I'd like to have a conditional that only gets the next value from a sequence is the current (or supplied) value is null.
Here are some trivial examples:
CREATE SEQUENCE ts
MINVALUE 1
START WITH 1
INCREMENT BY 1;
SELECT ts.NEXTVAL FROM DUAL;
SELECT COALESCE(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT NVL(ts.CURRVAL, ts.NEXTVAL) FROM DUAL;
SELECT CASE
WHEN ts.CURRVAL IS NOT NULL THEN ts.CURRVAL
ELSE ts.NEXTVAL
END
FROM DUAL;
Ideally multiple executions of any of these (or a better one of your design) should return the same value from the sequence, but mine do not.
I have one table and I created partition month wise. I also creates index on that table. How can I check whether parition will be used when I query that table? In explain plan I can see PARTITION RANGE as ALL. Is it using the partition?
View 2 Replies View RelatedI can get "CPU used by this session' value from v$sesstat. That is the value in centiseconds used by each session. How to convert the value into a percentage of total CPU resource ?
Or there are some other ways to gain the value. i.e. Percentage of Total CPU usage for each Oracle session.
I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>...
select distinct
file_sid,filename,control_numb,processing_date,file_class,
vendor_id,vendor_desc,
c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024
THEN to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(high_optimal_size+1)/1024|| 'kb'
ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add
a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G).
Is the critcal factor the use of distinct? or multiple windows? or something else?
i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.