SQL & PL/SQL :: How To Get Dynamic Columns Using Default EMP Table

Aug 18, 2011

i am practicing to get Dynamic columns as i have learnt from Orafaq SQL/PLSQL forum .i am using Default EMP table...first one is running smoothly as below:

1 DECLARE
2 v_sql VARCHAR2(32767);
3 v_refcur SYS_REFCURSOR;
4 BEGIN
5 v_sql := 'SELECT deptno ';
[code]....

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Dynamic Resultset For Linear Growing Columns In A Table?

May 7, 2013

Provided a sample table to determine the sales agents performance below.

Id AgenName Zone1 Zone2 Zone3 Zone4
1 ABC 90 85 65 55
2 SDD 45 67 78 90
3 ERF 85 30 52 45

If in any zone, the sales is less than 40, it should be failed.This can be determined using the below query

select Id, AgenName, Case when Zone1 < 40 or Zone2 < 40 or Zone3 < 40 or Zone4 < 40 Then 'Failed'
Else 'Eligible'
End as Flg_Comm
from sales_table;

But the problem is when the zone grows (zone5, zone6) every time the query requires to be added with or condition.

Is there a way to dynamically determine the result even when no. of columns grow (or added)?

View 1 Replies View Related

DEFAULT Value On Columns?

Mar 24, 2011

I wanted to know the consequences of adding a DEFAULT value to an existing column in a table.I understand that when you add a DEFAULT value to a column which is Nullable, Oracle updates all the null values for the column to the DEFAULT value, generating a lot of undo/redo data.

Is adding a DEFAULT value to a NOT NULL column a problem? As the column is NOT NULL, an update would not be done, so no undo/redo data will be generated.But will this cause a whole table scan? Is this advisable?

View 6 Replies View Related

SQL & PL/SQL :: Procedure With Default Columns

Apr 27, 2010

I have the following procedure...

CREATE OR REPLACE Procedure Updatetdef_normal_ranges (
i_tdef_range_id in number,
i_user_change_id in number default -1,
i_norm_range_low in number default NULL,

[Code]....

EXCEPTION

WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END Updatetdef_normal_ranges;

I am trying to execute it using the command

Execute Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1)

and I am getting the following error...

SQL> Execute Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1);
BEGIN Updatetdef_normal_ranges (i_tdef_range_id => 1, i_norn_range_high => 100.34, i_high_reflex_tdef_id => 100, i_low_reflex_tdef_id => 17, i_range_low_sched => -1); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'UPDATETDEF_NORMAL_RANGES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If I replace the update statement with dbms_output statement this procedure works fine.

View 25 Replies View Related

SQL & PL/SQL :: Unable To Add Columns With Default Value Due To Compression

Mar 22, 2011

We are trying to add 2 columns in a partitioned table.but we are getting below error:

SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables

But, it is not a compressed table.

select table_name, compression from user_tables
where table_name ='SVC_ORDER_CODE_FACT'

TABLE_NAME COMPRESSION
------------------------------ -----------
SVC_ORDER_CODE_FACT

actually we are trying to add 2 columns as below:

ALTER TABLE SVC_ORDER_CODE_FACT
ADD (MKT_FEATURE_KEY NUMBER default '-2', PREV_MKT_FEATURE_KEY NUMBER default '-2');

But, if we add column without default value,

View 2 Replies View Related

Application Express :: How To Set Default Displayed Columns

Jun 12, 2012

Env: APEX 4.1

For example, initially I created the reprot source as select c1, c2, c3 from t; later I added c4. But i was unable to figure out how to make c1, c2, c3, c4 become default displayed columns. I always have to click Action -> Select Columns, then move C4 from "Do not Display" to "Display in Report".

View 8 Replies View Related

SQL & PL/SQL :: Script To Generate Dynamic Columns

Mar 6, 2011

Is there any sql script to generate dynamic columns? pivot still needs the programmer to know the data to generate columns just like this.

select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

how about if i don't know the data in fk_department. is this possible in sql?

View 39 Replies View Related

SQL & PL/SQL :: How To Pass Dynamic Columns In Functions

Aug 16, 2010

I am facing with one problem while creation of function.

