table 1
empno, salary
1001,9800
1002,10000
1003,4000
1004,6000
table 2
sal_from, sal_to, rate
1000, 3000, 0
3000, 4500, 10
4500, 5500, 12
5500, 6500, 30
6500,999999 50
now how can i compare values from table 1 in table2 and apply RATE on table1 SALARY like (SALARY*RATE)
I am trying to create a simple parametised procedure when all I am doing is dynamically inserting data from a set of tables and cols on a where clause.
And for the result set I am also inserting these input variables for future ref.
----------------------------------
CREATE OR REPLACE PROCEDURE DATA_TEST (IN_TABLE_NAME IN VARCHAR2, IN_ATTRIBUTE IN VARCHAR2 )
iS
FirstClause VARCHAR2(3000) := 'INSERT INTO ABC (
COL1, COL2, COL3,COL4,COL5, COL6) ';
selectClause VARCHAR2(3000) :=
[Code]...
Now, the problem is that the proc is not compiling, because of that escape character.
Is there anything wrong that I am doing or some other settings needs to be done?
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
I am using oracle RAC 11g R2
is it in Orcale not possible to refenerence two tables in a with clause?
With CTE
AS
(
Select ...
),
[code]...
If I do a select on my first table I get this error: ORA-32035: unreferenced query name defined in WITH clause - With CTE AS
In Sql Server it works fine ...but how can I create two CTE Tables in Orcacle?
I have a query regarding nested tables while exporting. Are we not allowed to use Query clause during the export of nested table?
I am getting the error:
"EXP-00053: unable to execute QUERY on table NT_LP_COREBAL_MAINT because the table has inner nested tables
Export terminated successfully with warnings."
The exp command we are using is:
exp XXXX/XXXX@XXXX file=test1.dmp log=test1.log tables=nt_lp_corebal_maint query='where domain_id=10110307'.
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
I've below table structure with given table data. I need below given output i.e. a linear output. It should be dynamic i.e. there are only three columns for Manoj and four columns for Arun. If I insert one more row for Arun the new added row should also be reflected in the same row and every column should give same contact type.
CREATE TABLE TEST123
(
PERSON_NAME VARCHAR2(100),
CONTACT_TYPE VARCHAR2(20),
CONTACT_NO VARCHAR2(15)
) ;
[code]...
how go generate this output on single query.
I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?
bubbagumpshrimp
"ORA-01722: invalid number"
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;
I have a table and data like mentioned below.
create table emp( ename varchar2(20));
insert into emp values ('122');
insert into emp values ('abc');
insert into emp values ('0.2');
insert into emp values ('0-5');
insert into emp values ('25-30');
SQL> Select * from emp;
| ENAME |
-------------------
| 122 |
| abc |
| 0.2 |
| 0-5 |
| 25-30 |
I am running the code
SQL>select regexp_substr(ename , '^[[:digit:]]+.[[:digit:]]+$|^[[:digit:]]+$')
from emp;
AFTER RUNNING I AM GETTING THIS
| REGEXP_SUBSTR(ENAME,'^[[:DIGIT:]]+.[[:DIGIT:]]+$|^[[:DIGIT:]]+$') |
---------------------------------------------------------------------
| 122 |
| (null) |
| 0.2 |
| 0-5 |
| 25-30 |
| (null) |
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
Is there anyway to compare the non numeric data in a table to a numeric number.Want to run a query like
SELECT rank_id
FROM mas_rank
WHERE rank_code > 26 Rank_code contains numeric as well as some non numeric data
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.
Problems with each of the Oracle loaders.
1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.
2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.
I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions
1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?
2) Do any of you maybe have another strategy to do this?
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 data block that has hundreds of records. On a screen I display 20 records at a time. On that same screen I have "find badge" function where user types badge/employee id and presses a button to find the record in a data block. I have no problem finding the records in a list and pointing the curser to appropriate record. My question is (which is a user request) if the badge/employee id is found to make that record to be the first record in a list.
View 2 Replies View RelatedI have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View Relatedexplain limitation / restrictions on WITH clause in oracle.
View 9 Replies View RelatedI want if the user write for example in text box 'AM TK' the query display the resualt which has am alone tk alone, and that has both. I know that i should use the Like with % but i do not know how to write it in the set property. I have wrote
set_block_property('Employee_Other',default_where,'Upper(name) like '''||UPPER(:key_search.person_name)||'''') ;
This will bring the resualt just if the user write am tk. How i can modify it to return value as i explained above.
Want to filter a data using CASE statement in WHERE clause for the following scenario.
Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.
where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.
View 6 Replies View RelatedI need to export only the data from schemas or tables, how to do that with Oracle Data Pump? when we use schemas parameter this export all schema, not only the data right?
View 7 Replies View RelatedI created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies View RelatedI have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
Is there any way i can select * from all the tables owned by particular schema at once.
View 22 Replies View RelatedI want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using