PL/SQL Programming Tips & Techniques

Oracle PL/SQL Programming

This links to my blog posts and pages dealing with PL/SQL programming. They include PL/SQL programming solution architectures, examples, techniques, and structures. The blog pages show below the top-level page in the right hand column, while blog posts only show in the list below.

PL/SQL architecture ↓

A list of blog posts and pages dealing with Oracle PL/SQL architecture.

Learn about Oracle Database 11g New PL/SQL Features

Learn how to guarantee an external file before querying it.

Learn how to manage transaction control for external binary files in an Oracle Database.

Learn how to manage transaction control when deleting an external file.

Learn how to use an Adapter Object-Oriented design pattern in PL/SQL.

Learn how reference cursors really work – the why, when, and how.

PL/SQL examples ↓

A list of blog posts and pages with PL/SQL programming examples.

Learn about how PL/SQL formal parameter modes really work.

Learn about how you write a dynamic cursor and dynamic NDS cursor.

Learn about how you use a dynamic PL/SQL reference cursor, and how you don’t use it.

Learn how you implement an optional foreign key trigger.

Learn how you insert a colon in an NDS statement without treating like a bind variable.

Learn how you use the FOR UPDATE works with the WHERE CURRENT OF statement.

PL/SQL structures ↓

A list of blog posts and pages covering example of the Oracle PL/SQL language structures.

Learn how you call functions with or without empty parenthesis.

Learn how you reverse direction in a PL/SQL loop.

Learn how you work with PL/SQL formal parameters.

Learn the why, when, and how of using PL/SQL system reference cursors.

PL/SQL techniques ↓

A list of blog posts and pages showing techniques with Oracle PL/SQL.

Learn how you return a BFILE canonical file name reference from a PL/SQL function.

Learn how you return a BFILE file name reference from a PL/SQL function.

Learn how you return a collection through a PL/SQL result cache function.

Learn how you return a SQL object record collection.

Learn how you work with pipelined table functions.

If you have ideas of things that should be here that aren’t, leave a comment on the blog.

Written by maclochlainn

November 12th, 2008 at 11:15 pm

Posted in Uncategorized

19 Responses to 'Oracle PL/SQL Programming'

Subscribe to comments with RSS or TrackBack to 'Oracle PL/SQL Programming'.

Hi sir,
i need pl/sql function that seprates the each string from input comma seprated string. e.g. if input string is

xyz,pqr,wxys

then the function should return
xyz
pqr
wxys

i have sepreted string to fragments using the the below anonymous block but dont know how to return it if it have to be used in function
———————————————-

DECLARE
  str_sql VARCHAR2(4000);
  first_time VARCHAR2(40);
  pos NUMBER(10);
  sub_part VARCHAR2(40);
  len NUMBER(10);
  p NUMBER(10):=1;
  pos2   NUMBER(10);
BEGIN
  str_sql:='RR,UUUU5555,JKL'; 
  first_time:='TRUE';
  len:=LENGTH(str_sql);
  SELECT INSTR(str_sql,',',1,p) INTO  pos FROM DUAL;
  SELECT SUBSTR(str_sql,P,POS-1) INTO  sub_part  FROM DUAL;
  DBMS_OUTPUT.put_line(sub_part); 
  first_time:='FALSE';
  IF first_time'TRUE' THEN
    FOR i IN  1..len
      LOOP
        p:=p+1;
        SELECT INSTR(str_sql,',',1,p) INTO  pos2 FROM DUAL;
        pos:=pos+1;
        SELECT SUBSTR(str_sql,pos,pos2-pos) INTO  sub_part  FROM DUAL;
        DBMS_OUTPUT.put_line(sub_part);
        pos:=pos2;
        IF  pos2=0 THEN
          p:=p-1;
          SELECT INSinstr(str_sql,',',1,p)  INTO  pos FROM dual;
          SELECT SUBSTR(str_sql,pos+1,len)  INTO  sub_part FROM dual;
          DBMS_OUTPUT.put_line(sub_part); 
          EXIT;
        END IF;
      END LOOP;
    END IF;
END;

please help waiting for reply Thanks

swapnil

9 Feb 09 at 6:53 am

There’s an example on doing that with a |, it’s here. You should check the regular expression logic in the SELECT statement of the linked blog post.

If you need something else, let me know.

maclochlainn

9 Feb 09 at 11:12 am
hi sir,
i need the sql/plsql that will return all months
from last 12 months of the sysdate. i.e
if current monthh is FEB then result should be
march 08, april 08, jun 08, …, feb 09

how i can achive this Thanks in advance

swapnil

24 Feb 09 at 6:32 am

I think you’re looking for the ADD_MONTHS function. I’d blogged about it last year.

Here’s a quick example in PL/SQL:

SQL> DECLARE
  2    today DATE := SYSDATE;
  3  BEGIN
  4    FOR i IN 1..12 LOOP
  5      IF i = 1 THEN
  6        DBMS_OUTPUT.put_line('['||SYSDATE||']');
  7      END IF;
  8      DBMS_OUTPUT.put_line('['||add_months(SYSDATE,-i)||']');
  9    END LOOP;
 10  END;
 11  /

maclochlainn

24 Feb 09 at 12:57 pm

Thanks sir for your help.

swapnil

25 Feb 09 at 4:34 am
hi
what is different between LEN and LENGTH in PL/SQL

velu

17 Mar 09 at 11:28 pm

There isn’t a LEN function in PL/SQL unless you wrote one. The functions are LENGTH, LENGTH2, LENGTH4, LENGTHB, and LENGTHC. If you use LEN in the context of a PL/SQL program, you’ll get an identifier error, which indicate LEN doesn’t exist as a variable or function.

SQL&gt; DECLARE
  2    a VARCHAR2(10) := 'STRING';
  3  BEGIN
  4    DBMS_OUTPUT.put_line('['||LEN(a)||']');
  5  END;
  6  /
  DBMS_OUTPUT.put_line('['||LEN(a)||']');
                            *
ERROR AT line 4:
ORA-06550: line 4, column 29:
PLS-00201: identifier 'LEN' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

maclochlainn

18 Mar 09 at 9:37 am

Hi sir,
i have one doubt regarding outer join.
is necessary to have OR in below query.

SELECT A.y, B.y, C.y
FROM A
FULL OUTER JOIN B ON B.x = A.x 
FULL OUTER JOIN C ON C.x = A.x OR C.x = B.x

Thanks
SWAPNIL

swapnil

6 Apr 09 at 7:17 am

I think you’re looking for this type of output, which would mean the OR is not needed.

SQL&gt; SELECT *
  2  FROM a FULL JOIN b ON a.a = b.b FULL JOIN c ON b.b = c.c
  3* ORDER BY a, b, c
SQL&gt; /
 
         A D                   B D                   C D
---------- ---------- ---------- ---------- ---------- ----------
         1 One                 1 One                 1 One
         2 Two
         3 Three               3 Three
         4 Four                4 Four
                               5 Five
                                                     2 Two
                                 Two
                                                       Five
                                                       Four
                                                       Three
           Five
 
11 rows selected.

maclochlainn

6 Apr 09 at 11:07 pm

I need a procedure contaning which has input parameter.
for the input parameter there are multiple rows in table. There is a pl sql table column record (nested table) associated with each row.

Lets say input parameter passed = 1

lets say table has 2 rows for id= 1

table structure
id name pl/sql column(nested column)
1 s1 cd1
cd2
cd3
cd4

1 s2 cd5
cd6
cd7

now i want to generate below results and insert into one beow table where pl/sql column2 is nested column and combination_id is populated using sequence.

table structure(tab_result)

pl/sql column2 combination_id
cd1 cd5 1
cd2 cd5 2
cd3 cd5 3
cd4 cd5 4
cd1 cd6 5
cd2 cd6 6
cd3 cd6 7
cd4 cd6 8
cd1 cd7 9
cd2 cd7 10
cd3 cd7 11
cd4 cd7 12

Can we achive this ?
Thanks in Advance

swapnil

16 Apr 09 at 1:27 am
I’m not sure what you’re asking. Do you mean you start with:
```
1 s1 (cd1, cd2, cd3, cd4)
1 s2 (cd5, cd6, cd7)
```
I think you want to exclude 1 but I included it to try to understand and shifted the new sequence to the left most column. Is this your desired output?
```
1 1 s1 cd1
2 1 s1 cd2
3 1 s1 cd3
4 1 s1 cd4
5 1 s2 cd5
6 1 s2 cd6
7 1 s2 cd7
```
If so, this blog post should help. If you need more help, let me know.
maclochlainn

16 Apr 09 at 8:54 pm
Hi sir,
I want to call c program that is written in unix through pl/sql procedure. I want to implement this using External procedure in oracle.
Can you please elaborate me with a simple example including some tns settings to be done in for this task?

Thanks in Advance

swapnil

20 Apr 09 at 6:24 am
The quick list of things to do. You really need to direct these types of questions to the Oracle Technical Forums.

maclochlainn

20 Apr 09 at 6:34 pm
I have some questions regrading indexes. What is a bitmapped index? What are the various scenarios that cover when you should use a B-Tree versus a bitmapped index?

SWAPNIL

20 May 09 at 2:56 am
You can find the definitions in Designing and Developing for Performance, Release 11g, documentation. You can find the basic definitions in Section 2.5.3.2 Using a Different Index Type, more or less, the rule of thumb goes: “If you have a high cardinality table, use a b-tree; but if you have a low cardinality table, use a bitmap.” Cardinality means the degree of unique value sets.

Hope this is close to the answer you’re looking to find.

maclochlainn

23 May 09 at 4:26 pm
Hi sir,

i want to know how to implemnt parallel query processing in oracle(oracle 9i,10g) with a sutaible example.

Thanks in Adavnce
Swapnil

SWAPNIL

11 Jun 09 at 5:37 am
I did a quick search before writing something new and found this blog entry by Doug Burns. If that’s not what you need, let me know a bit more about what you want.

maclochlainn

14 Jun 09 at 8:34 am
Hello,

I’m trying to create a function utilizing Dynamic SQL. The function compiles fine, but when I go to call it using the select staement below, I get :

ORA-01006: bind variable does not exist
ORA-06512: at “ICIM.PROD_SALES_DATA”, line 24

select prod_sales_data(‘09′,’I',2009,’Awning’)
from dual;

CREATE OR REPLACE FUNCTION prod_sales_data
(p_ahl1 IN VARCHAR2, p_phl1 IN VARCHAR2, p_fisyr IN NUMBER,
p_prod_str IN VARCHAR2)
RETURN NUMBER IS
v_return NUMBER;
query_str VARCHAR2(10000);

BEGIN
query_str :=’select sum(a.qsh*a.unitcnt)’||chr(13)
||’from sh_units a, sh_product_desc b, sh_ad_glass c’||chr(13)
||’where a.unittype = b.unittype’||chr(13)
||’and a.sh_id = c.sh_id(+)’||chr(13)
||’AND a.ahl1 = ‘||chr(39)||p_ahl1||chr(39)||chr(13)
||’AND a.phl1 = ‘||chr(39)||p_phl1||chr(39)||chr(13)
||’AND a.fisyr =’|| p_fisyr||chr(13)
||’AND UPPER(b.descrip) = UPPER(‘||chr(39)||p_prod_str||chr(39)||’)'||chr(13)
||’AND a.invdt between b.eff_beg and b.eff_end’||chr(13)
||’and a.parts_flag ‘||chr(39)||’Y'||chr(39)||chr(13)
||’and a.unittype ‘||chr(39)||’OTHE’||chr(39)||chr(13)
||’and icim.sh_rept_chk_ortp(a.ortp) = 1′||chr(13)
||’and A.REPT_RELEASE_DT is not null’;
DBMS_OUTPUT.PUT_LINE(query_str) ;
EXECUTE IMMEDIATE query_str
INTO v_return
USING p_ahl1,p_phl1,p_fisyr,p_prod_str;
RETURN v_return;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END prod_sales_data ;
/

Any help as to what is going wrong would be greatly appreciated.

Thanks,

Tyler

Tyler

4 Dec 09 at 4:48 pm
In the query_str assignment, use :pahl1, :pahl2, :p_fisyr, and :p_prod_str. What do you intend for the CHR(39)? I’m curious if you meant CHR(38), which is a colon.

Hope this helps, Michael

maclochlainn

4 Dec 09 at 6:49 pm

MacLochlainns Weblog

Oracle PL/SQL Programming

19 Responses to 'Oracle PL/SQL Programming'

Leave a Reply

Recent Posts

Pages

Blogroll

Archives