PL/SQL :: Package Constant Variable In CASE?
Aug 20, 2013
Below SQL query (for making a view) is not working.
CASE WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_1
then 'New' WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_2 THEN 'old' ELSE 'N/A' end as
Status PKG_CONSTANTS.INV_TYPE_1 ==> PKG_CONSTANTS is a package and INV_TYPE_1 is a constant variable. INV_TYPE_1 CONSTANT VARCHAR2(10) := '55600'; INV_TYPE_2 CONSTANT VARCHAR2(10) := '55601';
error: oracle.dbtools.raptor.controls.sqldialog.ObjectActionController$EditorObjectActionListener .. some 10 similar errors are coming in Loggin Page of SQL Developer
View 11 Replies
ADVERTISEMENT
Jul 24, 2013
The idea is to use some constant value in PL/SQL code with requirement to feed it to Oracle as value but not bind variable. Such constants used in multiple places in the code, so wants to declare it but from DB point of view it should be value. In my case Oracle will choose much better execution plan with real value for the table.
I tried to use constant, e.g:
CODEdeclare
const1 constant number := 1;
beging
[Code].....
But in sqlarea it represented as: SELECT SUBSCRIBER_ID FROM SUBSCRIBERS WHERE STATUS = :B1
View 5 Replies
View Related
Jun 7, 2011
Identify a useful persistent package variable for the system.
If i am not wrong a package is like a box where you can add functions procedures inside of it!
variables are something like (x number :=2;) which those are part of functions and all.
2nd question is identify suitable package for the system that implements constraints.create the program package program.
View 3 Replies
View Related
Sep 11, 2012
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
View 10 Replies
View Related
Feb 26, 2013
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
View 11 Replies
View Related
Jun 10, 2010
I am trying to do something like this..
declare
a constant variable (1,2,3);
b number;
begin
select x
into b
from y
if b in a then...
What is the correct way of declaring multiple values in a constant and then using the IN clause.
View 9 Replies
View Related
Oct 16, 2013
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
[code]....
View 23 Replies
View Related
May 20, 2011
I am generating a start date, stop date, count and frequency from data in one of my tables.
The result is as follows:
Id,RecType,RecCode,PeriodCode,StartDate,StopDate,Cnt,Freq
10S1M6/1/200711/30/20081812
10S2M11/30/20089/30/20091012
10S2Q11/30/20108/18/201134
10L8A6/1/20079/30/200931
10L8A11/30/20108/18/201111
From this data, I need a record for each individual month,quarter,etc.:
select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= Cnt)
This returns ORA-00904: "CNT": Invalid Identifier. I don't get an error if I use a constant:
select y.*,MonthNo,Add_Months(StartDate,MonthNo*Frequency) from (
... Code to generate data ...
) y,(select rownum MonthNo from dual connect by level <= 3)
How can I get this to work using the "CNT" value instead of a constant?
View 7 Replies
View Related
May 13, 2011
using FORALL for inserting data into table.
Below pl/sql works fine when we write all the cursor data together:
DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN
[Code]...
but I want to insert including some constant value while inserting the data like
DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN
[Code]...
How to do this by using forall, or how to do without hiting performance ( more than 5000 rows i have to write.
View 5 Replies
View Related
Jun 13, 2012
I am loading data using sqlldr command in UNIX to an oracle table and want to concatenate timestamp to a file name in the "create_file_name" column in the code below.
I have the below code within the control file..
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL")
The load fails with SQL Loader error: "Expecting valid column specification, ",", ")", found keyword EXPRESSION found instead of column. How the timestamp to a filename can be appended?
View 5 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
Jun 3, 2010
differs on below SQLs and where I need to use "MAX" and "CASE" together?
select trunc(sysdate),max(case when trunc(sysdate)='03jun2010' then 'correctdate' end) dates
from dual
output dates
-------------------------------
6/3/2010 6/3/2010
[code]....
View 6 Replies
View Related
Sep 22, 2010
I'm starting to Oracle and am having a doubt, need to check if a variable is null if I need to make an appointment if you do not need to make another appointment. I thought I was doing fine, but Oracle is pointing this error: "ORA-00900: invalid SQL statement."
- Case
CASE
WHEN 1 = 1
THEN
select *
from BANANA
WHEN 1 = 2
[code]...
View 8 Replies
View Related
Feb 13, 2011
How to use decode and case in "where" and "from" clause of a select statement.
I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition
View 7 Replies
View Related
Nov 21, 2010
i have small requirement
case when type=1 then max(col2) else end
case when type=2 then max(col2) else end
i wanted to have difference 2 timestamp
type(datatype- numeric)
col2(datatype-timestamp)
all it should be in one select condition
View 17 Replies
View Related
Feb 7, 2007
Im trying to make a query to get the grades of students, I already figured out how to get the average from the exams. Now I would like to make like a select case or if statements to get the actual grade.
Something like this:
average > 89 then grade = 'A'
average > 79 then grade = 'B'
average > 69 then grade = 'C'
average > 59 then grade = 'D'
else grade = 'F'
View 2 Replies
View Related
Mar 23, 2009
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'
View 3 Replies
View Related
Feb 1, 2012
Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.
SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......
View 1 Replies
View Related
Jul 21, 2010
I would like to know does oracle_sid is case sensitive or not on Linux platform?also let me know on windows platform also?
View 14 Replies
View Related
Aug 10, 2011
select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and
( employee_status in(Case employee_status when {?Status}=1 then 'A'
when {?Status}= 2 then 'T'
When {?Status}= 3 then 'A'||','||'T'))
OR ( end_date >= {?START_DATE}
AND end_date <= {?END_DATE}
)
)
Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.
4 records for employee_status 'A'
3 RECORDS for employee_status 'T'
7 records for employee_status 'A' AND 'T'
How I should write a query to get 7 records.
View 2 Replies
View Related
May 24, 2011
I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
[Code]....
View 11 Replies
View Related
May 7, 2010
I am facing a problem while retrieving data from table using DECODE/CASE.
Table: PARAM_MSTR
MIN_VALMAX_VALPARAM_CODE DESCRIPTION DATATYPE
AB1000 HARD PARAMETERTEXT
CN1000 SOFT PARAMETERTEXT
0501001 CRYSTAL PARAMETERNUMBER
512001001 STONE PARAMETERNUMBER
Now I want to get the parameter description based upon the PARAM_CODE and a value passed which should be in range of MIN_VAL and MAX_VAL. Means when I pass PARAM_CODE=1000 and :parameter=A, then it should check the DATATYPE of the PARAM_CODE, in our case it is 'TEXT' so it should check the passed value between MIN_VAL and MAX_VAL like
:parameter BETWEEN MIN_VAL AND MAX_VAL and should return 'HARD PARAMETER'.
If I pass PARAM_CODE=1001, then the DATA_TYPE is 'NUMBER', so it will check the :parameter value as Number.
Like :parameter BETWEEN to_number(MIN_VAL) AND to_number(MAX_VAL)
For example:
PARAM_CODE :parametr Result
1000 A HARD PARAMETER
1000 C SOFT PARAMETER
1000 P NULL
1001 25 CRYSTAL PARAMETER
1001 99 STONE PARAMETER
1001 201 NULL
I have written a query using DECODE and CASE statement but it is not working properly.
SELECT * FROM param_mstr WHERE PARAM_CODE=1000 AND :parameter BETWEEN DECODE(DATATYPE,'NUMBER',CAST(MIN_VAL as NUMBER),MIN_VAL)AND DECODE(DATATYPE,'NUMBER',CAST(MAX_VAL as NUMBER),MAX_VAL)
View 3 Replies
View Related
Oct 11, 2011
Can we use the sequence.nextval,sequence.currval inside case block.
CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;
Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)
I have tried like this
UPDATE EQUALITY_TEST
SET SEQ=
CASE WHEN NUM1=NUM2
THEN SEQ_TEMP.NEXTVAL
ELSE
SEQ_TEMP.CURRVAL
END ;
SELECT * FROM EQUALITY_TEST;
Output
NUM1NUM2SEQ
120100
222101
322102
4122103
51212104
61212105
But Required Output
NUM1NUM2SEQ
120100
222100
322100
4122101
51212101
61212101
View 4 Replies
View Related
Oct 30, 2013
Can we use select statement in case statment for an update?
Example
UPDATE x SET field = CASE WHEN
(SELECT field1
FROM Y
WHERE field3 = x.field3
IS NOT NULL
THEN 'B'
ELSE 'C'
where cny# = 112160
View 3 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
Aug 30, 2011
is there a possible to use a case statement in insert clause?
View 5 Replies
View Related
Aug 19, 2010
I am having some records in the table. If the record num is
1--It should show the month as "Apr"
2--"May"
3--"Jun"
4--"July"
5--"Aug"
6--"Sept"
if it is having other than these 6 should show "0" for the remaining months.
View 10 Replies
View Related
May 16, 2013
How to use CASE stmt in WHERE clause?
View 3 Replies
View Related
May 14, 2012
The query has a case statement in the where clause so that results can be filtered. If I pass "ut" for sso_id then the query returns 21 rows. If I remove the case statement and hard code "a.sso_id like lower('ut'||'%')" then the query returns 41 rows. The query should be returning 41 rows all the time.
Problem:
When passing "ut" as an SSOID parameter to the Procedure the query returns 21 rows.Taking the query and hard coding "a.sso_id like lower('ut'||'%')" the query returns 41 rows.
Result:
query should be returning 41 rows when "ut" is passed an an SSOID parameter.
Returns 21 rows
procedure SSO (SSOID in varchar2 default null,
Name in varchar2 default null,
Campus in varchar2 default null,
Department in varchar2 default null,
[code]...
Returns 41 rows
open Results for
select a.sso_id,
(a.name_last||', '||a.name_first) as name,
b.site,
[code]...
Test Data
CREATE TABLE ID
(
SSO_ID VARCHAR2(60 BYTE),
NAME_FIRST VARCHAR2(100 BYTE),
NAME_LAST VARCHAR2(100 BYTE),
[code]...
Test Data
CREATE TABLE NT
(
LOWER_NT_ID VARCHAR2(60 BYTE),
DEPARTMENT VARCHAR2(100 BYTE),
[code]....
View 3 Replies
View Related
