SQL & PL/SQL :: Query With Connect Path

Nov 11, 2011

I am working on a query as below:

CREATE TABLE FDL_SNOP_SCDHUB.TEST_CONN
(
NAME1 VARCHAR2(100 ),
VALUE1 VARCHAR2(4000 ),
ID1 VARCHAR2(200 ) NOT NULL
);
[code]....

I have tried with sys_connect_by_path and wm_concat

with mst as (
select id1,name1,value1 from test_conn c
where ((c.name1='WORKFLOW_TYPE' and NVL2(:p_workflow_type,decode(:p_workflow_type,' SELECT ALL',' SELECT ALL',c.value1),0)=NVL2(:p_workflow_type,:p_workflow_type,0))
or (c.name1='SOURCE_TYPE' and NVL2(:p_source_type,decode(:p_source_type,' SELECT ALL',' SELECT
[code]....

but unable to group it by Id, all comes under one column

View 6 Replies


ADVERTISEMENT

Data Guard :: Specifying A Network Path In SQL Query

Apr 18, 2013

I have created 2 PCs (1 for Primary DB (IT_SERVER), 2 for Standby DB (IT_SERVER2) on Oracle VM Virtual Box in order to check my Data Guard configuration.

I have created a Shared Folder on 2nd PC (IT_SERVER2) for Oracle Standby DB. Now when I issue my query from 1st PC (Primary DB) to Create a Parameter file on 2nd PC Shared Oracle Folder, it returns following errors

SQL> CREATE PFILE='\IT_SERVER2E:ORACLEPFILESTLDB2.ORA' FROM SPFILE;
CREATE PFILE='\IT_SERVER2E:ORACLEPFILESTLDB2.ORA' FROM SPFILE
*
ERROR at line 1:
ORA-09210: sftopn: error opening file
OSD-04002: unable to open file
O/S-Error: (OS 67) The network name cannot be found.

Then I try this

SQL> CREATE PFILE=\IT_SERVER2'E:ORACLEPFILESTLDB2.ORA' FROM SPFILE;
CREATE PFILE=\IT_SERVER2'E:ORACLEPFILESTLDB2.ORA' FROM SPFILE
*
ERROR at line 1:
ORA-00911: invalid character

What to do?

View 8 Replies View Related

SQL & PL/SQL :: Query To List Last 11 Days - Connect By

May 24, 2010

I want to write a query that lists last 11 days. I tried the following query using CONNECT BY...

SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM DUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;

DAYS
--------------
20100514000000

For some reason it gives only the first day.. But this one works -

SQL> SELECT MIN_DT + LEVEL - 1 DAYS FROM
(
WITH MYDUAL AS (
SELECT 1 FROM DUAL
)
SELECT TRUNC(SYSDATE -10) MIN_DT, TRUNC(SYSDATE) MAX_DT FROM MYDUAL
)
CONNECT BY LEVEL <= MAX_DT - MIN_DT + 1;

DAYS
--------------
20100514000000
20100515000000
20100516000000
20100517000000
20100518000000
20100519000000
20100520000000
20100521000000
20100522000000
20100523000000
20100524000000

11 rows selected. I am not able to understand what is the difference between the two queries! Why is the second query able to do what the first query cannot?

View 5 Replies View Related

SQL & PL/SQL :: Query To Break Date And Connect By Level

Nov 7, 2012

I have following table

CREATE TABLE MAMALIK.DTE
(
FRM_DTE DATE,
TOO_DTE DATE
)
Insert into DTE
(FRM_DTE, TOO_DTE)
Values
[code]........

Result is

FRM_DTE TOO_DTE

01/07/201201/07/2012
03/07/201207/07/2012
11/07/201215/07/2012
31/07/201201/08/2012

i want to write query which should return data as

01-jul-2012
03-jul-2012
04-jul-2012
05-jul-2012
06-jul-2012
07-jul-2012
11-jul-2012
12-jul-2012
13-jul-2012
14-jul-2012
15-jul-2012
31-Jul-2012
01-Aug-2012

i want to generate dates between frm_Dte and too_dte in single column

View 4 Replies View Related

Forms :: How To Connect Query With Spread Table

Nov 2, 2010

how to connect query with Spread Table or load table data in spreadtable by using forms 6i

View 6 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

Client Tools :: Able To Connect From SQL-Assistant BUT Unable To Connect From SQL Developer

Jul 7, 2012

I have been troubling with this issue for the last two weeks...tried so much, but could not able to resolve....

sqlplus username/password@IPAddress:port/ServiceName

I am able to connect to DATABASE from SQLAssistant only when I provide string like ABOVE........If I dont mention the SERVICE, m unable to connect to DB.Now the biggest issue is with SQL Developer.....m not at all able to connect to DB using SQL Developer.

View 3 Replies View Related

Client Tools :: Connect Oracle Developer 6i Without Connect String

Aug 17, 2010

I have installed oracle developer 6i. in sqlplus i want to put my username and password but nothing in the connect string field.

View 1 Replies View Related

Connect Oracle Database From Delphi - Could Not Resolve Connect Identifier Specified

Oct 6, 2010

I have the following computer/setup:

Acer laptop, Intel Processor w/ Windows 7 Professional (64-bit)
Delphi 2010 Professional
Oracle XE (+ the Oracle XE client that comes with it)

I cannot can't to the Oracle database from Delphi. The error I keep getting is:

Alias is not currently opened. [Oracle][ODBC][Ora]ORA-12154:TNS: could not resolve the connet identifier specified.

Here's what I've done:

1. Create an ODBC connection to my Oracle database

- run C:WindowsSysWOW64odbcad32.exe to open the ODBC administrator.
- Data source name: bt_user
- TNS Server Name: localhost/xe
- user bt_user
- Clicked "test connection" -- Success.

2. Open Delphi and drop a TDatabase component on my main form.

- Set the Alias Name property to "bt_user" (the alias I set up in ODBC)
- Set database name to "bt_user".
- Checked "connected".

Alternatively, I tried using the Delphi ADO components (the dbGo components).

- Drop TADOConnection component on my form.
- Select "connection string" and open the dialog.
- Click "build string".
- Select "Microsoft OLE DB Provider for ODBC Drivers" from the selection.
- In "use data source", select "bt_user" from the drop-down list (my ODBC connection from #1)
- Click "test connection".

Again the same error.

My questions are:

1. Can Delphi work with Oracle XE (Express Edition)?

2. If so, then do I need to install the "Instant Client"?

3. Will the instant client for Oracle 10g work with Oracle 10g XE?

4. Do I need to install any additional ODBC driver(s)?

5. Why was I successful in creating the ODBC connection in Windows, but it failed in Delphi?

6. Will Visual Studio.NET (C#) work with Oracle XE, and what are the steps for setting that up?

View 2 Replies View Related

Forms :: How To Set Dynamic Path In 6i

Aug 6, 2008

I am using Oracle Form 6i. I have created pdf file from oracle report. On view button, I have written:

host('C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe C:dailypending.pdf');

I don't want to set this path on every machine. I want to set its dynamic path so that on which even I machine, I run the applicaiton, it will directly find AcroRd32.exe from any drive either C: or D: or anything else...

It might be Acrobat Reader 9.0 or 8.0 whatever version will be installed on client machine...I want to set path according to that..So that whatever version it would be, it doesnt affect the application..

View 4 Replies View Related

SQL & PL/SQL :: Retrieve The Path Of A Directory

Oct 27, 2011

I create a directory using this:

CREATE DIRECTORY xml_data AS 'c:\mytest';

How can I retrieve the 'C:mytest' in a select statement?

View 5 Replies View Related

Forms :: Path To Use DLL - Library Not Available

Oct 18, 2013

How to use the DLL? My DLL is loaded on the client host in the C: Documents and Settings USER webutil. When you try turning to the library, I get an error:

WUL-928 [CApiFunctions.set_invokeoncespec ()] EXAMPLE.DLL library is not available or does not contain a function MYFUNCTION

View 11 Replies View Related

SQL & PL/SQL :: Find The Longest Path

Mar 25, 2013

create table test
(
avarchar2(10),
bvarchar2(10)
)
/
insert into test values ('A', 'B');

[Code]...

We want to get the path from "A" to "E". In other words, if we start with any value of the column "a" we have to reach the "maximum" value of the column "b".

"maximum" in sense that we don't find the value of "b" in "a".

Example :
---------
E is not found in the column a, so E is the maximum of A.
- N is the maximum of K
- Z is the maximum of Q and the maximum of S

select ...
from ...
where a = 'A' should return E.

View 11 Replies View Related

How To Change Datafile Path

May 13, 2013

I need to change data file path, and i got some document also but one thing confusing me, we need to offline the Tablespace, so my concern is all the table space like system, sys, user, temp etc. need to offline and then alter the database?

View 9 Replies View Related

PL/SQL :: Path Between Two Siblings Node

Dec 31, 2012

I want to find the path between two nodes from oracle Hierarchical Table.

Consider the following case ---
NodeId --- ParentId
=============
1 >>>>>> 0
2 >>>>>> 1
3 >>>>>> 2
4 >>>>>> 3
5 >>>>>> 0
6 >>>>>> 5

Here I want to query in the database table that whether there is a path between node 3 and node 5 ? The previous query you provided work up to root node.

Here my expected result is 3-->2-->1-->0-->5

Again if I query in the table to get the path between 1 and 3 , I want to get the output in the following way -
1-->2-->3

So the query need to work in both the cases. Where root nod can act as an intermediate node or not.

how I can get it ?

View 3 Replies View Related

Import Oracle 9.2 Exp To 11.2 - How To Change Path

Dec 10, 2012

The source server (9.2) is /oracle/data/xxx the destination will be /u02/oradata/xxx how do I get the imp to change the paths.

I know you can't do db_file_name_convert, as that only works in dataguard with rman, and it looks like you can only set compatible down to 10.2, it's a very small DB, (5Mb) but it's a live license server DB, I need it intact.

I tried a straight import, but that just barfed with "IMP-00015: following statement failed because the object already exists:" on every statement.

EDIT: it's lying, the /oracle path doesn't exist, just FYI.

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

How To Change Oracle TNS Listener Path

Oct 29, 2010

how to change this path:

F:\app\MyLAP\product\11.1.0\db_1\BIN\TNSLSNR

to this path:

d:\app\MyLAP\product\11.1.0\db_1\BIN\TNSLSNR

in the service ?

View 2 Replies View Related

Backend Database 10.2.0.3 - Upgrade Path?

Jun 6, 2012

we have a very critical application running and the backend database is 10.2.0.3. we are planing on upgrading to 10g to 11.2.0.2 and looking ways to look with minimal downtime off production. steps for upgrade with very minimal downtime of appliction?

View 2 Replies View Related

Windows :: Value For PATH Environment Variable?

Jan 23, 2013

what value exactly I need to give for the PATH environment variable in oracle 11g running on windows xp?

View 15 Replies View Related

SQL & PL/SQL :: Find Shortest Path With Visits

Jul 3, 2013

I am find about an algorithm of shortest path with visits certain nodes.I have a table of Links with this fields: ID_Link, City1, City2 and distance...You have informations about shortest path with visits?

View 9 Replies View Related

Forms :: Find Path In Oracle 6i?

Nov 1, 2011

how can i get full path in oracle 6i???

View 2 Replies View Related

SQL & PL/SQL :: Fetch File Path From A Folder

Oct 4, 2013

We upload a file on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

View 12 Replies View Related

SQL & PL/SQL :: ORA-29280 / Invalid Directory Path

May 13, 2012

I started working on utl_files. When i Am working on this topic I struck with an error: Quote:ORA-29280: invalid directory path. Below are the steps I followed to work on utl_files,

STEP1: connect sys as sysdba

STEP2: @$ORACLE_HOME/rdbms/admin/utlfile.sql

STEP3: create or replace directory utl_data_files as '/home/oracle/siva/data/'; --(the directory path is already exists in the OS)

STEP4: below is the code

DECLARE
out_file UTL_FILE.file_type;
linebuf varchar2(1999);
directory_name varchar2(100):='utl_data_files';
BEGIN
out_file:=UTL_FILE.fopen(directory_name,'emp.dat','W');
for emp_cur in (SELECT * FROM scott.EMP)

[code].....

STEP5: then it's giving the below result/error info

Quote:

SQL ERRORCODE IS:-29280
SQL ERROR MSG IS:ORA-29280: invalid directory path

PL/SQL procedure successfully completed.

View 2 Replies View Related

Forms :: How To Set Registry Path Under 6i In CentOS

Dec 26, 2012

How set the registery path under forms6i in Centos

View 4 Replies View Related

Forms :: Setting Output Path

Feb 1, 2010

Recently i faced with a situation of redirecting a report output to a particular path in my server, and then opening the output by using web.show_document.

I was successful in redirecting it too a path , however i am not able to open the file using URL. We should setup virtual path in orion-web.xml and proceed further.

I pointed the output to an existing virtual path as below in my orion_web.xml file.

D:OraHome_2 oolswebhtml

and tried opening the output through the url

<<<server_name>/forms/html/filename.extn>>>

however i was not able to add new virtual path here .

View 3 Replies View Related

Install SQL Developer / Set A Path To Java

Oct 16, 2012

i try to install the sql developer, and i need to set a path to java it show: "Enter the full pathname for java.exe"

View 4 Replies View Related

PL/SQL :: ORA-29280 / Invalid Directory Path

Sep 9, 2013

When i am running the below script i am getting error like Invalid directory path. how to check the path by using dbms_ouput.

error : ORA-29280: invalid directory path
ORA-06512: at line 34

DECLARE
l_out_file  UTL_FILE.file_type;
g_convert_crlf  BOOLEAN := TRUE;
P_DATA VARCHAR2(32767);
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;

[code].....

View 1 Replies View Related

Forms :: Use Only AcroRD32. Exe Without Using Proper Path?

May 10, 2013

i m using oracle d2k i want to open a pdf file given proper path of C:Program FilesAdobeReader 9.0ReaderAcroRd32.exe but i want to use only AcroRD32. exe without using proper path how i use it

View 6 Replies View Related

Application Express :: Image Path In 4.2

Oct 20, 2013

I have noticed several posts here on the topic of image path in APEX.I have just upgrated to APEX 2.2.3,Uploaded an image via the application into the default images are, nothing too fancy,Trying to show this image in the logo area of the application, The image will not show.

This is hosted with a hosting firm, so I am not running the software on my own, just administering the workspace.In application user interface, I have the Logo field set to:

<img src="#APP_IMAGES#KKC.jpeg" type="image/jpeg" />I tried "#WORKSPACE_IMAGES#

also, and same issue.When I want to assign an image to a list item, it works because I simply have to pick/select from available images in the folder.But in this case, there is no selecting of the image as an option. I must supply the path.Read the documentation, and some cases here, and still no solution. This seems to be a known issue.

View 0 Replies View Related







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