SQL & PL/SQL :: Specific Columns In For All
Jun 9, 2010
I want to insert only specific number of columns into a table by using Bulk collect and Forall.
SQL> create or replace procedure bifa_proc
2 is
3 type etab is table of emp%rowtype index by binary_integer;
4 erec etab;
5 cursor c is select * from emp;
6 begin
7 open c;
[code]...
Warning: Procedure created with compilation errors.
PLS-00382: expression is of wrong type
PLS-00436: implementation restriction: cannot reference fields of
BULK In-BIND table of records
How to insert specific number of columns without declaring multiple table type definitions for each column by using bulk collect and forall.
View 5 Replies
ADVERTISEMENT
Jan 11, 2013
I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies
View Related
May 25, 2011
As we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
View 1 Replies
View Related
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Jun 16, 2010
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies
View Related
Apr 4, 2011
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
View 12 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 8 Replies
View Related
Jan 24, 2011
Can we do a specfic word count in sql. Requirement is there is a string "I am very very poor in sql" the word "very" occurs two times, so the result should be 2.
View 16 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the below condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 8 Replies
View Related
Dec 3, 2010
I am using oracle11g. I want to truncate subpartition on specific partion.
I have partition on statewise. Each state partion has 7 day sub partition.
For intance,
Partion TX
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion CA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion IA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Now i want to perform following tasks.
1. Need to truncate TUE sub partiion on TX partition.
2. Need to truncate WED sub partiion on CA partition.
3. Need to truncate SUN sub partiion on IA partition.
How do we do this?
The below statment truncate all TUE partition on all the partitions.
ALTER TABLE TRX_TABLE
TRUNCATE SUBPARTITION TUE;
How do i tuncate specfic sub partition on specific partition?
View 4 Replies
View Related
May 6, 2010
query only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
View 3 Replies
View Related
Oct 18, 2011
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE
CURSOR c_type_id_usg
IS
[Code]....
-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;
View 2 Replies
View Related
Oct 14, 2012
Say, a table has existing columns of A, B, D, and E.Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
View 6 Replies
View Related
Jun 11, 2012
How to add a new column to specified position in a existing table.I have using the oracle database 10g.This below code is not working in oracle 10 g
example:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER FIRST:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER AFTER JOB:
View 11 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the a condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 6 Replies
View Related
May 14, 2013
I'am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit with the following:
db_flashback_retention_target 1440
undo_management AUTO
undo_retention 900
Is it possible to enable flashback only to some specific tables?
For example, we are only interested in the capability of flashback tables scott.emp and scott.dept, no more.
View 2 Replies
View Related
Oct 10, 2008
i cant find any oracle specific methods for chunking data -if i had returned rows of numbers, how could i chunk them into ranges?
View 2 Replies
View Related
Apr 5, 2008
i have columns called data_received(data received from server) , URL (sites visited)
now i want to display top 50 sites based on highest data_received.URL column will have some sites repeated.
View 1 Replies
View Related
Mar 20, 2013
cookiemonster wrote on Wed, 20 March 2013 22:01Or if the user does anything to wipe the modified data - clear block, enter query etc - forms will also raise the alert.
i've uploaded a new RTF for Spanish but wen i login with the Spanish creds and run the program. Its not showing the changes i've chosen the right language and territory code.
View 1 Replies
View Related
Oct 11, 2012
i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.
View 16 Replies
View Related
May 9, 2013
I want to start by saying I am brand new to SQL. I have an access database linked to my oracle and am trying to query a very specific set of data and I can't seem to narroe it down. I have 244,000 lines in the DB and I'm trying to find items on a specific trype of vendor agreement. I may have the same item on multiple agreements. ex 1-, 1a-, 2-,2a-,3-,3a-,4-,4a-,5-,5a-,6-,6a-,7-,7a-,8-,8a-.
each item by agreement is on it's own line.
ex.
item ven_agrmt_ref
233 1a-xxx
233 4-xxx
233 4a-xxx
255 4a-xxx
I need to find a way to select just items that appear on a 4- or 4a- and no other agreement reference. The query I did so far pulls all of the 4- and 4a- agreements but will also pull items,like #233 in the example above, but not showing the 1a- agreement. I need it to overlook that item eventhough it does appear on the agreement I am looking for but also has an agreement I am not. the statement I am using right now is:
SELECT item, ven_item, ven_agrmt_ref, base_cost, vendor
FROM "all items by agreement"
WHERE ven_agrmt_ref >= '4'
ORDER BY item
View 3 Replies
View Related
Jul 20, 2012
I want to recompile a single procedure that is part of a package, without re-compiling other procedure/functions present in that package, is it possible?
View 1 Replies
View Related
Mar 18, 2013
I have a table with data in below format:
Table A
Id Value
1 R
2 S1
2 S2
3 T
the o/p in below format:
Col
R|S1|T
R|S2|T
The general is that multiple value for any given id must be aggregated separately.
View 1 Replies
View Related
Aug 12, 2010
I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
View 12 Replies
View Related
Oct 17, 2012
This is what I've been trying to do.
I have the following table:
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
View 4 Replies
View Related
Apr 15, 2010
I develop a form using Devloper9i AS, oracle 9i, Winxp
I need to use items like "ActiveX Control, OLE Container, Sound " but they are marked as obsolete by Developer9i AS;
so how can I use like this type of items when I establish a form design ".fmb" .
View 2 Replies
View Related
Nov 13, 2013
I have "After header" process (with PL/SQL code) in "Page Rendering" section and want to navigate to some page after this process has been completed. I created a branch (On Submit: After Processing) with Target Type: URL. But this branch is executed only after "Page Processing", not after "Page Rendering". Maybe , there is any APEX API functions for pages navigating..
View 2 Replies
View Related
Oct 11, 2013
however the problem we have is when testing a third party supplied application we have evidence of data loss when we crash an instance in the RAC cluster, however this only occurs during approximately 30% of tests, our suspicion is that the application is managing the failover and reapplying only certain types of transaction, the problem is as we have to perform multiple tests to get a reproduction it is immensely time consuming and expensive to test this and the vendor has a tendency to report that the issue is fixed in a patch and then we reproduce the bug during testing.
For a session I can cause it to crash using the oradebug event e.g. alter session set events
'sql_trace[sql: 7h35uxf5uhmm1] RDBMS.crash(0)'
Support have advised that there is no way to cause an instance crash this way, so I am in the process of raising this as a feature request however I would be interested in how other people currently test how third party applications respond to RAC node failures, we currently use a script that executes a kill -STOP to pasue logwriter then sleeps for a short period of time before aborting the database instance, this at least allows us to force a failure during the commit so we are more likely but we can't guarantee that we have tested the exact type of transaction that is causing the problem. Oracle 11.2.0.3 on SUSE 11
View 3 Replies
View Related
