MacLochlainns Weblog

Michael McLaughlin’s Technical Blog

Site Admin

Oracle PL/SQL Programming

with 29 comments

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

29 Responses to 'Oracle PL/SQL Programming'

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

  1. 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

  2. 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

  3. 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

  4. 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

  5. Thanks sir for your help.

    swapnil

    25 Feb 09 at 4:34 am

  6. hi
    what is different between LEN and LENGTH in PL/SQL

    velu

    17 Mar 09 at 11:28 pm

  7. 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> 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

  8. 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

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

    SQL> 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> /
 
         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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. I am trying to create a PL/SQl Program that puts an asterisk into a variable called v_asterisk for every $100 of an employee,s salary, I need the program to accept the employee number as the user input with a SQL*PLUS substitution variable. for the v_asterisk has to contain a string of asterisks to every $100 of the salary of the employees. For example if the salary is $800 then that would be followed by 8 ******** and so forth. Then I need to create a global variable that outputs the message T.Brady you have 8 stars!. Please need help as soon as possible.
    Thanks
    Charles L.

    Charles L.

    19 Mar 10 at 10:56 am

  21. Are you looking for this?

    SELECT LPAD(' ',sal/100,'*') FROM emp WHERE empno = &input;

    maclochlainn

    19 Mar 10 at 1:09 pm

  22. This is part of the problem but it has to use a loop and that is where I am having trouble I do not know which type to use so that each time it goes through it puts an asterisk in for each $100 of salary.

    Charles L.

    19 Mar 10 at 5:58 pm

  23. It sounds like a homework assignment. Just post the assignment.

    maclochlainn

    19 Mar 10 at 6:14 pm

  24. Yes it is a home work assignment. First you have to create an EMPLOY table which I have done and then insert the data which I have done. The tables has three fields they are as follows Empno, varchar2(3), Empname varchar2(20), EmpSal Number(4)

    Then the data is 123, Eddie Simmons, 2000 and so onuntill the last record which is a Null value for the empSal. I am to create a PL/SQL program that puts an asterisk into a variable called v_asterisk for every $100 of an employee’s salary. The program should do the following:
    a. Accept the employee number as the user input with a SQL*PLUS substitution variable.
    b. Create a variable (v_asterisk) that will contain a string of asterisk.
    c. Append an asterisk to the string for every $100 of the salary amount. For example, if an employee has a salary of $800 , the string of asterisk should contain eight asterisk. If the employee had a salary of $950 it should show ten asterisk.(Use a Loop!).
    d. Create a global variable called g_out which will contain the message which (for example) will look something like this; T.Brady you have 13 stars!*************
    e. This message will obviously change depending upon which Employee number was input. Add a print g_out after the END; statement in your program.
    f. If a record doesn’t have a salary value, make sure that the message says…you have NO stars!
    I have tried to write it every way I know but nothing is working Help would be great appreciated.
    Thanks
    Charles

    Charles L.

    19 Mar 10 at 6:41 pm

  25. I’m not sure you’re learning much if you’re google’n for an answer. BTW, I teach the equivalent course at University. Giving you a complete solution wouldn’t be fair to you or your instructor, and certainly not to classmates. However, there aren’t 5 steps to this problem as you’ve qualified, below are the 12 things you’ll need to code.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

    -- You declare a session variable before your anonymous block. Session variables are also bind variables.
VARIABLE g_out VARCHAR2(60)
 
DECLARE
 
  -- Declare a variable for the input, only initialize it in the execution section (best practice).
  v_input
 
  -- Declare control variables.
  -- One counts the hundreds and the other stores asterisks.
  v_counter
  v_asterisk
 
  -- Declare local variables to hold the output from an implicit cursor.
  v_empname
  v_empsal
 
BEGIN
 
  -- Assign vouchsafed input parameter:
  --  1. This means put it in an NVL function dealing with strings.
  --  2. Use the TO_NUMBER() function to convert the input to a number.
  v_input
 
  -- Since the cursor always returns a single row, use the SELECT-INTO syntax.
  SELECT   empname
  ,        empsal
  INTO     v_empname
  ,        v_empsal
  FROM     employ
  WHERE    empno = v_input;
 
  -- Calculate the number of one hundred dollar increments in the salary.
  --  1. IF the numerator of the division operation can be null, use the NVL to prevent that.
  --  2. Round the value before you assign it.
  v_counter
 
  -- Using a loop pad the empty string variable with asterisks.
  FOR i IN 1..v_counter LOOP
   v_asterisk
  END LOOP;
 
  -- Parse first and last name separated by a white space to first initial, a period, and last name.
  --  1. Use the <code>SUBSTR</code>, <code>INSTR</code>, and <code>LENGTH</code> functions to parse the string.
  v_empname
 
  -- Check counter to set message.
  IF v_counter > 0 THEN
 
    -- Assign asterisk output to session level (bind) variable.
    :g_out
 
  ELSE
 
    -- Assign NO starts output to session level (bind) variable.
    :g_out
 
  END IF;
 
END;
/
 
SELECT :g_out FROM dual;

    maclochlainn

    19 Mar 10 at 11:46 pm

  26. Thanks for your help that was way more then I needed I just could not figure out the loop and how to tie it in with the rest of the problem. thanks for the help. This is an on-line course I am taking for Oracle and there is no real help except for the internet and the tutorials that come with the book.

    Charles L.

    Charles L.

    20 Mar 10 at 11:37 am

  27. No worries, I didn’t want anybody to think we were destroying their leanring model.

    maclochlainn

    20 Mar 10 at 6:43 pm

  28. hi sir,
    while running the pl sql which i wrote on our department requirements,iam getting lot of errors which iam unable to debug.plz help me.
    pl sql:

    declare
scno varchar(13);
amt number(15):=0;
cust number(15);
demdt date;
mon1:=’30-NOV-09′;
mon2:=’31-MAR-10′;
exec_err exception;
begin
cursor dem_cur IS
SELECT ctscno,lpcustchglplddt FROM led_print,cons WHERE ctscno=lpscno AND c
tlink=’Y’ AND ctservtype=’75′ AND ctstat=’01′ AND lplddt BETWEEN ‘30-NOV-09′ AND
‘31-MAR-10′;
open dem_cur;
fetch dem_cur INTO scno,cust,demdt;
IF demdt BETWEEN mon1 AND mon2
amt:= cust+amt;
else
raise exec_err;
end IF;
end;
close dem_cur;
end;
/

    errors i am getting:

    mon1:=’30-NOV-09′;
*
ERROR at line 6:
ORA-06550: line 6, COLUMN 5:
PLS-00103: Encountered the symbol “=” when expecting one of the following:
constant exception
TABLE LONG_ double
The symbol “” was substituted FOR “=” TO continue.
ORA-06550: line 7, COLUMN 5:
PLS-00103: Encountered the symbol “=” when expecting one of the following:
constant exception
TABLE LONG_ double
The symbol “” was substituted FOR “=” TO continue
ORA-06550: line 10, COLUMN 8:
PLS-00103: Encountered the symbol “DEM_CUR” when expecting one of the
following:
:= . ( @ % ;
ORA-06550: line 13, COLUMN 1:
PLS-00103: Encountered the symbol “FETCH” when expecting one of the following:
begin FUNCTION package pragma procedure subtype type USE
cursor
form
The symbol “begin” was su
ORA-06550: line 15, COLUMN 1:
PLS-00103: Encountered the symbol “AMT” when expecting one of the following:
. ( * @ % &amp; – + / mod rem then an exponent (**) AND OR ||
The symbol “then” was substituted FOR “AMT” TO continue.
ORA-06550: line 20, COLUMN 1:
PLS-00103: Encountered the symbol “CLOSE” when expecting one of the following:
begin FUNCTION package pragma procedure form
ORA-06550: line 22, COLUMN 0:
PLS-00103: Encountered the symbol “;” when expecting one of the following:
begin FUNCTION package pragma procedure subtype type USE
cursor
form

    sruthi

    21 May 10 at 3:12 am

  29. Just a guess but you did some quick editing to provide a sample. Other than that, the first thing is you can’t define a cursor in the execution section. You define cursors in the declaration block. Second, your cursor defines three columns but your fetch returns two columns. You must fetch all columns into equivalent columns or use a record type that maps to the proper list of columns in the cursor definition.

    maclochlainn

    21 May 10 at 12:35 pm

Leave a Reply