PL/SQL Loop Types - Variables Incrementing Together

Nov 8, 2012

set server output on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........

the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8

this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:

set serveroutput on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......

I get the result:

1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8

I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Variables In Loop Procedure

Feb 15, 2010

PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....

it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..

View 3 Replies View Related

Loop - Two Variables (username / Password) Not Clearing Out?

Apr 15, 2008

The idea of the program below is to create a sql "module" (just a .sql file) that will loop and ask for a user id until the user enters the correct one. Here is the code I have thusfar:

set serveroutput on;
accept username prompt 'Enter user name here: ';
accept password prompt 'Enter password here: ';

declare
usernameCONFIRM VARCHAR(30);
passwordCONFIRM VARCHAR(30);
begin
select USERID into usernameCONFIRM from GAMEUSER where USERID = '&username';

[code]...

The main problem I'm seeing is that the two variables I ask for (username and password) aren't clearing out.

View 12 Replies View Related

SQL & PL/SQL :: Creating Loop - Sequence Should Add A Number In The End Of Variables

Jun 12, 2013

SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,

[Code]....

My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.

I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...

View 39 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.

Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?

Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;

View 8 Replies View Related

PL/SQL :: Possible To Use Forall Instead Of For Loop - End Loop

Nov 19, 2012

CREATE OR REPLACE PROCEDURE IND_MONITOR(P_tab VARCHAR2)
is
type ind_table is table of varchar2(20);
p_ind ind_table;
v_sql varchar2(2000);
begin
select index_name bulk collect into P_Ind from user_indexes where table_name=upper(P_tab);
for i in 1..p_ind.count loop
v_sql :='alter index '||p_ind(i)|| ' monitoring usage'
execute immediate v_sql using p_ind(i);
end loop;
end;

can i use forall instead of 'for loop ..end loop'

View 10 Replies View Related

Varying Return Types

Feb 17, 2012

I have two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:

select N'Test" "result" from dual;

On one the output is:
resu
----
Test

and on the other the output is

result
----------------
Test

This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.

Not being an Oracle DBA, I am not sure where to look.

View 1 Replies View Related

SQL & PL/SQL :: Various Collection Types Available In Oracle 9i?

Jun 15, 2010

What are the various collection types available in Oracle 9i.

Mention the differences between them.

View 4 Replies View Related

SQL & PL/SQL :: How Many Types Of Index Are There In Oracle

Nov 11, 2011

I read about many indexes as B tree index, function based index,Bitmap Index etc.how many types of Index are there in Oracle and what are they? wat are the categories and sub category? when to use what?

View 6 Replies View Related

SQL & PL/SQL :: How Many Different Types Of Indexes Have In Oracle

Jul 12, 2012

How many different types of indexes we have in oracle?

View 2 Replies View Related

SQL & PL/SQL :: All Types Of Matrix Report With PDF

Jan 23, 2011

Send all type of matrix report with examples.

View 1 Replies View Related

SQL & PL/SQL :: Types Of Oracle Index

Sep 26, 2011

Types of oracle index in detail. consider this situation, if a select query without any where clause takes much time to get data,then we will create index for that table.

Now again run the same select query. this time, whether the query takes much time again or will it work fast? whether the index is used for a select query execution or not?

How we can confirm that whether the oracle using the index normally?

View 2 Replies View Related

All Employee Types In Single Query

Jul 7, 2011

I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.

I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..

like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')

View 3 Replies View Related

SMON Not Clearing Up Some Transient Types

Dec 5, 2010

All nodes in RAC environment are available.But sessions are not load balanced for one/some of them.

Checking alert log the following oracle errors can be found:

alert_<INSTANCE>.log
ORA-21779: duration not active
ORA-06512: at line 1

The root cause of the problem is SMON that is not clearning up some transient types.

I've noticed this effect in Oracle 11g release 1, but there is a chance it happens for early Oracle versions.

View 1 Replies View Related

SQL & PL/SQL :: Find Maximum Value Within A Group For Corresponding Types

Oct 25, 2010

Test data for the problem is as follows.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> with t
2 as
3 (
4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all
5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all
6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all
7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all
[code].......

I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).

