| RefCursor%RowType and Row.VariableColumn [message #45788] |
Wed, 14 April 2004 20:30  |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
Hi Gurus,
I have an intersting question which I couldn't able to solve it... There are two thing... a) variable of refCur type b) to access a column of rowtype which is dynamic in nature (varaible)
The scenario is
Type refCur IS REF CURSOR;
myCur refCur;
myRow ????%ROWTYPE; -------- I want a rowtype of this cursor
BEGIN
sqlStmt:='select * from ' || v_tablename; ---- tablename is passed as a parameter to this procedure
OPEN myCur FOR sqlStmt;
LOOP
FETCH myCur into myRow; --- want this
EXIT WHEN myCur%notfound;
another_proc(myRow); ---calling another procedure and passing this entire cursor row.
END LOOP;
END;
now my question is to access the column in the second procedure i have to use varRow.Column1. But what is the way of acessing if we are getting this column dynamically. eg:
varColumn:='Column2';
dbms_output.put_line(varRow.varColumn); ---???????
How to solve this??? I have tries all the possible things but to no avail.... Please help me with this or can u suggest an alternative but the column is dynamic.
|
|
|
|
| Re: RefCursor%RowType and Row.VariableColumn [message #45807 is a reply to message #45788] |
Fri, 16 April 2004 23:25  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
scott@ORA92> CREATE OR REPLACE PROCEDURE another_proc
2 (p_sqlStmt IN VARCHAR2)
3 IS
4 myCur INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
5 varColumn VARCHAR2 (2000);
6 v_column_count NUMBER DEFAULT 0;
7 v_status INTEGER;
8 v_count NUMBER DEFAULT 0;
9 BEGIN
10 DBMS_SQL.PARSE (myCur, p_sqlStmt, DBMS_SQL.NATIVE);
11 FOR i IN 1 .. 255
12 LOOP
13 BEGIN
14 DBMS_SQL.DEFINE_COLUMN (myCur, i, varColumn, 2000);
15 v_column_count := i;
16 EXCEPTION
17 WHEN OTHERS THEN
18 IF (sqlcode = -1007)
19 THEN
20 EXIT;
21 ELSE
22 RAISE;
23 END IF;
24 END;
25 END LOOP;
26 DBMS_SQL.DEFINE_COLUMN (myCur, 1, varColumn, 2000);
27 v_status := DBMS_SQL.EXECUTE (myCur);
28 LOOP
29 EXIT WHEN (DBMS_SQL.FETCH_ROWS (myCur) <= 0);
30 FOR i IN 1 .. v_column_count
31 LOOP
32 DBMS_SQL.COLUMN_VALUE (myCur, i, varColumn);
33 DBMS_OUTPUT.PUT_LINE ('column ' || i || ': ' || varColumn);
34 END LOOP;
35 v_count := v_count + 1;
36 DBMS_OUTPUT.PUT_LINE ('------------------------------------');
37 END LOOP;
38 DBMS_SQL.CLOSE_CURSOR (myCur);
39 END another_proc;
40 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> CREATE OR REPLACE PROCEDURE some_proc
2 (v_tablename IN VARCHAR2)
3 AS
4 sqlStmt VARCHAR2(4000);
5 BEGIN
6 sqlStmt:='SELECT * FROM ' || v_tablename;
7 another_proc (sqlStmt);
8 END some_proc;
9 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXECUTE some_proc ('dept')
column 1: 10
column 2: ACCOUNTING
column 3: NEW YORK
------------------------------------
column 1: 20
column 2: RESEARCH
column 3: DALLAS
------------------------------------
column 1: 30
column 2: SALES
column 3: CHICAGO
------------------------------------
column 1: 40
column 2: OPERATIONS
column 3: BOSTON
------------------------------------
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE some_proc ('emp')
column 1: 7369
column 2: SMITH
column 3: CLERK
column 4: 7902
column 5: 17-DEC-80
column 6: 800
column 7:
column 8: 20
------------------------------------
column 1: 7499
column 2: ALLEN
column 3: SALESMAN
column 4: 7698
column 5: 20-FEB-81
column 6: 1600
column 7: 300
column 8: 30
------------------------------------
column 1: 7521
column 2: WARD
column 3: SALESMAN
column 4: 7698
column 5: 22-FEB-81
column 6: 1250
column 7: 500
column 8: 30
------------------------------------
column 1: 7566
column 2: JONES
column 3: MANAGER
column 4: 7839
column 5: 02-APR-81
column 6: 2975
column 7:
column 8: 20
------------------------------------
column 1: 7654
column 2: MARTIN
column 3: SALESMAN
column 4: 7698
column 5: 28-SEP-81
column 6: 1250
column 7: 1400
column 8: 30
------------------------------------
column 1: 7698
column 2: BLAKE
column 3: MANAGER
column 4: 7839
column 5: 01-MAY-81
column 6: 2850
column 7:
column 8: 30
------------------------------------
column 1: 7782
column 2: CLARK
column 3: MANAGER
column 4: 7839
column 5: 09-JUN-81
column 6: 2450
column 7:
column 8: 10
------------------------------------
column 1: 7788
column 2: SCOTT
column 3: ANALYST
column 4: 7566
column 5: 19-APR-87
column 6: 3000
column 7:
column 8: 20
------------------------------------
column 1: 7839
column 2: KING
column 3: PRESIDENT
column 4:
column 5: 17-NOV-81
column 6: 5000
column 7:
column 8: 10
------------------------------------
column 1: 7844
column 2: TURNER
column 3: SALESMAN
column 4: 7698
column 5: 08-SEP-81
column 6: 1500
column 7: 0
column 8: 30
------------------------------------
column 1: 7876
column 2: ADAMS
column 3: CLERK
column 4: 7788
column 5: 23-MAY-87
column 6: 1100
column 7:
column 8: 20
------------------------------------
column 1: 7900
column 2: JAMES
column 3: CLERK
column 4: 7698
column 5: 03-DEC-81
column 6: 950
column 7:
column 8: 30
------------------------------------
column 1: 7902
column 2: FORD
column 3: ANALYST
column 4: 7566
column 5: 03-DEC-81
column 6: 3000
column 7:
column 8: 20
------------------------------------
column 1: 7934
column 2: MILLER
column 3: CLERK
column 4: 7782
column 5: 23-JAN-82
column 6: 1300
column 7:
column 8: 10
------------------------------------
PL/SQL procedure successfully completed.
|
|
|
|
| Re: RefCursor%RowType and Row.VariableColumn [message #45827 is a reply to message #45807] |
Mon, 19 April 2004 22:53 |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
thanks for the solution... it was able to solve my first issue regding using dynamic cursors. but i was not able to solve the second query of how to use a variable in a rowtype variable to access the data.
I will give another example, i get one row of any table type in one of the procedure where i want to access one of the field but the field to be accessed is dynamic i.e. it comes from some variable
procedure(myRow EMP%rowtype)
is
varSrcCol varchar2(100):='name';
varDesCol varchar2(100);
begin
// varDesCol:=myRow.name; I want this to run so i want a statment like something
varDesCol := valueOf('myRow.' || varSrcCol); --????how to frame this in oracle
i tried doing something which i was not sure about:
dbms_sql.parse(c, 'select myRow.' || varSrcCol || ' from dual', dbms_sql.native);
dbms_sql.execute(c);
the select statment framed as
select myrow.name into varDesCol from dual;
works fine so i thought it can be used in dbms_sql.
please guide as i am really woried how to finish my task before this weekend.
thanks in advance
|
|
|
|
|
|
| Re: RefCursor%RowType and Row.VariableColumn [message #45902 is a reply to message #45889] |
Sun, 25 April 2004 20:30 |
sri
Messages: 154
Registered: February 2000
|
Senior Member |
|
|
Hi Barbara,
Thanks a lot for your reply and I have solved the thing by going through your solution itself... Its was a bit lengthy code but it solved my purpose of identifing the column on runtime and to do my processing. But the thing I want should be provided by oracle as few scripting languages provide "Eval" ;)
Thanks once again!!!
Warm Regards,
Sri
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
