Hanrahul   85 pts.  |   Apr 16 2009  8:12AM GMT

when i exeute

EXECUTE IMMEDIATE c1_rec.selectstmt || ‘ ‘|| c1_rec.clause into val1,val2;
this clause i will get 1 query which is executed and 2 output variable with datatype varchar2 and number will be returned

now

how can i see the result of this procedure

Hanrahul   85 pts.  |   Apr 16 2009  11:50AM GMT

when i execute

select selectstmt,clause
from kpimain where kpimainid = 901;

Output is

SELECTSTMT ————————————————————————————————–

select worktype a,
(sum(estlabcost)-sum(actlabcost))*100/sum(estlabcost) b
from workorder

CLAUSE
————————————————————
group by worktype having sum(estlabcost) > 0

NOW when i execute this

EXECUTE IMMEDIATE c1_rec.selectstmt || ‘ ‘|| c1_rec.clause

Output is

A B
——————– ———-
RCE -8.4045926
REMINDER 35.2218539
USERMOVE 22.6936353
COMPLO -25.377912
CUSSPECIAL -778.18256

38 rows selected.

so how i will get these multiple values with above procedure

Thanks in advance

Carlosdl   29820 pts.  |   Apr 16 2009  2:34PM GMT

I said that you data proceeds from many queries because you have a cursor, and inside the loop you have this line:

EXECUTE IMMEDIATE c1_rec.selectstmt || ‘ ‘|| c1_rec.clause into val1,val2;

So, if the cursor ‘c1_rec’ returns more than one row, you will have more than one different queries.

Does the below query return only 1 row ?

select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,’MON’) month,cautionmax,cautionmin from kpimain where kpimainid = kpiid;

Hanrahul   85 pts.  |   Apr 16 2009  3:05PM GMT

Yes

select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,’MON’) month,cautionmax,cautionmin from kpimain where kpimainid = kpiid;

Query return only 1 row

actualy i was trying to get multiple values into val1 and val2 variable so i use loop
but it is not working

Carlosdl   29820 pts.  |   Apr 16 2009  5:19PM GMT

Thanks for clarifying that.

So, you could use a simple select statement, and avoid the use of the cursor.

You have two options, use the procedure I suggested in the answer, to return a PL/SQL table (eliminating the use of the cursor), or you could return a REF CURSOR.

To return a REF CURSOR, you should do something similar to this:

Define the type:

PACKAGE the_types IS
  TYPE cursor_type IS REF CURSOR;
END;

And then your procedure would look something like this:

CREATE OR REPLACE procedure Vishal_PROC_sp1 (kpiid IN NUMBER, p_recordset OUT the_types.cursor_type) AS
 	l_selectstmt	varchar2(200);
 	l_clause		varchar2(200);
BEGIN
	 SELECT selectstmt,clause INTO l_selectstmt,l_clause
	   	FROM kpimain WHERE kpimainid = kpiid;
	 OPEN p_recordset for l_selectstmt||’ ‘||l_clause;
END;

Then you can fetch the data from the returned cursor in you calling procedure, in a similar way as how you do it with a normal explicit cursor.

Let us know if you need further help.

Hanrahul   85 pts.  |   Apr 17 2009  8:45AM GMT

Hi,

Thanks for replay it works
and there is actualy no need to create package I run this procedure as below and it works for me

CREATE OR REPLACE procedure Vishal_PROC_sp1
(kpiid IN NUMBER,
p_recordset OUT sys_refcursor)
AS
l_selectstmt varchar2(200);
l_clause varchar2(200);
BEGIN
SELECT selectstmt,clause INTO l_selectstmt,l_clause
FROM kpimain WHERE kpimainid = kpiid;
OPEN p_recordset for
l_selectstmt||’ ‘||l_clause;
END;
/

var v_kpiid Number;
BEGIN
:v_kpiid := 901;
END;
/
var g_Cursor;

EXECUTE Vishal_PROC_sp1(:v_Kpiid,:g_Cursor);

PRINT :g_Cursor;

Thanks again

Carlosdl   29820 pts.  |   Apr 17 2009  2:12PM GMT

You are right Hanrahul, there is no need to create the package.
I ran a small test, but I did it on an 8i database, in which SYS_REFCURSOR cannot be used as a type.

I’m glad it worked.

Hanrahul   85 pts.  |   Apr 17 2009  2:43PM GMT

Ok Thanks Again For Your Reply

I have 1 More Question

This Query Works

SELECT distinct replace(”’0001,0002”’,',’,”’,”’) as assetnum
FROM asset

O/P := ‘0001′,’0002′

===========================================================================

This Query Works

SELECT distinct(assetnum)
FROM asset
WHERE assetnum in
(’0001′,’0002′)

O/P := 0001
0002
===========================================================================
But This Does Not Work

SELECT distinct(assetnum)
FROM asset
WHERE assetnum in
(SELECTdistinct replace(”’0001,0002”’,',’,”’,”’) as assetnum
FROM asset);

O/P
:= no rows selected

Can You Help Me

Thanks Again For Reply

Carlosdl   29820 pts.  |   Apr 17 2009  5:39PM GMT

You are comparing two different things here, although they look similar.

Let’s see if I find the way to explain this.

This query:

SELECT distinct replace(”’0001,0002”’,’,’,”’,”’) as assetnum
FROM asset

Returns this: ‘0001′,’0002′

This is just one value: “‘0001′,’0002′“. I mean, this is just one string, containing this text: ‘0001′,’0002′ (the quotes are part of the string).

When you run this query:

SELECT distinct(assetnum)
FROM asset
WHERE assetnum in
(SELECTdistinct replace(”’0001,0002”’,’,’,”’,”’) as assetnum
FROM asset);

you are looking for records with assetnum = “‘0001′,’0002′ ”

For this query:

SELECT distinct(assetnum)
FROM asset
WHERE assetnum in
(<sub_query>);

to produce the same results as this one:

SELECT distinct(assetnum)
FROM asset
WHERE assetnum in
(’0001′,’0002′)

The <sub_query> should be a query that returns two rows, one being 0001, and the other 0002 (as varchars, but without the quotes).

Why do you need that REPLACE ?

Feel free to ask if I did not make myself clear.

Regards,

Hanrahul   85 pts.  |   Apr 20 2009  7:59AM GMT

Actually I want to write a procedure which takes input parameter and and gives result,
But for that input parameter i want to supply multiple values so how can i do it

Carlosdl   29820 pts.  |   Apr 20 2009  4:32PM GMT

You will probably need to use Dynamic SQL to achieve that.

For example:

EXECUTE IMMEDIATE ‘SELECT distinct(assetnum) FROM asset WHERE assetnum in (’||your_parameter||’);’;

where your parameter should contain something like this:

’0001′,’0002′

Hanrahul   85 pts.  |   Apr 29 2009  8:42AM GMT

hi
I want to write a procedure which takes input parameter and and gives result,
But for that input parameter i want to supply multiple values so how can i do it

Procedure Is As Follows

CREATE OR REPLACE PROCEDURE sp_Select_LaborStatus
(v_status IN varchar2,
v_labor_Cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN v_labor_Cursor FOR
  SELECT labor.status, labor.laborcode, person.displayname, labor.worksite, labor.worklocation,
       laborqual.qualificationid, qualification.description, laborqual.status as status2, laborqual.enddate
  FROM labor left outer join laborqual on labor.laborcode = laborqual.laborcode
  AND  <a href="http://labor.org" title="http://labor. " target="_blank">labor.org</a>id = <a href="http://laborqual.org" title="http://laborqual. " target="_blank">laborqual.org</a>id, person, qualification
WHERE
labor.status  in v_status
   AND labor.personid = person.personid
   AND laborqual.qualificationid = qualification.qualificationid
   AND <a href="http://laborqual.org" title="http://laborqual. " target="_blank">laborqual.org</a>id = <a href="http://qualification.org" title="http://qualification. " target="_blank">qualification.org</a>id
ORDER BY labor.laborcode, laborqual.qualificationid ;
END;
/

and v_status has values (’ACTIVE’,'INACTVE’)
so how can i supply these values to the above procedure

Carlosdl   29820 pts.  |   Apr 29 2009  6:52PM GMT

Something like this:

CREATE OR REPLACE PROCEDURE sp_Select_LaborStatus
(v_status IN varchar2,
v_labor_Cursor OUT SYS_REFCURSOR)
AS
l_selectstatement varchar2(500);
BEGIN
l_selectstatement := ‘SELECT …
FROM …
WHERE …
AND labor.status in (’ || v_status || ‘)’ ||
AND …
ORDER BY labor.laborcode, laborqual.qualificationid’;
OPEN v_labor_Cursor FOR l_selectstatement;
END;

Hanrahul   85 pts.  |   Apr 30 2009  8:38AM GMT

Hi thanks for reply
but i have problem

Procedure is created but when i execute it ,gives error like this

BEGIN Rahul_LaborStatus1(:assetnum,:g_Cursor); END;

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at “MAXIMO_BAE.RAHUL_LABORSTATUS1″, line 7
ORA-06512: at line 1
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable “g_cursor”

Procedure i created as below

CREATE OR REPLACE PROCEDURE Rahul_LaborStatus1
(v_status IN varchar2,
v_labor_Cursor OUT SYS_REFCURSOR)
AS
l_selectstatement varchar2(500);
BEGIN
l_selectstatement := ‘SELECT labor.status, labor.laborcode, person.displayname, labor.worksite, labor.worklocation,
laborqual.qualificationid, qualification.description, laborqual.status as status2, laborqual.enddate
FROM labor left outer join laborqual on labor.laborcode = laborqual.laborcode
AND  <a href="http://labor.org" title="http://labor. " target="_blank">labor.org</a>id = <a href="http://laborqual.org" title="http://laborqual. " target="_blank">laborqual.org</a>id, person, qualification
WHERE
labor.status in (’ || v_status || ‘)
AND labor.personid = person.personid
AND laborqual.qualificationid = qualification.qualificationid
AND <a href="http://laborqual.org" title="http://laborqual. " target="_blank">laborqual.org</a>id = <a href="http://qualification.org" title="http://qualification. " target="_blank">qualification.org</a>id
ORDER BY labor.laborcode, laborqual.qualificationid ‘;
OPEN v_labor_Cursor FOR l_selectstatement;
END;
/

var v_status varchar2(500);
execute :v_status :=’ACTIVE,INACTIVE’;
var g_Cursor refcursor;
EXECUTE Rahul_LaborStatus1(:v_status,:g_Cursor);
print :g_Cursor;

Carlosdl   29820 pts.  |   Apr 30 2009  10:01PM GMT

Try increasing the size of the l_selectstatement variable.

For example:

l_selectstatement varchar2(2000);

Also, for the procedure to work without more changes, the v_status must be sent to the procedure this way:

execute :v_status :="’ACTIVE”,”INACTIVE”’;