SQL & PL/SQL :: How To Fetch Name Of Column And Table Name
Nov 30, 2011how to get name of coloumn and table name which is being modified in trigger
View 8 Replies
ADVERTISEMENT
Fetch Multiple Value From A Column And Display It As A Row
Apr 19, 2011I need to fetch the data from a particular column and display it as a row of data.
For example,
Table
Col1 col2 col3
1 6 10
1 7 11
1 8 12
2 6 18
2 7 null
2 8 19
Data should be diplayed using col 2 as partition as below
col1 col3 col3 col3
1 10 11 12
2 18 null 19
Note: I have also referred to this link [URL]....
SQL & PL/SQL :: How To Fetch Column By Comparing Date
Oct 16, 2012i am fetching one record based on date but not able to fetch the data.
in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.
select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.
SQL & PL/SQL :: How To Fetch Value Which Column Has Timestamp Datatype
Oct 14, 2012 i am running one query which is here
INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );
in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.
getting this error.
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
SQL & PL/SQL :: Cursor To Fetch Column Names
Jul 25, 2013I have the following piece of
CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';
CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;
[Code]..
However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?
SQL & PL/SQL :: Ref Cursor As In Parameter / How To Fetch Column Values
Sep 21, 2010i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
PL/SQL :: Frame - Fetch Only 3rd Position From Column Customer-ts?
Dec 20, 2012have a column cstomer_ts from table customer, which contain records like:
"cstomer account"||"PRIVATE"||"foundation"
"cstomer account"||"PRIVATE"||"foundation of money"
"cstomer account"||"PRIVATE"||"Moneycost"now i want to fetch the third record from the column cstomer_ts which starts with 'Foundation'
i mean i want to fetch only 3rd postion frm the column cstomer_ts and tht should starts with 'Foundation'
Application Express :: How To Fetch Tabular Form Column Value Into Page Items
Sep 7, 2012I have created tabular form. I have set LOV for one column. Here my requirement is when i select the value from lov then it will fetch that value into page item.
I am using apex 4.1
Fetch All Information From Big Table For Those Key Present In Small Table
Jun 28, 2012I am having two tables
Table 1 having 16 cror rows .
Table 2 having 1000 rows
I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .
Forms :: ORA-24374 / Define Not Done Before Fetch Or Execute And Fetch
Mar 24, 2011FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH
Nov 1, 2012create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
SQL & PL/SQL :: Fetch Data From Table
May 11, 2011I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.
Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.
Is there any way to query that PLSQL table?
PL/SQL :: Fetch 1 Lac Rows From Table
Dec 15, 2012OS : widows 2003
Oracle 9i release 2
I need to Fetch 1 lakh rows from table (all rows) in fastest way.
How to do it?
Queries Will Fetch Second Highest Sal From Emp Table
Jan 19, 2012go through both the given queries...
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
PL/SQL :: Fetch Records From Employee Table
Oct 28, 2012I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.
1-5 records
6-10 records
11-15 records
16-20 records
46-50 records
Any one can give the query.
PL/SQL :: Fetch DISTINCT Values From Partitioned Table
May 12, 2013What is the fastest way to fetch DISTINCT values from partitioned table?
1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)
Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
CNTRY_KEY NUMBER,
STUDY_ID NUMBER,
PRD_KEY_YEAR NUMBER,
PRD_KEY_WEEK NUMBER,
DATE_FROM DATE,
[Code]...
-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)
** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR
[Code]...
** Explain Plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 6,235 Bytes: 629 Cardinality: 37
8 HASH UNIQUE Cost: 6,235 Bytes: 629 Cardinality: 37
7 CONCATENATION
3 PARTITION RANGE ITERATOR Cost: 1,985 Bytes: 1,031,900 Cardinality: 60,700 Partition #: 3 Partitions accessed #194 - #207
[Code]...
Partition #: 7 Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.
Forms :: Fetch Record From Table Using Item Code
Jun 7, 2013i want to fetch the data in my form from table, by using item_code.
eg when i write the item_code its value get matched in same table and fetch the another
columns by item_code.
SQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns
May 25, 2013Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
Join Two Oracle Tables For Fast Fetch Of Data From Table?
Jul 2, 2012join two oracle tables for fast fetch of data from table.
View 1 Replies View RelatedSQL & PL/SQL :: Oracle 9i - Block Logic To Fetch Records From Table And Compare
Dec 26, 2011 the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column
Jul 25, 2013From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedReports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention
May 15, 2011i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?
May 11, 2012I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
SQL & PL/SQL :: Add New Column To Table Between Column List?
Jun 16, 2010How to add column 'col_new' in below table after the column 'col1'?
Table name: ofq1
table structure.
columnname datatype
------- -------
COL1VARCHAR2(20 BYTE)
COL2NUMBER
COL3DATE
COL4DATE
All Columns Of A Table Are Part Of Primary Key Column Of That Table?
Feb 22, 2011if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?
SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table
Jul 18, 2012I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......
PL/SQL :: How To Get Column And Table Name By Given Column Value
Sep 28, 2012I have a requirement as to find all the tables and columns containing keyword "SELECT" in my database. I followed forum
Re: How to find the column name and table name with a value
But when i run the below query on 11.0.2g Oracle Database
select table_name,
column_name,
:search_string search_string,
result
from (select column_name,
[Code]...
I get
ORA-29913: error in executing contains callout ORA-20000: Oracle Text error: 29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
How To Fetch Data
Sep 19, 2011Table structure:
table x
x1 x2 (Columns)
1 x
2 y
1 z
now i need to o/p like this
1 x,z
2 y
how to fetch data in above format.
SQL & PL/SQL :: Fetch Into Object Type?
Oct 2, 2011what ios wrong in the following code
create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is
[code]..
SQL & PL/SQL :: Fetch Information For A Foreign Key
Jul 13, 2010the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.
View 3 Replies View Related