So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.

Datatype for the tables are :

Id : Number
compid : Number
Rel_Type : Number
enddt : Date

View 4 Replies View Related

SQL & PL/SQL :: How To Select Data From REF Within REF (object Types)

Mar 3, 2012

how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:

1 CREATE OR REPLACE TYPE object1 AS OBJECT (
2 object_id NUMBER(5),
3 object_name varchar2(10),
4 object_added DATE);
5 /
6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY);
7 /
8 INSERT INTO object1_tab
9 VALUES (1,'Daniel',sysdate);
10 /
11 show errors;
12 /
13 CREATE OR REPLACE TYPE object2 AS OBJECT (
14 object_id NUMBER(5),
15 object_job varchar2(10),
16 object1_ref REF object1 );
17 /
18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY);
19 /
20 INSERT INTO object2_tab
21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P
22 WHERE VALUE(p).object_id = &object_id));
23 /
24 select DEREF(object1_ref)
25 FROM object2_tab;
26 /
27 CREATE OR REPLACE TYPE object3 AS OBJECT (
28 object_id NUMBER(5),
29 object_location VARCHAR2(20),
30 object2_ref REF object2);
31 /
32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY);
33 /
34 INSERT INTO object3_tab
35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id));
37 /
38 show errors;
39 /
40 select object_id,object_location,DEREF(object2_ref)
41 FROM object3_tab;
42 /

As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:

OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF)
--------- -------------------- ----------------------------------
1 New York [SANTA.OBJECT2]

Contained within the object2 is the following:

SANTA.OBJECT2(1,'Developer','oracle.sql.REF@c4cb4aa6')

How would i view the ref for object1 within object3? I will also need to be able to verify the data in these REF in the new table they are used in.

View 3 Replies View Related

SQL & PL/SQL :: Using EXTEND In Nested Table Types?

Mar 4, 2013

need to use Extend() in nested tables in Oracle? What could be the problem if I do not use this method in my code?

I have a nested collection type (TABLE OF VARCHAR2(32)) declared in my package. My stored procedure takes the TABLE type as input and inserts that data into a database table.
I see that my code works fine without using EXTEND method.

View 10 Replies View Related

XE :: Subtypes Created Incorrectly As Types In 11G R2?

Jun 1, 2013

I'm trying to determine why subtype creation is not working in 11G R2 XE, while it works in 11G R2 EE.

Environment is XE 11.2.0.2 on Windows 7 X64 (yes, not supported but I don't have anywhere else to try this).

Here is a test case, using the SYSTEM account as that is what the developers use (yes, terrible practice) :

--type
-- this works, creates the type
CREATE OR REPLACE TYPE [schema].prod_type AS OBJECT (
pid INT,
pprice NUMBER,
MEMBER PROCEDURE display(pid IN NUMBER))
NOT INSTANTIABLE NOT FINAL;

[code]....

I've tried adding the schema name after UNDER, e.g. UNDER [schema].prod_type with the same result.

View 1 Replies View Related

PL/SQL :: How To Eliminate Duplicates From Record Types

Aug 21, 2013

How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."

error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000));  TYPE  ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1  ln_x_type; gc_stmt     varchar2(4000); Begin   gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions;    EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1;  ln_dist_x1:=      ln_x1 MULTISET UNION DISTINCT ln_x1; End; 

I need ln_dist_x1 to have distinct records from table.

View 12 Replies View Related

ORA-06553 / PLS-306 / Wrong Number Of Types Of Arguments

Apr 9, 2012

I'm trying to call a custom made PL/SQL function in a SQL query. I want to supply the values of the parameters during the query. I can call the function if I "hard code" the parameter values, but when I try to supply them I get the ORA-06553 error.

This call works:

select pkg_tm_import_util.wb_screen_hr_refresh_func('','','','','','','','','','','','') from dual

However, this does not, but should be the same as the call that works:

select pkg_tm_import_util.wb_screen_hr_refresh_func(
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Wrong Number Or Types Of Arguments In Call

Jul 11, 2011

i am getting PLS-00306: wrong number or types of arguments in call to 'SECURITY_AUDIT_DTL_TYPE' error below code.

CREATE OR REPLACE PROCEDURE load_data_audit_trail_dtl
AS
TYPE security_type IS TABLE OF SECUIRTY%ROWTYPE
INDEX BY PLS_INTEGER;
security_type_var security_type;

[code]....

View 7 Replies View Related

SQL & PL/SQL :: One Database Two Types Of Fonts English And Russian

Mar 1, 2010

I am a programmer in Oracle PL / SQL in Oracle 10g I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic). Is this possible and how? NLS_LANG ?

View 2 Replies View Related

PL/SQL :: Selecting From A View With Nested Object Types

Oct 17, 2013

I am using 11.2 DB. I have created 2 object types. The contact_t has been embedded into the student_t object and I have created a view with the nested object.How do you select the values from the contact_t object type in SQL from the view? i want to see the values of the contact_t object in speerate fields (ie. contact_name, city, state...)

CREATE OR REPLACE TYPE contact_t AS OBJECT  (    dcid      NUMBER(10),    contact_name      VARCHAR2(50),    city                    VARCHAR2(50),    state                   VARCHAR2(10),    zip                     VARCHAR2(10),    email                   VARCHAR2(100) ) 
CREATE OR REPLACE

[Code]....

View 2 Replies View Related

ORA-06553 / PLS-306 - Wrong Number Or Types Of Arguments In Call

Oct 24, 2011

When I try to run: select xa_time_cnv.utc_to_loc(sysdate ,('yyyy/mm/dd hh24:mm:ss') ) Fecha from dual; This message appears:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'UTC_TO_LOC'

the function is:
/* utc_to_loc
** Purpose: utc_to_loc is a function written to convert a utc time, to the local time zone.
*/
FUNCTION utc_to_loc(utc_datetime in DATE) return DATE IS
CURSOR c_get_utc_offset(utc_datetime DATE) IS
SELECT Offset FROM TimeTran WHERE
utc_datetime BETWEEN UTCStart and UTCStop;
[code]...

My database is on GMT -3 and I try to view convert UTC-TO LOCAL.

View 2 Replies View Related

PLS-00642 - Local Collection Types Not Allowed In SQL Statements

Feb 20, 2011

I write plsql code as following:

DECLARE
TYPE id_collection IS TABLE OF NUMBER;
TYPE title_collection IS TABLE OF VARCHAR2(60);
id ID_COLLECTION;

[Code]...

I got following error: PLS-00642: local collection types not allowed in SQL statements.

View 3 Replies View Related

SQL & PL/SQL :: User Defined Data Types In Cast Functions

Jun 22, 2010

I am migrating sybase to oracle database. A Java developer needs money datatypes.I said to them, please change the cast(<value> as number(19,4) in java code side. but they are not accepted because money data type is used most of the places.

select cast(0 as money) from bank_trans; this sql statements present in java code. I need to create user defined type is equivalent to money datatype.

My steps

I have create user defined data types

create or replace type money as object(money as numbeer(19,4)

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

create or replace type money is table of numbeer(19,4);

select cast(0 as money) from dual;

it shows inconsistent datatypes error.

View 10 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related

SQL & PL/SQL :: ORA-06553 / PLS-306 - Wrong Number Or Types Of Arguments In Call To (V)

Oct 25, 2012

I am getting an error while i was using merge statement.

error msg : [Error] Execution (138: 16): ORA-06553: PLS-306: wrong number or types of arguments in call to 'V'

View 1 Replies View Related

SQL & PL/SQL :: Column Validation For Types And Storing Data For Subtypes?

Apr 3, 2010

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?

View 4 Replies View Related

Server Administration :: Column Limit Same For All Types Of Table

Aug 10, 2010

Question 1: What is the no of column limits in a single table? Is it same for all type of tables.
Question 2: How to estimate the size for a table in advance with "undo generation amount, index size, redo log size"?
Question 3: What is the real time scenario for creating a table on another schema?
Question 4:I read the below line in a document, but I'm not able to understand it

"we can't move types and extent tables to a different schema when the original data still exists in the database"?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved