sunil -- Thanks for the question regarding "Unable to store dynamic select into cursor", version 9.0.5

You Asked

Dear Tom,
I am writing a SP which is using two table as

CREATE TABLE TEST2
(
AGE NUMBER,
NAME VARCHAR2(60 BYTE),
EMPID VARCHAR2(60 BYTE)
)

INSERT INTO test2 VALUES (25,'sunil','7940');



CREATE TABLE INPUT_PARAM
(
COLUMNNAME VARCHAR2(100 BYTE)
)

INSERT INTO input_param VALUES ('AGE');


--------------------------------------------------------------

Stored Procedure
--------------------------------------------------------------


CREATE OR REPLACE PROCEDURE Cal_Score_Test_tom

IS




selectstr VARCHAR2(4000);
LENGTH NUMBER ;
selectstrChanged VARCHAR2(4000);
vTable VARCHAR2(30) := 'input_param';
vCursor sys_refcursor;

CURSOR C2 IS
SELECT COLUMNNAME cname FROM input_param;

TYPE tDept IS TABLE OF input_param%ROWTYPE;
vDept tDept;

BEGIN

--selectstr:='Select ';
FOR J IN c2 LOOP
IF c2%ROWCOUNT != 0 THEN

selectstr:=selectstr||J.cname||',' ;
ELSE
selectstr:=selectstr||J.cname ;
END IF;
END LOOP;

SELECT LENGTH(selectstr) INTO LENGTH FROM dual;
selectstrChanged:=SUBSTR(selectstr,0, (LENGTH-1));
selectstrChanged:=selectstrChanged|| ' FROM test2 ';


OPEN vCursor FOR ('select ' || selectstrChanged);
FETCH vCursor BULK COLLECT INTO vDept;
CLOSE vCursor;

IF vDept.COUNT > 0
THEN
DBMS_OUTPUT.PUT_LINE('Elements in ---> ' || TO_CHAR(vDept.COUNT));

FOR n IN vDept.FIRST..vDept.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Elements in --->test1 ');

END LOOP;

END IF;

END;
/

---------------------------------------------------------------
-------------------------------------------------------------
and after excution i get the following output

SQL> set serveroutput on
SQL> BEGIN
2 FINLOS_PHP.CAL_SCORE_TEST_TOM;
3 COMMIT;
4 END;
5 /
Elements in ---> 1
Elements in --->test1


which is wrong .I am unable to retrieve all the values from the cursor.
IS there anyway to do so ??
Please help.

Regards
Sunil


Dear Tom,

I am trying to make a select query which will selecting the the column on the basis of the values present in the table for example.
If i am putting age in input_param table then my select statement will be

select age from test2;

now suppose after some time i need to select two parameters like age,empid like

select age,empid from test2;

then i will be able to do by putting just one entry into input_param (empid).

Utimately i want to some manipulations on the values returned by my final select ...in this case ..

select age,empid from test2;

As for i know the the best way will be if i can get this select into a cursor ...but i am unable to since i dont know datatype of the variable variable vDept and most probably this is causing wrong output

FETCH vCursor BULK COLLECT INTO vDept;


Hope this clearifies my requiremet and

thanks for your useful suggestions ....

and we said...

You cannot process a variable select list like that in PLSQL using a refcursor.

You can only use DBMS_SQL. It provides a procedural API to

a) describe a result set, detailing the number and datatypes of the retrieved columns
b) procedures to access the I'th column of a result set

That said, I will advise you to ABANDON this approach. Use STATIC SQL, don't be afraid to have 4 or 5 procedures - instead of one "super generic one"

If you are using dynamic sql at the drop of a hat - you are doing it wrong. dynamic SQL should be the path of LAST RESORT - when no other way works.

if you persist down this path, search this site for dbms_sql, hundreds of examples.
-------------------------------------------------------------------------------


I cannot being to say how bad it would be to have a variable (eg: LENGTH) named after a function (eg: LENGTH). Get into the habit of prefixing local variables with L_ and parameters with P_ and globals in packages with G_

... SELECT LENGTH(selectstr) INTO LENGTH FROM dual;...

why do that when:

l_length := length(selectstr);

would do it more efficiently? PLSQL is a programming language, this is not TSQL :)


why is your output wrong? there is, well, 1 row in test2 - and it is saying "I found one row, thanks" and then saying 'I am positioned at the first array element, but I decided to not print it out'

I'm at a complete loss as to what you are trying to do...

so maybe instead of posting code that doesn't work - you tell us what you are trying to do.

Reviews
January 10, 2008 - 11pm Central time zone Bookmark | Bottom | Top Reviewer: sunil vishwakarma  Thanks for giving a clear approach ...

---

Write a Review