I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
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.
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.
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.
with Courses as ( select 100 courseID from dual union all select 200 from dual union all select 300 from dual union all select 400 from dual union all select 500 from dual ), Assigns as (
[code]...
I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.
--Color table with 6 colors with Colors as ( select 'red' color from dual union all select 'blue' from dual union all select 'yellow' from dual union all select 'green' from dual union all select 'purple' from dual union all select 'teal' from dual )
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------ CREATE TABLE temp_table1 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
CREATE TABLE temp_table2 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE ------------ ------------ ----- --------------------- john smith MA 12/1/1979 null null AZ null null null CT null null null MA null null null CT null
I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexDelete ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA DRG-50857: oracle error in drexrdml ORA-01031: insufficient privileges [code]....
I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code from A,B where a.key_no=b.key_no(+) and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.
SQL how can we do this in a simple way? each tables has around millions records.
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate UNION All SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
Ways for improving the Table performance which holds million of records for oracle. Currently we have partitioning and indexing but it doesn't seem to work.
I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.
SQL> create or replace 2 PACKAGE casadm.sis_load_cpl_sis_reb_pgm_hist 3 IS 4 /********************************************************************** ******************
[code]....
ERROR at line 1: ORA-06550: line 1, column 7: PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
I have seen the below blog abut database driven menu in java. uRL..... look into the below tables and its data below.I have concerned inserting data in the tables and particularly about
ROLE_DETAILS,ROLE_MASTER and EM_MENUMAS data. select level lvl, ID, NAME, lpad(' ', level * 14, ' ') || NAME menu, parent_NAME,nodetype, page_name from em_menumas [code].....
Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
I have the following 3 columns in my VERSIONS table.
MAJOR MINOR MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions select max(minor) as max_minor from versions where major = :max_major select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?
PTN NAME DATE STATUS 11014419 Joe Dickson 2012-06-25 14:55:58 Delivered 11014419 Mike Draia 2012-06-25 14:28:17 Loaded 11014419 Bob Geber 2012-06-25 13:14:31 Received 11014419 Bob Geber 2012-06-25 13:14:31 Picked Up 11014419 Bob Geber 2012-06-25 13:14:31 Printed
I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.
Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.
SELECT SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS", COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL" FROM USER_REGISTRATION WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1, INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) ORDER BY MAILS DESC;
