| DYNAMIC Record? [message #357107] |
Mon, 03 November 2008 23:44  |
wmgonzalbo
Messages: 98
Registered: November 2008
|
Member |
|
|
Hi experts, i'm Wilbert, just found this site and its great with lots of interesting and helpful topics..
* Anyway, i just want to raise my concerns in PL/SQL.. I am using Oracle Database 10g Express Edition and creating a table with the following sample fields..
/**********/
TABLE: AUTO_PROCESS
SAMPLE FIELDS:
.
.
PROCEDURE_NAME_HERE
.
SOURCE_TABLE (VARCHAR2 500 BYTES) --> entered string will be put into the variable, -- v_srctable -- contains an existing table.
.
PARAMETERS -->(VARCHAR2 500 BYTES) --> entered string will be put into the variable, -- v_parameters -- contains statements like 'empid, empname, empjob'
.
EXPRESSION --> entered string will be put into the variable, -- v_expression -- which contains statements like 'where account_type = 1'
.
.
LAST_RUN_DATE
.
.
/**********/
* In my procedure i have the following code..
EXECUTE IMMEDIATE 'Select '||v_parameters||' from '||v_srctable||' '||v_expression BULK COLLECT INTO vrej_rec_array
/**********/
My prob is with the code 'BULK COLLECT INTO vrej_rec_array
', how can i make the 'vrej_rec_array' dynamic, depending on my select parameters from v_paramaters variable?
Normally i do it this way in the DECLARE block before the EXECUTE IMMEDIATE in the BEGIN block...
TYPE vrej_rec IS RECORD (
v_something1 VARCHAR2 (50 BYTE),
v_something2 VARCHAR2 (50 BYTE),
v_something3 VARCHAR2 (50 BYTE),
.
.
.
v_somethingn VARCHAR2 (50 BYTE));
TYPE vrej_rec_table IS TABLE OF vrej_rec;
vrej_rec_array vrej_rec_table;
* but since my select parameters from v_parameters should be dynamic / variable, HOW WILL I DO THAT?
* Comments, suggestions? Any help will do...
Thanks in advanced,
Wilbert
|
|
|
|
|
|
|
|
| Re: DYNAMIC Record? [message #357317 is a reply to message #357311] |
Tue, 04 November 2008 19:56  |
wmgonzalbo
Messages: 98
Registered: November 2008
|
Member |
|
|
-- Why does this code doesn't work in oracle 10g? Please see comment below..
Example 8-12 Using Native Dynamic SQL to Fetch into Records
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record emp%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job emp.job%TYPE;
BEGIN
v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
-- in a multi-row query
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
LOOP
/******* THIS CODE *******/
FETCH v_emp_cursor INTO emp_record;
--ORA-01007: variable not in select list
-- This must be FETCH v_emp_cursor INTO emp_record.ColumnA, emp_record.ColumnB, emp_record.ColumnC etc.
-- Any work around to solve this prob?
/*************************/
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
CLOSE v_emp_cursor;
-- in a single-row query
EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
END;
/
-- Please Help.. Any suggestions or comments will do..
Thanks in advance,
Wilbert
|
|
|
|
| Re: DYNAMIC Record? [message #357330 is a reply to message #357317] |
Tue, 04 November 2008 23:00 |
 |
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Who say it does not work?
SQL> DECLARE
2 TYPE EmpCurTyp IS REF CURSOR;
3 v_emp_cursor EmpCurTyp;
4 emp_record emp%ROWTYPE;
5 v_stmt_str VARCHAR2(200);
6 v_e_job emp.job%TYPE;
7 BEGIN
8 v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
9 -- in a multi-row query
10 OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
11 LOOP
12 FETCH v_emp_cursor INTO emp_record;
13 EXIT WHEN v_emp_cursor%NOTFOUND;
14 END LOOP;
15 CLOSE v_emp_cursor;
16 -- in a single-row query
17 EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
18 END;
19 /
PL/SQL procedure successfully completed.
SQL> @v
Version Oracle : 10.2.0.4.0
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Use SQL*Plus and copy and paste your session.
Regards
Michel
[Updated on: Tue, 04 November 2008 23:02] Report message to a moderator |
|
|
|
|
|
| Re: DYNAMIC Record? [message #357635 is a reply to message #357330] |
Thu, 06 November 2008 01:57 |
wmgonzalbo
Messages: 98
Registered: November 2008
|
Member |
|
|
| Michel Cadot wrote on Tue, 04 November 2008 23:00 | Who say it does not work?
SQL> DECLARE
2 TYPE EmpCurTyp IS REF CURSOR;
3 v_emp_cursor EmpCurTyp;
4 emp_record emp%ROWTYPE;
5 v_stmt_str VARCHAR2(200);
6 v_e_job emp.job%TYPE;
7 BEGIN
8 v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
9 -- in a multi-row query
10 OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
11 LOOP
12 FETCH v_emp_cursor INTO emp_record;
13 EXIT WHEN v_emp_cursor%NOTFOUND;
14 END LOOP;
15 CLOSE v_emp_cursor;
16 -- in a single-row query
17 EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
18 END;
19 /
PL/SQL procedure successfully completed.
SQL> @v
Version Oracle : 10.2.0.4.0
Regards
Michel
|
-- My bad, the code above works.. but how about if i changed it to..
/*****/
DECLARE
emp_record emp%ROWTYPE;
.
.
.
BEGIN
v_stmt_str := 'SELECT emdpid, empname, empjob FROM emp WHERE job = :1';
/*****/
-- then fetching using.. 'FETCH v_emp_cursor INTO emp_record' wont work anymore i have to fetch it using..
FETCH v_emp_cursor INTO emp_record.empid, emp_record.empname, emp_record.empjob;
-- to bad the code 'FETCH v_emp_cursor INTO emp_record' in that case wont work or is there a way for that?
Thanks,
Wilbert
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
of course not..