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.
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)
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).
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.
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?
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
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.
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.
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
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.
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;
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?
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.
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
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.
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?
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.
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.
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;
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.
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,
