Home

My blog is dedicated to Oracle Database Application Development and Database Administration

I always wondered why people do the blogging thing. Especially, for technical stuff, we already have the fine manuals, documentations, standard operating procedures etc., then, what’s the big deal in posting technical stuff in a blog? Well, I got my answer when I actually made my first post. A good blog post is about gathering rich information based on our own experience. The test cases that we prepare, are a result of the experience and expertise in that field.

We often learn things from various sources like books, online docs, videos, blogs etc., however, seldom we note down things or gather the information to make it crisp and clear. If we really start doing it that way, it would be helpful not only for ourselves for future reference, but also for others looking for similar information.

Note : To know about me, use the above link “About“. To view my posts, go to “Contents“, to view all posts together, click “All Posts“.

45 thoughts on “Home”

  1. Supratik said:

    Good Job… Keep it up.

    Reply

  2. Puja Keshri said:

    Wow….gud
    hope to see it flourishing.

    Reply

  3. Kumar Vivek said:

    Nice bro….

    Reply

  4. Praveen Inaganti said:

    Good post… Keep going

    Reply

  5. Arjun Ray said:

    Nice Posts……….

    Reply

  6. Sukhada said:

    Hi , I am interested to learn SQL n pl/sql. Do you provide any classes. Thank you

    Reply

  7. Kuruvilla said:

    hi Lalit

    Hi !! How r u ??

    Query –

    Change a column name dynamically using current date

    for eg if current date is 2014

    result –

    2014
    ——-
    kuru
    lalit

    Please help in solving this query using sql or pl

    Reply

    • Lalit Kumar B said:

      @kuruvilla,

      One easy way is to use dynamic sql using pl/sql. Another way is using PIVOT and crosstab query. Search google with these keywords, there are plenty of answers. If you want to ask a question, you could post it in orafaq forums or OTN forums or stack overflow. I am active there.

      Reply

  8. Pl/sql said:

    Hi do you provide pl/SQL online classes. If so could you email me the course details at oraserv2@yahoo.com

    Reply

  9. Muhammad ASif said:

    Hi lalit Kumar Sb, I am impressed by your profile, No doubt you have worked hard and now you are at good position. I need a suggestion by you please help. Sir, I am oracle certified profession in developer track 6i. Now i need your help please guide me that what should do in next. what certification will be more helpful for my career. Please guide about certification and route of certification suggested by you.. My email address is malikasif79@hotmail.com. I will be grateful to you for suggestion and help.

    Best regards,
    Muhammad Asif.

    Reply

  10. chris immanuel said:

    Lalit i have some question regarding the Oracle usage for data analysys such a S-Curve. would you mind helping me ?

    Reply

    • Lalit Kumar B said:

      Sure, please post your question. I would be happy to help you.

      Also, I would suggest you to post your question on OraFAQ and OTN forums too, since your question would be addressed by many other experts.

      Reply

  11. Chris Immanuel said:

    Im a java developer and I am starting an business application with oracle backend. so what kind of java framework do you suggest for ERP type kind of software ?

    Reply

  12. yatindra said:

    Hi Lalit,
    can you plz help in resolving errors in below code
    I want to read header from a file(script_file) and insert that data into external table . I am using below code and srcipt (script.ksh) to load the data, script.ksh contains

    /usr/bin/head -n 1 $1

    my file looks like :

    A00,45697,anbd,224444
    hdhhdh,54444
    nddnnd,577979
    Z99,2

    but when i run the below code

    CREATE TABLE validation_header (
    header1 VARCHAR2(5),
    header2 number,
    header3 VARCHAR2(8),
    header4 number
    )
    ORGANIZATION EXTERNAL (
    TYPE oracle_loader
    DEFAULT DIRECTORY import_ppm
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR import_ppm:’script.ksh’
    LOGFILE import_ppm:’script.log’
    FIELDS TERMINATED BY ‘,’
    (header1,header2,header3, header4
    ))
    LOCATION (‘script_file.txt’)
    );
    it gives me below error :

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-00554: error encountered while parsing access parameters
    KUP 01005: syntax error: found “identifier”: expecting one of: “column, record,identifier”
    KUP-01008: the bad identifier was: PREPROCESSOR
    can u any one help me on this

    Reply

    • Lalit Kumar B said:

      What is your database version, specific to 4 decimals? Also, did you check the permissions on the script file and data file? Script file should have execute permissions, and the data file should have read permissions to the Oracle user.

      It is successfully created for me:

      SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> DROP TABLE validation_header;

Table dropped.

SQL>
SQL> CREATE TABLE validation_header (
  2  header1 VARCHAR2(5),
  3  header2 number,
  4  header3 VARCHAR2(8),
  5  header4 number
  6  )
  7  ORGANIZATION EXTERNAL (
  8  TYPE oracle_loader
  9  DEFAULT DIRECTORY data_pump_dir
 10  ACCESS PARAMETERS (
 11  RECORDS DELIMITED BY NEWLINE
 12  PREPROCESSOR import_ppm:'script.ksh'
 13  LOGFILE import_ppm:'script.log'
 14  FIELDS TERMINATED BY ','
 15  (header1,header2,header3, header4
 16  ))
 17  LOCATION ('script_file.txt')
 18  );

Table created.

SQL>

      I just changed the default directory to test. Rest everything is same, so check the things I have suggested above.

      Reply

      • yatindra said:

        table is getting created for me also but when i m doing “select * from validation_header” then it is giving me above error

  13. yatindra said:

    my database version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit

    Reply

    • Lalit Kumar B said:

      >KUP-01008: the bad identifier was: PREPROCESSOR
      >Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit

      Your version doesn’t support PREPROCESSOR feature. It was introduced in Oracle 11g Release 2 and later backported to 11.1.0.7.

      Reply

  14. Lochan said:

    Hi Lalit,

    Can you please help me in the issue below. I am trying to avoid getting the result as output parameter in the store procedure and use two refcursors instead to manipulate data and execute a single query instead as:

    EXECUTE EMPLOYEE_DETAILS.GET_EMPLOYEES(30,1000);

    Is it even possible to achieve what I am thinking to do?

    create or replace PACKAGE EMPLOYEE_DETAILS AS

    TYPE DETAILS IS RECORD( 
    EMPLOYEE_ID NUMBER(6,0),
      EMPLOYEE_FIRST_NAME VARCHAR2(20),
      EMPLOYEE_LAST_NAME VARCHAR2(25)
      );

    TYPE TABLE_EMPLOYEES IS TABLE OF DETAILS;

    PROCEDURE GET_EMPLOYEES(
    EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE,
    EMP_SALARY employees.salary%TYPE   
    );

    END EMPLOYEE_DETAILS;
	
	
	I
	
	create or replace PACKAGE BODY EMPLOYEE_DETAILS AS

    PROCEDURE GET_EMPLOYEES(
    EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE, 
    EMP_SALARY employees.salary%TYPE
)

IS
    LC_SELECT         SYS_REFCURSOR;
    RC_SELECT         SYS_REFCURSOR;
    LR_DETAILS        DETAILS;
    TBL_EMPLOYEE      EMPLOYEE_DETAILS.TABLE_EMPLOYEES;

BEGIN
    OPEN LC_SELECT FOR 
        SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME 
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID=EMP_DEPT_ID
        AND EMPLOYEES.SALARY>EMP_SALARY;

    LOOP 
        FETCH LC_SELECT INTO LR_DETAILS;
        EXIT WHEN LC_SELECT%NOTFOUND;

        IF IS_VALID(LR_DETAILS.EMPLOYEE_ID) THEN           ------->Is_valid is a function which takes employee_id and looks         another  table to see the corresponding data and returns a boolean.
            
TBL_EMPLOYEE.extend();
            TBL_EMPLOYEE(TBL_EMPLOYEE.count()) := LR_DETAILS;
        END IF;
        OPEN RC_SELECT FOR SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM TBL_EMPLOYEE;
    END LOOP;
    CLOSE LC_SELECT;
    CLOSE RC_SELECT;

