<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[PL/SQL Tricks - Medium]]></title>
        <description><![CDATA[Practical tips and lesser-known techniques in PL/SQL to make coding more efficient. Quick examples and insights for cleaner, smarter SQL and better data handling. - Medium]]></description>
        <link>https://medium.com/pl-sql-basics?source=rss----54fac6ef0ef2---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>PL/SQL Tricks - Medium</title>
            <link>https://medium.com/pl-sql-basics?source=rss----54fac6ef0ef2---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 19:45:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/pl-sql-basics" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Left Trim(Leading Spaces), Right Trim(Trailing Spaces) & Removing Extra Middle Spaces]]></title>
            <link>https://medium.com/pl-sql-basics/left-trim-leading-spaces-right-trim-trailing-spaces-removing-extra-middle-spaces-26ec9d550b51?source=rss----54fac6ef0ef2---4</link>
            <guid isPermaLink="false">https://medium.com/p/26ec9d550b51</guid>
            <category><![CDATA[ltrim]]></category>
            <category><![CDATA[siebel-configuration]]></category>
            <category><![CDATA[rtrim]]></category>
            <category><![CDATA[replace]]></category>
            <dc:creator><![CDATA[Serhan Eroglu]]></dc:creator>
            <pubDate>Fri, 05 Oct 2012 07:42:52 GMT</pubDate>
            <atom:updated>2018-08-06T13:06:27.054Z</atom:updated>
            <content:encoded><![CDATA[<p>Function for Left Trim;</p><blockquote>function LTrimSpaces (OrgStr)</blockquote><blockquote>{</blockquote><blockquote>var Pattern = /^\s*/; //Regular Expression</blockquote><blockquote>var TrimStr = OrgStr.replace(Pattern,””);</blockquote><blockquote>return(TrimStr);</blockquote><blockquote>}</blockquote><p>Function for Right Trim;</p><blockquote>function RTrimSpaces (OrgStr)</blockquote><blockquote>{</blockquote><blockquote>var Pattern = /\s*$/;</blockquote><blockquote>var TrimStr = OrgStr.replace(Pattern,””);</blockquote><blockquote>return(TrimStr);</blockquote><blockquote>}</blockquote><p>Example for removing extra middle spaces and splitting the array</p><blockquote>var keyword = “Siebel Facts”;</blockquote><blockquote>var c = ‘ ‘;</blockquote><blockquote>var pointer1 = keyword.indexOf(c);</blockquote><blockquote>var pointer2 = keyword.lastIndexOf(c);</blockquote><blockquote>var keyword _length = keyword.length;</blockquote><blockquote>var first_part = keyword.substring(0,pointer1); //-&gt;“Siebel”</blockquote><blockquote>var last_part = keyword.substring(pointer2+1,keyword_length); //-&gt;“Facts”</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=26ec9d550b51" width="1" height="1" alt=""><hr><p><a href="https://medium.com/pl-sql-basics/left-trim-leading-spaces-right-trim-trailing-spaces-removing-extra-middle-spaces-26ec9d550b51">Left Trim(Leading Spaces), Right Trim(Trailing Spaces) &amp; Removing Extra Middle Spaces</a> was originally published in <a href="https://medium.com/pl-sql-basics">PL/SQL Tricks</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Lag() function]]></title>
            <link>https://medium.com/pl-sql-basics/lag-function-6573018073e4?source=rss----54fac6ef0ef2---4</link>
            <guid isPermaLink="false">https://medium.com/p/6573018073e4</guid>
            <category><![CDATA[analytical-function]]></category>
            <category><![CDATA[lag]]></category>
            <dc:creator><![CDATA[Serhan Eroglu]]></dc:creator>
            <pubDate>Tue, 24 Feb 2009 08:03:47 GMT</pubDate>
            <atom:updated>2018-08-06T13:06:18.061Z</atom:updated>
            <content:encoded><![CDATA[<p>If we had an <em>orders</em> table that contained the following data:</p><blockquote>ORDER_DATEPRODUCT_IDQTY25/09/200710002026/09/200720001527/09/20071000828/09/200720001229/09/20072000230/09/200710004</blockquote><p>And we ran the following SQL statement:</p><blockquote>select product_id, order_date, <br>lag (order_date,1) over (ORDER BY order_date) AS prev_order_date<br>from orders;</blockquote><p>and the result is…</p><blockquote>PRODUCT_IDORDER_DATEPREV_ORDER_DATE100025/09/2007&lt;NULL&gt;200026/09/200725/09/2007100027/09/200726/09/2007200028/09/200727/09/2007200029/09/200728/09/2007100030/09/200729/09/2007</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6573018073e4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/pl-sql-basics/lag-function-6573018073e4">Lag() function</a> was originally published in <a href="https://medium.com/pl-sql-basics">PL/SQL Tricks</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Difference betweeen rank and dense_rank]]></title>
            <link>https://medium.com/pl-sql-basics/difference-betweeen-rank-and-dense-rank-6d43a538edfd?source=rss----54fac6ef0ef2---4</link>
            <guid isPermaLink="false">https://medium.com/p/6d43a538edfd</guid>
            <category><![CDATA[rank]]></category>
            <dc:creator><![CDATA[Serhan Eroglu]]></dc:creator>
            <pubDate>Thu, 19 Feb 2009 21:10:55 GMT</pubDate>
            <atom:updated>2018-08-06T13:06:23.097Z</atom:updated>
            <content:encoded><![CDATA[<p>Partition is made over dept_no and ordered by salary for both rank and dense_rank. They both make the same job except the numeric ranking. The same number is assigned for records with the same order value in both of them. At dense_rank, the numeric assignment is made sequentially for next row. But in rank, it is not incremented sequentially.</p><p>If we need a list of employees in department 20 having the top 3 salaries using rank, ADAMS will be out of this list. This will be wrong for this kind of request.</p><p>with RANK_TEST as</p><p>( select 20 deptno, ‘SCOTT’ ename, 3000 sal from dual union all</p><p>select 20, ‘FORD’, 3000 from dual union all</p><p>select 20, ‘JONES’, 2975 from dual union all</p><p>select 20, ‘ADAMS’, 1100 from dual union all</p><p>select 20, ‘SMITH’, 800 from dual )</p><p>SELECT deptno, ename, sal,</p><p>DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) dr,</p><p>RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) r</p><p>FROM RANK_TEST</p><p>ORDER BY deptno, sal DESC;</p><p>DEPTNO ENAME SAL DR R</p><p>— — — — — — — — — — — — — — — — — — — — — — — —</p><p>20 SCOTT 3000 1 1</p><p>FORD 3000 1 1</p><p>JONES 2975 2 3 &lt;← —</p><p>ADAMS 1100 3 4 &lt;← —</p><p>SMITH 800 4 5</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6d43a538edfd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/pl-sql-basics/difference-betweeen-rank-and-dense-rank-6d43a538edfd">Difference betweeen rank and dense_rank</a> was originally published in <a href="https://medium.com/pl-sql-basics">PL/SQL Tricks</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Advenced Queuing (AQ)]]></title>
            <link>https://medium.com/pl-sql-basics/advenced-queuing-aq-59e29f04b8bf?source=rss----54fac6ef0ef2---4</link>
            <guid isPermaLink="false">https://medium.com/p/59e29f04b8bf</guid>
            <category><![CDATA[advanced-queue]]></category>
            <category><![CDATA[queue-type]]></category>
            <dc:creator><![CDATA[Serhan Eroglu]]></dc:creator>
            <pubDate>Thu, 19 Feb 2009 18:05:36 GMT</pubDate>
            <atom:updated>2018-08-06T13:06:38.984Z</atom:updated>
            <content:encoded><![CDATA[<pre>there are not so many stuff about advanced queues for beginner level.</pre><pre>this example may help you to figure out..</pre><pre>--  to create queue, some grants are given to our user</pre><pre>GRANT &quot;AQ_ADMINISTRATOR_ROLE&quot; TO YTS; <br>GRANT &quot;AQ_USER_ROLE&quot; TO YTS;<br>GRANT EXECUTE ON SYS.DBMS_AQ TO YTS;</pre><pre>-- queue table is created</pre><pre>BEGIN<br>  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE<br>  (<br>    QUEUE_TABLE           =&gt;        &#39;YTS.YTS_QUEUE_TABLE&#39;<br>   ,QUEUE_PAYLOAD_TYPE    =&gt;        &#39;YTS.YTS_QUEUE_PAYLOAD_TYPE&#39;<br>   ,COMPATIBLE            =&gt;        &#39;8.1&#39;<br>   ,STORAGE_CLAUSE        =&gt;        &#39;<br>                                     TABLESPACE D_SUPPORT_M<br>                                     PCTUSED    0<br>                                     PCTFREE    10<br>                                     INITRANS   1<br>                                     MAXTRANS   255<br>                                     STORAGE    (<br>                                                 INITIAL          1M<br>                                                 NEXT             1M<br>                                                 MINEXTENTS       1<br>                                                 MAXEXTENTS       UNLIMITED<br>                                                 PCTINCREASE      0<br>                                                 BUFFER_POOL      DEFAULT<br>                                                )&#39;<br>   ,SORT_LIST             =&gt;        &#39;ENQ_TIME&#39;<br>   ,MULTIPLE_CONSUMERS    =&gt;         FALSE<br>   ,MESSAGE_GROUPING      =&gt;         0<br>   ,SECURE                =&gt;         FALSE<br>   );</pre><pre>End;</pre><pre>-- queue is created</pre><pre>BEGIN</pre><pre>SYS.DBMS_AQADM.CREATE_QUEUE<br>  (<br>    QUEUE_NAME          =&gt;   &#39;YTS.YTS_QUEUE&#39;<br>   ,QUEUE_TABLE         =&gt;   &#39;YTS.YTS_QUEUE_TABLE&#39;<br>   ,QUEUE_TYPE          =&gt;   SYS.DBMS_AQADM.NORMAL_QUEUE<br>   ,MAX_RETRIES         =&gt;   5<br>   ,RETRY_DELAY         =&gt;   0<br>   ,RETENTION_TIME      =&gt;   0<br>   );</pre><pre>END;</pre><pre>-- queue is initiated</pre><pre>BEGIN</pre><pre>SYS.DBMS_AQADM.START_QUEUE<br>  (<br>    QUEUE_NAME =&gt; &#39;YTS.YTS_QUEUE&#39;<br>   ,ENQUEUE =&gt; TRUE <br>   ,DEQUEUE =&gt; TRUE <br>   );</pre><pre>END;</pre><pre>-- at the following, you can see the implemantion of the queue in a trigger (this script will not compile of course)</pre><pre>CREATE OR REPLACE TRIGGER something<br>   BEFORE INSERT OR UPDATE OR DELETE<br>   ON something    FOR EACH ROW</pre><pre>DECLARE</pre><pre>v_co_id              NUMBER;<br>   v_transaction_id     NUMBER (19);</pre><pre>.........</pre><pre>/***************************************************************************/</pre><pre>iMessage YTS.YTS_QUEUE_PAYLOAD_TYPE;                                     -- declarations for queue<br>   queue_options       DBMS_AQ.ENQUEUE_OPTIONS_T;<br>   message_properties  DBMS_AQ.MESSAGE_PROPERTIES_T;<br>   message_id          RAW(16);<br>   YTS_QUEUE_TBL    CONSTANT VARCHAR2(40) := &#39;YTS.YTS_QUEUE&#39;;<br>   p_status varchar2(1);</pre><pre>/***************************************************************************/</pre><pre>BEGIN</pre><pre>IF DELETING<br>  THEN</pre><pre>v_co_id := :OLD.co_id;</pre><pre>..........</pre><pre>ELSE</pre><pre>v_co_id := :NEW.co_id;</pre><pre>.............</pre><pre>END IF;</pre><pre>IF    (v_old_status = 1 AND v_status != 1)<br>      OR (v_old_status != 1 AND v_status = 1)<br>   THEN</pre><pre>v_transaction_id := to_number(935 || TO_CHAR (v_process_date, &#39;yyyymmddhh24missss&#39;));<br>     select DECODE (v_status, 1, &#39;Y&#39;, &#39;N&#39;) into p_status from dual;</pre><pre>/****************************************************************************/</pre><pre>iMessage :=  YTS.YTS_QUEUE_PAYLOAD_TYPE(v_transaction_id,v_unique_id,v_customer_id,v_dn_num,p_status,SYSDATE);   -- type is loaded and enqueued.</pre><pre>DBMS_AQ.ENQUEUE(<br>       queue_name =&gt; YTS_QUEUE_TBL,<br>       enqueue_options =&gt; queue_options,<br>       message_properties =&gt; message_properties,<br>       payload =&gt; iMessage,<br>       msgid =&gt; message_id<br>     );</pre><pre>/***************************************************************************/</pre><pre>END IF;</pre><pre>END;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=59e29f04b8bf" width="1" height="1" alt=""><hr><p><a href="https://medium.com/pl-sql-basics/advenced-queuing-aq-59e29f04b8bf">Advenced Queuing (AQ)</a> was originally published in <a href="https://medium.com/pl-sql-basics">PL/SQL Tricks</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>