SQL & PL/SQL :: DDL Trigger Not Working?

May 14, 2013

I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.

CREATE OR REPLACE PROCEDURE deepa.myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;

[Code].....

This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema

like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.

View 2 Replies


ADVERTISEMENT

Cursor In Trigger Not Working?

Dec 25, 2011

i have a problem with Cursor in trigger,

CODEcreate or replace trigger admin
                  after insert on admin
                  for each row
                  declare
                   bang varchar2(250):='ADMIN';  
                  
[code]...

i will try with select :new.i.column_name from dual, but not run (because i can't determination exactly column name thus i must use column_name variable)

View 6 Replies View Related

Log Of Materialized View - Trigger Not Working Properly

Oct 25, 2012

I have created a Table and a log of materialized view in an Schema SchemaAA of Server A

CREATE TABLE TABLA_TEST
(
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
CONSTRAINT PK_TEST PRIMARY KEY (TEST_PK)
);

CREATE MATERIALIZED VIEW LOG ON TABLA_TEST;

I have created a DB Link from server B with user REPLIC

I have granted SELECT to REPLIC to the table and the log materialized View.

I have created a table in instanceBA of Server B

CREATE TABLA_TEST
(
HOSPCODE NUMBER(3),
TEST_PK NUMBER,
TEST_TEXTO VARCHAR2(50),
FECHA_INSERT DATE,
FECHA_UPDATE DATE,
FECHA_DELETE DATE,
CONSTRAINT TTEST_PK PRIMARY KEY (HOSPCODE, TEST_PK);

I have created a materialized view in schema SchemaBB of Server B with a trigger

CREATE MATERIALIZED VIEW WORKAREA.TABLA_TEST70
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT TEST_PK,
TEST_TEXTO
[code].........

All schemas have the appropriate grants.

When inserting in TABLA_TEST (SchemaAA) and refreshing MView TABLA_TEST70 things go nicely.

But, when updating a record in the original TABLA_TEST and refreshing the MView the results in TABLA_TEST in SchemaBA are as if I have deleted the record (FECHA_DELETE is set to SYSDATE).

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger Not Working For Auto-increment

Jun 16, 2010

Step1

CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Step2

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Method1 Follow Step1 and Step2 and create a Trigger as below :

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Method 2 Follow Step1 and Step2 and directly have an insert statement as below:

INSERT INTO test (id, name) VALUES (test_sequence.nextval , 'Jon343');

Method 1, the Trigger is not getting created, however by following Method 2, I am able to generate auto-increment number.

View 8 Replies View Related

SQL & PL/SQL :: Write Trigger On A New Table - Body Not Working Properly?

Jun 29, 2010

I am trying to write a trigger on a new table. (dest_test) This is the first trigger that I have ever attempted (fairly new DBA) and I am having some trouble with the trigger body.It is a before insert trigger that will need to select from another table (dest) for a particular value being inserted (destination).

create table dest_test (
destination varchar2(4) not null,
db_name varchar2(10) not null
)

desc dest

[code]...

I am getting the exact opposite results than I want, though. If the value appears in dest, it is inserting into dest_test... NOT whatI want it to do!If the value doesn't appear in dest, it is throwing ora-6512 and ora-4088 errors. Is there a way to suppress these errors, or to graceful exit from the block so that the trigger completes without throwing these errors?

View 3 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

PL/SQL :: To_char Not Working / (||) Is Working With Join Query

Mar 22, 2013

I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.

I am using following two queries:

select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))

by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.

whats the diff between to_char and || symbol?

View 1 Replies View Related

Forms :: Call One Trigger Of Item In Trigger Of Form?

Jul 1, 2011

How can "call one trigger of item in trigger of form"

View 5 Replies View Related

SQL & PL/SQL :: How To Call Trigger In Another Trigger

Mar 10, 2010

Can we call trigger in another trigger,if it is possible any example.

View 1 Replies View Related

Sqlplus UI - Not Working?

Apr 22, 2008

I'm having some minor trouble with my sqlplus in Windows. Don't know which feature I accidentally edited by cosole seems to be in debug mode. Everything is ok, except that it print the old and new values of my variables. How can't I restore it to it's original behavior.

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Working Day

Jan 1, 2013

I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.

Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.

W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent

Required output is

Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W

Separate Query will be run for the following output.

Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31

If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.

View 10 Replies View Related

SQL & PL/SQL :: Update Is Not Working

Apr 27, 2012

