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 things down or collect 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 linkAbout“. To view my posts, go toContents“, to view all posts together, click All Posts“.

40 thoughts on “Home”

  1. Good Job… Keep it up.

  2. Wow….gud
    hope to see it flourishing.

  3. Nice bro….

  4. Praveen Inaganti said:

    Good post… Keep going

  5. Nice Posts……….

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

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

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

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

  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.

  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 ?

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

  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 ?

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

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

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

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

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

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

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

  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

  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 .

  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

  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’)
    );

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

    • @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>
      
      

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

    • 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

      • @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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s