Get Data Dynamically In Sql
Dec 3, 2012
below SQL I'm pulling data manually(by entering specialist name) instead of that I want this query to be modified so that the sys is pulled dynamically, I.e. like select specialist from t.
CODESELECT
type, Hari,Rakesh,Santhosh, Total
FROM (
select
(type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari,
max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh,
max( decode( SPECIALIST, 'Santhosh', cnt, null ) ) Santhosh,
Sum(cnt) total
from (
select
[Code]...
View 1 Replies
ADVERTISEMENT
Apr 20, 2012
I am trying to create a simple parametised procedure when all I am doing is dynamically inserting data from a set of tables and cols on a where clause.
And for the result set I am also inserting these input variables for future ref.
----------------------------------
CREATE OR REPLACE PROCEDURE DATA_TEST (IN_TABLE_NAME IN VARCHAR2, IN_ATTRIBUTE IN VARCHAR2 )
iS
FirstClause VARCHAR2(3000) := 'INSERT INTO ABC (
COL1, COL2, COL3,COL4,COL5, COL6) ';
selectClause VARCHAR2(3000) :=
[Code]...
Now, the problem is that the proc is not compiling, because of that escape character.
Is there anything wrong that I am doing or some other settings needs to be done?
View 9 Replies
View Related
Nov 22, 2012
I have a master table A that has 10+ columns along with data and a configuration table B that has only 2 columns i.e table name and column name. For ex:-
Table A:-
C1 C2 C3 C4 C5 C6
-- -- -- -- -- -----
1 2 3 4 3 6
4 5 6 5 5 5
Table B:-
Table_Name Column_Name
--------- ----------
A C1
A C2
A C3
Now I need to write a sql script that should take the table A Columns defined in Table B and then based on the columns it should spool the columns output from Table A.
For above ex:- I need to spool the C1,C2,C3 columns data from Table A in to some Unix path using SQL SCRIPT.
View 2 Replies
View Related
Jan 26, 2012
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
View 3 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
Feb 17, 2010
I have created one form using oracle ids. i want add value into combo box in runtime.
View 3 Replies
View Related
Dec 8, 2010
i am trying to pass operators dynamically.
SQL> create table test(Amount number,Name varchar2(10));
Table created.
SQL> insert into test values(100,'USA');
1 row created.
SQL> insert into test values(150,'NEWYORK');
1 row created.
SQL> insert into test values(200,'SCOTT');
[code].....
I would like to pass the operators from another table. How to pass the operators from other table.
View 8 Replies
View Related
Sep 15, 2013
I want to add number of zeros after a number dynamically. eg: If number is 2 and number of zeroes to be added is 5 then final output should be 200000;
View 12 Replies
View Related
Jul 9, 2013
I have to audit some table to log all modifications (inserts, updates, deletes). I wrote the following trigger (works as expected) :
-- Trigger for DOCUMENT table audit
CREATE OR REPLACE TRIGGER TAUDIT_IUD_DOCUMENT
AFTER INSERT OR UPDATE OR DELETE ON DOCUMENT
FOR EACH ROW
DECLARE
l_user VARCHAR2(64);
[code].........
Is there any way I can improve this ?I mean getting columns name and value (:NEW and :OLD) dynamically, instead of specifying them one by one (the trigger will have to be updated in case a new column is added). Something like this :
FOR i in 1..DOCUMENT.COLUMN_COUNT LOOP
l_row := l_row || DOCUMENT.COLUMN_NAME(i) || :NEW.COLUMN_VALUE(i) || l_separator;
END LOOP;
View 6 Replies
View Related
Jun 17, 2013
In alters I want to display a value dynamically.
like when deptno=10 came then it should dynamically displays the message like Department 10''.
when deptno=20 came then it should dynamically displays the message like Department 20''. and.....
View 4 Replies
View Related
Oct 14, 2011
In a pl/sql procedure code, I created a normal table (create table) using dynamic sql. Then I used that table in procedure for further processing. But while compiling, it gave error that table does not exist. I can understand that he table is not present in DB, so the error came. But at the same time I need to create a table dynamically, use it and drop it. Does it mean that I need to make every query referencing that table as dynamic ??
View 1 Replies
View Related
Aug 4, 2010
How to dynamically execute a select command whether it is retrieved a single record or multiple record in oracle database 10g. i have tried with the command execute immediate but it was not successful.
is it possible can i delete or drop multiple tables in a single drop and delete statement.
View 5 Replies
View Related
Apr 25, 2011
We have developed a code which builds a trigger on a atble dynamically at run time usinf Dynamic SQL command Execute immediate.
create or replace
PROCEDURE GENERATE_TRIGGER
(
P_TNAME IN VARCHAR2
[Code].....
I am not usre if the error is due to insufficient privileges as we are able to build tabl;e in the system dynamically using the above mentioned command.
View 1 Replies
View Related
May 3, 2013
We have an requirement to create xml data for entire database (selected tables) which are in hierarchy.Procedure should read node_mapping table having parent and child tables relationship info and build XML Select statement.
Currently it is building SQL statement whenthere are one parent having multiple childrens i.e Dept having emp, emp_act, emp_rsch..but when child node are having childrens then it is not working - it has to repeatedly call this procedure (recursive) and build below given SQL statement.
1. To change procedure to build xml sql statement when there are multiple childrens to child nodes (hierarchy)
2. To format the output in xml data
We are using ORACLE 11G and WINDOWS 7
CREATE TABLE node_mapping
(
NODE_ID NUMBER(5) PRIMARY KEY,
PARENT_NODE VARCHAR2(100),
CHILD_NODE VARCHAR2(100),
PARENT_NODEID VARCHAR2(50),
CHILD_NODEID VARCHAR2(50)
[code]....
View 1 Replies
View Related
Dec 27, 2012
I have the following control table.
ID S_OWNER SOURCE_TABLE A_OWNER ARC_TABLE CONDITION_COLUMN PERIOD_VALUE PERIOD_UNIT
1 wedb Auction_table wedb Arc_Auction_tableAuction_date 15 Days
1 wedb Sales_table wedb ArcSales_table Sales_date 180 Days
1 hr Accounts_table hr Arc_Accounts_tableAccount_date 2 Years
2 concor Concur_table con Arc_Concur_table Last_update_date 4 Months
Like this 1000 entries are there in the control_table.
I want to pass all the columns from my control table dynamically.I am able to pass all the columns dynamically,except the PERIOD_VALUE.I have stucked up how to implement this condition.My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.
For Example:
For Auction_date column I want to get the data which 15 days old.
For Sales_date column I want to get the data which 180 days old.
For Account_date column I want to get the data which 2 yers old.
For Last_update_date column I want to get the data which 4 months old.
[code]....
View 12 Replies
View Related
Jul 20, 2010
i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.
how can i find the columns names dynamically for filling the grid.
View 6 Replies
View Related
Mar 28, 2012
I wanted to calculate the time variance dynamically as below.I have one table called process_status where we can see the process name, start time, end time and the status .
1. Now i wants to calculate the duration(end_time - start_time) in hh:mi:ss format and i got the output using below query.
select name, start_time, end_time, status, to_char(trunc(sysdate) + (end_time - start_time), 'HH24:MI:SS') duration from process_status ;
I got currect oupput but i am stuggling with the below step.
2). Actually the process expectaiotn time is 2 hours only(fixed time). Now i wants to calculate variance b/w expected time and the duration time(end_time - start_time)
i.e variance = expected_time - (end_time - start_time ) ;
here expected time is 02:00:00(fixed).
I tried using with to_char and to_date function but no luck.
View 7 Replies
View Related
Mar 15, 2013
i want to create the list item dynamically on the when new form instance this is the code....
group_id := create_group_from_query('lst','select (imt.item_id), to_char(imt.item_code, from items_mt imt');
v_num := Populate_group(group_id);
populate_list(v_num,group_id);
but the list is not the populate.
View 14 Replies
View Related
May 8, 2013
We have an requirement to build (XML) Select statement dynamically based on the mapping table (which is in hierarchy format)
I have removed xml keyword, so query will not work. but need procedure which will recursively check mapping table (parent and child table) and build below given select statement.
Example 1:
SELECT dname,
(select ename,
[Code]....
View 8 Replies
View Related
Jul 4, 2011
I am working on 6i reports.I have below scenario to achieve.
I have FIND window and MAIN Window.
FIND Window: contains EMPNO, ENAME,and FIND button.
MAIN WINDOW: EMPNO, ENAME, SAL, JOB
If i enter something in FIND window, by clicking FIND button, it will populate data in MAIN window.I have below query in WHEN BUTTON PRESSED TRIGGER.
declare
cursor c1 is select empno, ename, job, sal from emp
where empno=nvl(:FIND.empno,empno)
and ename=nvl(:FIND.ename,ename)
begin
--here i am populating data into MAIN window block
end;
It's working fine.
REQUIREMENT: i want to pass where statement dynamically. Let us say if i pass only empno in FIND window, i want to pass 'WHERE empno=:FIND.empno' to existing select statement [I will keep select statement same without where condition]
If i pass empno and ename both, i want to pass 'WHERE empno=:FIND.empno and ename=:FIND.ename.
How to pass where condition to existing select statement based on parameters we pass in FIND window.
View 8 Replies
View Related
Oct 20, 2011
I Have a table like this. 12 rows and 8 columns.And column 1 Dt is going to change everytime,as date and months proceeds.and corresponding columns values are going to change.
I want to convert rows to columns dynamically..
DT COL1COL2COL3COL4COL5COL6 COL7
01/01/20111234567
01/02/2011131415161718 20
01/03/2011252627282930 32
01/04/201137383940414201/05/2011495051525354 55
01/06/2011616263646566 67
01/07/2011737475767778 29
01/08/2011858687888990 92
01/09/2011979899100101102 04
01/10/2011109110111112113114 115
i want result like this.
01/01/201101/02/201101/03/201101/04/2011
1 9 11 12
2 11 23 12
3 11 11 11
4
5
6
7
8
View 2 Replies
View Related
Oct 22, 2013
i need to create a replica of an existing table in same schema dynamically so as to reflect the modifications on replica table which has been done on primary table.this table we need to maintain as snapshot,like if a column is added or deleted then it should automatically be added or deleted on the replica table.how should i approach this.
View 4 Replies
View Related
Jan 2, 2011
I have a procedure p1 in schema s1.i want to call this procedure in schema s2 dynamically.how can i do?
View 4 Replies
View Related
Dec 3, 2010
I have created a procedure to build trigger dynamically using Dynamic SQL. Here procedure created successfully, when we execute the procedure to build trigger getting the following error.
Note: We are able to create a Trigger, but it is INVALID. if we get the code of a trigger and execute, trigger created a successfully with Valid Status.
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APPS.CREATE_TRIGGER", line 28
ORA-06512: at line 2
SQL*Plus internal error state 2087, context 47:0:0
Unsafe to proceed
View 2 Replies
View Related
Apr 19, 2010
How do you specify the value in START WITH dynamically during runtime. For example consider the bewlo scenario:
CREATE TABLE ISCT
(
ITEM_NO VARCHAR2(15 CHAR) NOT NULL,
ITEM_TYPE VARCHAR2(3 CHAR) NOT NULL,
ITEM_TYPE_SCO VARCHAR2(3 CHAR) NOT NULL,
[Code].....
Now if you create a view:
create or replace view test_v
(ITEM_NO, ITEM_TYPE, ITEM_NO_SCO,ITEM_TYPE_SCO)
as
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
[Code] .......
Then run the below query:
select * from test_v where item_no_sco = '00245905'
--
the output is got within a second. but the real scenario is that both table icont and isct have millions of rows. In that case even this small query of the view takes 10minutes as there a FULL TABLE JOIN.
How do I give the START WITH in the view to make it faster i.e. even with millions of rows the output of this comes in seconds:
SELECT T.ITEM_NO, T.ITEM_TYPE, CONNECT_BY_ROOT T.ITEM_NO_SCO,T.ITEM_TYPE_SCO
FROM
(
SELECT NVL(C.ITEM_NO_CHILD, I.ITEM_NO) ITEM_NO, NVL(C.ITEM_TYPE_CHILD,
I.ITEM_TYPE) ITEM_TYPE, I.ITEM_TYPE_SCO, I.ITEM_NO_SCO
[Code] .......
MC: Remove code tags on INSERT, CM take care to not exceed 80 characters when you add coce tags: reformat yourself or do not do it.
View 15 Replies
View Related
Feb 24, 2013
look at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? how can we do this interest calculation by an Oracle program?
Customer Table
CUSTOMER_ID AMOUNT START_DATE END_DATE
1000 40000 15-JAN-13 15-FEB-13
1001 34000 15-DEC-12 15-FEB-13
1002 35678 15-NOV-12 15-FEB-13
Rates Table
RATE_PCT LOW_TENURE HIGH_TENURE
5 0 30
6 0 60
7 0 90CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
[code]...
View 5 Replies
View Related
Sep 26, 2011
I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query
USING p_ht_nm, p_ht_treat_source, var_cycle (1),
var_cycle (2),
var_cycle (3),
var_cycle (4),
var_cycle (5),
var_cycle (6),
var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY:
/* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */
INSERT INTO z_ca_get_lot_id
SELECT DISTINCT attrbts.lot_id
FROM (SELECT DISTINCT lot_id
FROM z_alv_cert_lot_attrbts_syn
WHERE NAME = 'Heat'
AND UPPER (text_value) = :p_ht_nm) attrbts,
[code]...
View 8 Replies
View Related
Mar 3, 2011
I've got a situation where I need to populate a list box of lab numbers when the user selects a matching date received date.
Right now it only works when the user presses enter on the field used to accept a date.
It also works when a user fills in date and then clicks twice on the listbox of lab numbers. (assuming there are some records for the date in question).
Both fields are in a data block that is not connected to a table. Here is the when-mouse-doubleclicked trigger
LAUNCH_CALENDAR('DISPLAY.DATE_BEAN');
IF :DISPLAY.DATE_RECEIVED IS NOT NULL THEN
GO_ITEM('display.list_labno');
--POPULATE_LIST_ITEMS('date_received_records','display.list_labno');
do_key('list_values');
[Code]....
As you can see we have tried several different triggers to get this to work with a calendar date chooser (java bean). The bean works and allows for a date to be returned. However, it does not want to populate the listbox.
View 5 Replies
View Related
Jun 6, 2012
We are using oracle 10g. with our code, Currently Oracle partitions are size the same way, each partition is using 10MB for data and 12MB for indexes (with the 6 default indexes); even of very few records are written in the partition.
We create partitions in advance as a part of nightly job with 10 minutes duration.Can some intelligence can be added where based on statistics we can decide the size of partition dynamically? Lot of space is getting wasted because of this reason.
View 1 Replies
View Related
Aug 23, 2012
I have 12 different tables
Record_jan
Record_feb
.
.
.
Record_nov
Record_dec
Based on the month i need to do some i/o operations from these table.
For eg: If it is january then Record_jan table should be accesed or for nov Record_nov table should be accesed.
If I try to store the table name in some variable and then give the variable name in place of table name then it gives error.
var:=Record_month;
Select * from var;
this gives error.
View 3 Replies
View Related
