SQL & PL/SQL :: How To Create Hidden Column In Table

Feb 15, 2012

I want to create a column which will be having unique values but while selecting data from table i am not willing to display same column in output.

SQL> create table temp
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 );

Table created.

SQL> insert into temp values (1,2,3);

1 row created.

SQL> select * from temp;

C1 C2 C3
---------- ---------- ----------
1 2

I want C3 as a hidden column and while selecting like

select * from temp;
expected output is...

C1 C2
---------- ----------
1 2

My main target is, C3 column should not display in select * ... stm but column with data should be there in table.

View 7 Replies


ADVERTISEMENT

PL/SQL :: Create Table From A Column

Jan 28, 2013

Today I’m trying to make a “table” from a column. The information I’m using looks like:

AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ - K

I need my table have these columns:

Column1     Column2      Column3
AAA B CCC
DDD E FFF
GGG H III
JJJ K

So I began to divide the string into smaller pieces:

SELECT LEVEL reg,
REGEXP_SUBSTR('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K','[^|]+', 1, LEVEL) ferroc
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ-K') - LENGTH(REPLACE('AAA-B-CCC|DDD-E-FFF|GGG-H-III|JJJ–K','|')) + 1; What I get is:
>

REG ferroc
1     AAA-B-CCC
2     DDD-E-FFF
3     GGG-H-III
4     JJJ - K
>

After this step I’m lost, I try to do almost the same thing than in the first query but mi information get mixed.

View 4 Replies View Related

Create A Table Without Any Single Column In It?

Nov 15, 2012

Is it possible to create a table without any single column in it?

View 1 Replies View Related

SQL & PL/SQL :: Create Trigger On Certain Column For Table Structure

Nov 3, 2011

create trigger on certain column for table structure.

SQL> desc MXMS_BF_TXN_DTL_T
Name Null? Type
----------------------------------------- -------- ----------------------------
DOC_NO NOT NULL VARCHAR2(200)
SEQ_NO NOT NULL NUMBER(24)
GL_CODE VARCHAR2(200)
TXN_NATURE VARCHAR2(200)
TXN_TYPE_CODE VARCHAR2(200)
[code].....

I need to collect new and old data whenever update statement fire on DOC_NO,POLICY_KEY,CRT_USER column.i have created only audit table for the above as below structure .

Name Null? Type
----------------------------------------- -------- ----------------------------
TIMESTAMP DATE
WHO VARCHAR2(30)
CNAME VARCHAR2(30)
OLD VARCHAR2(2000)
NEW VARCHAR2(2000)

Description:- TIMESTAMP is for when the modification happen.
WHO is for username
CNAME is for column which is modified
OLD is for old value for the modified column
New os for new value for the modified column

View 3 Replies View Related

SQL & PL/SQL :: Create A Table With Column Name As UID But UID Doesn't Work

Jan 11, 2011

It shows me error when i create a table with column name as UID.

Is UID a inbuilt function or anything else?

View 2 Replies View Related

Create A Separate Sort Order Column On Table

Oct 31, 2006

I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.

View 2 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

Performance Tuning :: Create Partitioned Table With Column Of LONG Or LONGRAW?

Nov 3, 2010

the reason behind the below statements:

1) We cant create TABLE PARTITIONED on CLUSTER or INDEX on CLUSTER TABLE.

2) We cant create a partitioned table with the column of LONG or LONGRAW? (But how it could be possible with BLOB, CLOB?

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I 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

View 1 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

SQL & PL/SQL :: Detecting Hidden Characters

Jul 31, 2011

I am not sure if the problem is related to hidden characters but its my best guess so far. I am trying to enhance a part of the ERD by creating a lookup for a column one of the table that uses text (finite set of values).

CREATE TABLE N_AGREEMENT_STATUS
(
STATUS_ID NUMBER(2) PRIMARY KEY,
STATUS_NAME VARCHAR2(10 BYTE)
);
INSERT ALL
INTO N_AGREEMENT_STATUS VALUES (1, 'INACTIVE')
INTO N_AGREEMENT_STATUS VALUES (2, 'ACTIVE')
INTO N_AGREEMENT_STATUS VALUES (3, 'CLOSED')
INTO N_AGREEMENT_STATUS VALUES (4, 'CANCELLED')
SELECT * FROM DUAL;

when I try to update the source table no update takes place (0 records updated) if I used the following statement:

ALTER TABLE N_AGREEMENT ADD STATUS_ID NUMBER(2);
UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS = STATUS_NAME );

but it works fine only if I used:

UPDATE N_AGREEMENT SET STATUS_ID =
(
SELECT STATUS_ID
FROM N_AGREEMENT_STATUS
WHERE N_AGREEMENT.STATUS LIKE STATUS_NAME || '%'
);

The strange thing is that when I use:

SELECT N_AGREEMENT.STATUS, N_AGREEMENT.STATUS_ID
FROM N_AGREEMENT
WHERE N_AGREEMENT.STATUS = 'ACTIVE';

it returns correct results and all status = 'ACTIVE' appear correctly!

View 20 Replies View Related

How To Get Information Of Hidden Parameters

Sep 16, 2012

)How do you view the value of the parameter that is being used by instance? show parameter..?
2)How do you get the information of hidden parameters
3)What is the database object that stores information related to various types of db connections over network
4)How do you verify since when the db session is running
5)How do you verify the Originating machine details of the database session
6)How do you verify the name of program that the db session is running
7)What is the naming convenion of Base tables. Where is the information of base tables stored?
8)How are dynamic views created. Whre is the information of dynamic views stored?

View 8 Replies View Related

SQL & PL/SQL :: Unable To Remove Hidden Character From Field

Mar 9, 2011

I ran into the following issue as mention below.

select dump(column_name) from table where column_name2 = 'HP1';

dump(column_name)
--------------------------------------------------------------------------------
Typ=1 Len=5: 194,160,82,88,66

I am trying to get right of these hidden character 194 and 160. i tried different method as mention below,

1) translate(column_name, chr(194)|| chr(16),'')

View 7 Replies View Related

RAC & Failsafe :: Listing Hidden Parameters For All Instances

Nov 30, 2010

I used following statement (user SYS as SYSDBA)

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE'),
decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
[code].......

to list hidden parameters. However, when using it on RAC I found that only a singe instance data is displayed.

View 8 Replies View Related

Reports & Discoverer :: Sorting Data And Hidden Frames

Dec 15, 2011

Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.

View 3 Replies View Related

Server Administration :: Quick Check On Hidden Parameter?

Mar 20, 2013

quick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.

Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.

orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';
KSPPSTVL
--------------------------------------------------------------------------------
auto

orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always

orcl>
update: added 11.1.x.x to my wishlist

View 10 Replies View Related

SQL & PL/SQL :: UTF8 - Unable To Remove Hidden Character From Field

Mar 20, 2012

My database is in UTF8 character set..

And it is not supporting chr(194)||chr(160)

what character set you were using then ?

And.. is there a way to handle non breaking spaces in UTF8..

View 16 Replies View Related

Forms :: Alert Windows Appear To Be Hidden And Application Locks Up

May 6, 2011

I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.

Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;

View 1 Replies View Related

Application Express :: Default Value For Hidden Item And Populate Query?

May 8, 2013

1.-) i got a page that contains 2 regions, lets say :master (HTML text) and a detail (tabular form updateable report) in my tabular form i got a hidden item that should take the value from the master form. how do i do this ?

2.-) i am calling a form from another form, sending a couple of fields as a link parameters. the second form is called as expected, but i need that records on the second form that match the parameters get displayed. how do i achieve that ?

View 6 Replies View Related

Application Express :: ReCAPCHA Plug-in Creating 3 Hidden Field

Sep 6, 2012

i m using Apex4.1 and data base oracle 11g, browser firefox

i m using recaptcha plugins i had tried it with both way by creating 3 hidden field and and also directly with taking a item based on plugin. URL.....

More specifically, the page has two items for user input (the captcha and a text item) and a Submit button. If the captcha is successful, then the text item will be submitted and redirect to another page. The problem I'm running into is that the text item is always submitted regardless of the captcha.

View 0 Replies View Related

Application Express :: Set Hidden Or Expanded Mode Of (Hide And Show Region) In 4.2

Sep 12, 2013

I just created a "Hide and Show Region" from a template in APEX4.2, and would like to control the behaviour of this region, i.e. 

1. At the page load, this region should be in "Hidden" mode
2. Once the user Opens the region, it should stay as "Expanded" mode ( even the page gets submitted), until the user chooses the "Hidden" mode again. 

So I tried the following

1. Chose the Hide and Show Region(expanded) template for the APEX region
2. uses Dynamic action to force the "Hidden" mode by setting Style as style="display: none;"  at the page load event. 

But it still shows the region in  "Expanded" mode after the page gets loaded.

View 0 Replies View Related

SQL & PL/SQL :: Create Another Column - Combined_Month

Dec 15, 2010

I would like to create an SQL with some of these information.

2 columns -> Month_start, Month_end
Sample data:
1. 4/2010, 4/2010
2. 4/2010, 5/2010
3. 6/2010, 6/2010
4. 6/2010, 7/2010
5. 9/2010, 9/2010
6. 9/2010, 10/2010
....... ......

I would like to create another column called combined_month. The logics are the following:

1. If the Month_start = Month_end, get information from Month_start only. Query will return 1 line.
2. If the Month_start is different from Month_end, get information from Month_start and also, get information from Month_End as well. Query will return 2 lines.

The result will look like the following:

Combined_Month
4/2010 from (record # 1)
4/2010 -> (record # 2)
5/2010 -> (record # 2)
6/2010 -> (record # 3)
6/2010 -> (record # 4)
7/2010 -> (record # 4)
9/2010 -> (record # 5)
9/2010 -> (record # 6)
10/2010 -> (record # 6)

This 6 records will be regenerated into 9 records.

View 8 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i 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.

View 3 Replies View Related

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

May 11, 2012

I 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) ?

View 7 Replies View Related

SQL & PL/SQL :: Create New Column Based On MIN DATE

Jun 26, 2012

CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))

INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));

Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.

I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12

View 10 Replies View Related

Error Message When Attempting To Create 3rd Column

Jul 26, 2010

I've set up a query that creates 2 columns 'UVLCredit' and 'UVLDebit' and what I'm trying to do is subtract the 'UVLDebit' column from the 'UVLCredit' column and have the resulting value show up in a column called 'UVLTotal.

I'm multiplying 1 column times another to create a new column called UVLCredit, and then doing it again (with a different column) to create a 2nd new column called UVLDebit...the last thing I'm trying to do is to take the results of the 1st new column and subtract the results of the 2nd newly created column to create a 3rd new column called UVLTotal. The error states that the 'UVLDebit' column is an invalid identifier.

See code below....

CODESELECT          
T.PO_RELEASE_NBR, T.PO_LINE_STATUS, T.FACILITY,        
TI.STATUS_DATE, TI.QTY_ORDERED_UP,
TI.PO_UNIT_PRICE, TI.QTY_REC_TOTAL_UP, TI.QTY_INVOICED_UP,
[b]sum(TI.PO_UNIT_PRICE) * (TI.QTY_REC_TOTAL_UP) as "UVLCredit",
sum(TI.PO_UNIT_PRICE) * (TI.QTY_INVOICED_UP) as "UVLDebit",
[code]........        

View 3 Replies View Related

SQL & PL/SQL :: Create One BLOB Column Including One PDF File?

Dec 24, 2010

I use Database 11g .

I have 2 BLOB columns include pdf files.

How can I create one BLOB column include one one pdf file? merge 2 pdf files into one file.

View 4 Replies View Related

SQL & PL/SQL :: Create Rank On Column Without Using Rownum Function

Mar 17, 2013

can we create rank on a particular column without using rownum and rank function.

View 9 Replies View Related

Application Express :: How To Create 3D Column Chart

Sep 6, 2013

MONTHSTAT_NAMEPERCENTAGE_OF_TOTALApr-08USER_TIME35.43Apr-08SYS_TIME3.74Apr-08BUSY_TIME38.33Sep-13USER_TIME42.92Sep-13BUSY_TIME45.54Apr-08IOWAIT_TIME20.12Aug-13SYS_TIME2.43Aug-13IOWAIT_TIME0.04Aug-13BUSY_TIME45.05Aug-13USER_TIME42.59Sep-13SYS_TIME2.52Sep-13IOWAIT_TIME0.04select null, month, stat_name, Percentage_of_Total from oshistory

I want to create 3d column chart for above chart.

View 3 Replies View Related

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

Mar 22, 2013

I am describing a SQL statement to get it's column list:DECLARE

cur     NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;

[Code]....

Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?

View 4 Replies View Related







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