Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
Posted by: Edi Yanto on: April 14, 2007
Some programs must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements. Formerly, to execute dynamic SQL statements, you had to use the supplied package DBMS_SQL.
Now, within PL/SQL, you can execute any kind of dynamic SQL statement using an interface called native dynamic SQL.
The main PL/SQL features involved are the EXECUTE IMMEDIATE statement and cursor variables (also known as REF CURSOR).
Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package. The following example declares a cursor variable, then associates it with a dynamic SELECT statement:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
v_name employees.last_name%TYPE;
v_sal employees.salary%TYPE;
my_sal NUMBER := 2000;
table_name VARCHAR2(30) := ‘employees’;
BEGIN
OPEN emp_cv FOR ‘SELECT last_name, salary FROM ‘ || table_name ||
‘ WHERE salary > ‘ || my_sal;
LOOP
FETCH emp_cv into v_name, v_sal;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ‘ ‘ || v_sal);
END LOOP;
CLOSE emp_cv;
END;
/
Consider a different type of PL/SQL application where SQL statements are built on the fly, based on a set of parameters specified at run-time. For example, an application might need to build various reports based on SQL statements where table and column names are not known in advance, or sorting and grouping of data is specified by a user requesting a report. Similarly, another application might need to create or drop tables or other database objects based on the action specified by a user at run-time. Because these SQL statements are generated on the fly and might change from time to time, so we need dynamic sql.
The use of dynamic SQL makes such applications flexible, versatile, and concise because it eliminates the need for complicated programming approaches.
6 Responses to "Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables"
December 3, 2008 at 7:28 pm
@Edi Yanto: Useful – but clearly avoiding the real issue:
This example, though using IMMEDIATE, doesn’t solve anything, as the table name isn’t really variable, but known indirectly at compile type via the variable ‘table_name’.
The %rowtype is bound to table_name, which is (or should be), by problem defintion, unknown. We cannot use the %rowtype on a REF CURSOR, leaving us with the problem unsolved, as
“select * from table_name”
will not be parsable for any “table_name”s as we cannot resolve ‘*’ into any known %rowtype.
Feuerstein has a discussion of “Dynamic PL/SQL” in his “Oracle PL/SQL Programming”. As far as I can see, this (very tedious) method is the only way of solving the problem in a generic way.
If somebody has a better idea, please mail me!
BR
December 3, 2008 at 9:38 pm
Hi Dan, thanks for your comments….
Maybe you can solve your above problems with this REF CURSOR syntax example, hopefully be useful for you..
DECLARE
cur_ar VARCHAR2 (32767)
:= ‘SELECT *
FROM vat.mpp_vat_ar_trx_int_v
WHERE segment8 BETWEEN ”’
|| condition_start_date
|| ”’ AND ”’
|| condition_end_date
|| ””;
TYPE cur_type IS REF CURSOR;
CV cur_type;
cur_temp1 vat.mpp_vat_ar_trx_int_v%ROWTYPE;
BEGIN
IF condition_source IS NOT NULL
THEN
cur_ar := cur_ar || ‘ AND org_id = ‘ || condition_source;
END IF;
OPEN CV FOR cur_ar;
LOOP
FETCH CV
INTO cur_temp1;
EXIT WHEN CV%NOTFOUND;
INSERT INTO vat.vat_ar_transaction_int
VALUES (vdata_import_code, cur_temp1.transaction_number,
cur_temp1.npwp, cur_temp1.customer_number,
cur_temp1.org_id, ‘A’, ‘2′, ‘1′, ‘1′, ‘000′,
cur_temp1.segment6, cur_temp1.segment7,
cur_temp1.segment8, cur_temp1.segment9,
cur_temp1.segment10, cur_temp1.segment11,
cur_temp1.segment12, 0, 0, vcompany, user_id,
cur_temp1.segment17, cur_temp1.segment18, cur_temp1.segment19,
cur_temp1.segment20, cur_temp1.segment21,
cur_temp1.segment22, cur_temp1.segment23,
cur_temp1.segment24, cur_temp1.segment25,
cur_temp1.segment26, cur_temp1.segment27,
cur_temp1.segment28, cur_temp1.segment29,
cur_temp1.segment30, cur_temp1.segment31,
cur_temp1.segment32, cur_temp1.segment33,
cur_temp1.segment34, cur_temp1.segment35,
cur_temp1.segment36, cur_temp1.segment37,
cur_temp1.segment38, cur_temp1.segment39,
cur_temp1.segment40, cur_temp1.segment41,
cur_temp1.segment42, cur_temp1.segment43,
cur_temp1.segment44, cur_temp1.segment45,
cur_temp1.segment46, cur_temp1.segment47,
cur_temp1.segment48, cur_temp1.segment49,
cur_temp1.segment50, vdate, vuser, vdate, vuser, NULL,
‘N’);
END LOOP;
CLOSE CV;
END;
September 28, 2009 at 3:49 pm
@edi…
i think what dan means is its not useful to use IMMEDIATE in your sample because its already know thaat table you going to select is “employee”. know not because you put it into variable but because you using
v_name employees.last_name%TYPE;
v_sal employees.salary%TYPE;
i think it would be a lot more better if you change this into
v_name varchar2(255);
v_sal number;
that make your code is not dependent to any table until run time.
September 28, 2009 at 8:47 pm
@boy, do you know if u change this code to v_name varchar2(255), you’ll recode it, if any change to the length of the last_name column in employees table, otherwise would cause the procedure/function invalid. Try to think if this variable is define in many places, will be a lot of changes you have to do.
Leave a Reply
RSS Syndication
Edi Yanto
Oracle Applications Technical Consultant
My Certifications
Recent Comments
- The XML Publisher Concurrent Request Fails Due to the Output Post Processing (OPP)
- Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
- Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
- ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
- Securing PDF Output on XML Publisher
- Recompile Invalid Objects using DBMS_DDL or Custom Script Using EXECUTE IMMEDIATE
- APP-AR-96282: Unique Constraint AR.HZ_CUST_ACCT_SITES_U2 Violated
- Matching Invoices in Account Payables (AP)
- How to open Excel Spreadsheets from Oracle Applications Concurrent Request
- The Spirit of A Spider (Andrie Wongso)
- Edi Yanto: Hi Ang, You can find the solution for this problem at http://www.ediyanto.co.cc/unable-to-view-excel-output-xml-publisher-from-concurrent-program-req
- ang: xml view output to excel ORA-01403: no data found -------------------------------------------------------------- FRM-40735: WHEN-BUTTON-PRESSED
- Edi Yanto: Hi Ricky, Have you try to set the security setting (Enable Automatic prompting for file download) for your internet/local/trusted sites zone at inter
Edi Yanto (何万新)
- Oracle Top-N Query October 2, 2009Using RANK function, with a faster execution plan (WINDOW SORT PUSHED RANK) SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, RANK() OVER (ORDER BY SAL Desc) AS Emp_Rank FROM Emp) WHERE Emp_RankEdi Yanto
- Bersyukurlah… June 30, 2009AKU TAK SELALU MENDAPATKAN APA YANG KUSUKAI oleh karena itu AKU SELALU MENYUKAI APAPUN YANG AKU DAPATKAN. Kata-kata di atas merupakan wujud syukur. Syukur merupakan kualitas hati yang terpenting. Dengan bersyukur kita akan senantiasa diliputi rasa damai, tentram dan bahagia. Sebaliknya, perasaan tak bersyukur akan senantiasa membebani kita. Kita akan selalu […]Edi Yanto
- Cinta Sejati June 30, 2009Kenapa kita menutup mata ketika kita tidur ? Kenapa kita menutup mata ketika kita menangis ? Kenapa kita menutup mata ketika kita membayangkan sesuatu ? Kenapa kita menutup mata ketika kita berciuman ? Hal hal yang terindah di dunia ini biasanya tidak terlihat Ada hal hal yang tidak ingin kita lepaskan dan ada orang orang yang tidak ingin kita tinggalkan Tap […]Edi Yanto
- Nice Story June 8, 2009Share on FacebookEdi Yanto
- 5 Tips Agar Tak Mudah Lelah June 2, 2009Sebagian orang mengeluhkan kondisi tubuhnya yang cepat capai atau lelah. Kebanyakan orang menilai hal itu diakibatkan kerja ekstra. Tetapi, sebenarnya hal itu dilatarbelakangi kebiasaan seseorang sehari-hari. Berikut lima tips yang dikutip VIVAnews dari WebMD, Sabtu 25 April 2009 agar kondisi tubuh Anda tidak mudah capai atau lelah. 1. Lapar atau tidak, sara […]Edi Yanto
- Belajar dari Alphabet May 29, 2009A : AcceptTerimalah diri anda sebagaimana adanya. B : BelievePercayalah terhadap kemampuan anda untuk meraih apa yang anda inginkan dalam hidup. C : CarePedulilah pada kemampuan anda meraih apa yang anda inginkan dalam hidup. D : DirectArahkan pikiran pada hal-hal positif yang meningkatkan kepercayaan diri. E : EarnTerimalah penghargaan yang diberi orang lai […]Edi Yanto
- How To Get the List of All Responsibilities Assigned To A User May 29, 2009 Edi Yanto
- Query To Find Multi-Org Is Enabled Or Not May 29, 2009SELECT DECODE (multi_org_flag, ‘Y’, ‘Multi-Org Enabled’, ‘Multi-Org Disabled’ ) FROM fnd_product_groups; Share on FacebookEdi Yanto
- How To Find A Patch Is Applied For A Bug May 29, 2009 Edi Yanto
- Excel Limitations on Images in Oracle Apps May 23, 2009The Excel output can’t showing the image (even after you save and send to another person or open it at different notebook), but it is showing only small red color empty cross written ‘An Image’. Why??? As many of you know or have noticed, our Excel output for all flavors of BIP/XMLP is not true binary [...]Edi Yanto
Blogs.oracle.com Recent Posts (all languages)
- 週末はちょっとおしゃれな外苑前でスキルアップ 2/2 - ストレージ管理&チューニング -
- 週末はちょっとおしゃれな外苑前でスキルアップ 1/2 - アプリ・テスト -
- Moving of internal blog entries here
- Office 2007 files in SES 10.1.8.4
- Parallel Processing in ODI
- You're Invited: OPN Specialized Launch
- Уровни партнерства в новой программе и ключевые преимущества
- A Time/Hour Selector in ADF Faces - Did you know?
- Around the World
- EBS 12 Certified with 10gR2 10.2.0.4 DB on Windows Server x64 (64-bit)
Online Apps DBA
- Autoconfig in Oracle Apps R12 adautocfg.sh
- Oracle Service Bus 10.3.1 Installation
- Unable to call fnd_ldap_wrapper . create_user / update_user ORA-20001
- Custom Schemas are not analyzed when Gather Schema Statistics are submitted.
- How to create FMW 11g schemas (SOA, WebCenter) on Solaris (non Windows/Linux) platform ?
Visitors
Users Online
May 17, 2007 at 9:53 pm
really nice programming
thanx a lot