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
How can I copy multiple slides with a HP Scanjet 4050 to photoes using Maverick? HP apparently no longer supports this product for OS Maverick.VueScan VueScan FAQRead other 4 answers [More]
Hi GURU's My client is asking about automate GR against Purchase Order when he confirm the porcess order of GR consume material means when he confirm the process order, the GR is created for the comsume material( GR must be equal to consume qnty) aga [More]
I've found no way to sort search results in itunes.  For example, if I type the group "Rush" into the search bar, I get a list of songs recorded by rush and literally thousands of other songs and artists with the word rush in them. There is; how [More]
It does not seem to show the dimensional attributes and all the pretty pictures in the blogs/forums seem to work!!!! Can you tell me if I am missing something?Hi, View Generator appears to be an OLAP product, which is distinct from Workspace Manager. [More]
When i burn cds, they wont play on any other device?  what am i doing wrong?Have no clue! What type of CDs are you using?  What are you burning?  How are you burning?  What software and/or app are you using?  Can you play them on your computer?  ==== [More]
is there any alternative can i user for in, not in, like to optimize this sql statement. SELECT TKTNUM||'~'|| CUSNAME||'~'|| DECODE(PRTY,0,'PRIORITY 00', 1,'PRIORITY 01' ,2,'PRIORITY 02', 03,'PRIORITY 03', 04,'PRIORITY04','OTHERS') ||'~'|| TO_CHAR(NE [More]
Hi Experts, We have two environments Informatica 8.6.1 and 9.5.0 that we need to merge into a single one. Wanted suggestions on the best way to migrate. 1) Considering codepages are same, can 8.6.1 Deployment Groups be copied to 9.5.0 repository dire [More]
Hi , I have the following File[] File[] file1 = (File[]) ((List) t.getTransferData(DataFlavor.javaFileListFlavor)).toArray();At some point, I need to convert this File[] file1 to List<File> How can I do it? Please help. Any help in this regard will [More]
hi I get this error when I am trying to run a report what does this mean REP-1212: Object 'Body' is not fully enclosed by its enclosing object ''. and too whe can I find explanation using the error id like (REP-1212). Thanks in advance Ramkumarhello, [More]
Hi All, I have a scenario in GP. The Interactive Adobe Form is developed on NWDS. User1 signs on the Form and Submits to User2 for further processing. 1) Now, can User2 be made to view the digital signature of User1? 2) If yes, how is the digital sig [More]