I need to select the value of a column into different variables depending on the condition.
As in, I have
FILE_TYPE_CODERETENTION_DAYS CLR 5 SIZ 6 UOM 7 ADB 8
I need to get the Rention days into the 4 variables v_color_file_type_code, v_size_file_type_code, v_buyer_file_type_code AND v_uom_file_type_code Depending whether the FILE_TYPE_CODE is 'CLR' or 'SIZ' or 'ADB' or 'UOM'
I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
I'm facing ORA-28500 error while configuring HSODBC to SQL Server.
Below is the complete information related to listener.ora, sqlnet.ora and tnsnames.ora files. Listener status is showing dg4msql instance successfully started.
I guess I'm facing some issue while creating DB link.
In Oracle 11g Release 11.2.0.1.0 we need to create DB link as below
CREATE DATABASE LINK "DBLNK_ETM_PRODUCTION" CONNECT TO "USER_NAME" IDENTIFIED BY VALUES 'Password' USING 'ServerIPAddress:PortNumber/SID';
How to create DB link to SQL Server.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production "CORE11.2.0.1.0Production" TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;
SELECT * FROM MyTable@DBLNK_1 ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation. {08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (Name or service not known()). {01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from DBLNK_AVL_CLUSTER2 28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:" *Cause: The cause is explained in the forwarded message. *Action: See the non-Oracle system's documentation of the forwarded message. Error at Line: 5 Column: 20
I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).
The create table script for the lookup is like this:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119); etc.
The query is: select r.range_text as duration_range, nvl(count(*),0) as calls, nvl(SUM(call_duration),0) as total_duration from
[code]...
As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.
My questions: 1. Is it possible to get the left join to work with the duration range values as they currently are? 2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.
What I mean is (with only one time value and not lbound and ubound:
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1); INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);
When we are trying to create number data type column of a table with precision greater than actual value,it's accepting the definition of the table . But we are unable to insert any values into the table.how internally it stores the value
SQL> drop table precision_test; Table dropped SQL> create table precision_test(name number(2,5)); Table created SQL> insert into precision_test values (1); insert into precision_test values (1) [code]....
I have two sql queries. They run the one after another.
Query 1: select * from capital where member_status = 'MEMBER' AND rownum <= 25 order by price desc
Query 2: select * from capital where member_status = 'MEMBER' AND rownum > 26 order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
Select to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a1, to_char(to_date('10-02-2006 01:30:00 PM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') as a2, Case when to_char(to_date('10-02-2006 10:30:00 AM', 'DD-MM-YYYY HH:MI:SS AM'), 'HH:MI:SS AM') >
[code]...
from the above query i was expecting value '2' but its returning '1'. As I am using TO_CHAR its trying to compare characters. Is there a way, to compare times alone like less than, greater than?
I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT * FROM WORK.WORK_UNIT WHERE (to_char (current_garage) = :entry_blk.curr_garage OR 'all' = :entry_blk.curr_garage) AND route >= 0 AND run >= 0 AND ((package_id > 0 and work_code = 99) OR (package_id is null and work_code = 1)) and Nvl (Emp_No, 0) = 0 AND work_date = :entry_blk.p_work_date AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI') will give me data up to 2 hours
I have the below query which is doing FTS and is very expensive causing load to timeout.
I did my analysis and found that table is having large number of records and hence FTS is taking long time causing timeout from app side.
I proposed to have this table partitioned but this is still pending with business and they in meantime want some solution other solution to fix this issue.
ADD_REWARD_sp. Given the identifier of a project, add a new reward for the project. The procedure should return a unique identifier for the reward. The month and year indicated should be greater than or equal to the current month, or an error message should be generated. The pledge amount should be greater than zero. The number of backers, if not NULL, should be greater than 1. If the project is not found, generate an error message.
This is the procedure head:
create or replace PROCEDURE ADD_REWARD_sp( p_proj_id IN NUMBER, p_pledgeAmt IN NUMBER, p_rewardDesc IN VARCHAR2,
[code]...
Basically, what I am struggling with is how to check if th month and year is greater than or equal to current month. I suppose it would be easy with just checking SYSDATE, but in this case I need to use both month and year.
I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:
SELECT on_time INTO on_time2 FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date WHERE ROWNUM = 2; --RETURN NULL
I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query
SELECT on_time INTO on_time2 FROM (SELECT on_time FROM work.work_unit WHERE work_code = 1 AND emp_no = :entry_blk.p_emp_no AND work_date = :entry_blk.p_work_date) WHERE ROWNUM = 2;
I can't move forward in my form until I figure out why this is not returning records
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE V1 VARCHAR2(4000); BEGIN V1 := -- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000 EXECUTE IMMEDIATE V1; END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE v_Hire_date date :='12-Dec-2005'; v_five_years BOOLEAN; BEGIN IF [code].....
value of "Shared Pool Free %" in v$sysmetric is larger than 100% SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %'; VALUE GROUP_ID 186.45107 2 186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE -------------- ------------ -- shared_pool_reserved_size big integer 832149913 shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes Fixed Size 2163880 bytes Variable Size 8.1068E+10 bytes Database Buffers 2.0401E+10 bytes Redo Buffers 79310848 bytes
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description 78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps 78664 Alarm 78664 from 78664 CAMS 78664 RTU154
This column data can be varied from 1 to any number of words.
I am trying to practice some RMAN configuration settings.Version 11.2.0.1OS - Linux X86-64 What i wanted to validate is if there is no level 0 backup of database taken, then the level 1 backup will be automatically backing up all the blocks of the database(similar to full backup). RMAN> report schema; Report of database schema for database with db_unique_name DB101 List of Permanent Datafiles
I am not sure what these input bytes are which is not matching with the database size(posted in the beginning of thread)? why it has to show as db full when i actually run a incremental?
