SQL & PL/SQL :: Unable To Load Date Column Into CSV

May 10, 2012

I am using this procedure to load oracle data into csv file.

/* Formatted on 2012/05/10 17:18 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE generate_order_csv (
p_dir IN VARCHAR2,
p_file_name IN VARCHAR2,
p_data_file IN VARCHAR2 := NULL
)

[Code]...

-- This part outputs the HEADER

v_fh := UTL_FILE.fopen (UPPER (p_dir), p_file_name, 'w', 32767);

FOR j IN 1 .. col_cnt
LOOP
v_finaltxt :=
LTRIM (v_finaltxt || '|' || LOWER (rec_tab (j).col_name), '|');

[Code]...
.
-- 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;

[Code]..

When I execute this procedire I am facing two columns.

1) First column ORDER DATE is not laoding into .csv but when I execute query from SQL, I am able to see the date column values.

2) There is an overlapping of the result string. Last column is coming up into second row.

what changes to be done?

View 14 Replies


ADVERTISEMENT

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related

Unable To Load Data In Oracle 11g From XLS File?

Sep 6, 2011

CASE 1:

when i tried to load the data i got the below error,

Error starting at line 2 in command:
INSERT INTO RECON_MATCHED_DETAILS (RECON_MATCHED_DETAIL_OID, RECON_ID, STATEMENT_DATE, EXECUTION_DATE, TRANSACTION_NUMBER, TRANSACTION_DATE, TRADE_ID, TRANSACTION_TYPE, LINK_ID, ITEM_TYPE, ASSET_CODE, ISIN, BUYSELL_INDICATOR, SETTLEMENT_DATE, CURRENCY, QUANTITY, VALUE,

[code]...

CASE 2:

i tried to load the data in oracle 11g but i'm unable to load the data,and for testing i tried with a single row of data.but surprisingly the table filled with (null)s

View 3 Replies View Related

Forms :: Frm-18108 / Unable To Load The Objects

May 3, 2010

I use a form (ex. a.fmb) as a reference for another form (ex. b.fmb) . after I subclass the a.fmb into b.fmb and generate it,it works will. but next time I want to open b.fmb I get this error :

frm-18108 unable to load the following objects

source module: A.FMB
SOURCE OBJECT:STD_OBJECT_GROUP
source module: A
SOURCE OBJECT: BUTTON

View 2 Replies View Related

Unable To Load Dynamic Library Oci8.so?

Oct 23, 2013

When I try to start lampp, I am getting the below error. 

PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php/extensions/no-debug-non-zts-20090626/oci8.so' - libclntsh.so.10.1: cannot open shared object file: No such file or directory in Unknown on line 0

But I have  libclntsh.so.11.1 in my system. Why it is searching for libclntsh.so.10.1? 

View 3 Replies View Related

SQL & PL/SQL :: Unable To Load A Flat File Through Oracle Loader?

Sep 28, 2011

Issue: Unable to load a flat file through Oracle Loader

Below is the script that is being used:

drop table dl_fact_fac_data_xtern;
create table dl_fact_fac_data_xtern
(

[Code].....

After rnning this script, it prompts that table has been created; but once I fire the select command on the table I receive the following errors :

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 "data": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 10 column 11
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.

View 2 Replies View Related

Forms :: 91120 Internal Error - Unable To Load NLS Character Set?

Jun 3, 2011

I'm facing this error: FRM-91120: Internal Error: Unable to load NLS character set

when I try to run command runform50.

I've set my NLS_LANG to American_America.WE8ISO8859P1 (which the the same as my DB),
I've set the ORA_NLS33, ORA_NLS32 and ORA_NLS vars to /usr/oracle/OraHOme1/ocommon/nls/admin/data
I've set the NLSPATH to /usr/lib/nls/msg/%L/%N

If I run runform50 command without any options, it shows me the help file, so I think it is correctly installed..

I guess the same thing as you're probably thinking .. that I'm missing some charset ... but where ? they should be in ocommon/nls dir ? if so, I've already installed pretty much everything of the developer2000 cd

View 6 Replies View Related

Server Utilities :: How To Load Date Field Along With Timestamp Using Sqlldr

Apr 17, 2013

I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format

100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM

how to load the date filed along with the time stamp.

View 2 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

View 10 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

Server Utilities :: Loading Date Value Into DATE Column?

Oct 18, 2012

I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.

View 6 Replies View Related

Server Utilities :: How To Load Date And Time From Text File To Oracle Table Through Sql Loader

May 31, 2010

what i miss to load date and time from text file to oracle table through sqlloader

this is my data in this path (c:externalmy_data.txt)

7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30

my table in database emp2

create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);

the control file code in this path (c:externalctrl.ctl)

load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)

this is the error :

C:>sqlldr scott/tiger control=C:externalctrl.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
C:>

View 21 Replies View Related

Server Utilities :: To Load Single Column Data Into Multiple Columns

Mar 26, 2012

I want to load single column data into multiple columns.

View 3 Replies View Related

PL/SQL :: Unable To Round Date Function?

Feb 21, 2013

cant i round the date like this? or only sysdate can be rounded or if the date is stored in a variable can only be rounded

select round('21-feb-1999','mon') from dual;

View 2 Replies View Related

SQL & PL/SQL :: Converting Number Column To Date Column

Dec 25, 2012

I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD

CREATE TABLE T1.monthly
(
SEQ_NUM NUMBER(10) NOT NULL,
DAY_DK NUMBER(10) NOT NULL
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
[code]........

some sample data

SEQ_NUM DAY_DK
---------- ----------
990 20121225
991 20121225
992 20121225
993 20121225
994 20121225
995 20121225
996 20121225
997 20121225
998 20121225
999 20121225

When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.

desc t1.mth; ### my temorary table
Name Null? Type
----------------------------------------- -------- ----------------------------
SEQ_NUM NUMBER(10)
DAY_DK NUMBER(10)

Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.

alter table n546830.mth modify (DAY_DK date);

Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:

alter table t1.monthly exchange partition DEC_2012
with table t1.mth without validation;
alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"

View 12 Replies View Related

Forms :: FRM-40513 / Unable To Get Date / Time From Database

Sep 25, 2013

after spent a lot of time surfing the web looking for this error frm-40513 i did not found any answer about this, So I've removed the date items on my canvas and ran perfectly... after that i opened another copy of the form. In one item there was a initial value $$DBDATETIME$$ .. so that was the error.. i removed that value and i ran my form 11g (weblogic) again and the error has gone.

View 6 Replies View Related

Forms :: Unable To Get Records While Queried Through Date Field In Oracle

Oct 16, 2012

In a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records.

View 10 Replies View Related

PL/SQL :: Unable To Drop Unused Column?

Nov 14, 2013

SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER                          TABLE_NAME                          COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER            F_TRADING_COLLATERAL_SR                1 SQL>

View 6 Replies View Related

SQL & PL/SQL :: Unable To Rename A Column For Original Table?

Feb 14, 2012

i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show user
USER is "WEBSYSTEMD6"
SQL> alter table cbe rename column tnum to tnum_old;
alter table cbe rename column tnum to tnum_old
*
ERROR at line 1:
ORA-00900: invalid SQL statement

SQL> create table cbe_test as select * from cbe;

Table created.

SQL> alter table cbe_test rename column tnum to tnum_old;

Table altered.

SQL>

View 12 Replies View Related

SQL & PL/SQL :: Order By In Date Column

May 20, 2013

I am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.

select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments
where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC

Above SQL doesn't gives the output in proper order ,

View 3 Replies View Related

PL/SQL :: Update A DATE Column

Mar 14, 2013

There is a need for me to UPDATE a DATE column from one date to another. So as an example,

we have TABLE A and Table A has a column (DATE_TO) with date field value off

DATE_TO
31-DEC-99
12-APR-15
15-MAR-16

What I want to do is update '31-DEC-99' to '31-MAR-13' and for it to only effect '31-DEC-99' (the others are fine).

View 11 Replies View Related

PL/SQL :: How To Set Primary Key For A Date Column

May 28, 2013

i need to set primary key for a column consists of the datatype date.

View 5 Replies View Related

PL/SQL :: How To Select Max Value Of Date Column

Feb 14, 2013

How to select max value of date column which tables are having date coulmn.

View 1 Replies View Related

PL/SQL :: Substr Of Date As Column Name

Aug 27, 2013

Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this select s.xyz Today,s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'from sales_tab s.

View 9 Replies View Related

PL/SQL :: Index On Date Column

Aug 2, 2012

Is the index suggested on the date column.

Here is the nature of the date column in my case:

1. The table populates with 1000's of records every day with date being always incremental (current date).

2. The search criteria from the weba application (ADF) is based on the date, user gives the range.

3. From ADF I am referring to it as sql.Timestamp when building the query.

Does Index suggested on the date column here and if so what type of index ?

View 4 Replies View Related

SQL & PL/SQL :: Date Dimension With Column

Mar 14, 2011

creating a date dimension with columns like

id month_name date
1 Jan/2011 31/1/2011 (last date of the month)
2 feb/2011 28/2/2011!!

View 9 Replies View Related

Forms :: How To Add Date Column

Nov 12, 2011

i want to add date column.i need that if user enter date like'23-jan-80' or '23-01-1980' system accept it and save in database.

View 3 Replies View Related

Getting Time From Date Type Column?

Mar 3, 2011

I am working with Oracle 8i and I have a problem while applying the below query:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00

[code].....

The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27

I am using the same query on a different table and there's no problem on it:

select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21

I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved