undefine dates
declare
v_dateInput VARCHAR(10);
v_dates DATE;
begin
v_dateInput := &&dates;
v_dates := to_date(v_dateInput, dd-mm-yyyy );
DBMS_OUTPUT.put_line(v_dates);
end;
Not sure why whenever I run this code with ,for example , input of 03-03-1990, this error shows up.
Error report:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 6
01847. 00000 - "day of month must be between 1 and last day of month"
*Cause:
*Action:
Ha, good one. That s because && replaces the variable in-place, so your script becomes:
declare
v_dateInput VARCHAR(10);
v_dates DATE;
begin
v_dateInput := 03-03-1990;
v_dates := to_date(v_dateInput, dd-mm-yyyy );
DBMS_OUTPUT.put_line(v_dates);
end;
Note the absence of quotes. v_dateInput is, effectively, -1990 , because oracle calculates the numeric value of 03 - 03 - 1990. Of course, this doesn t work with the given format string.
To fix it, you need
v_dateInput := &&dates ;
When prompted to enter a value for dates make sure you put it in single-quotes, e.g.
Enter value for dates: 03-03-1990
Share and enjoy.
I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...
I ve got the following objects: CREATE FUNCTION CONSTFUNC RETURN INT DETERMINISTIC AS BEGIN RETURN 1; END; CREATE TABLE "FUNCTABLE" ( "ID" NUMBER(*,0) NOT NULL, "VIRT" NUMBER GENERATED ALWAYS AS ...
The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes ...
I was wondering, is there a way to make a kind of one to one left outer join: I need a join that matches say table A with table B, for each record on table A it must search for its pair on table B, ...
I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like: INSERT ALL ...
I just came across NULL values in NOT-NULL fields in our test database. How could they get there? I know that NOT-NULL constraints can be altered with NOVALIDATE clause, but that would change table s ...
I m studying databases and am currently working on a object-relational DB project and I ve encountered a small problem with the number of possible constraints in an object table. I m using "Database ...
we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...