Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle APEX Interactive Report Link Builder not working as expected?

PearlJul 9 2021

I'm trying to create column links for an IR, and have the following:
apexlinkbuilder.PNGBased on my understanding, this should create column links that direct to page 12, which is also an IR, and apply a filter on the column LEMMA_ID so that the only entries shown are those with LEMMA_ID values equal to the LEMMA_ID values from the clicked row of the IR on the current page. However, the links only direct to page 12, with no filter applied. Why might this be and how can I fix it?

Thank you!

Also:
image.pngLEMMA_ID values are type VARCHAR2 and the leftmost column (with the search glass icon) is the column links.

Comments

725039
>
Oracle 11.1.0.6.0 on IBM-AIX

When I try to connect database, I get the below error

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
>

Please show us exactly how you are trying to connect. Is it through a remote client? Copy and past both commands issued and errors.

Regards,
Phiri
Chinar
paste there TNSNAMES.ORA and sqlnet.ora files context.
KSG
Hi Phiri,

It's not client-server. I'm directly connecting to the database from the server itself.

$sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:09:34 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

when I try to to connect using

$sqlplus "sys/manager@testdb as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:33:54 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


But when I try to connect other database 'orcl'

sqlplus "sys/manager@orcl as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:35:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to connect to orcl with /as sysdba

sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:37:05 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

Please note: All the database are connecting porperly once the server is rebooted. I suspect there is lock in the port. Kindly note, listener is trying different different port no to connect to database (refer my first post pls)

Thanks
KSG
KSG
tnsnames.ora

LISTENER_TESTDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = integ)(PORT = 1522))


TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = integ)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
Chinar
Well
first "ORA-12154: TNS:could not resolve the connect identifier specified" occur for you because
you acn not set ORACLE_SID env variable fist SET ORACLE_SID=<SIDNAME>

second "ORA-12514: TNS:listener does not currently know of service requested in connect" for
this you must register service in listener as static or dynamic

for example add to listener.ora file such as

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SB)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(SID_NAME = SB)

)

then
lsnrctl stop
;lsnrctl start; and try connect again

Edited by: dba84 on Apr 16, 2010 4:23 AM
Chinar
Can you connect your db?
Hans Forbrich
KSG wrote:
Hi Phiri,

It's not client-server. I'm directly connecting to the database from the server itself.
Oracle is ALWAYS client-server. Even when client and server are on the same computer.

(Even the memory-only 'Bequeath adapter' mechanism is client-server.)

>
$sqlplus "/as sysdba"
The use of double-quotes implies that you learned this several versions ago. Has not been required for several versions.

Try

$ sqlplus / as sysdba

(and note the space after the '/'. Not absolutely required but highly recommended.)

>
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:09:34 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

assuming you are using bash, please display results of following (issued from command line)

set | grep -i ora
which sqlplus
tail /var/opt/oracle/oratab (or /etc/oratab if Linux)
lsnrctl status
Enter user-name:

when I try to to connect using

$sqlplus "sys/manager@testdb as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:33:54 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
please show response to command line

grep -i testdb $ORACLE_HOME/network/admin/tnsnames.ora

>
Enter user-name:


But when I try to connect other database 'orcl'

sqlplus "sys/manager@orcl as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:35:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to connect to orcl with /as sysdba

sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 16:37:05 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

Please note: All the database are connecting porperly once the server is rebooted. I suspect there is lock in the port. Kindly note, listener is trying different different port no to connect to database (refer my first post pls)
Listener does not 'try' on a different port.

However, (under normal circumstances) when listener forks/execs the server process AND that new server process is instructed to use the TCP/IP protocol by the tns substrate due to configuration (even when on local environment), the new server process will open a new TCP port for communication and the listener will tell the client to communicate on that.

>
Thanks
KSG
752163
Hey DBA84,

He is trying to connect the database locally.Then no concerned with listener here.

Even if the listener is down also,he can able to connect to the database.

Listener comes into picture when you are connecting remotely.

KSG:set the oracle_sid & try to connect.


Regards,
khaja
KSG
Please find the output..


set | grep -i ora

HOME=/glods1/oracle
LD_LIBRARY_PATH=/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib:
LIBPATH=/usr/lib:/glods/serena/dimensions/10.1/cm/lib:/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib::/glods/serena/dimensions/10.1/cm//lib
LOGIN=oracle
LOGNAME=oracle
MAIL=/usr/spool/mail/oracle
OLDPWD=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/integ/listener/alert
ORACLE_HOME=/glods1/oracle11g/app/oracle/product/11.1.0/db_1
ORACLE_LPARGS=-p
ORACLE_LPPROG=lpr
ORACLE_SID=testdb
PATH=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/glods1/oracle/bin:/usr/bin/X11:/sbin:.
PS1='<oracle@integ:${PWD:-$(pwd)}> '
PWD=/glods1/oracle11g/app/oracle/product/11.1.0/db_1/network/admin
SELF=oracle
SHLIB_PATH=/glods/oracle-10.2/lib:/glods/serena/dimensions/10.1/cm//lib:
USER=oracle
_=ORACLE_SID=testdb


which sql
which: 0652-141 There is no sql in /glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin /usr/bin /etc /usr/sbin /usr/ucb /glods1/oracle/bin /usr/bin/X11 /sbin .


more /etc/oratab

orcl:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
testdb:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N

lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 16-APR-2010 17:52:49

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=integ)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
Start Date 16-APR-2010 16:52:13
Uptime 0 days 1 hr. 0 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /glods1/oracle11g/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /glods1/oracle11g/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/integ/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=integ)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


grep -i testdb $ORACLE_HOME/network/admin/tnsnames.ora

grep -i t24db $ORACLE_HOME/network/admin/tnsnames.ora
LISTENER_TESTDB =
T24DB =
(SERVICE_NAME = TESTDB)


Thanks
KSG
Chinar
if you use sqlplus > connect username/pass@db then must use listener ok?
725039
>
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
>

Which SID are you trying to connect to? Is it testdb or orcl?


>
orcl:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
testdb:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
>

Looks to me like you have two Oracle Databases installed. If you would like to user testdb, you are going to have to start it up first.

Regards,
Phiri
Hans Forbrich
phiri wrote:
Looks to me like you have two Oracle Databases installed. If you would like to user testdb, you are going to have to start it up first.
Or at least set the ORACLE_SID and ORACLE_HOME properly before starting 'sqlplus / as sysdba'

Which is why I asked for 'set | grep -i ora' earlier. ;-)
725039
>
Which is why I asked for 'set | grep -i ora' earlier.
>

Yes indeed. :)

Regards,
Phiri
KSG
I set it manually

echo $ORACLE_HOME
/glods1/oracle11g/app/oracle/product/11.1.0/db_1

echo $PATH
/glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/glods1/oracle/bin:/usr/bin/X11:/sbin:.

echo $ORACLE_SID
testdb

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 18:44:30 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified



sqlplus "sys/manager@testdb as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 18:44:58 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

Still it's not getting connected. Please read my first post, in listener.log file... testdb port no is pointing to different at different time... I think there is block in the port... If the server is rebooted then there is no problem the connectivity...
Please help me to investigate further..

Thanks
KSG

Edited by: KSG on Apr 16, 2010 6:53 PM (typo)
725039
>
Still it's not getting connected. Please read my first post, in listener.log file... testdb port no is pointing to different at different time... I think there is block in the port... If the server is rebooted then there is no problem the connectivity...
Please help me to investigate further..
>

start your database.
sqlplus /nolog

startup
Regards,
Phiri
KSG
sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 16 19:09:11 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> startup
ORA-12154: TNS:could not resolve the connect identifier specified
SQL>
725039
>
orcl:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
testdb:/glods1/oracle11g/app/oracle/product/11.1.0/db_1:N
>

What is the SID of your database? Looks to me like you have orcl and testdb pointing to the same ORACLE_HOME.

How many times have you installed Oracle Database on your machine?

Regards,
Phiri

Edited by: phiri on 16-Apr-2010 15:49
KSG
Yes Phiri... There are two databases running in the server with same oracle home.

I can able to connect to orcl database

But, I cant able to connect to testdb database. Which I have posted in my earlier postings..

Thanks
KSG
725039
>
Yes Phiri... There are two databases running in the server with same oracle home.

I can able to connect to orcl database

But, I cant able to connect to testdb database. Which I have posted in my earlier postings..
>

Two instances, not databases, you can only have one database software installation in an Oracle Home

Very well then; do the following and let us know what happens.
. oraenv
The when prompted for SID, use testdb.

Regards,
Phiri

Edited by: phiri on 16-Apr-2010 16:32
KSG
The same result... Not connecting...

I suspect port level issue...

Thanks a ton
KSG
725039
>
The same result... Not connecting...

I suspect port level issue...

Thanks a ton
KSG
>

Have you by any chance been playing around with TWO_TASK variable? Show us the results of the following.
set | grep -i TWO
Regards,
Phiri
KSG
Hi,

set | grep -i TWO
DM_NETWORK_SERVER=/glods/serena/dimensions/10.1/cm//prog/dmlibsrv
TWO_TASK=dim10


:)

this above path shows a 10g database which is used as virtual machine.

Thanks
KSG
725039
Just unset it. Then everything should be fine.

Regards,
Phiri
725039
>
Hi,

set | grep -i TWO
DM_NETWORK_SERVER=/glods/serena/dimensions/10.1/cm//prog/dmlibsrv
TWO_TASK=dim10

:)

this above path shows a 10g database which is used as virtual machine.

Thanks
KSG
>

