Home » SQL & PL/SQL » SQL & PL/SQL » Need to insert values into table
Need to insert values into table [message #277340] Mon, 29 October 2007 12:14 Go to next message
rickymartin
Messages: 2
Registered: October 2007
Junior Member
Hey I need to insert values into table.So i used this code

ERR_STR="`$SQLPLUS -s << EOF $USERPASS
WHENEVER OSERROR EXIT OSCODE ROLLBACK;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
SET SERVEROUTPUT ON SIZE 999999
SET LINESI 200
SET FEEDBACK ON

declare
load_date date :='30-dec-2007';
dbms_output.put_line('entering');
begin
for i in 1..93
loop
insert into table_name values(2155,load_date);
load_date := load_date+1;
end loop;
end;
commit;
EXIT 0;
EOF`"

echo "$ERR_STR"

I want to print the message using dbms_output.put_line.
When I run the script it is saying ened ok but no values are inserted into the table. And what ever the messages given by sql will go into the err_str variable...right!

Thanks in advance
ricky
Re: Need to insert values into table [message #277371 is a reply to message #277340] Mon, 29 October 2007 16:17 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens when a date variable is not only declared as DATE, but used as a DATE? Instead of
load_date date :='30-dec-2007';
use
load_date date := TO_DATE('30-dec-2007', 'dd-mon-yyyy');


SQL> CREATE TABLE TEST (load_DATE DATE);

Table created.

SQL> DECLARE
  2    load_date DATE := TO_DATE('30.12.2007', 'dd.mm.yyyy');
  3  BEGIN
  4    FOR i IN 1 .. 5 LOOP
  5      INSERT INTO TEST VALUES (load_date);
  6      load_date := load_Date + 1;
  7    END LOOP;
  8    COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select * From test;

LOAD_DAT
--------
30.12.07
31.12.07
01.01.08
02.01.08
03.01.08
Previous Topic: File permissions
Next Topic: Index on Date Field
Goto Forum:
  


Current Time: Fri Dec 09 05:40:56 CST 2016

Total time taken to generate the page: 0.11248 seconds