Generate SQL Loader Control File Dynamically

In my previous blog I posted to Generate SQL Plus script Dynamically and now I am providing a SQL Script to generate SQL Loader Control File dynamically from SQL script. You need to just run this script in the schema where that table exists for which you want to create the control file and after running the script it will prompt you to enter the Table name and after entering the table name it will generate the Control file for that table. You may need to edit control file after generation but a basic Control File would be created, which could be a very time saving.

Below is the SQL Plus Script from which you can generate the Control File:
[code type="SQL"]SET ECHO OFF;
SET HEAD OFF;
SET PAGESIZE 100;
SET LINESIZE 100;
SET UNDERLINE OFF;
SET FEED OFF;
SET VER OFF;

ACCEPT tblname CHAR PROMPT 'Enter Table Name: ';

SPOOL yourtable.ctl

SELECT    'LOAD DATA
APPEND INTO TABLE '
       || '&tblname'
       || '
FIELDS TERMINATED BY '',''  OPTIONALLY ENCLOSED BY '''
       || CHR (34)
       || '''
TRAILING NULLCOLS ('
  FROM DUAL
UNION ALL
SELECT tab_cols
  FROM (  SELECT column_name || ',' tab_cols
            FROM user_tab_cols
           WHERE table_name = '&tblname'
        ORDER BY column_id)
UNION ALL
SELECT ')' FROM DUAL
/
SPOOL OFF
/
[/code]
Suppose if you will generate it for Scott.Emp Table, then the output of the control file would be as following:
[code type="SQL"]LOAD DATA                                                                                          
APPEND INTO TABLE EMP                                                                        
FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"'                                            
TRAILING NULLCOLS (
EMPNO,                                                                                            
ENAME,                                                                                            
JOB,                                                                                              
MGR,                                                                                              
HIREDATE,                                                                                          
SAL,                                                                                              
COMM,                                                                                              
DEPTNO                                                                                            
)                                                                                                  
[/code]

How to create SQL*Plus Script Dynamically in Oracle

I have created a stored procedure in Oracle to generate SQL*Plus script from a SELECT statement. It will take SQL SELECT statement as parameter and on that behalf it will create the SQL*Plus script as you can see in below example screen-shot, but it is having some limitations, which I will describe below but still it will definitely speed-up your work in creating SQL*Plus script from any given SQL Query. Also note that it will not PARSE the query, it will just read the columns from the statement between SELECT and FROM. You can pass the following types of SELECT statements as parameter to this procedure:
[success title="SELECT Without Aliases" icon="check-circle"]
SELECT
EMPNO, ENAME, JOB,
   MGR, HIREDATE, SAL,
   COMM, DEPTNO
FROM SCOTT_EMP S
[/success]
[success title="SELECT With Table Reference" icon="check-circle"]
SELECT
S.EMPNO, S.ENAME, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/success]
[success title="SELECT With Aliases" icon="check-circle"]
SELECT
S.EMPNO emp_no, S.ENAME employee_name, S.JOB Job,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/success]
[error title="SELECT With Expressions NOT ALLOWED" icon="exclamation-circle"]
SELECT
NVL(S.EMPNO, '19009') emp_no, S.ENAME employee_name, Decode(S.JOB, 'ABC', 'Y') Job,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
[/error]
NOTE: No need to pass the WHERE Clause of your query just pass the statement from "Select to... From ", you can add your query WHERE clause into script after the script generation.

Below is the stored procedure which you need to create in your schema to create SQL Scripts from it:
[code type="SQL"]CREATE OR REPLACE PROCEDURE create_sql_script (
   i_query        IN     VARCHAR2)
AS
   TYPE t_columns IS TABLE OF VARCHAR2 (100)
                        INDEX BY BINARY_INTEGER;

   v_columns   t_columns;
   v_query VARCHAR2 (4000)
         := REPLACE (REPLACE (i_query, CHR (13), ''), CHR (10), ' ');
   v_string VARCHAR2 (4000)
         := LTRIM (
               LOWER(SUBSTR (v_query,
                             INSTR (LOWER (v_query), 'select '),
                             INSTR (LOWER (v_query), ' from'))),
               'select ')
            || ',';
   i           NUMBER := 1;

   FUNCTION getstring (source_string    IN VARCHAR2,
                       field_position   IN NUMBER,
                       unterminated     IN BOOLEAN DEFAULT FALSE,
                       delimiter        IN VARCHAR2 DEFAULT ',')
      RETURN VARCHAR2
   IS
      iptrend           PLS_INTEGER := 0;
      iptrstart         PLS_INTEGER := 0;
      vcsourcestrcopy   VARCHAR2 (2000) := source_string;
   BEGIN
      IF unterminated
      THEN
         vcsourcestrcopy := vcsourcestrcopy || delimiter;
      END IF;

      IF field_position > 1
      THEN
         iptrstart :=
            INSTR (vcsourcestrcopy,
                   delimiter,
                   1,
                   field_position - 1)
            + LENGTH (delimiter);
      ELSE
         iptrstart := 1;
      END IF;

      iptrend :=
         INSTR (vcsourcestrcopy,
                delimiter,
                1,
                field_position);
      RETURN SUBSTR (vcsourcestrcopy, iptrstart, (iptrend - iptrstart));
   END getstring;                                         /* String Version */
BEGIN
   DBMS_OUTPUT.enable (200000);

   WHILE getstring (v_string,
                    i,
                    FALSE,
                    ',') IS NOT NULL
   LOOP
      v_columns (i) :=
         RTRIM (LTRIM (getstring (v_string,
                                  i,
                                  FALSE,
                                  ',')));

      IF INSTR (v_columns (i), '.') > 0
      THEN
         v_columns (i) :=
            SUBSTR (v_columns (i), INSTR (v_columns (i), '.') + 1);
      END IF;

      IF INSTR (v_columns (i), ' ') > 0
      THEN
         v_columns (i) :=
            LTRIM (SUBSTR (v_columns (i), INSTR (v_columns (i), ' ')));
      END IF;

      i := i + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('REM ********************************************');
   DBMS_OUTPUT.put_line ('REM * Script Name   : [Name].SQL');
   DBMS_OUTPUT.put_line ('REM * Description     : []');
   DBMS_OUTPUT.put_line ('REM * Author           : []');
   DBMS_OUTPUT.put_line ('REM * Created Date  : []');
   DBMS_OUTPUT.put_line ('REM * Parameter      : []');
   DBMS_OUTPUT.put_line ('REM ********************************************');
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('SET     HEADING   ON;');
   DBMS_OUTPUT.put_line ('SET     TERM      ON;');
   DBMS_OUTPUT.put_line ('SET     ECHO      OFF;');
   DBMS_OUTPUT.put_line ('SET     WRAP      OFF;');
   DBMS_OUTPUT.put_line ('SET     FEED      OFF;');
   DBMS_OUTPUT.put_line ('SET     VER       OFF;');
   DBMS_OUTPUT.put_line ('SET     UNDERLINE ON;');
   DBMS_OUTPUT.put_line ('SET     UNDERLINE =;');
   DBMS_OUTPUT.put_line ('SET     PAGESIZE  100;');
   DBMS_OUTPUT.put_line ('SET     LINESIZE  300;');
   DBMS_OUTPUT.put_line ('SET     COLSEP ,;');
   FOR n IN v_columns.FIRST .. v_columns.LAST
   LOOP
      DBMS_OUTPUT.put_line(   'COLUMN  '
                           || LTRIM (UPPER (v_columns (n)))
                           || ' FORMAT A20 HEADING '
                           || CHR (34)
                           || LTRIM (UPPER (v_columns (n)))
                           || CHR (34));
   END LOOP;

   DBMS_OUTPUT.put_line ('Spool YourFileName.csv');
   DBMS_OUTPUT.put_line (REPLACE (i_query, ';', ' '));
   DBMS_OUTPUT.put_line ('/');
   DBMS_OUTPUT.put_line ('Spool Off;');
   DBMS_OUTPUT.put_line ('Exit');
   DBMS_OUTPUT.put_line ('/');
   DBMS_OUTPUT.put_line (' ');
END;[/code]
Call this procedure as following:
[code type="SQL"]set serveroutput on;
BEGIN
   create_sql_script (
      'SELECT
S.EMPNO Employee_Number, S.ENAME Employee_Name, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S;');
END;[/code]
Now capture the output generated through DBMS_OUTPUT command and paste it into file and save it as .SQL. Below is the output of above SELECT statement:
[code type="SQL"]REM ********************************************
REM * Script Name   : [Name].SQL
REM * Description   : []
REM * Author        : []
REM * Created Date  : []
REM * Parameter     : []
REM ********************************************
SET     HEADING   ON;
SET     TERM      ON;
SET     ECHO      OFF;
SET     WRAP      OFF;
SET     FEED      OFF;
SET     VER       OFF;
SET     UNDERLINE ON;
SET     UNDERLINE =;
SET     PAGESIZE  100;
SET     LINESIZE  300;
SET     COLSEP ',';

COLUMN  EMPLOYEE_NUMBER FORMAT A20 HEADING "EMPLOYEE_NUMBER"
COLUMN  EMPLOYEE_NAME FORMAT A20 HEADING "EMPLOYEE_NAME"
COLUMN  JOB FORMAT A20 HEADING "JOB"
COLUMN  MGR FORMAT A20 HEADING "MGR"
COLUMN  HIREDATE FORMAT A20 HEADING "HIREDATE"
COLUMN  SAL FORMAT A20 HEADING "SAL"
COLUMN  COMM FORMAT A20 HEADING "COMM"
COLUMN  DEPTNO FORMAT A20 HEADING "DEPTNO"

Spool YourFileName.csv

SELECT
S.EMPNO Employee_Number, S.ENAME Employee_Name, S.JOB,
   S.MGR, S.HIREDATE, S.SAL,
   S.COMM, S.DEPTNO
FROM SCOTT_EMP S
/
Spool Off;
Exit
/[/code]
You need to make some changes in header section and in COLUMN statements to specify exact length of the line and column and if column is numeric type then need to change the format also, as shown in below example:

COLUMN  SAL FORMAT 999999.99 HEADING "SAL"

You must change the line size according to your output and column separator if it is other than Comma ','

SET     LINESIZE  300;
SET     COLSEP ',';

Please comment below if you got issues during execution, thanks.

Blogger created a new gadget Featured Post to highlight a particular post in blogger blog, so that visitors would be able to get the attention about that post and able to view it. The following are the steps to how to add a "Featured Post Gadget / Widget" to your blog in blogger.

Add Featured Post Gadget / Widget in Blogger Blog

1. Go to Layout section of the blog and click on Add a Gadget and then select Feature Post gadget from the popup window, as shown in below image:

Add Featured Post Gadget / Widget in Blogger

2. Then the following window will appear and then just choose Label from the drop down list and then choose the particular post in that label to show in Featured Post widget as shown in below image:

Blogger's new Featured Post Gadget

You are done, it will now show the Featured Post in your blog.

Check Oracle Database status in bash shell
You want to write a simple shell script in Linux / Unix to check whether Oracle Database is Up or not. The below is the example script which you can write using vi editor. Follow these steps to create and execute a shell script in Linux / Unix.

Run vi editor and add below content by changing values accordingly as mentioned in next paragraph.

#! /bin/bash
ORACLE_SID=FOXDEV
ORACLE_HOME=/orahome1/oracle/product/11.2.0/db_1
PATH=$ORACLE_HOME/bin:$PATH
echo "select 'Database is UP and Running!' db_status from dual;" | sqlplus -s system/psw
exit 0

Change the ORACLE_SID and ORACLE_HOME variable values to your Oracle database information and change the password also for system user or you can use any other user credential details as you are executing just a simple query from dual. After completing the editing save this file and give a name, for this example we can assume file name as isdbup.sh.

Now to execute the script "isdbup.sh" first you need to make the script executable, so give the following chmod command to make the file executable:

$ chmod u+x isdbup.sh

The above command will change the permission for the owner (u) of the file to executable (x) and now you would be able to execute this script as following:

$ isdbup.sh

db_status
---------------
Database is UP and Running!

Check the following 8 Links for best Oracle Forms examples with source code (Fmb files), which will work in any version of Oracle Forms. The examples are given for "Oracle Form's Triggers", "Hierarchical Trees", "Stacked Canvases", "Alerts", "List Item", "From Clause Query", "Data Filter" and on "Conditional Forms". These are also most viewed and liked posts of this blog and I am listing below so that these posts can not be missed from anyone. The following are the topics:

  1. Oracle Form's Trigger Example with Sample Form Module
  2. Create Hierarchical Tree in Oracle Forms.
  3. Learn to Create Stacked Canvas to Scroll Horizontal Tabular Data Block
  4. Learn to Create Dynamic Alert for All Types of Messages.
  5. Populate List Item Example - Oracle Forms
  6. From Clause Query Data Block Example - Oracle Forms
  7. Filter Records Before Query by Custom Data Filter
  8. Example for: If Value Exists Then Query Else Allow Create New Record

8 Most Required Examples for Oracle Forms

LockerDome is an interest based social network website founded in 2008. LockerDome - as it says, it helps you personalize the web. Yes with Lockerdome you can view the web content topic wise and also you can share your website or blog's content with Lockerdome in a particular topic or category. Means you don't need to add list of websites or social pages, but a topic containing multiple articles from multiple websites or social pages. You can create your on personalized pages for your favorite topics and in Lockerdome it is called Lockers, in which you can add external content of your interests as Hang and also you can add your own blog or website's content. A viewer can Vote also in Lockerdome by making an article Interesting or Not Interesting. Below is the screen shot of Lockerdome page for Tech topic:

Lockerdome tech topic page

For me Lockerdome is a good option to share my blog's content across the web to target people looking for interest based content. It is having many other features you can check your self by visiting the site with the following link https://lockerdome.com/

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.