Isn't this the part were you mark the post that answered your question, just so someone with a similar problem can easily figure out what best worked for you? :)

Regards,
Phiri
KSG
Phiri... testdb and orcl were working fine even when TWO_TASK was set earlier. This server works fine after every restart... but after a few days the problem araises... I suspect that port is keep on increasing the value when reaches threshold the connection cant able establish. Please look at the log file in the first post. I feel some thing is terribly wrong with the setting.

Thanks
KSG
Hans Forbrich
phiri wrote:
Two instances, not databases, you can only install one Oracle Database in an Oracle Home
Hmmmm. Are you sure about that?

Perhaps you mean "only one database software installation in one Oracle Home" (Which is different from "Oracle Physical Database structures". Which is different from "Oracle instance".)

You are correct that one ORACLE_HOME can provide the programs for multiple database instances. Unless you are dealing with RAC, each instance will manage a separate Oracle database. IOW, two instances in one ORACLE_HOME implies two databases under normal, non-RAC, conditions.
Hans Forbrich
KSG wrote:
Please find the output..
...
which sql
which: 0652-141 There is no sql in /glods1/oracle11g/app/oracle/product/11.1.0/db_1/bin /usr/bin /etc /usr/sbin /usr/ucb /glods1/oracle/bin /usr/bin/X11 /sbin .
However, I asked for 'which sqlplus', not 'which sql'
sb92075
I suspect that port is keep on increasing the value
This is a true statement for both success & failed connections.
725039
>
Perhaps you mean "only one database software installation in one Oracle Home"
>

Indeed Hans; that is exactly what I meant to say.

*"only one database software installation in one Oracle Home"*

I have since corrected that. Thanks.

Regards,
Phiri
Hans Forbrich
KSG wrote:
Phiri... testdb and orcl were working fine even when TWO_TASK was set earlier. This server works fine after every restart... but after a few days the problem araises... I suspect that port is keep on increasing the value when reaches threshold the connection cant able establish. Please look at the log file in the first post. I feel some thing is terribly wrong with the setting.
Client that use TCP/IP INCLUDING LOCAL CLIENTS will connect to the listener at port 1521 AND THEN will normally reconnect to a server process through a different port.

That way the listener is free to continue monitoring onb 1521

You may have an application that opens new connections and never releases those connections. Which is a completely different issue. Check 'ps -ef | grep -i oracletestdb' and 'ps -ef | grep oracleorcl'
725039
>
Phiri... testdb and orcl were working fine even when TWO_TASK was set earlier. This server works fine after every restart... but after a few days the problem araises... I suspect that port is keep on increasing the value when reaches threshold the connection cant able establish. Please look at the log file in the first post. I feel some thing is terribly wrong with the setting.
>

So I did not see this post; did you finally manage to resolve this?

>
16-APR-2010 13:00:56 * (CONNECT_DATA=(SERVICE_NAME=testdb)(CID=(PROGRAM=sqlplus@integ)(HOST=integ)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.92.5.37)*(PORT=34487))* * establish * testdb * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = integ)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

>

That error will continue as long as you do not have testdb being listened to by your listener.

Regards,
Phiri
EdStevens
khaja(DBA) wrote:
Hey DBA84,

He is trying to connect the database locally.Then no concerned with listener here.

Even if the listener is down also,he can able to connect to the database.

Listener comes into picture when you are connecting remotely.
Listener CAN com into the picture on the same server:
sqlplus /  
sqlplus / as sysdba
sqlplus scott/tiger
will use bequeath connections and not require the listener
sqlplus scott/tiger@orcl
will use the listener, whether local or remote.

KSG:set the oracle_sid & try to connect.


Regards,
khaja
EdStevens
phiri wrote:
>
Yes Phiri... There are two databases running in the server with same oracle home.

I can able to connect to orcl database

But, I cant able to connect to testdb database. Which I have posted in my earlier postings..
>

Two instances, not databases, you can only have one database software installation in an Oracle Home
BZZZT! Wrong answer!

You can very well have two databases with a single oracle home. And along with those two databases, you'd have two instances.

Instance and database are not the same thing. An instance is the memory and processes. The database is the files. The distinction becomes clear when you start dealing with RAC: 1 database, multiple instances managing that database.
>
Very well then; do the following and let us know what happens.
. oraenv
The when prompted for SID, use testdb.

Regards,
Phiri

Edited by: phiri on 16-Apr-2010 16:32
725039
Yes indeed Ed, thanks for spotting that one as well; Hans actually corrected me.

>
sqlplus /
sqlplus / as sysdba
sqlplus scott/tiger
>

The above were all making use of the listener because the OP had TWO_TASK variable set.

Regards,
Phiri
1 - 34

Post Details

Added on Jul 9 2021
#apex, #apex-discussions, #application, #link, #report
11 comments
2,273 views
1 mention