SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?
Jun 25, 2012
I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.
1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.
2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.
However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.
From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.
View 1 Replies
ADVERTISEMENT
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
Nov 15, 2011
Can we lock data in global temporary table?
View 4 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
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
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
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
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
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
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
Jan 17, 2011
I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.
DB1 uses some Global Temporary tables to write and store session level information.
I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.
DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.
Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.
View 1 Replies
View Related
Apr 18, 2013
There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.
I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!
These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';
[code]...
C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.
View 4 Replies
View Related
Aug 21, 2010
Since I need to do something along the lines of inserting an xml document into the database for later to use.
Anyway, the problem I get with the below is
Quote:
Error at line 95
ORA-00984: column not allowed here
Script Terminated on line 95.
It's on the INSERT line
declare
xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>
[Code].....
View 2 Replies
View Related
Feb 16, 2004
how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
View 3 Replies
View Related
May 1, 2012
I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..
Here is the code
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....
View 3 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
Oct 22, 2012
the query to copy the data in last 24 hours from one to another table
Database Version :- Oracle 11g
View 2 Replies
View Related
May 1, 2011
I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)
ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");
The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?
I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).
I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.
View 5 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
Aug 24, 2011
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
[code]...
View 4 Replies
View Related
Oct 17, 2006
how to add a primary key into the oracle table but how do i add a partial primary key?
View 2 Replies
View Related
Oct 11, 2011
I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.
I got the following error:
. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
espace PWRPLANT
IMP-00018: partial import of previous table completed: 7055845 rows imported.
Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .
View 7 Replies
View Related
Mar 3, 2011
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 have a .xls file, which has few columns with Korean data. I have problem converting it into .csv. I converted it to UNICODE TEXT, which was TAB separated. I replaced tabs with commas. but still saved it as .txt file. Now I am using utl_file package to open and read.
utl_file.fopen_nchar(l_dir, l_filename , 'R',8000);
I outputted the first header line, and instead of Korean fieldnames, it displayed some different characters.. cannot even copy-paste..
I am uploading the converted .txt as I cannot upload original .xls file here...
View 18 Replies
View Related
Apr 15, 2010
I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.
example ->
Data in clob field will be->
H|12|1233|fff|sss
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
L|1234|gggg|tttt|yyyyyy|rrrrr
L|1094|gggg|tttt|yyyyyy|rrrrr
L|1344|gggg|tttt|yyyyyy|rrrrr
L|1666|gggg|tttt|yyyyyy|rrrrr
L|188|gggg|tttt|yyyyyy|rrrrr
in detail table.
End of excercise will redult-> 1 record in header and 5 records in detail table.
I need to implement it in my project.
View 7 Replies
View Related
Mar 8, 2011
I need to read data from text file(located on application or db server or on some other server, however path is known to me.) and then append some data in it.
Data will be read and written on daily basis so i want to clear all data on date change.
View 3 Replies
View Related
Jan 26, 2011
Is it possible to read streaming continuous binary data from a web site using PL/SQL?
View 8 Replies
View Related
Jun 19, 2013
While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST
AS
TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50);
TYPE OFFER_DETAIL IS RECORD
(
GII_BU VARCHAR(50),
GII_OFFER NUMBER,
[code]........
View 4 Replies
View Related
