Posts Tagged ‘query’
Row Numbering With an Order By Clause
Posted by: Edi Yanto on: March 7, 2009
One of the most often uses of the pseudo column rownum is to provide sequence numbers to the records in a query. This feature is widely used in reports to represent systematic display of information.
SELECT ROWNUM, rcta.trx_number, rcta.trx_date
FROM ra_customer_trx_all rcta;
ROWNUM TRX_NUMBER TRX_DATE
1 40881-05 12/14/2005
2 38384-05 11/24/2005
3 42740-05 12/28/2005
4 42742-05 12/28/2005
5 08883-07 3/12/2007
…………………………………..
…………………………………..
However, when we [...]
Recompile Invalid Objects using DBMS_DDL or Custom Script Using EXECUTE IMMEDIATE
Posted by: Edi Yanto on: January 7, 2009
Operations such as upgrades, patches and DDL changes can invalidate schema objects. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base.
In order to compile a program, you must own that program (in other words, the [...]
Spell Number
Posted by: Edi Yanto on: October 28, 2008
CREATE OR REPLACE PACKAGE spell_number_pkg AS
/*
Spell Number in Indonesia and English
*/
AMOUNT_EXCEEDS_LIMIT EXCEPTION;
function to_words(
p_amount number
) return varchar2;
function spell_number(
p_number in number
) return varchar2;
END spell_number_pkg;
CREATE OR REPLACE PACKAGE BODY spell_number_pkg
IS
/*
Spell Number in Indonesia and English
*/
–*******************************************************************
–* PRIVATE MEMBERS
TYPE varchar2_tbl_t IS TABLE OF VARCHAR2 (30);
FUNCTION to_hundredth_words (p_amount NUMBER)
RETURN VARCHAR2
IS
l_translate_groups varchar2_tbl_t
:= varchar2_tbl_t (‘SATU’,
‘DUA’,
‘TIGA’,
‘EMPAT’,
‘LIMA’,
‘ENAM’,
‘TUJUH’,
‘DELAPAN’,
‘SEMBILAN’
);
l_tenth_groups varchar2_tbl_t
:= varchar2_tbl_t (”, ‘PULUH’, ‘RATUS’);
l_char VARCHAR2 (3);
l_length NUMBER;
l_number [...]
Use a FOR UPDATE Cursor
Posted by: Edi Yanto on: December 18, 2007
The cursor FOR UPDATE clause is only used with a cursor when you want to update tables in the database. Generally, when you execute a SELECT statement, you are not locking any rows. The purpose of using the FOR UPDATE clause is to lock the rows of the tables that you want [...]
Reducing Loop Overhead for DML Statements and Queries (FORALL,BULK COLLECT)
Posted by: Edi Yanto on: May 31, 2007
PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the result data to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL language features known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE [...]
Dynamic SQL with RETURNING BULK COLLECT INTO Clause
Posted by: Edi Yanto on: May 25, 2007
Only INSERT, UPDATE, and DELETE statements can have output bind variables. You bulk-bind them with the RETURNING BULK COLLECT INTO clause of EXECUTE IMMEDIATE. With this clause, you can see which data have been inserted, updated or deleted.
Dynamic SQL with BULK COLLECT INTO Clause
Posted by: Edi Yanto on: May 25, 2007
Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of aloop that issues a SQL statement in every iteration.
Bulk binding lets Oracle bind a variable in a SQL [...]
Assigning Nested Tables with Set Operators
Posted by: Edi Yanto on: May 16, 2007
The SQL language has long offered the ability to apply set operations (UNION, INTERSECT, and MINUS) to the result sets of queries. In Oracle Database 10g, you can now use those same high-level, very powerful operators against nested tables (and only nested tables) in your PL/SQL programs and on nested tables declared as columns inside [...]
Possible Exceptions for Collection Assignments
Posted by: Edi Yanto on: May 15, 2007
Assigning a value to a collection element can cause various exceptions:
If the subscript is null or is not convertible to the right datatype, PL/SQL raises the predefined exception VALUE_ERROR. Usually, the subscript must be an integer. Associative arrays can also be declared to have VARCHAR2 subscripts.
If the subscript refers to an uninitialized element, PL/SQL [...]
Exit Early from Nested FOR Loop
Posted by: Edi Yanto on: May 14, 2007
If you want to exit from any enclosing loop, but not just the current loop, you can label the enclosing loop and use the label in an EXIT statement.
<<outer>>
FOR i IN 1..5 LOOP
. . . . .
FOR j IN 1..10 LOOP
. . . . .
EXIT outer WHEN . . . .
. . . . .
END [...]
RSS Syndication
Edi Yanto
Oracle Applications Technical Consultant
My Certifications
- 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)
- Having SQL queries from different databases in one data template...
- Tovább vezeti az adattárházak piacát az Oracle: 40 %
- Főkedvenc BI és DW blogjaim 4: Kevin Closson blogja
- Intrinsic interoperability - what it is, and what it's not ..
- Common pitfalls during repository install
- Around the World
- 正しく伝えたい。次期Oracle Etnerprise Manager開発中画面
- Service Component Architecture (SCA) com SOA Suite 11g
- New ADF Faces contextInfo Component
- Source for PS1 Dynamic Tab Shell
Visitors
Users Online
Recent Comments