Sitemap

Amicuk Programming Answers

Select data fields with delimiter

2015-10-11   Views:0

Advertisement

hi, im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data

hi,
im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data, nt even the column names.
Format required-
data1|data2|data3
data11|data22|data33
Pls help.

The replay answer
Advertisement
The above answer will give data you can spool to a file in SQL*Plus.
If you want to write the data to a file from within PL/SQL, and you need something a little more generic to use for different queries, you can use something along these lines...
As sys user:
CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
/As myuser:
CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                     ,p_dir IN VARCHAR2
                                     ,p_header_file IN VARCHAR2
                                     ,p_data_file IN VARCHAR2 := NULL) IS
  v_finaltxt  VARCHAR2(4000);
  v_v_val     VARCHAR2(4000);
  v_n_val     NUMBER;
  v_d_val     DATE;
  v_ret       NUMBER;
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num     NUMBER;
  v_fh        UTL_FILE.FILE_TYPE;
  v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
  d := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
  FOR j in 1..col_cnt
  LOOP
    CASE rec_tab(j).col_type
      WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
      WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
      WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
    END CASE;
  END LOOP;
  -- This part outputs the HEADER
  v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
  FOR j in 1..col_cnt
  LOOP
    v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
  END LOOP;
  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
  UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  IF NOT v_samefile THEN
    UTL_FILE.FCLOSE(v_fh);
  END IF;
  -- This part outputs the DATA
  IF NOT v_samefile THEN
    v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
  END IF;
  LOOP
    v_ret := DBMS_SQL.FETCH_ROWS(c);
    EXIT WHEN v_ret = 0;
    v_finaltxt := NULL;
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                    v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
        WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                    v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
        WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                    v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
      ELSE
        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
      END CASE;
    END LOOP;
  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
    UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
  END LOOP;
  UTL_FILE.FCLOSE(v_fh);
  DBMS_SQL.CLOSE_CURSOR(c);
END;This allows for the header row and the data to be written to seperate files if required.
e.g.
SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
PL/SQL procedure successfully completed.Output.txt file contains:
empno,ename,job,mgr,hiredate,sal,comm,deptno
7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.
Adapt to output different datatypes and styles are required.

Go to See the other 13 answers

Select data fields with delimiter

Category:DefaultRelease time:2015-10-11Views:130

hi, im new to oracle. I'm working on unix oracle 11g. I need to know the way to fetch records with delimiters. I'd worked with informix before, and thr i use UNLOAD TO <filename> SELECT.... Is there any equivalent one in oracle. I just need the data[More]

Multi select date field

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Is there any idea how to combine multi select DATE field in the check-in screen? Thanks, eran.Dates are stored using a DATE field in the database in a database date format. As such, multiple values can not be stored in the same field, only single[More]

Error to select data field of same length and same type.

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have to select data from VBRK , VBRP and then from table CE4UCA1_ACCT based on the condition AKTBO   IN SO_AKTBO           AND PAOBJNR = LT_VBRK_VBRP-POSNR  AND PASUBNR IN SO_RKBNR           AND PRCTR   IN SO_PRCTR. So while coding it gave a sy[More]

Selecting Date fields from selectOneChoice box

Category:DefaultRelease time:-0001-11-30Views:130

Hi all i am having the below problem. i have a SelctOneChioce Box which has a list of TimeStampObject from a View object. i am getting a below error when i selct any values from the drop down list and submit the page. *Cannot convert 2011-08-03 00:00[More]

Visio 2010 - Shape Data Fields Are Not Saving

Category:DefaultRelease time:2015-10-11Views:130

I am having a problem with a number of shape data fields which are not saving data that has been entered into it, whether by entering it through the "shape data" or "define shape data" dialog boxes, or by editing the "shape sheet&[More]

Selection date in Delivery creation

Category:DefaultRelease time:-0001-11-30Views:130

Hello, If the schedule line is not confirmed in sales order (due to zero atp stock i think ) and if I try to create a delivery, system will give a response that " no schedule lines are due for delivery  with a yellow question mark against the message[More]

Select-options for date field.

Category:DefaultRelease time:2015-10-11Views:130

Hi all, i need to give select options for Date field.How can i give that. Thanks & Regards Ravi.Hi Ravi, Use the Component WDR_SELECT_OPTIONS to include select options in Web Dynpro ABAP. Follow these steps: 1. In your Component , "Used Componet&[More]

Date field not updated when select on F4 value on editable ALV Grid

Category:DefaultRelease time:2015-10-11Views:130

Can some one look into this to see what was wrong. I have a report that display fields extracted from a ZTable and display on an ALV Editable Grid. The data are displayed as read only mode for these fields: Field A -   type char20 and have a search h[More]

How to select data from a table using a date field in the where condition?

Category:DefaultRelease time:2015-10-11Views:130

How to select data from a table using a date field in the where condition? For eg: data itab like equk occurs 0 with header line. select * from equk into table itab where werks = 'C001'                                                   and bdatu = '3[More]

In AP invoice i need to make "Terms date " field to calculate the date automatically when i select the payment terms

Category:DefaultRelease time:2015-10-11Views:130

In AP invoice i need to make "Terms date " field to calculate the date automatically when i select the payment terms. How can i do this?Hi, You can select the Payment Term at PO Header Level. When you book an invoice, that Payment Term will auto[More]

Date field on Selection Screen

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I need to create Date field (From and To) in the selection screen. The Date format should appear as YYYY-MM-DD. I have defined this requirement as below. select-options: s_date for sy-datum no-extension default sy-datum. Due to above line, da[More]

Hot
Hello: hummmm! need help in create jsp (xml, xsl, xslt or whatever) whit dinamic html components events like checkbox, i.e: When i click in a checkbox or radio button, the action (event) must make me generate dinamyc html code like show fields or for [More]
Hi, I'm trying to use the commons-fileupload-1.1.jar file and I've placed it in my WEB-INF/lib folder. When I try to compile my java though, I get an error that reads: package org.apache.commons.fileupload does not exist. I'm importing like this: imp [More]
Hi, Is anyone else having problems after applying the latest AirPort Base Station update (version 5.1) found here http://www.apple.com/support/downloads/airportbasestationupdate2007001forwindows .html Before this update I was able to use the AirPort [More]
1. Is it possible in java to change the screen resolution with some method? 2. Is it possible to change color mode(16bit, 32bit) with some method in java, and if so is there a point in doing so? 3. If you make an application with a "background" [More]
okay so on steam im trying to play Garrys Mod and every time i open it the game closes and gives me an error saying "hl2.exe has stopped working." it happens on all valve games. ive tried everything and now i just want to try to uninstall direct [More]
Hello, we have developed a Webdynpro application that stores and retrives data from a MS SQL Server DB. We store a String in a vchar(500). If we read the value directly from the MS SQL Server, all the characters appear, but if we use the method getSt [More]
Just got an iPod nano, and need some help,thanks in advance for any help you can give. Now onto my problem,when I plug my nano into my PC it appears as a removeable disk in the My Computer file, I open up the removeable file and four folders appear. [More]
Hello, When I'm using jdeveloper, and changing the window to another application (i.e: notepad), when I try to return to the Jdeveloper window, it is frozen for several seconds. I KNOW that the problem is that Jdeveloper tries to connect somewhere in [More]
Hello, So I've seen many topics with varying problems with the recent iTunes Update. After the last update my itunes is completely screwed. I've tried everything in all the other topics but nothing seems to work. I think it is related to the Apple Ap [More]
I have tried twice to upgrade to the Student membership of Creative Cloud and have provided my credit card details twice but have yet to be upgraded... what is going on?Hi NyssaM It looks like the orders haven't been approved.  Let me check this with [More]