I am getting this error when i am trying to use dbms_sql.execute_and_fetch to get result of
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SQL", line 1721
ORA-06512: at line 136
Purpose of this code is to get trend of those components of shared pool which have been in top 5 list anytime. Because the top 5 list varies i have to use list aggregate in pl/sql to get the PIVOT working for me. Purpose of the PIVOT is to transpose the bytes for each of the components of the shared pool into separate columns.
I am able to get this code working in 12c using the dbms_sql.return_result function but somehow not able to get the equivalent working in 11g.
Below is the code i use in 11g and which is not working
col bytes for 999,999,999,999
col name for a30 trunc
define p_days=&2
define p_inst_num=&1
prompt
prompt
declare
sql_query clob;
cols clob;
l_cursor NUMBER := dbms_sql.open_cursor;
l_dummy integer;
l_dummy2 integer;
l_columns dbms_sql.desc_tab;
l_value NUMBER;
begin
select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group (order by NAME)
into cols
from (
with snapids as
(
select /*+ materialize */ snap_id from dba_hist_snapshot where instance_number=&&p_inst_num and begin_interval_time >sysdate-&&p_days
)
,rnks as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,'yyyy/mm/dd hh24') s_hr,
dhsg.name,
dhsg.bytes,
rank() over (partition by dhsg.snap_id order by dhsg.bytes desc ) rnk
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND pool='shared pool'
ORDER BY 1 asc
)
,toprnk_pools as
(
select /*+ materialize */ distinct name from rnks where rnk <6
)
,toprnk_statsonly as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,'yyyy/mm/dd hh24') s_hr,
dhsg.name,
dhsg.bytes
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si,
toprnk_pools tp
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND dhsg.name=tp.name
AND dhsg.pool='shared pool'
ORDER BY 1 asc
)
SELECT distinct name FROM toprnk_statsonly );
sql_query :=
'
with snapids as
(
select /*+ materialize */ snap_id from dba_hist_snapshot where instance_number=&&p_inst_num and begin_interval_time >sysdate-&&p_days
)
,rnks as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,''yyyy/mm/dd hh24'') s_hr,
dhsg.name,
dhsg.bytes,
rank() over (partition by dhsg.snap_id order by dhsg.bytes desc ) rnk
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND pool=''shared pool''
ORDER BY 1 asc
)
,toprnk_pools as
(
select /*+ materialize */ distinct name from rnks where rnk <6
)
,toprnk_statsonly as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,''yyyy/mm/dd hh24'') s_hr,
dhsg.name,
round(dhsg.bytes/1024/1024,0) bytes
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si,
toprnk_pools tp
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND dhsg.name=tp.name
AND dhsg.pool=''shared pool''
ORDER BY 1 asc
)
select * from
(
select *
from toprnk_statsonly
)
pivot
(
MIN(BYTES) for NAME in ('||cols||' )
)';
dbms_sql.parse(l_cursor, sql_query, dbms_sql.NATIVE);
dbms_sql.describe_columns(l_cursor, l_dummy, l_columns);
FOR i IN 1..l_columns.count LOOP
dbms_sql.define_column(l_cursor, i, l_columns(i).col_type);
END LOOP;
l_dummy := dbms_sql.execute_and_fetch(l_cursor, TRUE); /* this is the reason for the error */
--
--
FOR i IN 1..l_columns.count LOOP
dbms_sql.column_value(l_cursor, i, l_value);
dbms_output.put_line(l_columns(i).col_name ||' = '||l_value);
END LOOP;
end;
/
I have adapted this code with help from the below link
Display Dynamic EXECUTE Output Within pl/sql From sqlplus
i am getting this error when running it.
Below is the code i use in 12c and which is working fine.
col bytes for 999,999,999,999
col name for a30 trunc
define p_days=&2
define p_inst_num=&1
prompt
prompt Showing free memory trend
prompt
declare
sql_query clob;
cols clob;
l_cursor NUMBER := dbms_sql.open_cursor;
l_dummy NUMBER;
l_columns dbms_sql.desc_tab;
l_value NUMBER;
rc sys_refcursor;
begin
select listagg('''' || NAME || ''' as "' || NAME || '"', ',') within group (order by NAME)
into cols
from (
with snapids as
(
select /*+ materialize */ snap_id from dba_hist_snapshot where instance_number=&&p_inst_num and begin_interval_time >sysdate-&&p_days
)
,rnks as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,'yyyy/mm/dd hh24') s_hr,
dhsg.name,
dhsg.bytes,
rank() over (partition by dhsg.snap_id order by dhsg.bytes desc ) rnk
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND pool='shared pool'
ORDER BY 1 asc
)
,toprnk_pools as
(
select /*+ materialize */ distinct name from rnks where rnk <6
)
,toprnk_statsonly as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,'yyyy/mm/dd hh24') s_hr,
dhsg.name,
dhsg.bytes
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si,
toprnk_pools tp
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND dhsg.name=tp.name
AND dhsg.pool='shared pool'
ORDER BY 1 asc
)
SELECT distinct name FROM toprnk_statsonly );
sql_query :=
'
with snapids as
(
select /*+ materialize */ snap_id from dba_hist_snapshot where instance_number=&&p_inst_num and begin_interval_time >sysdate-&&p_days
)
,rnks as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,''yyyy/mm/dd hh24'') s_hr,
dhsg.name,
dhsg.bytes,
rank() over (partition by dhsg.snap_id order by dhsg.bytes desc ) rnk
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND pool=''shared pool''
ORDER BY 1 asc
)
,toprnk_pools as
(
select /*+ materialize */ distinct name from rnks where rnk <6
)
,toprnk_statsonly as
(
SELECT
/*+ materialize */
to_char(dhs.begin_interval_time,''yyyy/mm/dd hh24'') s_hr,
dhsg.name,
round(dhsg.bytes/1024/1024,0) bytes
FROM
DBA_HIST_SGASTAT dhsg,
DBA_HIST_SNAPSHOT dhs,
snapids si,
toprnk_pools tp
WHERE
dhs.snap_id=si.snap_id
AND dhs.instance_number=&&p_inst_num
AND dhsg.snap_id=dhs.snap_id
AND dhsg.instance_number=dhs.instance_number
AND dhsg.dbid=dhs.dbid
AND dhsg.name=tp.name
AND dhsg.pool=''shared pool''
ORDER BY 1 asc
)
select * from
(
select *
from toprnk_statsonly
)
pivot
(
MIN(BYTES) for NAME in ('||cols||' )
)';
/* 12c working code starts here */
open rc for sql_query;
dbms_sql.return_result(rc);
/* 12c working code end here */
end;
/
Below is the sample output when working fine.
I am not familiar with DBMS_SQL at all so not sure what is going wrong here.
