Home » SQL & PL/SQL » SQL & PL/SQL » DYNAMIC Record?
DYNAMIC Record? [message #357107] Mon, 03 November 2008 23:44 Go to next message
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 #357114 is a reply to message #357107] Tue, 04 November 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 8 Coding Dynamic SQL

In the end, have a deep and conscientious look at last section: "Avoiding SQL Injection in PL/SQL".

Regards
Michel

Re: DYNAMIC Record? [message #357311 is a reply to message #357114] Tue, 04 November 2008 18:16 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
thanks for the link Michel C.!
Re: DYNAMIC Record? [message #357317 is a reply to message #357311] Tue, 04 November 2008 19:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 #357338 is a reply to message #357330] Wed, 05 November 2008 00:13 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hmm.. now that's wierd..
Probably coz im using TOAD v 8.6.0.38?

Thanks for the tips. Will look into that.

Thanks,
Wilbert

[Updated on: Wed, 05 November 2008 00:19]

Report message to a moderator

Re: DYNAMIC Record? [message #357635 is a reply to message #357330] Thu, 06 November 2008 01:57 Go to previous messageGo to next message
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


Re: DYNAMIC Record? [message #357637 is a reply to message #357107] Thu, 06 November 2008 02:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you trying to avoid Ref cursor ? Why are you afraid of it ?

Smile
Rajuvan.
Re: DYNAMIC Record? [message #357647 is a reply to message #357637] Thu, 06 November 2008 02:33 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
LOL Laughing of course not..

Its just that I should process/make a 'dynamic' or varying SELECT parameters..

Its all because I cant seem to make a dynamic RECORD or OBJECT..

EXECUTE IMMEDIATE 'Select '||[varying parameter/s]||' from '||[varying table]||' '||[varying where condition] BULK COLLECT INTO vrej_rec_array

If i can make 'vrej_rec_array' dynamic/varying depending on my SELECT parameters, then that would so so so lovely... Sad

Regards,
Wilbert
Re: DYNAMIC Record? [message #357649 is a reply to message #357647] Thu, 06 November 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Execute immediate is then the wrong tool in this, you have to use dbms_sql.

Regards
Michel
Re: DYNAMIC Record? [message #357682 is a reply to message #357107] Thu, 06 November 2008 04:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
If i can make 'vrej_rec_array' dynamic/varying depending on my SELECT parameters, then that would so so so lovely


I think OP is looking for the Rowtype variable based on Cursor if I am not wrong .

Smile
Rajuvan

Re: DYNAMIC Record? [message #357684 is a reply to message #357107] Thu, 06 November 2008 04:35 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Link was wrong .

This is the correct link .

Smile
Rajuvan
Previous Topic: Question on EXEC procedures stored in variables
Next Topic: Simple Query that involve prefix
Goto Forum:
  


Current Time: Sat Dec 03 13:52:41 CST 2016

Total time taken to generate the page: 0.05450 seconds