END GET_EMPLOYEES;
END EMPLOYEE_DETAILS;

    I try to compile above code and it gives me an error saying the table or view does not exist.Can you please help me or guide me on the
    result I am trying to achieve. I would really appreciate your help.

    Reply

    • Lalit Kumar B said:

      Hi Lochan,

      No, this is not a good approach. You have over complicated it. The IS_VALID function is also of no use here. All you need to do is join the two tables in the cursor query itself and get the valid rows. In my opinion, the entire PL/SQL code you posted could be done in a single SQL statement. Finally, all you need is employee details from employees table based on certain join condition on another table.

      Reply

  15. yatindra said:

    Hi Lalit,

    Can you please help to resolve below problem

    I have two tables ERROR_DESCRIPTION and ERROR_COLUMN.

    ERROR_DESCRIPTION has below data :

    "error processing column abc"
"error processing column cde"
"error processing column fgh"

    ERROR_COLUMN has below data:

    "abc",123334,"jdjjd"
"jdjd",2344,"djjd"
"djjd",234,"kkfkf"

    at last my data should look like this :

    error processing column abc -"abc",123334,"jdjjd"
error processing column cde - "jdjd",2344,"djjd"

    so on …

    i am trying to achieve this using cursors .

    declare
cursor c_log is select * from ERROR_DESCRIPTION where error_data_log like'error%' ORDER BY error_data_log;
r_log ERROR_DESCRIPTION %ROWTYPE;
v_error varchar2(1000);
cursor c_dsc is select * from ERROR_COLUMN; r_dsc ERROR_COLUMN%ROWTYPE; begin open c_log;
loop
fetch c_log into v_error;
open c_dsc ;
fetch c_dsc into r_dsc
dbms_output.put_line( 'error is'||v_error||'-'||r_dsc.xyz);

close c_dsc ;

end loop;
close c_log;
end ;

    i am not able to get desired result .

    “r_dsc.xyz” is column defined for that record type

    Reply

  16. yatindra said:

    Hi Lalit ,
    please can you help me to resolve below query ..
    i want to accept all records from a file so that no record should go to discard or bad file using external table concept.

    below is the code

    create table ext_tab2 (
    mprn char(10))
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY IMPORT
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE IMPORT:’test.bad’
    LOGFILE IMPORT:’test.log’
    FIELDS TERMINATED BY ‘,’
    (mprn char(10)))
    LOCATION (‘abc.txt’)
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

    all the data from file(‘abc.txt’) should get inserted into ext_tab2 table .

    Reply

  17. yatindra said:

    Hi Lalit,
    can you please help me to solve below problem.

    while using external table one BAD file generated . BAD file(“abc.bad”) has data like
    “E51″,12345,555333,”anbd”,”kksksk”,
    E51″,12345233,55333,”anbd”,”kksksk”

    how can i insert above data into table in a single column as varchar.
    currently the default field seperator defined in oracle is ‘,’ and its inserting only the 1st field.
    how to override this default definiton to consider entire record and insert into a single column

    Reply

  18. yatindra said:

    Hi Lalit,
    can you plz help me in resolving below issue

    I have a CSV file which needs to be loaded into a table .While inserting the data into the table using “external table”concept , for the records rejected from CSV file we can get the row no. and field name from LOG file but for the accepted rows i.e. for rows which got inserted into the table how can we get the row number (which row from CSV file got inserted into the table );

    CREATE TABLE emp_external
    (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
    )
    ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY my_data
    ACCESS PARAMETERS
    (RECORDS DELIMITED BY NEWLINE
    LOGFILE my_data:’data.log’
    BADFILE my_data:’data.bad’
    DISCARDFILE my_data:’data.disc’
    fields terminated by ‘,’
    MISSING FIELD VALUES ARE NULL
    )
    LOCATION (my_data:’emp.csv’)
    );

    Reply

  19. Aditi Mehta said:

    Hello Lalit,
    My ques is :
    My input string is ad-meht,fl-flat rate,bc-bank cust
    I required output as ad,fl,bc
    Will it be possible??

    Reply

    • Lalit Kumar B said:

      @Aditi,

      You could use REGEXP_REPLACE to match the pattern and replace with your desired string.

      For example,

      
SQL> WITH DATA AS(
  2  SELECT 'ad-meht a,fl-flat rate,bc-bank cust' str FROM dual
  3  )
  4  SELECT regexp_replace(
  5                      str
  6                      ,'([[:alpha:]]{2})\-([[:alpha:]]{4})([[:space:]])([[:alpha:]]+)'
  7                      ,'\1')
  8  FROM DATA;

