SQL & PL/SQL :: Dynamic Query In View For Correct Column Name
Feb 20, 2012I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: Dynamic Way To Insert Data Into Correct Column In Table?
Dec 26, 2011i used sql loader to import data from csv file to my db.but every time the columns places are changed.o i need dynamic way to insert data into correct column in the table.
in csv file contains column name and i insert this data to temp table, after that i want to read data over column name.also i read the column names from (All_Tab_Columns) to make combination of column name between temp table and All_Tab_Columns table to insert data to right place...
SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)
Dec 5, 2012I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies View RelatedSQL & PL/SQL :: Dynamic Column Value Query?
Nov 3, 2010I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)
CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),
[Code]...
After the Data is loaded, we see data look like the above.
(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.
If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?
Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL
[Code]....
SQL & PL/SQL :: How To Use Column Names Generated From Dynamic Query
Feb 8, 2013I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.
For that i have used a ref cursor to open and insert the table.
In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn
find below the sample
TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;
[Code].....
PL/SQL :: Checking Correct Data Format Using Sql Query?
Aug 30, 20131) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-
how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-
how do i check whether all dates in date column are in above format or not using sql query?
SQL & PL/SQL :: Oracle View Have Dynamic View Function?
Jul 28, 2010Are oracle view have Dynamic view function?
View 8 Replies View RelatedSQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy
Oct 15, 2013I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
JDeveloper, Java & XML :: Design Dynamic Table And Insert In A View
Feb 25, 2011I'm in a situation where i have to design dynamical XML table that gonna get inserted in a view:
late'si consider the following table:
CREATE TABLE FB_XML
(
ID NUMBER(10) NOT NULL,
FB_ID NUMBER(10),
XMLCONTENT CLOB
)
the XML content field have content like the following:
XMLROOT(XMLELEMENT (
"Form",
XMLELEMENT ("elements",
(SELECT XMLAGG (
[code].......
so late consider for the first row i insert for the
* id:1
* fb_id : 25
* xmlcontent:
XMLROOT(XMLELEMENT (
"Form",
[code]........
the code works "fine" here but the xml in the fb_xml table is not EXECUTED it simply escaped by oracle , i know i should add something so that the XML have to execute and generate.
SQL & PL/SQL :: Get Dynamic Column Name And Corresponding Value
Jan 11, 2011I want get dynamic column names and its corresponding values...Consider the below sample code
CREATE TABLE LECTURER1
( ID NUMBER(5,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
MAJOR VARCHAR2(30),
CURRENT_CREDITS NUMBER(3,0),
PRIMARY KEY (ID));
[code]....
get the dynamic column values.
SQL & PL/SQL :: Dynamic Column Name
Nov 17, 2011How do I add a column (Status) to a select query based on the data returned.
Select Col1, Col2
from
Table 1, Table 2, Table 3
Where
Table 1.Key1 = Table 1.Key1 and
Table 2.Key1 = Table 3.Key1 and
Table 1.Col1 in ('xyz','yty','ttr')
Output Expected:
Col1 Col2 Col3(Status)
xyx 1mst Sucess
yty Null No Data Found
ttr Null No Data Found
The Col1 value yty and ttr does not exist in the database
Dynamic Column Resizing
Apr 26, 2010I have a stored procedure that updates a series of columns (set at varchar(500) just to ensure there is ample space for whatever data is inserted into it) .Once it has updated though I've wanted to trim the column width down to the maximum size of the column value. I determine the max size of the column length and use that in a pl/sql command to ensure the width isn't smaller than the largest data entry.
When I try to do this I get the following message:
ORA-01441: cannot decrease column length because some value is too big
Now after hunting this down it seems as though you cannot decrease the size of a column once data has been inserted into it.
Any success in reducing the column width of a table that has data in it?
Why do I need to do this? I am exporting this table from Oracle into a propriety application and I am trying to reduce teh size of the export table by removing any unecessary space the unused spaces in the column. This will benefit the local app in addition to reducing the impact on the filesystem storage.
Dynamic Old Column Name In Triggers?
Jul 26, 2004I have column names stored in a table(say t1).
Inside a trigger I have to dynamically get the : old. values for only these columns.
This I need because during update trigger on a table (say t2) I am supposed pickup the coumn names stored in t1 and get the : old values of only these columns to insert into a new table .
SQL & PL/SQL :: Dynamic Update Of Column Name?
Jan 23, 2012i have creatd view,in which i need to show the entry of year in column wise,though the data is in row /record wise.By iteration i can manipulate the year in decode function like " (DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance))",but is there any way to chachge the alias name /column name,here is the query
select
SUM(DECODE(a.Year,(select distinct max(year)-1 from BI_BALANCE_SHEET), a.Balance)) as year2010,
SUM(DECODE(a.Year,(select distinct max(year)from BI_BALANCE_SHEET) , a.Balance))as Year2011
from DEMO a
SQL & PL/SQL :: Dynamic Column In Output?
Oct 15, 2013I need to have columns dynamically defined in my SQL based on the column values in the table.
Selct emp_no,dept_no,grade from emp;
emp_no dept_no grade
----------------------------------
1 10 A
2 20 B
3 20 A
4 10 C
5 10 A
6 20 C
7 10 B
8 20 C
I need the output as
dept_no Count_A Count_B Count_C
---------------------------------------------
10 2 1 1
20 1 1 2
I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D.
SQL & PL/SQL :: Dynamic Column And Values?
May 30, 2012I have two string one is comma separated values as a column name and another string which is having the comma separated value corresponding to the column name.
Example:
string1:='col1,col2,col3'
string2:='1,aaple,'2-jul-75'
now i have to create and run a query which will be like that
select * from emp where col1=1 and col2='apple' and col3='2-jul-75'
SQL & PL/SQL :: Dynamic Rows To Column
May 18, 2010converting Rows to Column for a specific group.
Following is the query
SELECT Lpad(papf.employee_number, 6, '0') "File#",
papf.full_name,
Decode(ppee.processing_type, 'R', Decode(ppee.element_name,
'Regular Wages USD', peev.screen_entry_value,
'Regular Salary USD', Round(peev.screen_entry_value / 26, 2),
0),
[Code]...
Which gives me out put as
Example 1
Employee Number Name Value Rate Type Type Assignment Id
-----------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66
I want to convert above out to look like following
Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id
-------------------------------------------------------------------------------------------------------------------
4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66
Example of Query Output 2.
Employee Number Name Value Rate Type Type Assignment Id
--------------------------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 1345 N Patent Bonus 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66
Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased
ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId
-------------------------------------------------------------------------------------------------------------------
4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64
5 E2 6923.08 S Reg Sal 65
6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66
PL/SQL :: Dynamic Column In A Cursor
Sep 1, 2012So, for lack of a better way to explain it, I need to do the following.
I need a EXECUTE IMMEDIATE to be able to use a CURSOR but its saying its not declared. Is their a way to pass the cursor so a Execute Immediate can use it...
V_SQL := 'REPLACE(V_DATA,'TEST',CUR_DATA.'||V_FIELD||');';
EXECUTE IMMEDIATE (V_SQL);
I know thats just a small part... but it shows the end goal. I need to append a VARCHAR that has the COLUMN NAME I need from the CURSOR. CUR_DATA is the open cursor. V_DATA is just a VARCHAR with text in it.
PL/SQL :: How To Use Column Names Generated From Dynamic SQL
Feb 8, 2013I have a problem with Dynamic SQL.
I have written an SQL which will dynamically generate the Select statement with from and where clause in it.
But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.
For that i have used a ref cursor to open and insert the table.
In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn
find below the sample code:
TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;
v_table VARCHAR2 (4000) := NULL;
v_where VARCHAR2 (4000) := NULL;
v_ins_tab VARCHAR2 (4000) := NULL;
v_insert VARCHAR2 (4000) := NULL;
v_ins_query VARCHAR2 (4000) := NULL;
[Code]...
How to fetch the column names here
|| ');';
EXECUTE IMMEDIATE v_ins_query;
END LOOP;
SQL & PL/SQL :: Define A Cursor Using Dynamic Query?
Aug 29, 2010Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.
I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).
SQL & PL/SQL :: Using Escape Character In Dynamic Query?
Aug 28, 2012I'm trying to replace the variable value v_tblname in dynamic SQL. But not able to escape the single quotes character.
DECLARE
v_sqlcols VARCHAR2(2000);
v_sqlcols1 VARCHAR2(2000);
v_tblname VARCHAR2(50) := 'DEPT';
BEGIN
v_sqlcols := q'[SELECT LISTAGG(COL,',' ||CHR(10)) WITHIN GROUP (ORDER BY COL)
FROM (
[code]....
SQL & PL/SQL :: How To Get Count On Executing Dynamic Query
Sep 16, 2010l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';
by using the execute immediate or any other command, how can i check whether the query returned any rows or not?
SQL & PL/SQL :: Dynamic Query For Swapping Names
Mar 25, 2011writing dynamic sql query for swapping names:
the output shuld be like that
input output
a a
a b b a
a b c c b a
a b c d d c b a
i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.
SQL & PL/SQL :: Using NULL And DECODE In Dynamic Query
Sep 2, 2011small piece of PL SQL code. how to make this query.Requirement is that a concurrent program is run with parameters and one of them i_num_org_id is non mandatory so it can come as NULL...Now in an existing code which i have to change, it uses a query as
SELECT xyz
FROM abc_table
WHERE <various conditions>
AND DECODE(i_num_org_id,NULL,1,table.organization_id) = NVL(i_num_org_id,1);
Now with the above way, if the program is run with some value for i_num_org_id or run as normal query (with NULL as the value) inside a PLSQL procedure/package then it runs fine.This query if you run in Toad etc then too it will work fine but if it is made a dynamic SQL and then used as either EXECUTE IMMEDIATE or opened as a cursor then we get a "Missing expression". I created this small anonymous block to test this and this will go into missing expression error
declare
l_num_org_id NUMBER := NULL;
l_temp VARCHAR2(100);
l_sql varchar2(1000);
begin
l_sql := 'SELECT '||''''||'abcd'||''''||'
[code].....
how i can reformat this query so that even if NULL value comes for i_num_org_id then it is handled.I am aware about CASE but that cannot be used in WHERE clause i guess.
PL/SQL :: Pipelined Function With Dynamic Query?
Nov 12, 2013 Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.
create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,
req_id number); type t_disc_req is table of r_disc_req; function F_GetDiscReq return t_disc_req pipelined; procedure P_ProcessDiscReq;end; CREATE OR REPLACE PACKAGE BODY test13_pkgAS FUNCTION F_GetDiscReq RETURN t_disc_req PIPELINED IS lo_disc_req r_disc_req; BEGIN FOR r_row IN (SELECT disc_line_id, req_id FROM edms_disc_lines_stg WHERE ROWNUM < 10) LOOP lo_disc_req.disc_line_id := r_row.disc_line_id; lo_disc_req.req_id := r_row.req_id; PIPE ROW (lo_disc_req);
[code]...
SQL & PL/SQL :: How To Overcome 32k Limit In Dynamic Query
Dec 22, 2010I have a dynamic query that is ending up getting larger than 32k and this query is the base of a ref cursor that would in turn return the results to the application. How can I solve this problem since the largest content of a pl sql variable or literal is 32k ( said by oracle ) ?
PROCEDURE sp_large_query (c1 OUT sys_refcursor)
IS
BEGIN
OPEN c1 FOR ' SELECT STATEMENT WITH MORE THAN 32K ';
END;
SQL & PL/SQL :: Dynamic Sql Query String - Getting Error?
Aug 22, 2010FUNCTION get_attributed_sedol( p_ref_number IN VARCHAR2,
p_field IN VARCHAR2 )
RETURN VARCHAR2
IS
l_query VARCHAR2(1000);
[code]...
It gives me an error on the line where the execute immediate statement is.
Quote:
ORA-00905
missing keyword
Cause: A required keyword is missing.
Action: Correct the syntax.
But when I check my stack trace table to see what the actual query string looks like, I see this
Quote:
SELECT sedol INTO l_attributed_sedol_code FROM integration.tmp_attributed_sedol WHERE CLIENT_LEDGER_REF='LEAE057090' AND ROWNUM=1
There's nothing wrong with that, is there? It executes fine if I try it manually.
SQL & PL/SQL :: Transpose And Dynamic Column Depending On Result
Nov 4, 2013I am having a table structure like below.
city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........
i have to place the data in following manner.
city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
How Many Quotes To Use In Dynamic Query Using IN Clause With String
Nov 23, 2011I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')
My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"
How many quotes are supposed to go around val1 and val2?
I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''
When I run the procedure in Oracle it works with '''val1'',''val2'''
SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure
May 14, 2013I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:
Procedur MyProc
(
In_EmpID Number default null,
In_EmpName Varchar2 default null,
in_JoinDate Date default null
[code]....
I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.