SQL & PL/SQL :: Temporary Disable All Constraint In Particular Table?
Jan 7, 2012
i have one table HR.employees
SQL> desc hr.employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL>
in this table i want to temporary disable all constraint in this employees table
View 4 Replies
ADVERTISEMENT
Aug 22, 2010
Is there any logical difference between DISABLE CONSTRAINT and DISABLE NOVALIDATE, in order to accept any new values into the table regardless of violating anything?
View 2 Replies
View Related
Dec 25, 2007
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
View 1 Replies
View Related
Nov 15, 2011
I am using temporary table.
PROCEDURE Return_Summary(WX IN dbms_sql.varchar2_table,
WX OUT SYS_REFCURSOR) IS
Begin
FOR i IN 1 .. Pi_ WX.count LOOP
/* I need to put this results in a temp table or table object Can I use temp table for this or do we have any other recommended method. The loop might execute max of 10 times and for each run it might return 100-200 records. */
select WX_NM,
WX_NUM
from TAB A, TAB B, TAB C
where A.KEY1 = B.KEY1
and B.KEY1 = C.KEY1
and C.WX = WX(i);
End Loop;
End;
View 5 Replies
View Related
Aug 30, 2013
creating a temporary table.i have this query mentioned below
CREATE TABLE WEBPEN AS (SELECT PNSR_PPO_NO PPO,PNSR_FILE_NO,
DECODE(F_GET_APPLN_NO(PNSR_PK),'1',PNSR_VOL_NO,F_GET_APPLN_NO(PNSR_PK)) APPLN_NO,
PNSR_FULL_NAME NAME,
TO_CHAR(PNSR_DOB,'DD/MM/YYYY') DOB,
TO_CHAR(PNSR_DOR,'DD/MM/YYYY') DOR,
F_GET_ADBK_NAME(PNSR_TO_PENSION) TREASURY,
PNSR_SPOUSE_NAME SPOUSE,
[code]....
This creates a table webpen with around 54107 rows. What i am want is every time run "select * from webpen" it should run the above query and give the result as per the values in main table M_PENSIONER ,M_PEN_DCRG_WITHHELD.
What i want is it should truncate the existing values and insert the value by running the above mentioned query .
View 6 Replies
View Related
Jun 5, 2005
I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.
1)INSERT #konfig
exec sp_configure.
2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go
how migrate for example code to Oracle?
View 11 Replies
View Related
Feb 20, 2010
What is the best option for GLOBAL TEMPORARY TABLE
1) option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS. and wheverever this is used for calculation commit at the end of porcedure.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
)ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin
[Code]....
2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
);
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
[Code]....
View 26 Replies
View Related
Jun 12, 2012
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
View 2 Replies
View Related
Apr 26, 2011
Is there a neat way other than having to reformat the data in a temporary table to do the following,I've got the following content in a table:
CODECustid Type Nb Amount
1 Deposit 2 10000
1 Withdrawal 1 4000
I'd like to show the data in this manner:
CustID Deposit DepositAmount Withdrawal WithdrawalAmount
1 2 10000 1 4000
View 3 Replies
View Related
Mar 12, 2013
I have created global temporary tables to be used in my stored procedure, in order to view reports which i created in JASPER. Since global temporary tables are session based, when multiple users are trying to generate the report, every user is getting inconsistent data.
To make it clear, what i meant is if a user A tries to view a report with some filter criteria and simultaneously user B is trying to generate the same report with another filter criteria, User A is getting User B's report data and User B is getting User A's report data. How can we avoid this problem?
View 1 Replies
View Related
Dec 6, 2011
how to drop global temporary table?
while droping global temporary table we are getting below error
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"
View 1 Replies
View Related
Dec 21, 2011
How to allow only "CREATE GLOBAL TEMPORARY TABLE" DDL in a schema. I have to restrict all DDLs performing by a particular schema except GT Table.
View 8 Replies
View Related
Dec 3, 2011
I am using a global temporary table in which place data from a few different queries.
It then select it out into a cursor.
This procedure works fine in PL.SQL Developer and Toad. It doesn't have to be adjusted.
Java has a problem though, as the data is gone when the Java call attempts to acquire it. This is due to session pooling I suppose.
So, my question is somewhat composite.
Is there a setting in Java (JDeveloper) that I could overcome this with? Perhaps a momentary "Hold" on a session?
View 6 Replies
View Related
Mar 13, 2013
create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
View 9 Replies
View Related
Oct 17, 2012
create or replace procedure p_populate_gtt
as
begin
insert into gtt
select last_name,first_name,null from funcdemo where rownum <51;
update gtt set vote=100
where ln ='Tim';
end;
/
gtt is my global temp table. i am updating vote column which is null to 100.But i am not able to update it
View 21 Replies
View Related
Jun 11, 2012
How can i create a temporary table using EXECUTE IMMEDIATE ??
Like:
CREATE GLOBAL TEMPORARY table new_table as (Select * from old_table);
View 11 Replies
View Related
Nov 15, 2011
Can we lock data in global temporary table?
View 4 Replies
View Related
Nov 7, 2012
how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.
CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE
[code]...
why insert into table temporary not successfully in job_submit.
View 3 Replies
View Related
Jul 15, 2013
The actual flow, works on this way:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).
(X isn´t important on the new flow)
Now, i need to add some steps on the normal flow:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).
The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.
Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).
I need:Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.
View 7 Replies
View Related
Feb 17, 2011
I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit
create or replace
PACKAGE BODY BILL AS
FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;
ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........
View 12 Replies
View Related
Mar 3, 2010
create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;
-- when am trying to execute this
begin
test;
end;
-- showing ora-01031, insufficient privileges.
View 9 Replies
View Related
Jul 9, 2013
I am getting different result when I run my dashboard procedure I am using temporary table with "ON COMMIT PRESERVE ROWS", below is the information I am running my attendance dashboard procedure which will display the employee attendance status based IN and OUT punches the status like AA-full day absent, GG-Full day Present, AG-First half absent,GA-Second half absent.
Now when I run the first time my procedure for first time I am getting status AA even though IN and OUT timings are correct and if run it again then it is displaying the status for same employee as GG I didn't understand the problem where it is effecting the status
View 4 Replies
View Related
Apr 8, 2010
I knew, how to enable or disable a constraint on a table.
>alter table <table_name> disable constraint <constraint_name>;
But if we know all the data is correct, it's better to disable all constraints to improve the performance.
We are allowed to Disable or Enable only a single constraint.
Enable or Disable All Constraints.
View 11 Replies
View Related
Aug 11, 2010
I am trying to disable the index i am getting the error like
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
the table have partition. how to disable the index in partition table.
View 1 Replies
View Related
Jan 4, 2011
I'm working on a java program connected to a Oracle 9i db. I'm inexperienced about Oracle specific capabilities. The experienced Oracle users
I would like to know if there's a way to create a temporary table so that:
- the table will be deleted automatically when a specific db connection closes (obligatory)
- the table is visible for just one specific connection (optional)
What I would like to do is:
1 - Get the result set for query A.
1a - process the results
1b - store the results in a performance friendly way ( I thought about a temp table)
2 - Run query B over result set A.
3 - Run query C over result set A.
X - The stored result set A has to be removed before/when the program ends (ideally when the db connection closes).
The problem isn't how to do this in java. The problem is to do it in a way so that the table will be automatically removed when the db connection used to create it will be closed so that's unimportant if the java program crashes or ends normally.
View 7 Replies
View Related
May 15, 2013
Global temporary Table Name:
1.MT_GBL
2.DT_GBL
3.DT2_GBL
Base Table Name:
1.MT
2.DT
3.DT2
My Steps:
1.Insert all data from global table to base table.
2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??
View 4 Replies
View Related
Sep 8, 2012
how to resolve the following issue ...
insert into GTT (select query) inserting less no. of rows than that returned by the query.
The global temporary table has ON COMMIT PRESERVE ROWS.
View 3 Replies
View Related
Mar 21, 2013
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
View 16 Replies
View Related
Jun 21, 2010
How can we overcome mutating trigger error using global temporary table.
Suppose if we use the following trigger we will get mutating trigger error.
CREATE OR REPLACE TRIGGER t1
AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
UPDATE emp SET sal=sal+100;
END;
/
View 27 Replies
View Related
Apr 19, 2012
I have schema a and b in the same database with synonym table a (schema a) to schema b. When I try to disable trigger from table a in schema b; I got this message:
ORA-00942: table or view does not exist
I have granted all permissions to schema b and I am able to SELECT FROM table a in schema b
View 11 Replies
View Related
