MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle PL/SQL Programming

with 56 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. In case you’re looking for my PL/SQL books, here are a couple links: Oracle Database 11g PL/SQL Programming or the Oracle Database 11g PL/SQL Programming Workbook.

Written by maclochlainn

November 12th, 2008 at 11:15 pm

Posted in Uncategorized

56 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

  30. Hello sir,

    i’ve tried referring two of ur books on pl/sql and couldn’t use any! the books say the reference code is available on mc graw hill’s site, but all it has is book’s description. plz help me in getfing the code for ur solo book on 11g.

    thanks,
    Rajat

    ps:the co policy prohibits accessing blogs-including technical ones; so am accessing this on a mobile phone. plz excuse any typos or sms lingo.

    Rajat

    11 Oct 10 at 8:48 am

  31. Rajat,

    The Oracle Press page for Oracle Database 11g PL/SQL Programming is here and the code is in this zip file. The Oracle Press page for Oracle Database 11g PL/SQL Programming Workbook is here and the code is in this zip file.

    Hope that solves the problem for you. If not, let me know.

    maclochlainn

    13 Oct 10 at 9:24 pm

  32. Hi Sir,

    I want to run below script to update around 1 million records in the table.

    Plain update was taking too much time so we went with below approach.
    Still i am facing issue to get better execution time. Can you please help me out?

    Please suggest any better approach if any.

    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

    DECLARE
 
  TYPE fa_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_id FA_ID;
 
  CURSOR cur IS
    SELECT /*+Parallel(fc  5)*/
            cc_id
    FROM cc_mast  fc
    WHERE 12 <=
     (SELECT MONTHS_BETWEEN (SYSDATE,
                             (SELECT MAX(cc_txn_date)
                              FROM c_transaction fct_in
                              WHERE fct_in.cc_id = fc.cc_id))
      FROM DUAL)
    AND fc.ms_status = 'T'
    AND fc.cc_number  'no info';
 
BEGIN
  OPEN cur;
  LOOP
    FETCH cur
    BULK COLLECT INTO v_id LIMIT 1000;
    FORALL i IN v_id.FIRST .. v_id.LAST
      UPDATE /*+  Parallel(5) */cc_mast  fc
      SET fc.cc_number = 'no info'
      WHERE fc.cc_id = v_id (i);
    EXIT WHEN v_id.COUNT = 0;
  END LOOP;
 
  CLOSE cur;
END;
/

    Thanks in Advance
    Swapnil

    Swapnil

    18 Oct 10 at 2:23 am

  33. hi sir i wanna create a trigger on a table for column it should not accept the date greater than a sysdate how i can

    sreekanth

    18 Apr 11 at 1:37 pm

  34. The easiest thing is to intercept the date and reassign it to the current SYSDATE value. The code would look like:

    1
2
3
4
5
6
7
8

    CREATE OR REPLACE TRIGGER some_trigger 
BEFORE INSERT OR UPDATE ON some_trigger
FOR EACH ROW
  WHEN (NEW.column_name < SYSDATE)  -- You want to replace it.
BEGIN
  :NEW.column_name := SYSDATE;
END;
/

    maclochlainn

    18 Apr 11 at 10:42 pm

  35. Hi Mac,

    I am PL/SQL developer at my organization.

    I have to implement data security at table level by using oracle VPD.

    I am aware about creation of policies, logon trigger etc in oracle virtual private database.

    Actual problem statement is that:

    We need to implement VPD to show the CREDIT CARD in database such that they should appear like:

    ********123

    for those who are not authorised; and for privileged user it should appear has the whole number like:

    123344455123.

    MY QUESTION IS: can we implement above thing using oracle VPD(10G).

    Please share your valuable thoughts and thanks in advance.

    Thanks
    swap

    swap

    13 Oct 11 at 12:06 am

  36. Your month example could be easier:

    DECLARE
  today DATE := SYSDATE;
BEGIN
  FOR i IN 0 .. 12 LOOP
    dbms_output.put_line('[' || add_months(SYSDATE,-i) || ']');
  END LOOP;