CREATE OR REPLACE FUNCTION fun1(
v_finyear VARCHAR2,
v_yrno NUMBER,

[Code].....

The function created successfully...

But it return the wrong values, like

SQL> SELECT fun1('2004-05', 8, 'FEB')
2 FROM DUAL
3 ;

TB('2004-05',8,'FEB')
--------------------------------------------------------------------------------
FEB

SQL> spool off;

2004-05 is the value of TABLE1
08 is also value of TABLE1
FEB is the Column_name of TABLE1

Actually, i have a value in FEB month, but it return FEB. FEB is the column_name of 'TABLE1'

View 1 Replies View Related

SQL & PL/SQL :: Transpose Dynamic Rows To Columns

Jul 15, 2013

I have a result table from a select query. Create_table.txt

FDAVAILABILITY_2GAVAILABILITY_3GTCH_TRAFFICHOSRCSSR
2013 Jun0.9870.992928359.09430.98360.975
2013 W270.99210.994328512.48650.98320.9756
2013 W280.99320.993728659.32560.98430.9734
2013 W290.99530.993328355.02630.98480.9741

I need to transpose this table to below one. But since my rows in 1 table is dynamic and it will increment each week and month I couldn't get a correct result from unpivot.

2013 Jun2013 W272013 W282013 W29
AVAILABILITY_2G0.99 0.99 0.99 1.00
AVAILABILITY_3G0.99 0.99 0.99 0.99
TCH_TRAFFIC28359.094328512.486528659.325628355.0263
HOSR 0.9836 0.9832 0.9843 0.9848
CSSR 0.975 0.9756 0.9734 0.9741

Is it possible to create above table from attached , with a select statement?

View 7 Replies View Related

Storing The Values Of Dynamic Columns In Oracle

Nov 18, 2012

I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..

table t1 has the following columns mapping.

col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----

In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.

I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).

for i in 1.2
loop

l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;

end loop;

my problem is : how to hold these values and update the above table?

View 2 Replies View Related

SQL & PL/SQL :: Rows To Columns With Dynamic Allocation Column Names

Nov 16, 2012

I want the output from two tables with rows to columns and generate dynamic basing on the columns.

For example: Table A (a1) , Table B(a1,b1)

Data: A B
------ ----
a1 a1 b1
--- ---- ----
1 1 x
2 1 y
3 2 a
2 b
2 c

o/p: Columnname col_1 col_2 col_3
-----------------------------------------------
a1 b1_1 b1_2 b1_3
----- ---------------------
1 x y
2 a b c

Columns should be generated based on the second table second column.

View 18 Replies View Related

PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns

Jun 6, 2012

I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:

SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS

If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....

However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....

View 7 Replies View Related

Forms :: Dynamic Columns - Dropdown Menu In Separate Block

Aug 27, 2013

I have 2 tables T1, T2

T1 is having 2 columns t1_c1, t1_c2
T2 is having 3 columns t2_c1,t2_c2,t2_c3

I need to design a form which should be like

there must be a pop item (Dropdown menu) in a separate block.

If I select 1 from the pop item,
T1 with 2 columns should display in a tabular form.
If I select 2 from the pop item,
T2 with 3 columns should display and T1 with 2 columns should hide.

So the form should be dynamic based on the selected pop item. Is there any chance to do this?

View 3 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

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

SQL & PL/SQL :: Default Values / Distinguishing Between Passed And Default Nulls

Nov 16, 2010

I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]

View 11 Replies View Related

SQL & PL/SQL :: Set Default Value For A Column Of Table

Aug 30, 2010

How to set default value of particular column of a table ?

View 5 Replies View Related

Add Constraint Default Value For Table Column With Name?

Oct 26, 2009

I would like to add a constraint "default value" for a table column, with a name.

I know how to do it for a constraint "not null" : ALTER TABLE tablename MODIFY columnname CONSTRAINT constraintname NOT NULL;

But I don't know how to do it for a constraint "default value".How can I do ?

View 2 Replies View Related

SQL & PL/SQL :: Import With A Default Constraint On Table?

May 28, 2012

I have a table as below. This table is not partitioned.

create table t1
(
d1 date,
n1 number not null
);

[Code]....

I took an export dump of the above table and after that I renamed the table t1 to t1_old. Then I recreated the table as below with a default constraint on d1 field.