with a as (
SELECT subscriber_agn,max(ixml_id) ixml_id, claim_id
FROM
(

[Code].....

i want to combine above two and need only one sql stmt.

View 2 Replies View Related

SQL & PL/SQL :: In Clause Is Not Working?

Sep 18, 2013

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');

IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)

If I pass one name 'KIRAN-KUMAR' from .net It's working.

If I pass multiple names from .net query not returning any records.

View 4 Replies View Related

Oradim Is Not Working

Dec 13, 2010

I trying to create the new service for my physical standby database. It's on the same vista machine. But somehow it's not working.

C:\Oracle\product\10.2.0\mayuprd\database>oradim -new -sid -intpwd ananya -start
mode manual
DIM-00003: An argument is missing for the parameter.

View 5 Replies View Related

SQL & PL/SQL :: Recycle Bin Not Working

Jun 14, 2010

I went through the link given in orafaq and tried to work on my system but the things did not work.

SQL> select *
2 from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> ALTER SYSTEM SET recyclebin = ON;

System altered.

SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ON

SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> SHOW RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;

no rows selected

View 6 Replies View Related

SQL & PL/SQL :: Select INTO Is Not Working

Mar 8, 2013

why my "select into" not working.i also create the table testjobs but also not working

SQL> select *
2 into testjobs
3 from jobs;
into testjobs
*
ERROR at line 2:
ORA-00905: missing keyword

View 8 Replies View Related

Oracle 11g EM Not Working (Linux OS)

Jan 20, 2011

I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.

But it throws exception that database instance is unavailable?

View 5 Replies View Related

Self Join No Longer Working?

May 20, 2010

I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.

Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.

On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.

View 4 Replies View Related

Working With Date In Oracle?

Oct 31, 2011

I tried to insert date in date column from another table date column. Below is SP ans execute procedure.

CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from

[code]....

execute test ('27-10-2011 12:01:01');

It gives error as Invalid number.

View 2 Replies View Related

SQL & PL/SQL :: EXISTS Not Working In Oracle 10g

Mar 30, 2012

following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.

SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: INSERT INTO Table Not Working?

Oct 26, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I am doing a simple INSERT INTO from the sql developer window. it shows that 1 row inserted, but when i query, it doesnt give th values.

insert into events_main(event_id,event_number,last_update_Date, last_updated_by)
values(162,999999, sysdate, 'rkhatiwala');
COMMIT ;

this does not give any error, but when i do

select * from events_main
where event_id = 162

it does not return anything.

View 20 Replies View Related

SQL & PL/SQL :: LEFT JOIN And NVL Isn't Working

Nov 29, 2011

Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Here's a simplified version of the query I am having problems with:

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)

Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.

In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)

I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?

View 7 Replies View Related

Enterprise Manager :: OEM Is Not Working

Sep 24, 2010

I tried to start my oem managing but got the following error.

[oracle@Eagle2 ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/product/11.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_Eagle_boomiedb not found.

View 19 Replies View Related

Forms :: GET_FILE_NAME Is Not Working

Aug 6, 2013

I am developing a form using Forms [32 Bit] Version 6.0.8.11.3 (Production). In my custom form I have given a Push_Button and my idea is to invoke a File open diaglog box when I hit the button.

So, i wrote the below code under 'when-button pressed ' triger. But, the button is not invoking the File Open diaglog box in TEST environment.

=====
DECLARE
XXFILENAME VARCHAR2(50);
BEGIN
XXFILENAME := get_file_name('C:', NULL,
file_filter=> 'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|');
END;

View 7 Replies View Related

Forms :: Progress Bar Not Working

Dec 11, 2011

i have this procedure in my form to make some checks on employee data:

Procedure Pu_Check_Emp_Positions_Prc Is
Cursor vc_get_users_data_case1 Is
Select A.User_No, A.User_Responsility, A.User_Position, B.User_Status
From Import_Emp_Positions_Dtl A,
Shows_Users B
Where A.User_No = B.User_No
And (Substr(A.User_Position, 1, 1) = 'R' Or Substr(A.User_Position, 1, 1) = 'r')
And A.User_Responsility Is Not Null
And B.User_Status In (2, 3, 4, 7, 8);
--And A.User_No = 'XSER0001';
[code]....

i need to run this progress bar and finished when the procedure finish.

View 7 Replies View Related

SQL & PL/SQL :: Replace Command Is Not Working?

Jun 18, 2013

database 10GR2, Character_set WE8ISO8859P1

The following command not working.

update consumerappliedform
set name =replace (name,'¿','‡');

What is the problem.

View 3 Replies View Related

SQL & PL/SQL :: Save Point Is Not Working?

Dec 20, 2011

In oracle 9i, I have a table and i inserted the more then 3 records and while inserting each records i have created the save point. But, now i rollback to that particular save point, the whole transaction get roll backed.

Here the similar example what i have tried using SQL Developer Eg:

insert into dept (deptid,dept_name) values (3,'Purchase');
savepoint aa;
insert into dept (deptid,dept_name) values (4,'IT');
savepoint bb;
insert into dept (deptid,dept_name) values (5,'System');
savepoint cc;
rollback to bb;

View 10 Replies View Related

SQL & PL/SQL :: Ref Cursor Syntax Working In 8i But Not In 11g?

Feb 17, 2011

why this code work in 8i but not in 9i, 10g, 11g

declare
type typ_curseur is ref cursor;
l_cursor typ_curseur;

[Code]....

When running in 9,10 or 11 I get the error ORA-22806: not an object or REF. After investigation I found that the problem is the bind variable ":bind.variable". Notice the dot between bind and variable. If i remove the dot or replace it by underscore everything work fine.

I am just looking for some documentation about this problem to know if it was some new restriction starting in 9i and if there is something to set to make it work like in 8i or maybe it will be better to change all our program that are using this kind of syntax. Actually i did not find anything on metalink.

View 4 Replies View Related

Page 2 - (RELATIONSHIP) - Not Working?

Mar 6, 2011

Quote: Originally Posted by Silverj2k11

ALTER TABLE CONTRACT
ADD FOREIGN KEY (client_id)
REFERENCES client(client_id);

Didn't work! I am now losing my mind.

ORA-00904: "CLIENT": invalid identifier You do have a table named CLIENT?

View 7 Replies View Related

SQL & PL/SQL :: Calculating Working Hours?

May 8, 2010

I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours

The following query take 63.499 sec

SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,

[Code].....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved