Recursion - Concatenation On Database?

Nov 27, 2009

We are using Oracle 11g (11.1.0).I'm not all that prolific when it comes to writing queriesI have a table...

Table1
-----------------------------------------
oid narr parent
-----------------------------------------
1 some narrative null
2 more narrative 1
3 a bit of test narrative 2

Simply put, I need a query that will recurse up through each rows parent and return the concatination of all parents and itself narrative.

Expected output
------------------------------------
some narrative
more narrative some narrative
a bit of test narrative more narrative some narrative

The requirement is that this is one single query as it will be called from a third party application we are using. We need this recursion and concatenation to be done on the database, as while we have control over the database queries that get executed we have no control over the internal source of this third party applications.I have been digging around for a bit, I have tried using a combination of JOINS and UNIONS but keep hitting a brick wall.

The best I could come up with is...
SELECT concat(n1.narr, n2.narr) FROM table1 n1
JOIN table1 n2 ON n1.oid = n2.parent

But this only returns two lines, for oid's 2 and 3, and only concatinates with the immediate parent.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Importance Of Recursion Functions

Aug 31, 2012

we need to use recursion functions in pl/sql?? How can we use them in efficient way?? Also focus on mutual recursion in real world scenario.

View 4 Replies View Related

SQL & PL/SQL :: Find All Data Items In Hierarchy Recursion?

Jan 21, 2003

I need to find all "report to" supervisors in a hierarchical chain. A binary search won't work, as each level may contain > 2 supervisors. Example:

1

2 3 4

5 6 7 8 9

10

10 reports to 9
9 reports to 4
5 and 6 report to 2
7 and 8 report to 3
2, 3 and 4 report to 1

Result for all reporting to 1 would be a table containing 2, 3, 4, 5, 6, 7, 8, 9, 10 (order is unimportant!).

I think I need to use recursion. I can do this in C; need code for PL/SQL??? (I can't get the "tree" to display well!)

View 4 Replies View Related

SQL & PL/SQL :: Concatenation On Columns

Feb 23, 2012

I have problem with query ( i'll only use DUAL table for example):

SELECT T.DUMMY P1,
T.DUMMY P2,
T.DUMMY C1,
T.DUMMY C2,
T.DUMMY C3,
T.DUMMY C4,
T.DUMMY C5
FROM DUAL T;

I need to concatenate only columns with name starting with C and add separator between them. Is there any function do concatenate columns using separator like:

SELECT T.DUMMY P1,
T.DUMMY P2,
DBMS_SOMETHING.SOME_FUNCTION(',', C1,C2,C3,C4,C5)
FROM DUAL T;

Or I must do like this:

SELECT T.DUMMY P1,
T.DUMMY P2,
C1 || ',' || C2 || ',' || C3 || ',' || C4 || ',' || C5
FROM DUAL T;

problem is that I have 3.000+ columns in 23 different selects :/

View 3 Replies View Related

PL/SQL :: How To Do String Concatenation

Nov 22, 2012

I am trying
  
IF LENGTH(v_final_string) < 3800 THEN
        SELECT nvl2(v_final_string,v_final_string
          ||',' ,v_final_string)
          || temp.temp_string
        INTO v_final_string
        FROM DUAL;
        DBMS_OUTPUT.put_line ('v_final_string=' || v_final_string );
      ELSE
        EXIT;
      END IF;inside a loop.

But it's not concatenating. I am alwas getting empty v_final_string

View 9 Replies View Related

Concatenation Of Fields For Output

Mar 15, 2007

I have the following fields:

Addressln1
Addressln2
Suburb
Town

I know how to concatenate them

trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address

1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.

View 2 Replies View Related

PL/SQL :: Result String Concatenation

Jul 11, 2012

I am trying to spool the data to a file , and my query has 115 columns and out of which 20 columns have varchar2(2000). And its throwing result string concatenation too long.

I tried using to_clob function , but spool file does not show the complete result set.

View 4 Replies View Related

Result Of String Concatenation Is Too Long

Jan 4, 2008

I am receiving a ORA-01489: result of string concatenation is too long error on the following code. The size of the MNO_NOTE fields is: MNO_NOTES_1 X(2000). I'd rather not modify the DB table column size, but rather that capacity of the "notes", or whatever structure the concatenated string is stored in. Could I use the substring method?

SELECT TO_DATE(TO_CHAR(mno_date_recorded,'yyyymmdd')||
TO_CHAR(mno_time_recorded,'0009'),'yyyymmddhh24mi')
AS create_date,
stf_id AS create_user,
RTRIM(MNO_NOTES_1)||
RTRIM(MNO_NOTES_2)||
RTRIM(MNO_NOTES_3)||
RTRIM(MNO_NOTES_4)||
RTRIM(MNO_NOTES_5)||
[code]...

View 1 Replies View Related

SQL & PL/SQL :: Tricky Concatenation Of Data In One Column

Nov 26, 2010

Every prospect person in the prospect table are approached with a proposal. This proposal record is stored in the proposal table. Proposal is managed by many primary and proposal managers over the period of proposal. They are assigned the proposal in the assignment table. Primary managers have an assignment type 'PM' and proposal manager have a assignment type of 'PS'

i Need to make below1 and 2 changes to the script.

1-when proposal is current (proposal.active_ind = 'Y' ) then extract only currently assigned primary and proposal managers (assignment active_ind = 'Y') and these managers should be assigned after the proposal has started (assignment.start_date >= proposal.start_date )

2-when proposal is not current (proposal.active_ind = 'N' ) then extract even not curRent assigned primary and proposal managers (not use this in criteria assignment active_ind = 'Y') and these managers should be assigned only after the proposal has started (assignment.start_date >= proposal.start_date )

Primary/Proposal Manager column : Format to print as below:

All Primary managers seperated with space "/" between and all proposal managers after that and "*" at the end

For Example : Mary Steve Roger / Chris Danny Veronica * also, current script only extracts 5 primary managers and 4 proposal managers but thats ok. i can put 10 max(decode statements in the output cause i think they cannot have more than 10 managers.

/*script*/
SELECT DISTINCT p.prospect_id "PROSPECT ID"
,p.prospect_name "Prospect Name"
,pro.proposal_id
,pro.proposal_title "Proposal Title/Purpose"
, MAX(decode(pm_seq
,1
,primary_manager_name
,NULL)) || ' ' ||
[code]...

View 4 Replies View Related

ORA-01489 - Result Of String Concatenation Is Too Long

Jul 25, 2012

I have a sql query which has around 115 columns and out of which 25 columns are of varchar2(2000) and when I run the query I get the ORA-01489: result of string concatenation is too long error.

I tried to use to_clob function for the columns having varchar2(2000) and if I run the sql from toad , it works fine, but when I tried to run the same query from sqlplus and spool to a file, the result doesn't come in a single line. I have tried to import the spool file to my local and open it , but still it doesn't come in a single line, the data is trucated This is how my data looks in the spool file.

1-L31OGM|Red|1|Due|Qualified|02/08/2012||02/08/2012| you are missing a message.

These are the below set options used in the query . I even tried set long 100000000 and also set longchucksize option also, I have tried with WRAP OFF and WRAP ON also ,but still it doesn't work.

SET HEADING OFF
SET WRAP OFF
SET LINESIZE 32000
SET FEEDBACK OFF
SET PAGESIZE 0
SET LONG 32000
SET TRIMSPOOL ON
SET ECHO OFF
SET TERMOUT OFF

get the data in a single line and using utl_file package is not an option in our project due to security reason.

View 4 Replies View Related

Sql Query - Insert Concatenation String To Table?

Aug 6, 2012

i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:

for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....

update trap set TrapNum = (
select trim(both from to_char(substr(TrapNum,1,4),'0000'))||
trim(both from to_char(substr(TrapNum,5,1),'00'))||
trim(both from to_char(substr(TrapNum,6,3),'000'))||
substr(TrapNum,9,2)||
trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap)
where length(Trapnum)=12

but i got error ORA-01427

View 1 Replies View Related

SQL & PL/SQL :: Generate PAN Number By Concatenation Of Data From Different Tables?

Jul 11, 2011

How to generate a PAN number by concatenation of data from different tables

View 3 Replies View Related

SQL & PL/SQL :: Column Values Concatenation From Multiple Rows

May 30, 2013

I am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:

SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....

but for some reason regexp_replace does not seem to work with clob and I get:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

Is it possible to eliminate duplicates in the model before the concatenation?

View 6 Replies View Related

SQL & PL/SQL :: ORA-01489 - Result Of String Concatenation Is Too Long

Aug 8, 2011

my rdbms, os version

SYS@prod> select PLATFORM_ID, PLATFORM_NAME from v$database;
PLATFORM_ID
-----------
PLATFORM_NAME
-------------------------------------------------------------------------
12
Microsoft Windows x86 64-bit
[code]...

as I googled the solution does not seems to apply to my case.it very puzzling that such a short query can produce

ORA-01489: result of string concatenation is too long.

View 3 Replies View Related

ORA-01489 / Result Of String Concatenation Is Too Long

Jul 30, 2013

when i run the interrogation bellow :

SELECT 'Existing Tables: ' || LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name) tablenames FROM user_tables;

i receive the error :

ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

View 1 Replies View Related

SQL & PL/SQL :: ERROR At Line 1 - ORA-01489 - Result Of String Concatenation Is Too Long

Aug 13, 2013

I am getting below error when i try to update in my table

ERROR at line 1:
ORA-01489: result of string concatenation is too long

how to resolve this. Note :This is Update query not select query

View 2 Replies View Related

Data Guard :: Apply Patches On Oracle Database With Logical Standby Database?

Apr 22, 2011

I got a primary database with a logical standby database running Oracle 11g. I got two client applications, one is the production site pointing to the primary one, another one is just a backup site pointing to the logical one.Things will only be written into the primary database every mid night and client applications can only query the database but not add, update nor delete.And now, I want to apply the latest patch on both of my databases. I am also the DNS administrator, I can make the name server pointing to the backup site instead of the production one.I want to firstly apply the patch on the logical one, and then the physical one.

I found some reference which explains how to apply patches by adopting "Rolling Upgrade Method". however, I want to avoid doing any "switch over" mentioned in the reference because I can make use of name server. Can I just apply patches as the following way?

1)Stop SQL apply
2)Apply patches on logical standby database
3)let the name server point to the backup site
4)Apply patches on the primary database
5)Start SQL apply
6)Let the name server point back to the production site

View 2 Replies View Related

Data Guard :: Flashback Snapshot Standby Database Or New Primary Database After Failover?

Aug 30, 2013

If flashback is enable in physical standby database 1. If we failover at 11AM can I flash back NEW primary database to 6 AM ? 2. if I convert physically standby database to snapshot standby database at 11AM ,  Can I  flashback snapshot standby database to 6 AM and do some works on it (DML operations)  then   converting the snapshot standby database into physical standby database ?  

View 1 Replies View Related

Streams :: Apply Archivelog / Generated Before Reset-log Of Source Database In Destination Database

Feb 9, 2013

Database Version - 10.2.0.4.0
OS - Red Hat Linux

We have configured oracle one way stream between two databases. Source database is capturing the changes (No downstream configured). Configuration was working fine but destination database was lagging behind very much i.e about 15 days behind the source database. We are ok with this but the problem is now that , as per client request we have restored previous backup and open the database with resetlog option in source database. After resetlog , archivelog sequence has been changed and stream is not working.

Can I apply the previous archivelog (before resetlog archivelogs ) in destination database anyway.Source database is a production database.

View 1 Replies View Related

RMAN :: Clone 2 Node RAC Database To Single Instance Non-RAC Database Using Existing Backup

Jul 25, 2012

I tried to clone a 2 node rac database to single instance non rac database using existing backup. I have not used connectivity to target or catalog. rman duplicate finished with below messages:

rman auxiliary sys/******@dbracdup
RMAN> duplicate database to dbrac spfile backup location '/oracle/backup';
...
...
Finished recover at 25-JUL-12
Segmentation fault

And the database was in mount stage, and when i tried to open database it failed with below error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19838: Cannot use this control file to open database .

View 4 Replies View Related

Retrieve Info From Multiple Databases / Insert Into Central Database Via Database LINKS

Jun 10, 2013

I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.

However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:

DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....

View 1 Replies View Related

Performance Tuning :: Export SQL Plan From Test Database To Prod Database?

Jul 16, 2013

An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.

The same query in a test database completes in less than 2 mins.

I would like to export the sql plan from test database to prod database.

how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.

View 2 Replies View Related

Server Administration :: Database Configuration Assistant Not Working / When Create New Database

Mar 26, 2011

i am installing oracle database 8.1.7 on dell server power edge 2650 first time database successfully installed but when i want to crate new database by Database Configuration Assistant it is not working for new database creation.

View 1 Replies View Related

Client Tools :: Connecting Directly To Required Database (not Test Database)

Feb 17, 2012

we have a production database 'X'. Now i have created a test database 'T' and did'nt configured another listener to it! The issue is when i cam connecting to oracle through sqlplus i am directly connecting to Test database 'T' but not the production database 'X'----ofcourse i can login to production DB afterwards. but initially i want to access the production database 'X'.

View 15 Replies View Related

Export/Import/SQL Loader :: Refresh A PROD Database Into TEST Database

Jun 17, 2012

I need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.

View 1 Replies View Related

Data Guard :: Creating Standby Database From Active Database Using RMAN

Dec 21, 2012

creating the standby database from Active database using RMAN and getting the below issue after i executed the duplicate command.

Version of Database:11g(11.2.0.1.0)
Operating System:Linux 5
Error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2012 17:26:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
[code]....

provide any work arounds to proceed further in creating the standby database.

View 1 Replies View Related

Data Guard :: Switch (convert) Primary Database To Standby Database?

Jan 30, 2013

I have installed oracle 11g standard edition one and created both primary and standby database. now i want to know how to switch(convert) primary database to standby database.

View 8 Replies View Related

Backup & Recovery :: RMAN Using Target Database Control File / Separate Catalog Database

Mar 14, 2012

How one should know whether RMAN is using target database control file or using separate catalog database. Also what one should do if he dont have catalog users credentials.

View 3 Replies View Related

RMAN Duplicate Target Database For Standby From Active Database

Aug 23, 2010

I have failed to duplicate target db for standby from active db using spfile with the following errors:

RMAN-03009: failure of backup command on prim1 channel at 08/11/2010 05:25:09
ORA-19558: error de-allocating device
ORA-19557: device error, device type: DISK, device name:
ORA-17627: ORA-01041: internal error. hostdef extension doesn't exist

[Code].....

The script has successfully created on standby db all controlfiles and also has copied 2 data files DATA01.DBF and DATA02.DBF into the correct location. Then the errors above kicked in and stopped the rman dup process.

View 4 Replies View Related

Server Utilities :: Import Table From Another Database By Using Database Link?

Mar 17, 2010

i would like to import a table from another database by using database link.

impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1

i created the public database link as system user

it gave error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

[code]....

after that i try to import as system user like

impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1

but error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.

when i use IMP utility

imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser

View 3 Replies View Related







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