create table t1
(
d1 date default to_date('01/01/1100','DD/MM/YYYY','NLS_CALENDAR=GREGORIAN'),
n1 number not null

[Code]....

But the problem here is the data import is taking too much time than what I expected. I can't afford a maxvalue partition here as of my DBA team mentioned if you add maxvalue partition adding partition later in a stage is difficult on this table

apply in this scenario and make the import faster. I am using oracle 10.2.0.1.0 version.

View 2 Replies View Related

PL/SQL :: Use Asterisk Along With Default Value While Selecting On A Table

Jul 18, 2012

Is there any way to use * along with the default value while selecting on a table...I need something as follows:

select *, 'JUNK' from table_name;

(or)

select 'JUNK', * from table_name;I know it will not work.

View 4 Replies View Related

Server Administration :: Default Partition In Table?

Oct 17, 2011

How can i check whether a partition tbale have default partition?

View 9 Replies View Related

Change Default Value Of Certain Column In A Table In Oracle 11g

Aug 18, 2013

I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote   

ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut

after I inserted new data to test the operation, I found that the new record has a value

= null for the column cus_taxthen 

I tested using the following query select

data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...

Change the default value of the column cus_tax.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable

Aug 30, 2011

I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.

Explanation:

In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.

I need to query on this dynamic table and return the result as return result. But i am not able to do it.

Here T_web_loylty_report_table is a type.

CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Employee Table In Scott Schema Created By Default

Oct 3, 2011

I am using Oracle 11g Release 11.2.0.1.0

OS: Windows

I am using the Employee table in Scott schema which created by default.

10,20,30,40,50 are the department ids.

I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids

Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30

View 7 Replies View Related

SQL & PL/SQL :: Create Table With Author Column Default Value As Administrator

May 25, 2013

I am trying to write a script to create a table with the author column default value as "Administrator" I use this execute immediate statement1;

Why does it create the table successfully with ',AUTHOR VARCHAR2(30) DEFAULT 1' and NOT with ',AUTHOR VARCHAR2(30) DEFAULT Administrator' ?

I want to set the COLUMN DEFAULT have to "Administrator" when I create the table.

When I try character I get this error "ORA-00984: column not allowed here"

statement1:='CREATE table TEST_ID2'
||'(TEST_ID NUMBER(6)'
||',test_name VARCHAR2(40)'
||notnull||
',date_created DATE DEFAULT SYSDATE '

[code]....

View 13 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

SQL & PL/SQL :: SELECT From Dynamic Table Name?

Jul 12, 2013

I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.

Here is the code :

DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);

[Code]....

But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.

View 3 Replies View Related

PL/SQL :: Insert Dynamic Table

Nov 9, 2012

Oracle 11.2

I trying to create a procedure that could update one or more different table into clob data - but only those tables that contain your search (indexed) word.

I have a 3 tables: item, common_lookup, member

>
CREATE INDEX i_item_barcode ON item (item_barcode) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_item_title ON item (item_title) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_item_subtitle ON item (item_subtitle) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_common_lookup_meaning ON common_lookup (common_lookup_meaning) INDEXTYPE IS ctxsys.context;
CREATE INDEX i_account_number ON member (account_number) INDEXTYPE IS ctxsys.context;

[Code]....

Now is the question.. If it possible to create the procedure search_execute which can add data depending on subbmited parameters ?
If it possible, can You give me an example (dbms_output/update_clob), for search_execute to recive something like that:

>
search_execute ('Wars', item, item_title)
>
>

select * from clob data :
1      1068     ASIN: B00003CX5P     1014     Star Wars - Episode I      The Phantom Menace      (CLOB)     (BLOB)          PG      MPAA     22-mar-2005     3     16-lut-2012 17:31:55     3     16-lut-2012 17:31:55     
2      1069     ASIN: B00006HBUJ     1014     Star Wars - Episode II      Attack of the Clones      (CLOB)     (BLOB)          PG      MPAA     22-mar-2005     3     16-lut-

[Code]....

I try write something like this (it is not compile - and I know where is mistake, I tried to figure it out in several different ways - This is just my way of thinking expression) :
>
CREATE OR REPLACE PROCEDURE search_execute (v_text IN VARCHAR2, v_column_name IN VARCHAR2, v_table_name IN VARCHAR2)
IS
v_clob CLOB;
v_tmp_clob CLOB;
CURSOR c1 IS (SELECT * FROM v_table_name WHERE contains (v_column_name, v_text) > 0);
CURSOR c2 IS (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name);
BEGIN

[Code]...

View 0 Replies View Related

Server Utilities :: Import A Table With Table Already Present With New Columns

Mar 31, 2010

I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.

TABLE_EXISTS_ACTION=TRUNCATE
The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
[code]...

It giving error while doing an import.

View 4 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Table Creation Using Timestamps?

Nov 28, 2010

I have a requirement to create the table monthly.The tablename will be the o/p of the following select query.

select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual

I am trying to create the table for creating the backup of the existing table TBR_AND_SECURITY_DEPOSIT.

create table (select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual) select * from TBR_AND_SECURITY_DEPOSIT

but it is throwing error as invalid table name.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved