Forum Stats

  • 3,873,122 Users
  • 2,266,506 Discussions
  • 7,911,426 Comments

Discussions

Inserting With Clause

Umut Tekin
Umut Tekin Member Posts: 12 Red Ribbon
edited Aug 17, 2020 9:31AM in SQL & PL/SQL

Hi everybody,

I have one issue about my SQL/PLSQL. Here, it is my code and this works fine:

with function mod_get_host(p_host in varchar2) return varchar2 is

begin

return UTL_INADDR.GET_HOST_ADDRESS(p_host);

exception when others

then

return UTL_INADDR.GET_HOST_ADDRESS(p_host||'.fw.somedomainname.com.tr');

end;

IPTAB as (select  case

when db_target_name  like '%CDB%' then substr(db_target_name,instr(db_target_name, '_') + 1)

else db_target_name

end dbname, mod_get_host(regexp_substr(value, 'HOST\=(.+)\)\(',1,1,NULL,1)) hostname,

regexp_substr(value, 'PORT\=([:0-9 :]+)',1,1,NULL,1) port,

a.* FROM (select distinct DB_TARGET_NAME, type, value FROM RAPOR.ASV_GV$LISTENER_NETWORK

where db_target_name not like '%PCDBZP%'

and db_target_name not like '%PCDBYP%'

and db_target_name not like '%PCDBCP%'

and db_target_name not like '%PCDBEP%') a where type in ('REMOTE LISTENER', 'LOCAL LISTENER')),

ASV_DB_IPS_VW1 as (select * from iptab

where hostname is not null

and port is not null

and dbname not like '%CDB%'),

IPTAB_fromVW as(

select dbname, listagg(hostname, ',') within group (order by hostname) hostname

from ASV_DB_IPS_VW1 

group by  dbname),

PORTTAB_fromVW as(select dbname, listagg(port, ',') within group (order by port) port

from (select distinct dbname, port from ASV_DB_IPS_VW1  )

group by  dbname)

select i.dbname,

case

when i.hostname like '%OneIP%' then i.hostname||',SOMEIPs'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

else i.hostname

end IPs,

p.port from IPTAB_fromVW i join PORTTAB_fromVW p

on i.dbname = p.dbname;

Also, I created table:

create table rapor.ASV_DB_IPS(dbname varchar2(100), IPS varchar2(4000), Port varchar2(255));

and I want to insert data into this table as according to the https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9539062000346471801. Yet, got this error:

ORA-06553: PLS-103: Encountered the symbol "IPTAB"

ORA-06553: PLS-103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   . ( * % & = - + ; < / > at in is mod remainder not rem

   <an exponent (**)> <> or != or ~= >= <= <> and or like like2

   like4 likec between || multiset member submultiset

The symbol ";" was substituted for "EXCEPTION" to continue.

The code thrown an error:

insert /*+ WITH_PLSQL */ into rapor.ASV_DB_IPS (dbname,IPS,port)

with function mod_get_host(p_host in varchar2) return varchar2 is

begin

return UTL_INADDR.GET_HOST_ADDRESS(p_host);

exception when others

then

return UTL_INADDR.GET_HOST_ADDRESS(p_host||'.fw.somedomainname.com.tr');

end;

IPTAB as (select  case

when db_target_name  like '%CDB%' then substr(db_target_name,instr(db_target_name, '_') + 1)

else db_target_name

end dbname, mod_get_host(regexp_substr(value, 'HOST\=(.+)\)\(',1,1,NULL,1)) hostname,

regexp_substr(value, 'PORT\=([:0-9 :]+)',1,1,NULL,1) port,

a.* FROM (select distinct DB_TARGET_NAME, type, value FROM RAPOR.ASV_GV$LISTENER_NETWORK

where db_target_name not like '%PCDBZP%'

and db_target_name not like '%PCDBYP%'

and db_target_name not like '%PCDBCP%'

and db_target_name not like '%PCDBEP%') a where type in ('REMOTE LISTENER', 'LOCAL LISTENER')),

ASV_DB_IPS_VW1 as (select * from iptab

where hostname is not null

and port is not null

and dbname not like '%CDB%'),

IPTAB_fromVW as(

select dbname, listagg(hostname, ',') within group (order by hostname) hostname

from ASV_DB_IPS_VW1 

group by  dbname),

PORTTAB_fromVW as(select dbname, listagg(port, ',') within group (order by port) port

from (select distinct dbname, port from ASV_DB_IPS_VW1  )

group by  dbname)

select i.dbname,

case

when i.hostname like '%OneIP%' then i.hostname||',SomeIPs'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

when i.dbname like '%onedbname%' then 'onedbname'

else i.hostname

end IPs,

p.port from IPTAB_fromVW i join PORTTAB_fromVW p

on i.dbname = p.dbname;

What am I missing?

Thanks in advance!

Best Answer

Answers