Login or Register to Ask a Question and Join Our Community


Programming

Oracle Database: INSERT INTO with TO_DATE function

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Oracle Database: INSERT INTO with TO_DATE function
# 1  
Old 10-20-2010
Oracle Database: INSERT INTO with TO_DATE function
Hello,

I am writting a procedure in shell script as,
Code:
set serveroutput on
declare
date_gen DATE := $DATEGEN;
begin
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE(date_gen,'YYYY-MM-DD'));
end;
/

Where DATEGEN is unix string variable which I need to use into INSERT INTO statement.
I am receiving error message as,
Code:
SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11  date_gen DATE := 20100913;
                 *
ERROR at line 3:
ORA-06550: line 3, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 10:
PL/SQL: Item ignored
ORA-06550: line 7, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored

How can I assign string variable to date_gen DATE; in procedure?
How can I use this date into INSERT statement?
Could you please help me out in inserting data into table with TO_DATE function.
Please help me out...
Thanks in advance.
# 2  
Old 10-20-2010
What happens if you change the third line to:
Code:
date_gen DATE := "$DATEGEN";

# 3  
Old 10-20-2010
Quote:
Originally Posted by Poonamol
....
I am writting a procedure in shell script as,
Code:
set serveroutput on
declare
date_gen DATE := $DATEGEN;
begin
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE(date_gen,'YYYY-MM-DD'));
end;
/

Where DATEGEN is unix string variable which I need to use into INSERT INTO statement.
I am receiving error message as,
Code:
SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11  date_gen DATE := 20100913;
                 *
ERROR at line 3:
ORA-06550: line 3, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 3, column 10:
PL/SQL: Item ignored
ORA-06550: line 7, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored

...

Change this line in your script -

Code:
date_gen DATE := $DATEGEN;

to this -

Code:
date_gen VARCHAR2(10) := '$DATEGEN';

(A)
Quote:
How can I assign string variable to date_gen DATE; in procedure?
Before you understand how you can assign a string to a DATE variable, you need to understand that it's not a good practice to do so. Because you are relying on Oracle's implicit conversion rules by doing so.
So instead of doing that, declare the variable as a string and assign a string to it.

(B)
Quote:
How can I use this date into INSERT statement?
Just stick in the variable in your INSERT statement.
Of course, if you follow (A), then you'll have to convert your variable (which is a string) to a date first.

Code:
VALUES($EMPNAME,$EMPID,TO_DATE(date_gen,'YYYY-MM-DD'));

TO_DATE => converts string to date
TO_CHAR => converts date to string

You are using TO_DATE to convert a DATE (the variable date_gen) to a DATE. Again, that's not a good practice.

tyler_durden

Note:
(Oracle actually allows a date to be converted to a date using the TO_DATE function; it's an unintended consequence of implicit conversion.)
# 4  
Old 10-21-2010
Thanks for reply.
I changed code as,
Code:
INS_REC=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,'YYYY-MM-DD'));
end;
/
EOF)

Now I am receiving error as,
Code:
SQL> TO_DATE (20100913,"YYYY-MM-DD")
*
ERROR at line 8:
ORA-00984: column not allowed here

Pleae help me out.
Thanks in advance.
# 5  
Old 10-21-2010
Quote:
Originally Posted by Poonamol
...
I changed code as,
Code:
INS_REC=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,'YYYY-MM-DD'));
end;
/
EOF)

Now I am receiving error as,
Code:
SQL> TO_DATE (20100913,"YYYY-MM-DD")
*
ERROR at line 8:
ORA-00984: column not allowed here

Pleae help me out.
...
Nope, that's not what I had suggested to do in my post. Do yourself a favor - read the post again and follow the instructions in there.

Sorry, I can't help you unless you put in some effort to actually read and understand my post.