END;

    Mehmet Kirazoglu

    20 Oct 11 at 5:56 am

  37. I suppose that’s true if you want to run it for any day and return twelve days a month apart. The program seeds beginning and ending dates for months.

    maclochlainn

    20 Oct 11 at 5:20 pm

  38. Hi Mac,

    I am PL/SQL developer at my organization.

    I have to implement data security at table level by using oracle VPD.

    I am aware about creation of policies, logon trigger etc in oracle virtual private database.

    Actual problem statement is that:

    We need to implement VPD to show the CREDIT CARD in database such that they should appear like:

    ********123

    for those who are not authorised; and for privileged user it should appear has the whole number like:

    123344455123.
    MY QUESTION IS: can we implement above thing using oracle VPD(10G).

    Please share your valuable thoughts and thanks in advance.

    Thanks
    swap

    swap

    3 Nov 11 at 5:24 am

  39. Yes, you can do it in Oracle 10g. It’s called fine-grained VPD and relies on the DBMS_APPLICATION_INFO packages ability to write tot he CLIENT_INFO column maintained for any connection. I presented on this at the Utah Oracle User Group but they’ve pulled down the archive of presentations and I’m not sure where it’s archived at the moment.

    You can find it discussed in the Writing a Security Profile Stored Procedure section of this article that I wrote for OTN. It should get you started and from what you’ve said you should be able to do it fairly quickly.

    maclochlainn

    3 Nov 11 at 9:37 am

  40. Hi Mac ,

    Thanks for your inputs for VPD.

    But still i am not getting that how we can mask perticular column value using DBMS_APPLICATION_INFO package.

    Thanks
    Swapnil

    Swap

    11 Nov 11 at 3:31 am

  41. Hi,
    I have two objects with same name i.e table and view.
    when i write query with that name, i am getting table records. But i want to get view records HOw?
    plz help me..

    venkat

    1 Sep 12 at 1:24 pm

  42. You can’t have a table and view with the same name in the same schema. Namespace rules disallow that. You can have a table and a trigger or a view and a trigger in the same schema because triggers have their own namespace.

    Is it possible that the table is in your home schema and the view is in another schema? If so, this would be how you would access it:

    SELECT * FROM other_schema_name.view_name;

    maclochlainn

    1 Sep 12 at 9:52 pm

  43. thank you sir..

    venkat

    2 Sep 12 at 11:55 am 

  44. 1
2
3
4
5
6
7
8
9
10
11
12
13

    DECLARE
   v_name friends.fname%TYPE;
   v_grade friends.id%TYPE;
BEGIN
   SELECT fname, grade
      INTO &ssv_name, v_grade
   FROM friends
   WHERE v_name = fname;
   DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0));
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123');
END;

    What’s wrong with this PL/SQL program?

    This is to input the name and then output the name with his/her grade. Kindly help me with this sir. Thank you.

    Rod quizon

    28 Nov 12 at 7:08 am

  45. While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.

    There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local v_name variable. If you’d taken the time to create the tables and try it, you should generate an error like this:

    SQL> /
Enter VALUE FOR ssv_name: Harry
OLD   6:       INTO &ssv_name, v_grade
NEW   6:       INTO Harry, v_grade
      INTO Harry, v_grade
           *
ERROR at line 6:
ORA-06550: line 6, COLUMN 12:
PLS-00201: identifier 'HARRY' must be declared
ORA-06550: line 7, COLUMN 4:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, COLUMN 4:
PL/SQL: SQL Statement ignored

    Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:

    • The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
    • The & (ampersand) preceding ssv_name makes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the &ssv_name input value is a valid locally declared variable or defined session bind variable name.
    • Assuming you input a valid identifier, the next problem is that the WHERE clause uses an equality comparison operator against the v_name local variable, which holds a null value. That means the SELECT-INTO always fails on a no data found error unless you add an assignment statement for the v_name variable.

    If I was wrong and this wasn’t for Steven’s Q&A, then I owe your teacher an apology for answering the question for you rather than you figuring it out.

    Hope it helps you and it would be great if you update whether this was for class or some on-line quiz.

    maclochlainn

    28 Nov 12 at 11:58 am

  46. […] answer to a question posed in blog page […]

    How not to use a SELECT-INTO

    29 Nov 12 at 7:09 pm

  47. This is reveised code of previous one
    **************************************

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    DECLARE
    X VARCHAR2(500);
    Y VARCHAR2(50);
    Z INTEGER :=0;
    ST INTEGER :=1;
    N INTEGER;
BEGIN
    X:='RR,UUUU,5555,JKL';
    ST :=1;
    SELECT REGEXP_COUNT(X, ',')+1 INTO N FROM DUAL;
    FOR I IN 1..N LOOP
        SELECT DECODE(I,N,LENGTH(X)+1,INSTR(X,',',1,I)) INTO Z FROM DUAL;
        SELECT SUBSTR(X,ST,DECODE(I,1,Z-1,Z-ST)) INTO Y FROM DUAL;
        ST:=Z+1;
    DBMS_OUTPUT.PUT_LINE(Y);
    END LOOP;
END;
    output:
********
RR
UUUU
5555
JKL

    rajasekar

    27 Aug 13 at 6:47 am

  48. how to return object as out parameter from stored procedure in oracle 11g plz give some example

    Raja

    24 Sep 13 at 6:35 am

  49. Raja, I put together a full example for you. Thanks, Michael

    maclochlainn

    19 Dec 13 at 6:39 pm

  50. Hi,

    can you please explain me about the below functions how it works.

    NUMTODSINTERVAL

    NUMTOYMINTERVAL

    I tried googling and ora docs but didn’t understood their examples and significance of these functions.

    Can you please explain me with examples ..

    Thanks in advance
    Swapnil

    swapnil

    9 Apr 14 at 3:50 am

Leave a Reply