Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
dblink in PL/SQL block..

dblink in PL/SQL block..

2006-01-23       - By Onkar N Tiwary

Reply:     1     2     3  

hi all,

I have one small problem. Below is my query which is running on the sql
prompt:

*select username||'('||sid||','||serial#||') ospid = ' ||  process ||'
program = ' || program username,to_char(LOGON_TIME,' Day HH24:MI')
logon_time,to_char(sysdate,' Day HH24:MI') current_time,sql_address,
LAST_CALL_ET   from **v$session@(protected)* <v$session@(protected)> *where
status = 'ACTIVE' and rawtohex(sql_address) <> '00' and username is not null
order by last_call_et;*

* select max(decode(piece,0,sql_text,null))
||max(decode(piece,1,sql_text,null)) ||max(decode(piece,2,sql_text,null))
||max(decode(piece,3,sql_text,null)) sql_text from **
v$sqltext_with_newlines@(protected)*<v$sqltext_with_newlines@(protected)>
*where address = x.sql_address and piece < 4;*


but when I am using the same sql in the PL/SQL block as below:

*create or replace procedure showsql as
--    x number;
   cursor c1 is
select username||'('||sid||','||serial#||
               ') ospid = ' ||  process ||
               ' program = ' || program username,
            to_char(LOGON_TIME,' Day HH24:MI') logon_time,
            to_char(sysdate,' Day HH24:MI') current_time,
            sql_address, LAST_CALL_ET
       from v$session@(protected)
      where status = 'ACTIVE'
        and rawtohex(sql_address) <> '00'
        and username is not null order by last_call_et;
   cursor c2 is
select max(decode(piece,0,sql_text,null)) ||
                         max(decode(piece,1,sql_text,null)) ||
                         max(decode(piece,2,sql_text,null)) ||
                         max(decode(piece,3,sql_text,null))
                              sql_text
                    from v$sqltext_with_newlines@(protected)
                   where address = x.sql_address
                     and piece < 4;
begin
   for x in c1 loop
       for y in c2  loop
           if ( y.sql_text not like '%listener.get_cmd%' and
                y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
           then
               dbms_output.put_line( '-- ---- ---- ---- --' );
               dbms_output.put_line( x.username );
               dbms_output.put_line( x.logon_time || ' ' ||
                                     x.current_time||
                                     ' last et = ' ||
                                     x.LAST_CALL_ET);
               dbms_output.put_line(
                         substr( y.sql_text, 1, 250 ) );
           end if;
       end loop;
   end loop;
end;
/*
Its throwing the following error:
*

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE SHOWSQL:

LINE/COL ERROR
-- ----- -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
4/2      PL/SQL: ORA-04052 (See ORA-04052.ora-code.com): error occurred when looking up remote object
        SYS.V_$SESSION@(protected)
        ORA-02030 (See ORA-02030.ora-code.com): can only select from fixed tables/views

4/2      PL/SQL: SQL Statement ignored
15/2     PL/SQL: SQL Statement ignored
21/37    PL/SQL: ORA-00904 (See ORA-00904.ora-code.com): "X"."SQL_ADDRESS": invalid identifier
26/13    PL/SQL: Statement ignored
26/18    PLS-00364: loop index variable 'Y' use is invalid
SQL>

*Any idea???????


--
Thanks & Regards,
T. Onkar Nath
OneAPPS Enterprise Technology Pvt. Ltd.
      to_onkar@(protected)
      onkarnath.tiwary@(protected)

<div>hi all,</div>
<div>&nbsp;</div>
<div>I have one small problem. Below is my query which is running on the sql
prompt:</div>
<div>&nbsp;</div>
<div><strong>select username||'('||sid||','||serial#||') ospid = ' ||&nbsp;
process ||' program = ' || program username,to_char(LOGON_TIME,' Day HH24:MI')
logon_time,to_char(sysdate,' Day HH24:MI') current_time,sql_address, LAST_CALL
_ET&nbsp;&nbsp; from
</strong><a href="mailto:v$session@(protected)"><strong>v$session@(protected)
_cochin</strong></a>&nbsp;<strong>where status = 'ACTIVE'&nbsp;and rawtohex(sql
_address) &lt;&gt; '00'&nbsp;and username is not null order by last_call_et;<
/strong>
</div>
<div>&nbsp;</div>
<div><strong>&nbsp;select max(decode(piece,0,sql_text,null)) ||max(decode(piece
,1,sql_text,null)) ||max(decode(piece,2,sql_text,null)) ||max(decode(piece,3,sql
_text,null))&nbsp;sql_text&nbsp;from </strong><a href="mailto:v$sqltext_with
_newlines@(protected)">
<strong>v$sqltext_with_newlines@(protected)</strong></a>&nbsp; <strong>where
address = x.sql_address&nbsp;and piece &lt; 4;</strong><br>&nbsp;</div>
<div>&nbsp;</div>
<div>but when I am using the same sql in the PL/SQL block as below: </div>
<div>&nbsp;</div>
<div><strong>create or replace procedure showsql as<br>--&nbsp;&nbsp;&nbsp; x
number;<br>&nbsp;&nbsp;&nbsp; cursor c1 is <br>&nbsp;select username||'('||sid|
|','||serial#||<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ') ospid = ' ||&nbsp; process ||<br>&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '
program = ' || program username,
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to
_char(LOGON_TIME,' Day HH24:MI') logon_time,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to_char(sysdate,' Day HH24:MI')
current_time,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; sql_address, LAST_CALL_ET<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; from <a href="mailto:v$session@(protected)">
v$session@(protected)</a><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where
status = 'ACTIVE'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and
rawtohex(sql_address) &lt;&gt; '00'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; and username is not null order by last_call_et;<br>&nbsp;&nbsp;
&nbsp; cursor c2 is<br>&nbsp;select max(decode(piece,0,sql_text,null)) ||
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
max(decode(piece,1,sql_text,null)) ||<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; max(decode(piece,2,sql_text,null)) ||<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; max
(decode(piece,3,sql_text,null))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sql_text
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from <a href="mailto:v$sqltext
_with_newlines@(protected)">v$sqltext_with_newlines@(protected)</a><br>&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where address = x.sql_address<br>&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and piece &lt; 4;
<br>begin<br>&nbsp;&nbsp;&nbsp; for x in c1 loop<br>&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; for y in c2&nbsp; loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if ( y.sql_text not like '%listener.get_cmd%'
and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')<br>&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; then<br>&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; dbms_output.put_line( '-- ---- ---- ---- --' );
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; dbms_output.put_line( x.username );<br>&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms
_output.put_line( x.logon_time || ' ' ||<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x.current_time||<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' last et = ' ||
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x.LAST
_CALL_ET);<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line(<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr( y.sql_text, 1, 250 ) );<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end if;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end loop;<br>&nbsp;&nbsp;&nbsp; end
loop;
<br>end;<br>/</strong></div>
<div>Its throwing the following error: </div>
<div><strong>
<p>Warning: Procedure created with compilation errors.</p>
<p>SQL&gt; sho err<br>Errors for PROCEDURE SHOWSQL:</p>
<p>LINE/COL ERROR<br>-- ----- -- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---<br>4/2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PL/SQL: ORA-04052 (See ORA-04052.ora-code.com): error
occurred when looking up remote object<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp; <a href="mailto:SYS.V_$SESSION@(protected)">
SYS.V_$SESSION@(protected)</a><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp; ORA-02030 (See ORA-02030.ora-code.com): can only select from fixed tables/views</p>
<p>4/2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PL/SQL: SQL Statement ignored<br>15/2&nbsp
;&nbsp;&nbsp;&nbsp; PL/SQL: SQL Statement ignored<br>21/37&nbsp;&nbsp;&nbsp; PL
/SQL: ORA-00904 (See ORA-00904.ora-code.com): &quot;X&quot;.&quot;SQL_ADDRESS&quot;: invalid identifier<br>26
/13&nbsp;&nbsp;&nbsp; PL/SQL: Statement ignored<br>26/18&nbsp;&nbsp;&nbsp; PLS
-00364: loop index variable 'Y' use is invalid
<br>SQL&gt;</p>
<p></p></strong>Any idea???????<br clear="all"><br>-- <br>Thanks &amp; Regards,
<br>T. Onkar Nath<br>OneAPPS Enterprise Technology Pvt. Ltd.<br>&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp; <a href="mailto:to_onkar@(protected)">to_onkar@(protected)<
/a><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <a href="mailto:onkarnath.tiwary
@(protected)">
onkarnath.tiwary@(protected)</a><br>&nbsp;</div>