tyler_durden
# 6  
Old 10-21-2010
Now I am receiving error for $DATEGEN as,
Code:
declare
date_gen VARCHAR2(10) := "$DATEGEN";
begin
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES(BBBBB,DDDD2,TO_DATE(date_gen,'YYYY-MM-DD'));
end;
/

INS_REC=
SQL*Plus: Release 9.2.0.5.0 - Production on Thu Oct 21 08:32:10 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16  date_gen VARCHAR2(10) := "$DATEGEN";
                         *
ERROR at line 2:
ORA-06550: line 2, column 26:
PLS-00201: identifier '$DATEGEN' must be declared
ORA-06550: line 2, column 10:
PL/SQL: Item ignored
ORA-06550: line 11, column 23:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
ORA-06550: line 14, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement


SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

Its not taking value of $DATEGEN.
here is script part,
Code:
set -vx
REC=$(sqlplus.......
set serveroutput on
declare
date_gen VARCHAR2(10) := '$DATEGEN';
begin
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE(date_gen,'YYYY-MM-DD'));
end;
/

If i changed the code as,
Code:
date_gen VARCHAR2(10) := $DATEGEN;

Here are the values, but its not reflecting in date_gen variable.
Code:
set serveroutput on
declare
date_gen VARCHAR2(10) := 20100913;
begin
INSERT INTO BIOS_TRANSCODING (  OLD_ID_CSU,
                                NEW_ID_CSU,
                                MIGRATION_DATE
                                )
                                VALUES (
                                BBBBBB,
                                BDBDBD,
                                TO_DATE (date_gen,"YYYYMMDD")
                                );

Last edited by Poonamol; 10-21-2010 at 03:41 AM.. Reason: code modified
# 7  
Old 10-21-2010
Quote:
Originally Posted by Poonamol
...
If i changed the code as,
Code:
date_gen VARCHAR2(10) := $DATEGEN;

...
Why ??

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

6 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

Identify a specific environment Oracle variable to connect a remote Oracle database ?

Good evening I nned your help pls, In an unix server i want to connect to a remote oracle databse server by sqlplus. I tried to find out the user/passwd and service name by env variable and all Ive got is this: ORACLE_SID_REPCOL=SCL_REPCOL ORACLE_SID=xmeta ORACLE_SID_TOL=SCL_PROTOLCOL... (2 Replies)
Discussion started by: alexcol
2 Replies

2. Programming

Need help on Insert data to mySQL database

Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my sensor mySQL database in phpMyAdmin from my control.php. I'm using Raspberry PI as my hardware and follow a few tutorials to create my own Web Control Interface, it works perfectly without insert method.... (1 Reply)
Discussion started by: aoiregion
1 Replies

3. Shell Programming and Scripting

Pass a variable string in To_Date Oracle function in shell script

Hello, I am trying to execute an SQL query from shell script. A part of script is something like this: fromDate=`echo $(date +"%F%T") | sed "s/-//g" | sed "s/://g"` $ORACLE_HOME/sqlplus -s /nolog <<EOD1 connect $COSDBUID/$COSDBPWD@$COSDBSID spool... (4 Replies)
Discussion started by: sanketpatel.86
4 Replies

4. Solaris

Can't create database after Oracle Database installation

I installed Oracle 10 software on Solaris 11 Express, everything was fine execpt I can't create database using dbca.rsp file. I populated file with following options. OPERATION_TYPE = "createDatabase" GDBNAME = "solaris_user.domain.com" SID = "solaris_user" TEMPLATENAME = "General... (0 Replies)
Discussion started by: solaris_user
0 Replies

5. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

6. Shell Programming and Scripting

Insert value of vmstat to database.

Hi guys , I m trying to store the output of vmstat 1 10 into a database. I have done the necessary homework to connect bash to interact with the database. Program logic. 1)storing the output of vmstat 1 10 to a file named abc.txt 2)I m applying a filter to vmstat to get desired output... (4 Replies)
Discussion started by: pinga123
4 Replies
Login or Register to Ask a Question