SQL & PL/SQL :: Transpose Function In Link

Jul 5, 2010

my table is like this.

1 ON1 Abc
2 ON2 Bcd
3 ON3 Xyz
4 ON4 Bcd
5 ON5 Abc
6 ON6 Pqr

I want to write select query to get result in below format:

User Ordnum
Abc ON1,ON5
Bcd ON2,ON4
Xyz ON3
Pqr ON6

I am using Oracle 8i, I tried transpose function in the link URL.....

View 24 Replies


Created Database Link But Does Not Function

Jun 19, 2012

in a 9.2.0 db I create a db link but does not function :


SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :

Z:>sqlplus /nolog
SQL*Plus: Release Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.

View 4 Replies View Related

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

SQL & PL/SQL :: Created Function For Knowing Status Of Link Which Connect With Remote Database

May 22, 2013

I have created a function for knowing the status of link which connect me with remote database.function is as follows

CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);

Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.

View 1 Replies View Related

SQL & PL/SQL :: Kind Of Transpose Table

Sep 1, 2011

My need is to build columns for each month in table and find changes between monthes. My first try works fine, but next step should be performed using wider period.

with t2 as (
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 840 as currencyid, 3649509 as dealid, 7.5 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 12 as avalue from dual union all
select date '2008-02-29' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 12 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 3 as dealtypeid, 826 as currencyid, 3735006 as dealid, 13.5 as avalue from dual union all
select date '2008-01-31' as arcdate, 1867763 as cid, 2 as dealtypeid, 826 as currencyid, 3611270 as dealid, 13 as avalue from dual union all

As you can see, I've got virtual table named jan2008, feb2008, mar2008, apr2008 left-joined to tt1 table. But what should I do with much more wider period, for example 5 years? Can I do it without creating virtual tables for each month?

View 7 Replies View Related

SQL & PL/SQL :: Transpose (Not Pivot) A Table

Aug 18, 2010

I have a table in the following format:

CTitle1, CTitle2, CTitle3, CTitle 4, CTitle5

I have an application that requires the data to be in the following format:

CTitle1, Row1Val1,Row2,Val1,Row3Val1,Row4Val1,Row5Val1
CTitle2, Row1Val2,Row2,Val2,Row3Val2,Row4Val2,Row5Val2
CTitle3, Row1Val3,Row2,Val3,Row3Val3,Row4Val3,Row5Val3
CTitle4, Row1Val4,Row2,Val4,Row3Val4,Row4Val4,Row5Val4
CTitle5, Row1Val5,Row2,Val5,Row3Val5,Row4Val5,Row5Val5

So I am truly trying to transpose the columns and rows and not doing a traditional pivot. How I can do this in Oracle 9i?

View 9 Replies View Related

SQL & PL/SQL :: Transpose Data And Display It

Jul 1, 2011

I have to transpose data and display it using Sql. How can I do that?

Here is how the data looks in the table now:

Deal Cashflow Date
0007 1228888 01/12/2011
0007 898998 02/12/2011
0007 999999 03/12/2011
0008 888888 01/12/2011
0008 777777 02/12/2011

When I transpose the data, it should look like this:

Deal 01/12/2011 02/12/2011 03/12/2011
0007 1228888 898998 999999
0008 888888 777777

Never had to do this in the past.

View 6 Replies View Related

PL/SQL :: How To Transpose Cols To Rows

Aug 6, 2013

I have a requirement to transpose the column to rows like the one below, i am not getting the logic on how to do it, If any of you have come across this logic,

Existing Table:ABCD1234
Hypothetical table: ColumnValueA1B2C3D4

View 2 Replies View Related

SQL & PL/SQL :: Query To Transpose Column Names To Row

Mar 9, 2011

Query : select * from scott.dept where deptno=10;


But i want the above resultset as below,


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

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

SQL & PL/SQL :: Transpose And Dynamic Column Depending On Result

Nov 4, 2013

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

View 6 Replies View Related

PL/SQL :: Conditional Transpose Of Column Data Into Rows

Jul 26, 2013

I am using Oracle Database 11g Enterprise Edition Release - 64bit Production versionI have following table - 
drop table t2;

create table t2(BATot,Ly_BATot,LLy_BATot,BScTot,Ly_BScTot,LLy_BScTot,BAMSTot,Ly_BAMSTot,LLy_BAMSTot) as select
5000,2000,12600,20000,45600,35000,45000,56000,65000 from dual ;

select null class, batot,ly_batot,lly_batot,bsctot,ly_bsctot,lly_bsctot,bamstot,ly_bamstot,lly_bamstot from t2; Simple DML I am using -

- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        5000       2000      12600      20000      45600      35000      45000      56000       65000


View 7 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like


View 11 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  int getVal (int a);

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

PL/SQL :: Database Link From 11g To 10g?

Jul 5, 2012

I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;

It Returns
Database link created.

select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

what changes I need to make to tnsnames and listener at both servers.

View 1 Replies View Related

Database Link Not Active

May 15, 2013

I am newbie to oracle database.

I am using oracle 9i version and in EMCA satndalone application . I have created database links.

The database links have been created, and three databases are local.

When i am pressing the test button in emca . It gives notification database link not active.

What should i do?

View 3 Replies View Related

How Long Will It Take To Get Download Link

Mar 24, 2011

i just bought the oracle scripts from rampant. any know how long will it take for me to get the download link. for these scripts...

View 3 Replies View Related

Forms :: 6i - How To Link Different Form

Oct 22, 2010

the following problem. am using oracle forms 6i on windows 2003 server.

I have two tables Loan and deduction

loan_number number(5)unique not null,
loan_name varchar(15)not null,
loan_type varchar(15)not null,
actual_amount number(10)not null,


Both have loan_number columns as the primary key.

I created two separate forms using loan table and Deduction table.

Also according to requirement, when commiting in the loan form ,if 'loan_purpose' is 'PERSONAL' THEN deduction form SHOULD BE OPENED.


WHAT I WANT IS when deduction form pops up, it should have a matching loan_number as in loan form What shall I do to achieve this???.

View 4 Replies View Related

SQL & PL/SQL :: Using A Database Link In A Scheduled Job?

May 12, 2010

A procedure in a package uses a database link. The database link is defined without a username and a password. The user that uses the database link is supposed to be present in the opposite database as wel.

When the procedure is started in SQL*Plus or TOAD it runs perfect. But the procedure has to run in a scheduled job. And that doesn't work. The procedure fails because of: ORA-01017 invalid username/password. The user that is used to run the scheduled job is the right one.

View 5 Replies View Related

SQL & PL/SQL :: Database Link Creation

Jan 28, 2013

I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? give some steps in creation of a database link.

View 13 Replies View Related

DB LINK To External Database?

Jul 10, 2013

i need access to a view running on another database server. i configured my db link and tried to compile the view. but the following message occurs: ora-12154: TNS: Connection Identifier..

View 16 Replies View Related

10g Materialized View Using A Database Link

May 20, 2009

In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?

The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ

However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL..
I can't any info specific to 10gR2.

We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.

On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows:
CONNECT TO metadatashll IDENTIFIED BY password

Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';

The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows

CONNECT TO metadatashll IDENTIFIED BY password

Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).

View 6 Replies View Related

Fetching Data - Need Link Between Tables

Jun 16, 2011

In my select statement i am fetching the data from


I need appropriate links for that tables.

View 2 Replies View Related

Link Server Database To Oracle DB?

Jan 9, 2012

I found a link to a topic concerning this issue (click here), but the question mentions that the poster was looking for a "band-aid" fix...if this method will work for a more long-term solution

View 1 Replies View Related

How To Link Textfield In Form With Table

Jun 17, 2011

how to link my text field in the oracle form with the table..i created this form with text field in it,and i also created the tables with its constraint, but when i enter the data in the text field it doesn't apply to the constraint and it is also not inserted in the tables ,how to make the form commit to the database?

View 2 Replies View Related

Link Oracle 10g (XE) To Sql Server Express

Dec 3, 2010

I have been trying to link/connect Oracle XE 10g to Sql Server 2008 Express using Oracle heterogeneous services without any luck. I have MS ODBC drivers loaded on my PC which I know work as I can create and test the connectivity of a DSN which is successful, however when I try to link Oracle to that ODBC driver I get the following error message.

TNS-12545: Connect failed because target host or object does not exist.

Here is listener.ora file.



Here is how the tnsnames entry is defined.

SqlServer =

Here is a copy of the initSqlServer.ora file.

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
# HS init parameters

Just to be clear the ODBC DSN is named "SqlServer". I followed through all of the steps outlined in the following URL.


There seems to be a disconnect between the ODBC driver and SQL*Net. Possibly due to Oracle HS?

View 5 Replies View Related

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