PL/SQL :: How To Free Temporary LOB Returned By A Function

Apr 4, 2013

I have a business need to have a db function that would construct and return a (temporary) CLOB value.

here is its sample code:

create or replace package PKG_TEST_CLOB
as
function FN_TEST_TEMP_CLOB
return clob;

[code]....

when this function is invoked from a SQL Statement...

***
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual;
***

... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1

when this function is invoked via a PL/SQL block...

***
declare
l_clob clob;
begin
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual;
end;
/
declare

[code]....

... the counter doesn't budge

In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.

View 0 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pipeline Function - No Rows Returned

Aug 11, 2011

I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.

SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.

Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.

CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...

View 16 Replies View Related

SQL & PL/SQL :: How To Handle Ref Cursor Returned From Another Function

Dec 16, 2011

declare
type osd_refone is ref cursor;
osd_ref osd_refone;
l_status number;
[code]......

abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.

ERROR at line 8:
ORA-06550: line 8, column 12:
PLS-00221: 'OSD_REF' is not a procedure or is undefined
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

View 6 Replies View Related

SQL & PL/SQL :: How To Obtain Data Returned By Function When Executed Using Dynamic Query

May 25, 2011

I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?

The code I have is as follows:

CREATE OR REPLACE PACKAGE qi_test IS

TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........

I want to be able to execute the above function using dynamic SQL. Initially tried:

DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......

...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:

f2_data := qi_test.get_f2_data;

...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.

View 10 Replies View Related

SQL & PL/SQL :: Order Of Bytes Returned By Dump() Function In Reverse Key Indexes?

May 28, 2013

Oracle Version: 11gR2: 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 X86_64

Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.

As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?

According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.

I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link

[URL]

So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.

I'm going to write down here the test that I did, where is/are my error(s)

Test Case:
(I use a copy of the employees table in hr sample schema)
SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees;
Table created.
SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);

[code]...

Now, a test SQL Query using the index we've just defined (just for giving an example)

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

FIRST_NAME DUMP_RESULT
-------------- ----------------------------------
Kelly Typ=1 Len=5: 4b,65,6c,6c,79

[code]...

So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79

SQL> SELECT chr(to_number('4b', 'xx')) ||
2 chr(to_number('65', 'xx')) ||
3 chr(to_number('6c', 'xx')) ||
4 chr(to_number('6c', 'xx')) ||
5 chr(to_number('79', 'xx')) first_name
6 FROM DUAL;

FIRST_NAME
--------------------
Kelly

Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.

Now let's rebuild the index

SQL> ALTER INDEX emp_test_idx REBUILD REVERSE;
Index altered.
SQL>

Once the index keys have been reversed, I run the very same query in order to see the difference

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

[code]...

So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)

View 8 Replies View Related

SQL & PL/SQL :: Return Ref Cursor To Temporary Table From Function

Feb 17, 2011

I have strange problem when i try to return a ref cursor holding data from a select on a oracle global temporary table. If i iterate through the cursor , i can see the values but the function as such returns nothing through the ref cursor. I tried the temporary table as both delete on commit and preserve on commit

create or replace
PACKAGE BODY BILL AS

FUNCTION FILTERI RETURN BILL.refcursor IS
testcursor BILL.refcursor;

ttstatus INT;
iSuccess INT;
returns INT;
TruncatedSQL1 VARCHAR2(32767);
BEGIN
[code].........

View 12 Replies View Related

SQL & PL/SQL :: Declare Returned Value - Getting Error

Jan 28, 2013

while trying to execute this

declare ret_val number;
begin
exec p_buildinfo('252657020001', to_date('20120820','YYYYMMDD'),to_date('20120928','YYYYMMDD'),ret_val, 0);
DBMS_OUTPUT.PUT_LINE('Value Returned Is : '||ret_val) ;
end;

I getting the below error

ORA-06550: line 3, column 10:
PLS-00103: Encountered the symbol "P_BUILDINFO" when expecting one of the following:

:= . ( @ % ;
The symbol ":=" was substituted for "P_BUILDINFO" to continue.

the procedure structure is

CREATE OR REPLACE
PROCEDURE p_buildsinfo ( var_p_cod CHAR := NULL,
var_p_dat_from DATE := NULL,
var_p_dat_to DATE := NULL,
po_var_l_nxt_seq IN OUT NUMBER,
var_p_consol_flg NUMBER default 0
)

View 14 Replies View Related

SQL & PL/SQL :: Oracle MAX Date - BLANK Row Returned?

Aug 2, 2013

If running the following query:

select s.effective_date
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;

There are no rows returned.However, if I am running the other query:

select MAX(s.effective_date)
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;

There is a BLANK row returned.

View 11 Replies View Related

Counting Rows - Difference In Values That Are Returned?

Sep 26, 2010

difference in the values that are returned?

select count(*) from aaa;
COUNT(*)
----------
1000001

select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202

View 5 Replies View Related

SQL & PL/SQL :: Get Total Number Of Rows Returned By Query?

Sep 17, 2010

DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;

[Code].....

How can I get the total number of rows returned by the query?

I want to be able to check omething like c1.ROWS = 0

View 4 Replies View Related

SQL & PL/SQL :: Record Event If No Rows Are Returned Between Two Timestamp?

Feb 21, 2013

I have employees under a supervisor defines as below:

select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'

Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:

select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1),
case when CLOCK_OUT_DATE is null then
'Not clocked out'
else

[code]....

The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.

How can we modify query to show those 26 people as 'Not clocked In'

View 7 Replies View Related

Free Oracle Download For Fedora 5

Nov 30, 2006

i am looking for a free download by oracle meant specifically for Linux. i am using fedora core 5 32 bit version of Linux.

View 1 Replies View Related

Query Free Percentages Of Tablespaces

Jan 9, 2013

how can i check using query the free percentages of my tablespaces

View 10 Replies View Related

ODP.NET :: Is ODAC Free For Oracle10g User

Aug 6, 2012

My company brought Oracle10g server some years ago. I've noticed that ODAC support Linq and could be used to connect Oracle 10g.

Can we use ODAC 11.2 Release 4 (11.2.0.3.0) with Oracle Developer Tools for Visual Studio for commerical use due we already purchased Oracle 10g server?

View 1 Replies View Related

Storing List Of Values Which Are Returned By A Select Query?

Oct 9, 2012

I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.

how to proceed further and the ways how can I write this requirement.

View 1 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How Can I delete the returned two rows?

1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Use Records Returned By Query As Column Names In A Select?

Jul 3, 2011

is it possible to use the records returned by a query as column names in a select query.

select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

View 3 Replies View Related

PL/SQL :: Characters Is Greater Than 4 / Actual Data Without Lpad Should Be Returned

Oct 15, 2012

For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd

but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.

View 7 Replies View Related

Sga-max-size - System Free Memory Decreased?

Sep 18, 2012

i have altered the sga_max_size to 2gb.i found the system free memory is also decreased.Is system free memory changes when sga_max_size is changes?

View 1 Replies View Related

Oracle 9i Forms And Reports Free Download?

Sep 11, 2010

from where I can free download Oracle 9i Forms And Reports.

View 2 Replies View Related

Server Administration :: How To Know Which Block Contains Free Space

Feb 26, 2013

for some reason,i want to know which data block contains free space,or which table/index contains free space.

View 6 Replies View Related

PL/SQL :: Tablespace Allocated / Used / Free And % - Used By Every Schema Level

Mar 11, 2013

One tablespace used in two different schemas ,in this case how much space used,free space and persentage of used in each schema level.

Example: USERS tablespace used in two different schemas HR,SCOTT. in this suche case to find the total size of tablespace, used and free space of each schema level.I used the below query not giving appropriate result.

SELECT TABLESPACE_NAME,
OWNER, round(SUM(BYTES/1024/1024),2) "Used Space in MB"
FROM DBA_SEGMENTS
where tablespace_name not in ('SYSTEM','TEMP','SYSAUX','EXAMPLE','UNDOTBS1') 
group by tablespace_name,owner
order by tablespace_name

View 4 Replies View Related

How To Get Free Space On Operating System Drive

Nov 5, 2013

is it possible to get information about free space on drive/path for example with some DBMS_*, UTL_* package or with stored function based on Java

(e.g. CREATE OR REPLACE FUNCTION GetFreeSpace(driveLetter IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'DiskSpace.getFreeBytes(java.lang.String) return java.lang.String') but for JDK 1.3 

View 8 Replies View Related

Reports & Discoverer :: REP-1825 / Before Report Trigger Returned FALSE

Feb 24, 2011

I am oracle clinical 4.6. In Oracle clinical when i am trying to submit "Print DCF" it should run and generate the report in PDF format. But I am getting the following error message.

MSG-00010: 328900: SRW.RUN_REPORT failed.
Updating failure text.
REP-1825: Before Report trigger returned FALSE.

View 11 Replies View Related

SQL & PL/SQL :: Filtering Dataset Based On Records Returned By Table Join

Nov 10, 2010

I need to work on this requirement.

There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.

One of four conditions are possible for each Supply Reorder Number:

•Both table queries return no records

oPopulate all the output fields with nulls

•T1 returns a record, but T2 returns no records

oPopulate output fields with values from the join of T1 and T11.

•T1 returns no records, but T2 returns one record

oPopulate output fields with values from the join of T2 and T22.

•T1 returns a record, and T2 returns a record

oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.

oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .

oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.

How do we filter the dataset based on result of table join ?

View 1 Replies View Related

SQL & PL/SQL :: How To Record / Nested Table Type Based On Row Returned By Week

May 10, 2011

Is there a way we could define a record or a nestedtable with a type based on weak refursor i.e

TYPE RC IS REF CURSOR;
C2 RC;
Type t is table of c2%rowtype;
Following is some more explanation of what I am trying to do.

I have a table T with column A and B. Column A is a primary key with number 1,2,3,4,5,6, Column B has diffrent sql stmts stored. i.e 'Select * from emp', Select count(1) from dept' and so on. So table will look like

1 Select * from emp
2 Select count(1) from dept

Now I want to select statements stored in table T one by one and execute them by using cursor. Problem arises as i need to fetch the cursor into some variable but the outcome of each statment is diffrent and oracle does not allow to use cursorname%rowtype for a weak ref cursor.

View 3 Replies View Related

Server Administration :: Free Space In Standby Database?

Jul 23, 2011

How do we find the free space in tablespaces in a standby database

View 4 Replies View Related

Server Administration :: How To Reclaim Temp Free Blocks

Jul 26, 2011

we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?

TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;

TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426

1 row selected.
further when i checked the session details using TEMP segment, i got below output:

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE

3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?

there is one more column - SEGBLK#
explain what is the exact meaning of this column ?

to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;

View 3 Replies View Related

Server Administration :: Check Free Extents In Tablespace

Apr 29, 2012

which data dictionary view would you query to see the free extents in a tablespace.

i use dba_free_space;

SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200

View 2 Replies View Related

Partition Drop Not Increasing Free Space In Dba_free_spaces?

Jan 16, 2013

I am Using Oracle 11.2.0.3. I had a script that droped partitions for a table which should have dropped nearly 30 gb of data. We have data and index tablespace.

Query of dba_free_space showed no change on data tablespace. However, index tablespace showed increase in free space.

select  sum(bytes / (1024 * 1024 * 1024)) "Size (GB)"  from dba_free_space where tablespace_name ='&tbs_name'

I am using following command to drop partition

ALTER TABLE table_name
DROP PARTITION "partition_name"
UPDATE GLOBAL INDEXES;

What should be done to increase free space shown for tablespace after partition drop?

View 2 Replies View Related







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