Reg - Handle Not Showing Alias Name?

Oct 1, 2012

My OTN account handle shows the number but till yesterday it was showing the Alias Name "ranit B".

Handle:      912545
Email:      
Status Level:      Newbie (20)
Registered:      Feb 6, 2012

[code]...

View 13 Replies


ADVERTISEMENT

How To Add Alias In Select * Query

Feb 23, 2009

I want to select all the field from the table and want to put alias for only field i m just wondering is there any way to write a query something like

select *, emp as employee
from empMaster

here i want to display all the column but only with emp column i want to put alias.

View 1 Replies View Related

Alias Not Usable In WHERE Part?

Apr 30, 2007

why this query works fine:

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
FROM zajezd Z
ORDER BY Z.JMENO

But when I try to use the alias to filter the results like this (note the WHERE part):

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK

[code]...

Then it throws an error message ORA-00904: Unknown Identifier for the column/alias POCET_ZASTAVEK?

View 2 Replies View Related

SQL & PL/SQL :: How To Add Alias Column Name To Group By

Aug 10, 2011

I have the following query:

SELECT AGENCY, COUNT(*)
FROM (SELECT A.AGENCY,SUM(A.NUM_LOGIN)
FROM GOVAGENCY A
WHERE AGENCY = 'DOD1'
GROUP BY A.AGENCY
[code].......

The results are:

AGECNY COUNT(*)
-------- -----------
DOD1 1
DOD2 1
2

The rollup value has a blank label instead I want to have a label to the rollup result also ex:

AGENCY COUNT(*)
--------- -----------
DOD1 1
DOD2 1

DEPT. OF DEFENSE 2

View 5 Replies View Related

SQL & PL/SQL :: Using Alias In Same SELECT Statement

Sep 1, 2011

I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).

Ex:

Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;

Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?

View 12 Replies View Related

SQL & PL/SQL :: Putting A Condition On Alias Name

Jun 6, 2012

Is there any way through which we can put forth a condition on Alias name instead of column name in a table.

For example: Select emp_name "Employee Name" fom dual where emp_id is not null;

Instead can there be a workaround so I can put "Employee Name" in where clause?

View 7 Replies View Related

SQL & PL/SQL :: How To Substring Alias Field

Mar 7, 2011

Is it possible to substring an alias field?

I am looking to substring the following so that the age comes back as 2 digits rather than an indefinite number

months_between (c.fatt,b.birth_dt)/12 age

View 2 Replies View Related

Alias In Inner Join Query

Feb 27, 2008

In my Oracle 9i Schema, I have two tables:

Table One

pocOne pocTwo
2 3
2 4
1 2

Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden

My SQL to fetch all records with Smith works great:

select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'

Now I need to create an alias for the Name field. Here is my attempt:

select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'

This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?

View 3 Replies View Related

SQL & PL/SQL :: Alias Used In Start By Connect With Program Gives Error

May 3, 2011

We have a query which uses start by and connect with method. this query works fine in our earlier version 10. But when we migrated to 11g, we are facing problem with the query.

Scenario 1:
------------
There is a table in Schema 1 and created a view for the same.

Table : create table alias_test1 ( A varchar2(10),B varchar(10),C varchar2(20),D varchar2(40), E varchar2(10),F number(1));
View: create or replace view alias_vw1
as select A, B,C,D, E,F from alias_test1

Values :
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block1', '136', 'TOTBANK', 'Total Bank', ' ', 1);
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block2', '136', 'PPRSEGHKB', 'HKB', 'TOTBANK', 2);

Now in the schema B, created a synonym for the view to access. create synonym alias_vw1 for alias_vw1@link.world

Query :
select srl_no,cde,pdesc,lvl,is_leaf,hcy
from( select srl_no,
cde, pdesc, level lvl,
connect_by_isleaf is_leaf,
Ltrim(replace(sys_connect_by_path (rpad(Trim(cde),19,' '),'~'),'~',' ')) hcy
[code]...

View 3 Replies View Related

PL/SQL :: Same Alias For 2 Tables / A Table Without Join in A Query?

Jan 24, 2013

In a query can we have the same alias for more than 1 table as in the following example

Select C.ContractNum, B.Billnum, B.Billamt,A.
From Contractmaster C,
Billdetails B,
Address A,
Currencymaster c,
Where B.billtype = 1
and C.Contractnum = B.Contractnum
and C.customerref = A.Customerref
and c.currencycode ='EUR'

In the above query, Table Contractmaster has an alias C --in capitals  while table Currencymaster has an alias c - in small caps ...

Is this possible in Oracle 11g ? Also i found that the table Currencymaster has no Join conditions I executed the query, without any errors!

View 4 Replies View Related

SQL & PL/SQL :: Alias Error - ORA-00933 SQL Command Not Properly Ended?

Mar 7, 2013

SQL> select lo.location_id,te.employee_id
2 from LOCATIONS AS lo,
3 TEST AS te
4 where te.employee_id = 169;

ERROR at line 2:
ORA-00933: SQL command not properly ended

View 2 Replies View Related

Client Tools :: Checking Users Execute Privilege On ALIAS LIBRARY

Nov 19, 2012

I want to check if a user has execute previlege on ALIAS LIBRARY,is there any table in which i can check this

View 7 Replies View Related

SQL & PL/SQL :: PLS-00402 - Alias Required In SELECT List Of Cursor To Avoid Duplicate Column

Oct 25, 2013

I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.

for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,

[Code].....

View 6 Replies View Related

Handle When No Value In (Select Into)?

Mar 24, 2008

I have a stored procedure that does a "select name into v_name" SQL statement, which works fine. The only problem is when the query finds no data (the procedure will error because there is no value to put into the variable). Now i have a work around to this by running the query first with a count statement (which will always have results) and then if it is not equal to 0, then i will run the select into.

My question is, is there a better way to handle this kind of issue?

View 1 Replies View Related

SQL & PL/SQL :: How To Handle The Exception

May 19, 2011

The following code is working fine,But the thing is if column already exists in the table,then also the other statements should be executed instead of coming out of procedure.SO how can I handle that exception??

SQL> CREATE OR REPLACE PROCEDURE sp_execparameters(tname IN VARCHAR2,
colname IN VARCHAR2,datatype IN VARCHAR2)
2 AS
3 v_sqlstr1 VARCHAR2(1000);
4 BEGIN
5 v_sqlstr1 := 'alter table '||tname||' add '||colname ||' '|| datatype ;
[code].........

View 3 Replies View Related

How To Handle The Old Data

Oct 6, 2013

we are using oracle database. We hold the company records in the database. The records of the company should be available at anytime but over years the database keeps growing. Now how to handle the old data. All the data is important but if this goes for few more years then we need more and more disk space to handle. Is there any efficient methodologies to handle the old data? For us old mean the data that is 10 year old. 

View 9 Replies View Related

Backup & Recovery :: How To Link ASM File Alias And Database File Names

Nov 12, 2012

During a duplicate process to a new database name, rman crashed after the restore but before the switch datafile all.So now, we have under ASM the data files under the correct (new) diskgroup but v$datafile contains the previous names (and so diskgroup) and v$datafile_header is empty. RMAN is completly lost, our solution is to manually rename each file under SQL*Plus using ALTER DATABASE RENAME FILE. Unfortunately, we are using or migrating to OMF, so file names are meaningless and we are unable to associate ASM files with database files.

Any way (query or anything else) to associate the ASM files to the database files. Here's an abstract of what we have for one (small) tablespace:

ASMCMD [+ORAXQG1_L136_DG1/ORAXPG1/DATAFILE] > ls -l N47CAW*
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.276.799152039
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.318.799151641
SQL> select file#, name from v$datafile where ts#=17
2 /
[code]...

View 11 Replies View Related

SQL & PL/SQL :: How To Handle A File With UTL_FILE

Mar 11, 2013

I Got a scnario like a upload file contains

1.Header(Contains the File Name,Branch Name,MIS date)
2.Body(Customer Details)
3.Footer (File Name,Contians Total Number of Records and Number of Customers)

Like

CustomerDet_11-12-2013.csv,Chennai,11-12-2013
101,AAA,Savings,'M',10000
102,BBB,Current,'O',2000
103,CCC,Deposit Acct,'F',3000
CustomerDet_11-12-2013.csv,3,15000

How to handle this scnario through UTL_File

View 31 Replies View Related

SQL & PL/SQL :: How To Handle Multiple Exceptions

Apr 11, 2012

I am having a pl/sql code with two blocks namely one inner block and outer block

declare
v_v1 varchar2(10);
v_v2 varchar2(10);
v_v3 varchar2(10);

[Code].....

from the above code I want to execute both the inner block exception and outer block exception and is there any way to pl/sql engine that execute the outer exception first and inner next

View 7 Replies View Related

Forms :: How To Handle TList

Jul 7, 2010

I handle newly the Tlist. I have Tlist Which is generated on runtime by the following code

Declare
cursor C1 is select 'A' col
from table
union
select 'B' col
[code]......

The list have 3 element now. But when i try get the element count in tlist using Get_list_element_count. i am not getting anything.

View 1 Replies View Related

Forms :: How To Get Window Handle

Feb 9, 2010

The following code works fine in the form 6i, but in the same form 10g I get map_handle='0' and win_handle='0' instead of certain value as in 6i.

map_handle:=get_item_property('MAP.MAP_ITEM',WINDOW_HANDLE);
win_handle:=get_window_property('CG$WINDOW_1',WINDOW_HANDLE);

MAP.MAP_ITEM has Chart Item type.

View 3 Replies View Related

SQL & PL/SQL :: How To Handle Exception For ORA-06550

Nov 17, 2011

I trying to Assign XML content to the clob variable inside the pl/sql block, But i am getting the Below Error:

declare
t clob;
begin
t := 'xml content exceeds 32000 characters'

update test
clob_cloumn = t;
where id =2;

exception
when others then
null;
End;

ORA-06550: line 5, column 4:
PLS-00172: string literal too long

I need to handle this exception, i know it length exceeds 32000 characters, but even though i need to handle the exception and to perform other operation after handling the exception.

View 5 Replies View Related

PL/SQL :: How To Handle Breaks On Collection

Oct 22, 2012

I'm working on a plsql program and i'm using collections. I loop the collection and delete rows of it depending on the edits of my program. Here is the question.

if my collection holds
rows
[1]value
[2]value
[3]value

i can simply do something like FOR indx in invoice.first..invoice.lasthowever if i delete row 2 of my collection i get an error. no data found.
ive been researching this site

[URL].......

rows
[1]value
[3]value
[4]value

is there a way to tell plsql i just want it to loop the collection from top to bottom regardless of the index values?

View 2 Replies View Related

How To Handle Unique Constraint Violation

Feb 5, 2013

I've set up Oracle Streams replication between multiple N-way zones like this:

CODE------------------------------
Nway1 |Intersect| Nway2
DB1 --|-- DB3 --|-- DB5              DB1, DB2, DB3, DB4 - N-way1
  |   |    |    |    |               DB3, DB4, DB5, DB6 - N-way2
DB2 --|-- DB4 --|-- DB6              DB3, DB4 - intersection zone
      |         |  
-------------------------------

Physically DB1 .... DBN connected sequentially, so I want to prevent segmentation if some DB is unaccessible, but at the same time fight unneeded redundancy which uses too much link bandwidth to send N-1 LCR-s to all members of a single N-way group (so I want to split one big N-way zone into smaller ones and sequentially connect them into chain - it significantly reduces load on link if N is big enough (>10)). Also I want to have 2 DB in intersection zone to prevent single point of failure.

This scheme has one drawback - if change originated on DB3 or DB4, then it will be propagated (more correctly - applied and captured again) to DB5 and DB6 by both DB1 and DB2 (and, as far as I know, I have no means in capture rules to detect state of DB2 from DB1 and vise versa), so on DB5 and DB6 I get:

CODEORA-00001: unique constraint (DUMMYUSR.UNIQUE_RECORDS) violated error

I've set up standard conflict handler for apply process:

CODE

declare                                                                                        
    cols DBMS_UTILITY.NAME_ARRAY;                                                              
begin                                                                                          
    cols(1) := 'no';                                                                          
    cols(2) := 'name';                                                                        
    cols(3) := 'ddate';                                                                        
    dbms_apply_adm.set_update_conflict_handler(                                                
        object_name       => 'DUMMYUSR.DUMMYTBL',                                              
        method_name       => 'DISCARD',                                                        
        resolution_column => 'no',                                                            
        column_list       => cols                                                              
    );                                                                                        
end;

but it seems that it does not handle uniqueness conflicts. What is the best way to handle uniqueness conflict (is there a better way than to write custom error handler) and how serious is the impact on insert performance of having unique constraint and corresponding error handler. (In real world I will have to deal with tables with metainformation and without any keys).

Also, how to proceed with no error or raise exception from apply error handler with error that caused this handler to run? In oracle docs I can find only example that modifies LCR and runs lcr.EXECUTE(TRUE), but what to do if I don't want to reexecute LCR, but merely check error code and propagate error if it is not ORA-00001?

View 1 Replies View Related

Clob Handle In Custom Type In PHP

Apr 17, 2013

I created a custom type what it has a clob member variable:

CREATE TYPE custom_type AS OBJECT(
c_type INTEGER,
c_number NUMBER(38, 8),
c_varchar2 VARCHAR2(4000 CHAR),
c_clob CLOB,
[code]........

The inserting and updating works with constructor: ... custom_type (to_clob('foo')) . But if the data is longest than 4000 characters then the PHP isn't access to it.

So:
The normal case:
$sql = ("INSERT INTO table ( clob_field ) VALUES ( EMPTY_CLOB() ) RETURNING clob_field INTO :clob");
$stid = oci_parse($conn, $sql);
$clobdescr = oci_new_descriptor($conn, OCI_DTYPE_LOB);
oci_bind_by_name($stid, ':clob', $clobdescr, -1, OCI_B_CLOB);
oci_execute($stid);
$clobdescr->save('more than 4000 chars');
...

This case:
I tried:
$sql = ("INSERT INTO table ( ctype ) VALUES ( custom_type(EMPTY_CLOB()) ) RETURNING ctype.c_clob INTO :clob");
$stid = oci_parse($conn, $sql);
$clobdescr = oci_new_descriptor($conn, OCI_DTYPE_LOB);
oci_bind_by_name($stid, ':clob', $clobdescr, -1, OCI_B_CLOB);
oci_execute($stid);
$clobdescr->save('more than 4000 chars');

ORA says: "ORA-00904: CTYPE.C_CLOB: invalid identifier";

How to?Or how to access with any solution?

View 2 Replies View Related

How To Handle Spaces In Path Name Of File With PL/SQL

Apr 10, 2009

How do I handle spaces in the path name of a file with PL/SQL? This is what I am trying to do.

start f:folder namefolder name with spacesfoldernamescript.sql

I keep getting an error:

"The file 'f:folder.sql' does not exist."

Is there a special character I need to put in there for the spaces? How can I get this to work?

View 3 Replies View Related

SQL & PL/SQL :: How To Handle Null In Date Time

Jan 13, 2013

create table top22 (timed timestamp);
insert into top22(timed)
values (current timestamp),(null);
select timed from top22;
2013-01-14 10:50:22.17
NULL
select 'no is '||coalesce("num",'') from "bifrost"."top22"

getting error in DB2

if i remove this coalesce function
select 'no is '||timed from "bifrost"."top22"
no is 2013-01-14 10:50:22.17
NULL

but i need the second result is no is null

View 9 Replies View Related

SQL & PL/SQL :: Handle Exception Invalid Identifier

Feb 27, 2013

In my code i am selecting a column which does not exist for a table so i m trying to handle that error in exception handler but i am getting error

I want to handle ORA-00904 invalid identifier error

DECLARE
l_count INTEGER:=0;
invalid_identifier_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_identifier_exception, -06550);
invalid_identifier_exception1 EXCEPTION;
[code]......

select sdsd from emp;
*
ERROR at line 14:
ORA-06550: line 14, column 8:
PL/SQL: ORA-00904: "SDSD": invalid identifier
ORA-06550: line 14, column 1:
PL/SQL: SQL Statement ignored

View 3 Replies View Related

SQL & PL/SQL :: How To Handle Exception And Continue Process

Aug 24, 2012

I am trying to execute the below package. While executing i face a problem where when NO DATA FOUND the excpetion is handled and coming out of the loop.but i want to to continue the loop after handling the exception.

Is there anyway i can modify the code

CREATE OR replace PACKAGE BODY pkg_purge_archive_check
AS
PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER)
IS
v_num_1 NUMBER(10);
v_num_2 NUMBER(10);
v_multiplier NUMBER(10);

[code].....

View 15 Replies View Related

SQL & PL/SQL :: Trigger To Handle Primary Key Violation?

Sep 5, 2012

I am trying to handle PK violation error on a certain table, on INSERT, my best guess is I should use a trigger. The basic idea is this:

The table consists of 7 columns, and 6 of them are PK, and the seventh one is "amount". I want to handle PK violation in such way that, if it occurs during INSERT, then instead of inserting a new row, it should just update the "amount".

View 16 Replies View Related







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