Home » Developer & Programmer » Forms » how to pass excel general value to database tables date field (form 6i)
| how to pass excel general value to database tables date field [message #587594] |
Mon, 17 June 2013 05:44  |
 |
sameer_da
Messages: 74
Registered: April 2013
Location: India
|
Member |
|
|
i've following code
declare
i INTEGER:=0;
flag boolean:= FALSE;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;
vcInsert:=vcInsert||i_vcColumn1;
vcInsert:=vcInsert||') values(';
if length(i_vcColumn1) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).VTDATE||''''; --to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
---vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
end if;
i am fetching data from excel all data is of general type,
here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE ,
How can i pass '13-06-13 05:54:33' to database table.
if i put this condition
vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS')
i get the error
to_char must be declare.
if i remove this line
i am getting this error
ORA-01843: not a valid month
what should i do? please help.
[Updated on: Mon, 17 June 2013 06:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to pass excel general value to database tables date field [message #587729 is a reply to message #587607] |
Tue, 18 June 2013 05:24  |
|
sameer_da
Messages: 74
Registered: April 2013
Location: India
|
Member |
|
|
hello cookiemonster,
here is my full code
PACKAGE BODY PK_EXCEL_TO_DB IS
PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_vcColumn5 IN VARCHAR2,i_vcColumn6 IN VARCHAR2,i_vcColumn7 IN VARCHAR2,
i_vcColumn8 IN VARCHAR2,i_vcColumn9 IN VARCHAR2,i_vcColumn10 IN VARCHAR2,i_vcColumn11 IN VARCHAR2,i_vcColumn12 IN VARCHAR2,i_vcColumn13 IN VARCHAR2,i_vcColumn14 IN VARCHAR2,
i_vcColumn15 IN VARCHAR2,i_vcColumn16 IN VARCHAR2,i_vcColumn17 IN VARCHAR2,i_vcColumn18 IN VARCHAR2,i_vcColumn19 IN VARCHAR2,i_vcColumn20 IN VARCHAR2,i_vcColumn21 IN VARCHAR2,
i_vcColumn22 IN VARCHAR2,i_vcColumn23 IN VARCHAR2,i_vcColumn24 IN VARCHAR2,i_vcColumn25 IN VARCHAR2,i_vcColumn26 IN VARCHAR2,i_vcColumn27 IN VARCHAR2,i_vcColumn28 IN VARCHAR2,
i_vcColumn29 IN VARCHAR2,i_vcColumn30 IN VARCHAR2,i_vcColumn31 IN VARCHAR2,i_vcColumn32 IN VARCHAR2,i_vcColumn33 IN VARCHAR2,i_lData IN tDataList) IS
vcInsert VARCHAR2(3500);
i INTEGER:=0;
flag boolean:= FALSE;
BEGIN
i:=i_lData.COUNT;
:BLK_MAIN.T4:= vcInsert;
vcInsert:='INSERT INTO CRM_DELT_IMPORT (';
if length(i_vcColumn1) > 0 then
flag:=TRUE;
vcInsert:=vcInsert||i_vcColumn1;
end if;
if length(i_vcColumn2) > 0 then
IF flag THEN
vcInsert:=vcInsert|| ','||i_vcColumn2;
else
vcInsert:=vcInsert||i_vcColumn2;
end if;
end if;
if length(i_vcColumn3) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn3;
end if;
if length(i_vcColumn4) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn4;
end if;
if length(i_vcColumn5) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn5;
end if;
if length(i_vcColumn6) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn6;
end if;
if length(i_vcColumn7) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn7;
end if;
if length(i_vcColumn8) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn8;
end if;
if length(i_vcColumn9) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn9;
end if;
if length(i_vcColumn10) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn10;
end if;
if length(i_vcColumn11) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn11;
end if;
if length(i_vcColumn12) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn12;
end if;
if length(i_vcColumn13) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn13;
end if;
if length(i_vcColumn14) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn14;
end if;
if length(i_vcColumn15) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn15;
end if;
if length(i_vcColumn16) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn16;
end if;
if length(i_vcColumn17) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn17;
end if;
if length(i_vcColumn18) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn18;
end if;
if length(i_vcColumn19) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn19;
end if;
if length(i_vcColumn20) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn20;
end if;
if length(i_vcColumn21) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn21;
end if;
if length(i_vcColumn22) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn22;
end if;
if length(i_vcColumn23) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn23;
end if;
if length(i_vcColumn24) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn24;
end if;
if length(i_vcColumn25) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn25;
end if;
if length(i_vcColumn26) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn26;
end if;
if length(i_vcColumn27) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn27;
end if;
if length(i_vcColumn28) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn28;
end if;
if length(i_vcColumn29) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn29;
end if;
if length(i_vcColumn30) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn30;
end if;
if length(i_vcColumn31) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn31;
end if;
if length(i_vcColumn32) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn32;
end if;
if length(i_vcColumn33) > 0 then
vcInsert:=vcInsert|| ','||i_vcColumn33;
end if;
--
vcInsert:=vcInsert||') values(';
--
if length(i_vcColumn1) > 0 then
vcInsert:=vcInsert||''''||i_ldata(i).V1||'''';
end if;
if length(i_vcColumn2) > 0 then
IF flag THEN
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V2||'''';
else
vcInsert:=vcInsert|| ''''||i_ldata(i).V2||'''';
end if;
end if;
if length(i_vcColumn3) > 0 then
vcInsert:=vcInsert|| ','||''''||to_date(i_ldata(i).V3,'DD-MM-RR HH24:MI:SS')||'''';
end if;
if length(i_vcColumn4) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V4||'''';
end if;
if length(i_vcColumn5) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V5||'''';
end if;
if length(i_vcColumn6) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V6||'''';
end if;
if length(i_vcColumn7) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V7||'''';
end if;
if length(i_vcColumn8) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V8||'''';
end if;
if length(i_vcColumn9) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V9||'''';
end if;
if length(i_vcColumn10) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V10||'''';
end if;
if length(i_vcColumn11) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V11||'''';
end if;
if length(i_vcColumn12) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V12||'''';
end if;
if length(i_vcColumn13) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V13||'''';
end if;
if length(i_vcColumn14) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V14||'''';
end if;
if length(i_vcColumn15) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V15||'''';
end if;
if length(i_vcColumn16) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V16||'''';
end if;
if length(i_vcColumn17) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V17||'''';
end if;
if length(i_vcColumn18) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V18||'''';
end if;
if length(i_vcColumn19) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V19||'''';
end if;
if length(i_vcColumn20) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V20||'''';
end if;
if length(i_vcColumn21) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V21||'''';
end if;
if length(i_vcColumn22) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V22||'''';
end if;
if length(i_vcColumn23) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V23||'''';
end if;
if length(i_vcColumn24) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V24||'''';
end if;
if length(i_vcColumn25) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V25||'''';
end if;
if length(i_vcColumn26) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V26||'''';
end if;
if length(i_vcColumn27) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V27||'''';
end if;
if length(i_vcColumn28) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V28||'''';
end if;
if length(i_vcColumn29) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V29||'''';
end if;
if length(i_vcColumn30) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V30||'''';
end if;
if length(i_vcColumn31) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V31||'''';
end if;
if length(i_vcColumn32) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V32||'''';
end if;
if length(i_vcColumn33) > 0 then
vcInsert:=vcInsert|| ','||''''||i_ldata(i).V33||'''';
end if;
vcInsert:=vcInsert||')';
FORMS_DDL(vcInsert);
:BLK_MAIN.T4:= vcInsert;
--END LOOP;
STANDARD.COMMIT;
END;
END;
in the insert statement, when i try to insert value at V3
if i use TO_DATE then no error is generated and value gets inserted into table but ONLY DATE NO TIME
if i use TO_CHAR then i get error
Error 307 at line ..
too many declarations of 'TO_CHAR' match this call
Please suggest something.
Thanks
Sameer
|
|
|
|
| Re: how to pass excel general value to database tables date field [message #587740 is a reply to message #587729] |
Tue, 18 June 2013 06:00 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As I said to_date itself needs to be in the string. Not the result of to_date.
You're getting a string that looks like this:
INSERT INTO CRM_DELT_IMPORT (.......) values (....., '18-JUN-2013', ....);
You're getting it in that format because you are concatenating the result of the to_date to a string. To do this oracle has to implicitly convert the date back to a char. It'll do this with the default date format - which I assume doesn't contain a time component.
You need a string that looks like this:
INSERT INTO CRM_DELT_IMPORT (.......) values (....., to_date('18-JUN-2013 12:00:00', 'DD-MON-YYYY HH24:MI:SS'), ....);
So the to_date command is in the final insert statement that is run and no implicit conversion happens.
So play around with the quotes in your code until you get the result above.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:02:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
