Skip to Main Content
Forums

SQL & PL/SQL

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!

performance issue with the Oracle SQL query

Sunny_JMay 9 2014 — edited May 15 2014

Dears

Good evening

I'm new to start using Oracle SQL , I have a sql query which is taking more time to execute in the production .

Each table in the query contains 1.2 million records and DBA suggested to use the 'Oracle - hints' . i dont have proper knowledge on this topic and facing problems to implement this hints to imrpovise the performance . In the product the Informatica jobs are failed and stuck with this query performance issue .

i request from this forum for an urgent help for me to resolve this issue by using 'hints' . kindly help me .

 

SELECT
CASE.ID,
CASE.DTYPE,
CASE.VERSION,
CASE.EXTERNAL_REF,
CASE.CREATION_TS,
RQ.TYPE
FROM
PAS_CASE CASE,
AS_REQUEST RQ,
PAS_CONTEXT CN
where rq.case_id = case.id
AND rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)
and CN.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
AND CAST(CN.CREATION_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND
CAST(CN.CREATION_TS AS DATE) <  TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

------ 2nd query

SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
TASK.MAIN_REQ_TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select  cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
,    PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999

) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on RA.REQUEST_ID = task.ID
and RA.ID between $$LOW_ID1 AND $$HIGH_ID1
UNION

SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
MAIN_REQ.TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select  rq.id,rq.type
from PAS_REQUEST rq
,    PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on RA.REQUEST_ID = main_req.ID

--- 3rd query

SELECT

RB.ID,

RB.DTYPE,

RB.VERSION,

RB.TYPE,

RB.CREATION_TS,

RB.TASK_ID,

RB.COLOR,

RB.GLOBAL_RESULT ,

TASK.MAIN_REQ_TYPE

FROM

PAS_RESULTBLOCK RB

join

(

select tsk.ID , tsk.TYPE, main_req. main_req_type

from PAS_REQUEST tsk

join

(

select  cn.id as context_id, rq.TYPE as main_req_type

from PAS_REQUEST rq

,    PAS_CONTEXT cn

where rq.id = cn.request_id

and rq.DTYPE = 'MAINREQUEST'

and rq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and rq.ID between 20141800000000000 AND 20141999999999999

) main_req

on tsk.parent_context_id = main_req.context_id

and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')

and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1

) task

on rb.task_id = task.ID

and rb.ID between $$LOW_ID1 AND $$HIGH_ID1

and RB.TYPE is not null

and RB.TYPE IN

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

UNION

SELECT

RB.ID,

RB.DTYPE,

RB.VERSION,

RB.TYPE,

RB.CREATION_TS,

RB.TASK_ID,

RB.COLOR,

RB.GLOBAL_RESULT,

TASK.MAIN_REQ_TYPE

FROM

PAS_RESULTBLOCK RB

join

(

select tsk.ID , tsk.TYPE, main_req. main_req_type

from PAS_REQUEST tsk

join

(

select  cn.id as context_id, rq.TYPE as main_req_type

from PAS_REQUEST rq

,    PAS_CONTEXT cn

where rq.id = cn.request_id

and rq.DTYPE = 'MAINREQUEST'

and rq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and rq.ID between 20141800000000000 AND 20141999999999999

) main_req

on tsk.parent_context_id = main_req.context_id

and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')

and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1

) task

on rb.task_id = task.ID

and rb.ID between $$LOW_ID1 AND $$HIGH_ID1

and RB.TYPE is nullSELECT

RB.ID,

RB.DTYPE,

RB.VERSION,

RB.TYPE,

RB.CREATION_TS,

RB.TASK_ID,

RB.COLOR,

RB.GLOBAL_RESULT ,

TASK.MAIN_REQ_TYPE

FROM

PAS_RESULTBLOCK RB

join

(

select tsk.ID , tsk.TYPE, main_req. main_req_type

from PAS_REQUEST tsk

join

(

select  cn.id as context_id, rq.TYPE as main_req_type

from PAS_REQUEST rq

,    PAS_CONTEXT cn

where rq.id = cn.request_id

and rq.DTYPE = 'MAINREQUEST'

and rq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and rq.ID between 20141800000000000 AND 20141999999999999

) main_req

on tsk.parent_context_id = main_req.context_id

and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')

and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1

) task

on rb.task_id = task.ID

and rb.ID between $$LOW_ID1 AND $$HIGH_ID1

and RB.TYPE is not null

and RB.TYPE IN

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

UNION

SELECT

RB.ID,

RB.DTYPE,

RB.VERSION,

RB.TYPE,

RB.CREATION_TS,

RB.TASK_ID,

RB.COLOR,

RB.GLOBAL_RESULT,

TASK.MAIN_REQ_TYPE

FROM

PAS_RESULTBLOCK RB

join

(

select tsk.ID , tsk.TYPE, main_req. main_req_type

from PAS_REQUEST tsk

join

(

select  cn.id as context_id, rq.TYPE as main_req_type

from PAS_REQUEST rq

,    PAS_CONTEXT cn

where rq.id = cn.request_id

and rq.DTYPE = 'MAINREQUEST'

and rq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and rq.ID between 20141800000000000 AND 20141999999999999

) main_req

on tsk.parent_context_id = main_req.context_id

and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')

and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1

) task

on rb.task_id = task.ID

and rb.ID between $$LOW_ID1 AND $$HIGH_ID1

and RB.TYPE is null

--- 4th query

SELECT
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL,
RES_BLK.MAIN_REQ_TYPE
FROM
PAS_RESULTITEM RI
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select  cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
,    PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.NAME IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is not null
UNION

select
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL ,
RES_BLK.MAIN_REQ_TYPE
from pas_resultitem ri
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select  cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
,    PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is null

-- 5HT QUERY

SELECT

TSK.ID,

TSK.VERSION,

TSK.DTYPE,

TSK.CASE_ID,

TSK.TYPE,

TSK.CORRELATION_ID,

TSK.INITIATOR,

TSK.EXECUTOR,

TSK.CATEGORY,

TSK.PARENT_CONTEXT_ID,

TSK.CREATION_TS,

MAIN_REQ.MAIN_REQ_TYPE

FROM

PAS_REQUEST  tsk

join

(

select  cn.id as context_id, rq.TYPE as main_req_type

from PAS_REQUEST rq

,   PAS_CONTEXT cn

where rq.id = cn.request_id

and rq.DTYPE = 'MAINREQUEST'

and rq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and rq.ID between 20141800000000000 AND 20141999999999999

) main_req

on tsk.parent_context_id = main_req.context_id

and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')

and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1

UNION

select

MREQ.ID,

MREQ.VERSION,

MREQ.DTYPE,

MREQ.CASE_ID,

MREQ.TYPE,

MREQ.CORRELATION_ID,

MREQ.INITIATOR,

MREQ.EXECUTOR,

MREQ.CATEGORY,

MREQ.PARENT_CONTEXT_ID,

MREQ.CREATION_TS,

MREQ.TYPE

from PAS_REQUEST mreq

     ,   PAS_CONTEXT cn

where mreq.id = cn.request_id

and mreq.DTYPE = 'MAINREQUEST'

and mreq.TYPE in

(

'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',

'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'

)

and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

and cn.ID between 20141800000000000 AND 20141999999999999

and mreq.ID  between 20141800000000000 AND 20141999999999999
This post has been answered by Etbin on May 10 2014
Jump to Answer

Comments

OrionNet
Hello,
Can you post your get_price procedure/function?

Try using anonymous pl/sql block simple example, 
CREATE OR REPLACE FUNCTION getpmnorgrow (empid emp.id%TYPE)
   RETURN emp%ROWTYPE --
IS
   result   emp%ROWTYPE;
BEGIN
   SELECT *
   INTO result
   FROM emp
   WHERE id = empid;

   RETURN result;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      raise_application_error (-20100, 'error');
      RETURN NULL;
END;
/

DECLARE
   retval   emp%ROWTYPE;
   empid    NUMBER;
BEGIN
   empid    := 1;

   retval   := getpmnorgrow (empid);
   DBMS_OUTPUT.put_line ('employee name=' || retval.name);
   COMMIT;
END;
Regards
Tubby 
variable my_cursor refcursor;
exec :my_cursor := get_price('0123','p1,p2,p3');

print :my_cursor
OrionNet
Check this link , discussing the same error


844608

Regards
247890
Hi,

Thanks for your response.
-------------------------------------------------------------------------

Edited by: user6773 on Jan 5, 2009 6:05 AM
247890
Hi Tubby,

I get an error msg when I try this. Not sure ..
Karthick2003
Whats the error you are getting?
247890
When I try to test it:

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype
SomeoneElse
select get_price('0123','p1,p2,p3') from dual;
Why do you have p1,p2,p3 in quote marks?

Is your function expecting a character string here? Or should they be separate parameters?
247890
Yes, it is expecting a string of values for the second parameter (set of products).
SomeoneElse
I have no idea what your function does, but try this:

SQL> var results refcursor
SQL> exec :results := get_price('0123','p1,p2,p3');
SQL> print results
247890
Thanks for your response. That works. But, I am not sure if this is useful when this function is called by a java application.
SomeoneElse
But, I am not sure if this is useful when this function is called by a java application.
Of course not. You asked why this didn't work in SQL Plus, so this is a SQL Plus solution.

If you're using Java and having trouble, you can post another question.
Boneist
It would be really, really helpful if you could post your function, as has already been requested!

I could ask you "My car doesn't work start, unless I'm parked on a hill, why not?" but I bet you'd have trouble pinpointing the exact problem that my car has got...
Satyaki_De
I have plenty of questions.

Question:

Why don't you post your actual script here?

a. Your Company Restrict You.
b. You don't want to post.
c. You don't understand what our volunteers have repeatedly ask
d. None of the above.

Now,

Solution - In case of ->

a. Then post one dummy solution which also facing the same error prone scenario and ask your requirement clearly.

b. Then hard luck. Our help will be up to this due to your response.

c. Our volunteers are asking repeatedly to post the actual code here. And, again as i've already explained in point (a) - if you have any problem to post then follow solution a.

d. Then try to read oracle documentation and specifically read how collection and ref cursor works and then try to implement that by yourself.

I hope i've covered almost all the points for you.

Regards.

Satyaki De.
247890
wow!

Sorry, it took me a little time to check this. I am not sure if my company objects to this. So, I have posted this code earlier and then, I removed it again.

I didn't mean to ask a random question and expect answer. After reading few similar threads on this forum, I thought this question is more independent of what code does. Sorry for any inconvenience caused...
-------------------------
create or replace package types as
type cursor_type is ref cursor;
end types;

CREATE OR REPLACE FUNCTION GET_multi_PRICE(P_STORE IN VARCHAR2,
--P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
v_inStr IN VARCHAR2
)
RETURN types.cursor_type IS
v_cursor types.cursor_type;
V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
V_SEQ RPPRODPRICE.SEQNUM%TYPE;
v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
MSG VARCHAR2(200) := NULL;
v_sql VARCHAR2(4000) ;
v_instr_rp VARCHAR2(200);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
FROM RPPRICESTORE STR, RPPRICESHEET PS
WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
AND STR.SEQNUM = PS.SEQNUM
AND PS.STATUS = 'D'
AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
AND (STR.EXPDATE = '99999999' OR
TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10))
AND STR.STORE = P_STORE
AND STR.EFFECTIVEDATE =
(SELECT MAX(S.EFFECTIVEDATE)
FROM RPPRICESTORE S
WHERE S.STORE = P_STORE
AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
AND (S.EXPDATE = '99999999' OR
TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)));
--=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));

IF V_PR_SH_TYPE = 'A' THEN
IF V_ADV_TYPE = 'E' THEN


v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI
WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
dbms_output.put_line(v_sql);

OPEN v_cursor FOR v_sql;
RETURN v_cursor;
-- ADV Rule based
END IF;

END IF;
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
WHEN OTHERS THEN
MSG := SUBSTR(SQLERRM, 1, 70);
DBMS_OUTPUT.PUT_LINE(MSG);
END;
/
Satyaki_De
I'm posting your query in a formatted manner so that everyone can easily understand your coding ->
create or replace package types as
  type cursor_type is ref cursor;
end types;

CREATE OR REPLACE FUNCTION GET_multi_PRICE
 (
   P_STORE IN VARCHAR2,
   --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
   v_inStr IN VARCHAR2
 )
RETURN types.cursor_type 
IS
    v_cursor types.cursor_type;
    V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
    V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
    V_SEQ RPPRODPRICE.SEQNUM%TYPE;
    v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
    V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
    V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
    V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
    v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
    V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
    V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
    V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
    V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
    V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    MSG VARCHAR2(200) := NULL;
    v_sql VARCHAR2(4000) ;
    v_instr_rp VARCHAR2(200);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
  SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
  INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
  FROM RPPRICESTORE STR, RPPRICESHEET PS
  WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
  AND STR.SEQNUM = PS.SEQNUM
  AND PS.STATUS = 'D'
  AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
  AND (
         STR.EXPDATE = '99999999' 
         OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
      )
  AND STR.STORE = P_STORE
  AND STR.EFFECTIVEDATE =(
                            SELECT MAX(S.EFFECTIVEDATE)
                            FROM RPPRICESTORE S
                            WHERE S.STORE = P_STORE
                            AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                            AND (
                                    S.EXPDATE = '99999999' 
                                    OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                )
                         );
  --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
  v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));

  IF V_PR_SH_TYPE = 'A' THEN
    IF V_ADV_TYPE = 'E' THEN
      v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
               ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
               ' AND PRI.PRODCODE in (' || '''' || v_instr_rp || '''' ||
               ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
      dbms_output.put_line(v_sql);
      
      OPEN v_cursor FOR v_sql;
      
      RETURN v_cursor;
    -- ADV Rule based
    END IF;
  END IF;
exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
  WHEN OTHERS THEN
    MSG := SUBSTR(SQLERRM, 1, 70);
    DBMS_OUTPUT.PUT_LINE(MSG);
END;
N.B.: This is your post - i just format it.

Regards.

Satyaki De.
Satyaki_De
I'm not so sure whether your previous solution is working or not.

But, i've made some modification in your code and this might work for you ->
CREATE OR REPLACE FUNCTION GET_multi_PRICE
 (
   P_STORE IN VARCHAR2,
   --P_PRODCODE IN RPPRODPRICE.PRODCODE%TYPE,
   v_inStr IN VARCHAR2
 )
RETURN types.cursor_type 
IS
    v_cursor types.cursor_type;
    V_PR_SH_CODE RPPRODPRICE.PRICESHEETCODE%TYPE;
    V_BASE_SH_CODE RPCOROLLARY.BASEPRICESHEETCODE%TYPE;
    V_SEQ RPPRODPRICE.SEQNUM%TYPE;
    v_fin_seq RPPRODPRICE.SEQNUM%TYPE;
    V_BASE_SEQ RPCOROLLARY.BASESEQNUM%TYPE;
    V_ADV_TYPE RPPRICESHEET.ADVTYPE%TYPE;
    V_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_PR_SH_TYPE RPPRICESHEET.PRICESHEETTYPE%TYPE;
    v_fin_pr_sh_type RPPRICESHEET.PRICESHEETTYPE%TYPE;
    V_ADJ_TYPE RPCOROLLARY.ADJTYPE%TYPE;
    V_ADJ_AMT RPCOROLLARY.ADJAMT%TYPE;
    V_DISC_TYPE RPCOROLLARY.DISCTYPE%TYPE;
    V_END_PRICE RPCOROLLARY.PRICEENDING%TYPE := 0;
    V_FINAL_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    V_RET_PRICE RPPRODPRICE.PRICE%TYPE := 0;
    MSG VARCHAR2(200) := NULL;
    v_sql VARCHAR2(4000) ;
    v_instr_rp VARCHAR2(200);
    
    cursor c1
    is
      SELECT  chr(39)||
              SUBSTR(v_inStr,                    
                      INSTR(v_inStr,',',1,LEVEL) + 1,                    
                      NVL(
                          LEAD(INSTR(v_inStr,',',1,LEVEL)) OVER (ORDER BY INSTR(v_inStr,',',1,LEVEL)) - 1 , 
                          LENGTH(v_inStr) + 1 
                        ) - INSTR(v_inStr,',',1,LEVEL)                   
                    )||
              chr(39) ils       
      FROM ee,DUAL 
      CONNECT BY LEVEL <= LENGTH(v_inStr) - LENGTH(REPLACE(v_inStr,',')) + 1;
      
    r1 c1%rowtype;
    
    v_instr_p  varchar2(2000);
-- 1. Check to see if there is a SALE PRICE for the selected product
--=======(a) Get ADV price sheet for selected store from RPPRICESTORE that are in effect for the selected date
--======= Check to ensure that the price sheet has been deployed ('D')
--======= Get ADVTYPE field from RPPRICESHEET table to determine the type of ADV price sheet (E or R)
--======= If multiple records found, select the one with the most recent effective date.
BEGIN
  SELECT STR.PRICESHEETCODE, STR.SEQNUM, PS.ADVTYPE, PS.PRICESHEETTYPE
  INTO V_PR_SH_CODE, V_SEQ, V_ADV_TYPE, V_PR_SH_TYPE
  FROM RPPRICESTORE STR, RPPRICESHEET PS
  WHERE STR.PRICESHEETCODE = PS.PRICESHEETCODE
  AND STR.SEQNUM = PS.SEQNUM
  AND PS.STATUS = 'D'
  AND TO_DATE(STR.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
  AND (
         STR.EXPDATE = '99999999' 
         OR TO_DATE(STR.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
      )
  AND STR.STORE = P_STORE
  AND STR.EFFECTIVEDATE =(
                            SELECT MAX(S.EFFECTIVEDATE)
                            FROM RPPRICESTORE S
                            WHERE S.STORE = P_STORE
                            AND TO_DATE(S.EFFECTIVEDATE, 'yyyymmdd') <= SUBSTR(SYSDATE, 1, 10)
                            AND (
                                    S.EXPDATE = '99999999' 
                                    OR TO_DATE(S.EXPDATE, 'yyyymmdd') >= SUBSTR(SYSDATE, 1, 10)
                                )
                         );
  --=======(b) Get price from RPPRODPRICE. Check to make sure that ACDSTATUS is not D, or SD (Product deleted from price sheet)
  v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
  
  ---New Code for splitting the string
  
  for r1 in c1
  loop
    v_instr_p := v_instr_p || r1.ils ||',';
  end loop;
  
  v_instr_p := rtrim(v_instr_p,',')
  
  ---End Of the New Script

  IF V_PR_SH_TYPE = 'A' THEN
    IF V_ADV_TYPE = 'E' THEN
      v_sql := 'SELECT PRI.PRICE FROM RPPRODPRICE PRI WHERE PRI.PRICESHEETCODE = ' || '''' || V_PR_SH_CODE || '''' ||
               ' AND PRI.SEQNUM = ' || '''' || V_SEQ || '''' ||
               ' AND PRI.PRODCODE in (' || '''' || v_instr_p || '''' ||
               ') AND (PRI.ACDSTATUS IS NULL OR PRI.ACDSTATUS NOT IN (''D'', ''SD''))';
      dbms_output.put_line(v_sql);
      
      OPEN v_cursor FOR v_sql;
      
      RETURN v_cursor;
    -- ADV Rule based
    END IF;
  END IF;
exception
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
  WHEN OTHERS THEN
    MSG := SUBSTR(SQLERRM, 1, 70);
    DBMS_OUTPUT.PUT_LINE(MSG);
END;
/
And, now you should pass as you are trying to pass the value previously and see the result.

N.B.: Not Tested...

Regards.

Satyaki De.
isotope
What's the version of your database ?
select * from v$version;
isotope
247890
It is 8.1.6. Thanks,
Satyaki_De
Oh! Goodness.

This is almost primitive DB.

Did you check my solution? Is it working?

Regards.

Satyaki De.
247890
Yes, I tried your solution. It is erroring out near OVER (line 34).

True, it is a very old version. But, I can't help it. I have no control over it. I can execute this function just fine in Oracle 9i and above. But, not in in 8.1.6. I was wondering if there is any other way of achieving this. Thanks,
Satyaki_De
Oh! dear,

Over isn't supported in Oracle 8.

Really bad.

So, we have to explore some different ares to achieve the same.

It is working in Oracle 9i.

Regards.

Satyaki De.
247890
:(

There are all sorts of problems with this because of this version.

I can't even use Oracle SQLDeveloper to connect. Other pl/sql developing tools don't work right either. Even if they do for couple of day, they eventually giveup.
Satyaki_De
First you can test this code ->
create type np is table of number;
  2  / Type created. 
SQL> ed
Wrote file afiedt.buf   
1     Create or Replace Procedure myProc(myArray np)
2      is   
3      i   number(10);   
4      rec emp%rowtype;   
5      Begin   
6         for i in 1..myArray.count   
7         loop   
8           select *   
9           into rec  
10           from emp  
11           where empno = myArray(i);  
12        dbms_output.put_line('Employee No:'||rec.empno||' Name:'||rec.ename);  
13        end loop;  
14*   End myProc;  
15  /
Procedure created. 

SQL> declare
  2      v np:=np(6666,7777);   
  3  begin   
  4    myProc(v);   
  5  end;   
  6  / 
  Employee No:6666 Name:prithwi 
  Employee No:7777 Name:Avik
If collection is working in 8 then you can implement that in your code.

Regards.

Satyaki De.
isotope
user6773 wrote:
It is 8.1.6. Thanks,
SQL>
SQL>
SQL> @ver

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>
SQL> --
SQL> create or replace package types as
  2    type cursor_type is ref cursor;
  3  end types;
  4  /

Package created.

SQL>
SQL> CREATE OR REPLACE FUNCTION get_multi_price
  2   (
  3     P_STORE IN VARCHAR2,
  4     v_inStr IN VARCHAR2
  5   )
  6  RETURN types.cursor_type
  7  IS
  8      v_cursor types.cursor_type;
  9      V_PR_SH_CODE varchar2(1);
 10      V_SEQ number;
 11      MSG VARCHAR2(200) := NULL;
 12      v_sql VARCHAR2(4000) ;
 13      v_instr_rp VARCHAR2(200);
 14      -- blah, ...
 15  BEGIN
 16   /*
 17     blah, blah, blah, ...
 18   */
 19   V_PR_SH_CODE := 'X';
 20   V_SEQ := 1;
 21   --
 22   v_instr_rp := REPLACE(v_instr,chr(44),chr(39)||chr(44)||chr(39));
 23
 24  -- returns multiple rows
 25   v_sql := 'SELECT 100*rownum FROM all_objects PRI WHERE ''X'' = ' || '''' || V_PR_SH_CODE || '''' ||
 26            ' AND ''1'' = ' || '''' || V_SEQ || '''' ||
 27            ' AND ''p2'' in (' || '''' || v_instr_rp || '''' ||
 28            ') AND (''X'' NOT IN (''D'', ''SD'')) and rownum <= 3';
 29  -- dbms_output.put_line(v_sql);
 30   OPEN v_cursor FOR v_sql;
 31   RETURN v_cursor;
 32  exception
 33    WHEN NO_DATA_FOUND THEN
 34      DBMS_OUTPUT.PUT_LINE('There is no data for the STORE and PRODUCT_CODE combination provided.');
 35    WHEN OTHERS THEN
 36      MSG := SUBSTR(SQLERRM, 1, 70);
 37      DBMS_OUTPUT.PUT_LINE(MSG);
 38  END;
 39  /

Function created.

SQL>
SQL> -- this doesn't work in SQL*Plus in 8.1.7
SQL> select get_multi_price('0123','p1,p2,p3') as rslt from dual;
select get_multi_price('0123','p1,p2,p3') as rslt from dual
       *
ERROR at line 1:
ORA-00902: invalid datatype


SQL>
SQL> -- so use this method in SQL*Plus
SQL> var results refcursor
SQL> exec :results := get_multi_price('0123','p1,p2,p3');

PL/SQL procedure successfully completed.

SQL> print results

100*ROWNUM
----------
       100
       200
       300

SQL>
SQL>
What's the hold up ?

isotope
Satyaki_De
Oh! dear.

I was completely overlooked it. It is a function and op is returning in form of ref cursor. ;)

Good point.

Regards.

Satyaki De.
247890
It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?
isotope
user6773 wrote:
It works from sqlplus this way. But, we need to call this function from java code. I am not sure if creating a variable and printing it would work from java call. Will it work?
Of course not, as has been mentioned by SomeoneElse earlier in this thread. You have been asking for a SQL*Plus solution since the get-go:
user6773 wrote:
Hello all -

I have a function that returns a ref cursor.

When I test this function using PL/SQL developer, it works just fine and returns ref cursor with a set of price values for a set of products.

However, when I test the same function from SQL*Plus, it errors out.

select get_price('0123','p1,p2,p3') from dual;

Error at line 1:
ORA-00902: invalid datatype

Could you please help me out. Thanks in advance.
and all the suggestions over here are for SQL*Plus.

This is not a java forum. This is a SQL and PL/SQL forum. If you are expecting someone to post a Java/JDBC program to help you out, then you are barking up the wrong tree.

You may want to check out the appropriate forum for a java solution.

isotope
Satyaki_De
Here is another way to print the value from ref cursor without using the print statement and that might be helpful for you in your Java ->
satyaki>
satyaki>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:01.56
satyaki>
satyaki>
satyaki>create or replace procedure gen_cur(str in varchar2,rc in out sys_refcursor) 
  2  is   
  3    str1  varchar2(500); 
  4  begin   
  5    str1 := 'select empno,ename,hiredate,sal            
  6             from emp            
  7             where '||str;               
  8             
  9    open rc for str1;   
 10  exception   
 11    when others then     
 12      dbms_output.put_line(sqlerrm); 
 13  end; 
 14  /

Procedure created.

Elapsed: 00:00:01.18
satyaki>
satyaki>
satyaki>declare 
  2   type a is record
  3     (                    
  4        empno    number(4),                    
  5        ename    varchar2(30),                    
  6        hiredate date,                    
  7        sal      number(10,2)                  
  8     );  
  9   rec a;    
 10   b sys_refcursor;  
 11   src varchar2(300); 
 12  begin   
 13    src:= 'sal between 2000 and 7000'; 
 14    gen_cur(src,b);   
 15    loop     
 16      fetch b into rec;      
 17       exit when b%notfound;            
 18         dbms_output.put_line('Employee No:'||rec.empno||' - '||                      
 19                              'Name:'||rec.ename||' - '||                           
 20                              'Hire Date:'||rec.hiredate||' - '||                     
 21                              'Salary:'||rec.sal);   
 22     end loop;   
 23     close b; 
 24  exception   
 25    when others then     
 26      dbms_output.put_line(sqlerrm); 
 27  end; 
 28  /
Employee No:7566 - Name:JONES - Hire Date:02-APR-81 - Salary:2975
Employee No:7698 - Name:BLAKE - Hire Date:01-MAY-81 - Salary:2850
Employee No:7782 - Name:CLARK - Hire Date:09-JUN-81 - Salary:4450
Employee No:7788 - Name:SCOTT - Hire Date:19-APR-87 - Salary:3000
Employee No:7839 - Name:KING - Hire Date:17-NOV-81 - Salary:7000
Employee No:7902 - Name:FORD - Hire Date:03-DEC-81 - Salary:3000

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.02
Regards.

Satyaki De.
Satyaki_De
Sorry Duplicate post.... :(

Regards.

Satyaki De.

N.B.: Please check my last post in the previous page.

Edited by: Satyaki_De on Jan 6, 2009 12:23 AM
1 - 30
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2014
Added on May 9 2014
#issue, #oracle, #performance, #query, #sql, #with
14 comments
5,065 views