Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Oracle Database: INSERT INTO with TO_DATE function
Top Forums Programming Oracle Database: INSERT INTO with TO_DATE function Post 302466009 by durden_tyler on Monday 25th of October 2010 06:54:22 AM
Old 10-25-2010
Quote:
Originally Posted by Poonamol
...Now I got the solution of my problem, Here is shell script part of code change
Code:
DATE_FORMAT="YYYYMMDD"
DATE_FORMAT=`echo "'"$DATE_FORMAT"'"`
 
EMPNAME=`echo "'"$EMPNAME"'"`
EMPID=`echo "'"$EMPID"'"`
DATEGEN=`echo "'"$DATEGEN"'"`

Using above syntax I am able to insert records into my table as,
Code:
INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,$DATEFORMAT));

...
Yuck... haven't seen such a crude script in a long time!
Here's a more elegant version -

Code:
$
$
$ # show the content of the shell script
$
$ cat -n tst.sh
     1  #!/usr/bin/bash
     2  EMPNAME=$EMP_NAME;
     3  EMPID=$EMP_ID;
     4  DATEGEN=$DATE_GEN;
     5  DATEFORMAT=$DATE_FORMAT;
     6  sqlplus -s test/test <<EOF
     7  whenever sqlerror exit 1
     8  whenever oserror exit 2
     9  INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES('$EMPNAME',$EMPID,TO_DATE('$DATEGEN','$DATEFORMAT'));
    10  prompt One row inserted into table TBL1...
    11  EOF
    12  echo "Return value = $?"
$
$ # set the values of the shell variables and export them
$
$ EMP_NAME="ABC XYZ"     ; export EMP_NAME
$ EMP_ID=1001            ; export EMP_ID
$ DATE_GEN="01012010"    ; export DATE_GEN
$ DATE_FORMAT="MMDDYYYY" ; export DATE_FORMAT
$
$ # now run the shell script
$
$ ./tst.sh
 
1 row created.
 
One row inserted into table TBL1...
Return value = 0
$
$ # verify that the record was inserted
$
$ echo "select * from tbl1;" | sqlplus -s test/test
 
EMPNAME                             EMPID EMPBDATE
------------------------------ ---------- ---------
ABC XYZ                              1001 01-JAN-10
 
1 row selected.
 
$
$
$

tyler_durden
 

6 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

LEARN ABOUT PHP

oci_set_client_info

OCI_SET_CLIENT_INFO(3)													    OCI_SET_CLIENT_INFO(3)

oci_set_client_info - Sets the client information

SYNOPSIS

       bool oci_set_client_info (resource  $connection, string	$client_info)

DESCRIPTION

	Sets the client information for Oracle tracing.

	The  client  information  is registered with the database when the next 'roundtrip' from PHP to the database occurs, typically when an SQL
       statement is executed.

	The client information can subsequently be queried from database administration views such as V$SESSION.

	The value may be retained across persistent connections.

PARAMETERS

	      o $connection
		-An Oracle connection identifier, returned by oci_connect(3), oci_pconnect(3), or oci_new_connect(3).

	      o $client_info
		- User chosen string up to 64 bytes long.

RETURN VALUES

	Returns TRUE on success or FALSE on failure.

EXAMPLES

       Example #1

	      Setting the client information

	      <?php

	      $c = oci_connect('hr', 'welcome', 'localhost/XE');

	      // Record the client information
	      oci_set_client_info($c, 'My Application Version 2');

	      // Code that causes a roundtrip, for example a query:
	      $s = oci_parse($c, 'select * from dual');
	      oci_execute($s);
	      oci_fetch_all($s, $res);

	      sleep(30);

	      ?>

	      // While the script is running, the administrator can see the client
	      // information:

	      sqlplus system/welcome
	      SQL> select client_info from v$session;

NOTES

       Note

	      Oracle version requirement

	      This function is available when PHP is linked with Oracle Database libraries from version 10 g onwards.

       Tip

	      Performance

	      With older versions of OCI8 or the Oracle Database, the client information can be set using the Oracle  DBMS_APPLICATION_INFO  pack-
	      age. This is less efficient than using oci_set_client_info(3).

       Caution

	      Roundtrip Gotcha

	      Some  but  not  all  OCI8  functions  cause roundtrips. Roundtrips to the database may not occur with queries when result caching is
	      enabled.

SEE ALSO

       oci_set_module_name(3), oci_set_action(3), oci_set_client_identifier(3).

PHP Documentation Group 												    OCI_SET_CLIENT_INFO(3)
All times are GMT -4. The time now is 08:03 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy