I need to store a date that will always be x years before the current date. Is doing something like
select sysdate -x from dual
Going to result in any weird issues down the road? Also, because I haven't explicitly specified the date, when queried, will the value always be sysdate - x (What I want to happen) as opposed to dateRowWasCreated - x?
I have a requirement to execute the query only once for all the columns mapped to a particular table. Lets take an example..
table t1 has the following columns mapping.
col1 col2 col3 col4
variable 1 table abc col1_abc ----
variable 2 table abc col2_abc ----
variable 3 table xyz col1_xyz ----
variable 4 table xyz col2_xyz ----
variable 5 table xyz col3_xyz ----
variable 6 table xyz col4_xyz ----
In col4 , we have to update the values of col3 (column) ... it is very easy to update the values by executing the query for each column but my requirement is ... I want to execute the query once for a table ( example select col1_abc,col2_abc from table_abc ... this will return always a single value after adding other conditions in where clause) and why I want to do this? .. because there can be multiple (might be 30 variables values)
from a single table.
I have converted the columns into rows by using listagg function now I have lets a dynamic query ( for the above example).
for i in 1.2
loop
l_query := ' select col1_abc, col2_abc from table_abc';
open c1 for l_query;
end loop;
my problem is : how to hold these values and update the above table?
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
1220.01.10
1321.01.10
1423.01.10
1526.01.10
1627.01.10
1729.01.10
1831.01.10
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel
(
start_date,
end_date
)
AS
SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE
T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE
A | 2010 11 01 | 2011 02 11
B | 2011 01 20 | 2011 02 15
C | 2011 03 13 | 2011 04 10
D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE
X1 | 2011 02 16 | 2011 03 12
X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]-------------------------
B : ------[---]------------------------
C : --------------[---]----------------
D : ----------------[------]-----------
Result :
X1 : -----------[--]--------------------
X2 : -----------------------[--------]--
Sample 2:
Travel Date : -[--------------------------------]--
BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------
Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--
How can I do it using PL SQL ?
I am trying to store an image in a table. What is the easiest way to do this?
View 8 Replies View RelatedI have a varchar field in a table. When I store the following string, some undefined character is storing.
String to be stored: Test String
String actually stored: Test ¿ string
Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.
I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS
NLS_CHARACTERSET - WE8ISO8859P1
NLS_NCHAR_CHARACTERSET - AL16UTF16
I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.
View 3 Replies View Relatedis there any method to store a value in "copy buffer" ?
I mean, for example, if i have defined a global varaiable as
Quote::global.name := 'orafaq';
Then what i need is a command something like:
windows_copy_buffer := :global.name ;
So that later on when i press control+v, then it paste the word "orafaq".
i basically need to setup a schema for globalization. i need to handle brittish dollar signs, american dollar signs. etc.what is a global character setting i can use to handle almost everything?
my problem right now is that when i store a brittian dollar sign it converts it to a box. i need this preserved so i can select it and use it later.will the WE8ISO8859P1 character setting work? where is a good place to view all of the types of character settings? and what they are for. etc. and also how do you change such things? not for the session, but for a schema wide
here are my current sessions
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[code]...
My current task is to store XML file from local system ( OS :XP)into server (OS : Linux) specified location with PL/SQL procedure.
Below is the XML file,
<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
[code]......
How to complete this task.
Since I need to do something along the lines of inserting an xml document into the database for later to use.
Anyway, the problem I get with the below is
Quote:
Error at line 95
ORA-00984: column not allowed here
Script Terminated on line 95.
It's on the INSERT line
declare
xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>
[Code].....
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003
12/3/2003
12/4/2003
12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.
I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also). I cannot use dblinks and other techniques like this. The point is - how to avoid dealing with encoding?
I mean, that in the best scenario, I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. How this can be accomplished? If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.
If this is not possible, what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
I have a form with 3 blocks in our oracle application. Block1 is a single record block. Block2 and block 3 are multi record blocks with no of records 5. Block1 and block2 are in same canvas1 of a window1. Block3 is in canvas2 of window2. There is a relation between block1 and block2. Another relation with block2 and block3 is also available.
Now my issues is while creating a record, first i entered the data in block1 (which is a single record block). Then i entered data in five rows of second block. Then i entered data in block3(multi record block) by pressing the button in block2 (each button for each row). But before saving the entire form, when i checked the data in block3 by pressing the button of first record of block2, screen shows empty.
Code in button of block2 is as follows
show_window(WIN2');
Go_block('BLOCK3');
Code in button of block3 is as follows
hide_window('WIN2');
go_block('BLOCK2');
If post; is included in block3 button. then problem we will be solved. But we don't want to use post; as it lock the table.
I have a web page written by pl/sql and html which some jpg files are published. Currently they are linked by html from a directory source. There are almost 70000 files which makes 1.15 gb. I want to store them in our oracle database but I have never done this before.
What I want to learn is how will be the performance for selecting from many jpg files with select statement and how much space would they keep in the database?
I am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.
I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.
i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.
I have following structures:
SQL> CREATE TYPE address_typ AS OBJECT (line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));
2 /
Type created.
SQL> CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));
2 /
Type created.
SQL> CREATE TYPE phone_list_typ AS TABLE OF phone_typ;
[code]...
My questions are:
1. I have created an object table person_obj of parent type. I have not created object tables of salesperson_typ and customer_typ and want to store data related to both in person_obj. What is the difference between storing data in an object table of parent type and object table of subtypes? In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?
2. Second question is regarding validating the column values before the creation of object instance. Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0. I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose? Is there any other alternate way?
I am trying to insert In multiple table where one tbl PK is FK of other , I achieved that functionality using Instead of Insert trigger.(PK is handled by another trigger) -- main functionNow On successfully insert i need to captured the PK in Hidden variable to pass it on further for more insert on other table (Pk again being Fk to this table)--- secondary function
View 6 Replies View RelatedI have to design a database so that the performance is optimum and joins are optimum between tables.
Requirement - I have several banks as clients globally, like HSBC, RBS, AMEX etc. Each bank operate globally, and are divided like the geographies, regions etc. For eg. HSBC is operating from US, UK, ASIA, AUSTRALIA. Each geoagraphy is further divided/childs, like, in ASIA has INDIA, PAKISTAN, SRI LANKA, CHINA. Further, it is divided, like in India, it has cities like DELHI, BOMBAY, CHENNAI etc., then further it has within Delhi - Lajpat Nagar, Rohini, Pashim Vihar etc.
As per the above scenario, I need to store addresses, phone numbers, of all the branches of all clients. Also, there is one more condition like in the above defined hierarchy, later on, a new hierarchy can be added, like In INDIA, it can further divided in States and then its local cities.
I am having problem in inserting a long file (80000 B) into CLOB cloumn in oracle database 10g.
ERROR at line 23:
ORA-06550: line 23, column 1:
PLS-00172: string literal too long
I am calling the oracle stored procedure through unix shell script as fallows.
#!/bin/ksh
cd /usr/home/dfusr/backup
i=`cat pe_proxy_master_2160.Thu.log | sed "s/'/''/g"`
sqlplus username/password@db 1> temp.log <<!
DECLARE
BEGIN
Write_Text_To_CLOB(1,'$i');
COMMIT;
END;
/
The file pe_proxy_master_2160.Thu.log is about 50000 B.
My Stored Procedure is fallows
CREATE OR REPLACE PROCEDURE Write_Text_To_CLOB (
p_id IN NUMBER
, p_clob IN VARCHAR2
)
IS
[code]....
I also tried with ojdbc5.jar and ojdbc14.jar files in class path.
I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also).I cannot use dblinks and other techniques like this.
The point is - how to avoid dealing with encoding?
I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.
what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?
I am new to the Oracle Forms.
I need a Master Detail oracle form for Oracle EBS.
The form should use only one table.
I need the form should look like User Form in EBS.
I created 2 data blocks and designed. When I run the form and key in some data, I notice multiple records stored.
like
Empno Ename Sal Comm
101 null null null
null John 2300 500
Empno is in Master Block.
And remaining are in Detail block. How to remove the duplicate records so that my required output should be like
Empno Ename Sal Comm
101 John 2300 500
Oracle Database Version: 11.2.0.2.0
We have a requirement below:
The basic requirement is to send an email to the user about order information, with specific subject and the corresponding e-mail body.
Here the E-mail body is very exhaustive, so we are asked to store the content of the e-mail in a word document and store this word document in a tables column of type BLOB.
Once we store this file in the BLOB column, when we send the e-mail to the customer we need to publish the content of the file in the e-mail body and send the e-mail to the user (This E-mail body has some dynamic content as well, that needs to be generated on the fly). This e-mail sending part is done by the shell script.
How can i publish the content of the BLOB object in the e-mail body.
I'm using dynamic sql (DBMS_SQL) to define columns of ref cursor. It works Ok but the problem is when i'm using PL/SQL CURSOR in the REF CURSOR. Then,I'm getting :
Error at line 3
ORA-00932: inconsistent datatypes: expected NUMBER got CURSER
ORA-06512: at "SYS.DBMS_SQL", line 1830
ORA-06512: at "TW.PRINT_REF_CURSOR", line 28
ORA-06512: at line 9
Here is my code:
set serveroutput on
exec DBMS_OUTPUT.ENABLE(1000000);
declare
l_cursor sys_refcursor;
begin
[code]....
Is there a solution or bypass?
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
I am newbie to oracle and using oracle 10g as database. I want to get dates between two dates .... let me give an example
suppose a user enters 1-Aug-2010 - 31-Aug-2010 , so i should get all dates in between from date and to date.
something like "select date..or whatever from dual where date between 1-Aug-2010 and 31-Aug-2010 " like this type or other.
I want the dates between 2 dates. Suppose i give the dates 01-jan-2010 and 31-jan-2010 and i need the following output.
01-jan-2010
02-jan-2010
03-jan-2010
.
.
.
.
31-jan-2010
I got all the month_numbers when i did this
[/b]select distinct t.f_month_number
from time_dim t
where
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)[b]
but when i add
select distinct t.f_month_number,p.start_date,round(replace(p.total_sal,',','')/12,2) as Monthly_sal
from time_dim t, employee p
where
t.f_date = p.start_date and
f_date between (select start_date from employee where emp_id = 111 ) and
(select add_months(start_Date,12) from employee where emp_id = 111)
i got only one month value.
I am looking to subtract two columns and get the difference.
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