REGEXP_R
--------
ad,fl,bc

SQL>

      Reply

  20. Navin said:

    Hi Lalit,

    I’ve question and need your help.

    My query returns ” ‘Party = 23048738, Country = (0) ”Party = 8376478123, Country = UNITED STATES (0) , Zip = 60123-4300 (75) ‘base_score 50 “. I would like to create Party, County & Zip columns with above values in single row until each break.

    Thanks,
    Navin

    Reply

    • Navin said:

      My query returns: ” ‘Party = 23048738, Country = (0) ”Party = 8376478123, Country = UNITED STATES (0) , Zip = 60123-4300 (75) ‘base_score 50 “

      I’m expecting as below,

      Party County Zip
      —————————————————————–
      23048738
      8376478123 UNITED STATES 60123-4300

      Reply

      • Lalit Kumar B said:

        @Navin,

        Please post a working test case. Either provide create and insert statements or WITH clause with sample data. And clearly show your expected output.

  21. Ranjan Kumar Jena said:

    Hello Lalit,

    I have a question could you please help me on this..

    CREATE OR REPLACE PACKAGE PKG_MULTI
    AS
    TYPE T1 IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
    PROCEDURE PROC_ENAME(P_ENAME IN T1);
    END;
    /

    ————————————————————————————————-

    CREATE OR REPLACE PACKAGE BODY PKG_MULTI
    AS
    PROCEDURE PROC_ENAME(P_ENAME IN T1)
    AS
    V_SAL NUMBER(7, 2);
    BEGIN
    FOR I IN P_ENAME.FIRST..P_ENAME.LAST
    LOOP
    SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = P_ENAME(I);
    DBMS_OUTPUT.PUT_LINE(V_SAL);
    END LOOP;
    END;
    END;
    /

    My requirement is how I can pass multiple column values in a single parameter of this procedure, I have tried this but not getting result.

    Example If I will pass PKG_MULTI.PROC_ENAME(‘KING’, ‘TURNER’, ‘SCOTT’, …) then it should return their corresponding salaries

    Reply

  22. Ankit Bajpai said:

    Hi Lalit,

    I have read your a lot of posts on STACKOVERFLOW and started to follow you. I also have a lot of interest in SQL and wanted to become an Database Application Developer just like you. I also wanted to complete OCA and OCP certifications which you already have with you. So can you please suggest me some good course material or any guidence that you can provide.

    Thanks in advance.

    Reply

    • Lalit Kumar B said:

      Hi Ankit,

      Good that you are looking to complete the certifications. I would recommend you to stick to Oracle documentation for correct information. The certification syllabus will give you a good idea on what areas you need to focus. However, certification is not just to pass the exam but to use the knowledge in your experience.

      One good book I can suggest you by Kevin Meade, I am one of the technical reviewers of this book. You can find it on Amazon https://www.amazon.com/dp/1501022695/?tag=stackoverfl08-20

      If you don’t want to buy the book, then you can focus on online materials. You must try stuff by your own to have the practical understanding. My source of knowledge has had been Oracle documentation, Oracle-Base site which is owned by Tim Hall and forums like OTN and Orafaq where I am a moderator.

      All the best,
      Lalit

      Reply

  23. Rashmi said:

    Hi Lalit
    Regarding comma delimited string into rows.
    Instead of rows I need output as columns, no of columns can be vary as per your word value example.
    Pls help to achieve this

    Reply

  24. Ram said:

    Could you please help me with the below need

    Table output:
    Contract_Start_Date​|Attributed_Members​|Care_Coordination_Payment|PROGRAM_NM
    A1|A2|A3|TP Plus Commercial
    B1|B2|B3|TP Base
    C1|C2|C3|MA MLR

    Needs to convert/Pivot/unpivot as below:
    Metric|TP Plus Commercial| TP Base|MA MLR
    Contract_Start_Date|​ A1| B1| C1
    Attributed_Members​| A2| B2| C2
    Care_Coordination_Payment| A3| B3| C3

    Reply

Leave a comment