PL/SQL :: Selecting Into A Varray?

Mar 29, 2013

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production

[code]...

SELECT job_request_id,
         CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
    FROM mytable
GROUP BY job_request_id;

ORA-22814: attribute or element value is larger than specified in type

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Distinct Value From VARRAY?

Feb 21, 2012

I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.

declare
type t is varray(10) of varchar2(10);
t1 t;
type r is table of varchar2(10) index by binary_integer;
r1 r;
begin
t1 := t('A','B','A','B','A','B','C');
select distinct * into r1 from table(select * from t1);
END;

View 1 Replies View Related

SQL & PL/SQL :: Varray To Cursor

Mar 31, 2011

Iam passing the varray to cursor as show below.

type t is varray(50) of number;
t1 t;
cursor c1(t3 t) is select name from temp2 where id in(t3(1),t3(2));
name1 varchar2(20);
begin
t1 :=t(501,384,3242,106);
[code]......

my question varray size is 50, while creating the cursor i have to pass the 50 values like t3(1),t3(2).....t3(50). I have tried to use t3.first..t3.last in IN condition but throwing some error, any other method/hints to pass the values dynamically to cursor IN condition.

View 5 Replies View Related

SQL & PL/SQL :: Table And VArray

Aug 23, 2011

i have a doubt in plsql table and varray.. is this two things data will write on disc or it will perform through ly a memory level? if its in memory level i need a document about that.

View 14 Replies View Related

SQL & PL/SQL :: How To Use VArray In Using Clause Of Execute Immediate

Sep 23, 2010

I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".

The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"

Environment:

create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));

insert into table_x values('a','b','c','d');
commit;

Code, simplified with static number of columns:

declare
type v is varray(10) of varchar2(20);
v_values v:=v('A','B','c');
myupdate varchar2(2000);
begin
myupdate:='update table_x set a=:a, b=:b where c=:c';
dbms_output.put_line(myupdate);
execute immediate myupdate using v_values;
end;

I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY.

View 11 Replies View Related

SQL & PL/SQL :: Bulk Collect Into Varray

Mar 7, 2011

I'm getting error message

PLS-00386: type mismatch found at 'RECORD_VARRAY' between FETCH cursor and INTO variables

while executing the below code.

PROCEDURE MAIN_BULK_COLLECT(P_STARTDATE IN TIMESTAMP DEFAULT NULL,
P_ENDDATE IN TIMESTAMP DEFAULT NULL,
P_ROW_COUNT IN NUMBER DEFAULT 1000,
O_RECORD_VARRAY OUT NOCOPY SSAM_VARRAY_TYPE,
P_ERROR OUT VARCHAR2) AS
[code]....

I'm able to run the program successfully using FOR LOOP instead of BULK COLLECT but wish to run using bulk collect.

View 6 Replies View Related

SQL & PL/SQL :: Using VArray For Known Column Names?

May 2, 2013

I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.

Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.

Here is my
declare
to_num number;
field_name varchar2(15);
[code]....

View 9 Replies View Related

SQL & PL/SQL :: VArray - Subscript Beyond Count?

Nov 4, 2011

From my previous topic, I learnt a few things about varray, And started coding the below

I will be accepting 20 elements of arrays, each of size 2000, so I created the below

create or replace type t_var_type is varray(20) of varchar2(2000);

create or replace procedure p_test(in_arr t_var_type) is

v_send varchar2(2000);
v_t_var_type t_var_type;
begin

[Code]....

I am getting subscript beyond count error, Though I handled it in procedure by extending the array

View 11 Replies View Related

SQL & PL/SQL :: Initialization Of Varray Variable?

Aug 30, 2011

1.)different ways to initialize a varray variable. one way i know is

declare
type nt_type is varray(4) of numer;
nt nt_type:=nt_type();
begin
...
..
...
end;

2.)is it possible to initialize a varray variable in begin section.

3.)
decalre
type nt_type is varray(4) of number;
nt nt_type;
begin
null;
end;

in this situation if i want to initialize a varray variable "nt" in separate stmt how to do it and where to write it?

View 3 Replies View Related

SQL & PL/SQL :: Varray Of Object Type

Jun 23, 2010

I want to pass varray of object as out parameter (more than 1 ), Tested the below code getting error.

Note : I want to get value from table and assign it to varray object and pass varray as out parameter.

Here below the code I tested.

create type emp_type as object (
emp_no number,
emp_name
);
create type emp is varray(10) of emp_type;
[code]........

Getting error while above code.

View 11 Replies View Related

SQL & PL/SQL :: VARRAY Error In BLOCK

Oct 7, 2013

I trying to write plsql anonymous block to return the name of all the employees who belongs to a specific department . I am writing below script for this and getting the error where as the same logic if i use only in a function instead of creating it within a procedure , i am not getting any error .

Script :

DECLARE
TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30);
FUNCTION getEmpArray (p_no IN NUMBER)
RETURN EMPARRAY
AS
[code].......

Error report:
ORA-06550: line 18, column 4:
PLS-00103: Encountered the symbol "end-of-file"

when expecting one of the following:

begin function package pragma procedure form
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

View 28 Replies View Related

SQL & PL/SQL :: Clause Retrieving Information From Varray

Apr 12, 2012

select *
from
table(it_count_tc('bbs_swe_tve_db'))

gives this output:

COLUMN_VALUE
------------
0
2
37
8
0
1
0
0
1
3
0
0

12 rows selected

BUT: what I what is like this

Jan Feb Mars April May ...
0 2 37 8 0 ...

it_count_tc is a function returning a varray of 12 integers input is a varchar2 string

View 2 Replies View Related

Loading Data Via Sql Loader Into Varray Tables

Aug 19, 2010

Is it Possible doing SQL LOAD into Varray table having two inner objects.

find the structure of the table and its types for your reference

CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT (
AGE NUMBER ( 3 ),
NAME VARCHAR2 ( 14 )
);
/

CREATE OR REPLACE TYPE OB_TEST_INFO_VARRAY AS VARRAY( 400 ) OF OB_TEST_INFO ;
/

CREATE OR REPLACE TYPE OB_TEST_MAINTENANCE AS OBJECT (
BREAKOUT_TYPE NUMBER ( 1 ),
EXISTING_STRIPS_FLAG NUMBER ( 1 ),
OB_TEST_INFO OB_TEST_INFO_VARRAY
);
/

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: Fill VARRAY Using Table Fields And While Loop?

Jun 13, 2011

I am using arrays on this procedure but would like to use a WHILE LOOP to fill the array. The first element uses a balance forward amount unlike the next 11 elements so I would think I have no other choice for the first element.

jde_account_balances is the table name that stores the amounts.

Here is what I currently have coded:

X INTEGER;
Y INTEGER;
Z INTEGER;

[code]...

View 7 Replies View Related

SQL & PL/SQL :: How To Access The Record Type Elements Of Varray

Jun 11, 2013

i have created one varray whose elements are of record type. Now how can i access those record type elements?

structure of table t1:
select * from t1;

IDDESCRIPTION

1a
2b
3c

select * from t2;

ID1DESCRIPTION1

4aa
5bb
1cc

declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......

View 13 Replies View Related

SQL & PL/SQL :: Move Data From Varray Column To A Table

Mar 28, 2013

I have to move the data from a Varray column to a table.

Lets say I have created a Varray as described below,

create or replace TYPE "BT_TYPE" AS OBJECT (
R_ID NUMBER,
P_EVENT VARCHAR2(100))
/

create or replace TYPE "BT_VR" AS varray(20) of BT_TYPE
/

And I have a used this Varray as the column datatype in table

Create table BT_MASTER(
BT_ID_SEQNUMBER(5),
BT_DETAILBT_VR);

And this table contains data.

I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.

Create Table BT_STG(
BT_STG_ID NUMBER(5),
R_ID NUMBER(5),
P_EVENT VARCHAR2(100)
);

One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.

Is there any other simpler way by which I could create a table similar to a VARRAY/Object?

Something similar to,

create table test as select * from BT_VR

View 4 Replies View Related

PL/SQL :: Uses And Advantages - Associative Array / Nested Table And Varray?

Jun 4, 2013

What is the real time uses for Associative array, nested table and varray ?

View 2 Replies View Related

Server Utilities :: Loading Data Via SQL Loader Into VArray Tables

Aug 19, 2010

Is it Possible doing SQL LOAD into Varray table having two inner objects.

find the structure of the table and its types for your reference

CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT (
AGE NUMBER ( 3 ),
NAME VARCHAR2 ( 14 )

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Selecting 6th Top Value

May 9, 2010

how to select the sixth highest earner in my employees table.how to select 6th lowest earner..

View 18 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.

CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)

[code]....

View 17 Replies View Related

Selecting The Most Recent Value?

Sep 7, 2007

Have a table like this:

ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360

I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:

1246/1/2006410
2247/1/2007360

I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.

View 3 Replies View Related

PL/SQL :: Selecting Data For One Day

Jun 18, 2013

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');

View 4 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I need to find the top two values value for each ID Number:

CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));

INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');

Desired Result:

ID Number 1st 2nd
12345679 30 30
99999999 30 25

View 7 Replies View Related

Selecting From Two Tables - How To Get Value From Session

Jan 31, 2013

I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.

View 2 Replies View Related

Selecting 100 Number Rows?

Jan 15, 2011

how can I select whole table in parts of 100 rows?

If I have primary key I can:

CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;

How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?

View 1 Replies View Related

Selecting Via Multiple Tables

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]...

What I get is in green (services are calculated more than 2 times BUT in the same report).

What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]...

One person (zmo_kodas) can have a lot of reports (ats_nr).

Every report can have one or more services (pas_kodas).

View 1 Replies View Related

SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any way to select the clob value from dblink.?

View 7 Replies View Related

SQL & PL/SQL :: Selecting Only Part Of A String

Feb 25, 2010

I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Selecting First Row From Multiple Joins?

Apr 2, 2010

I'm putting together a path to select a revision of a particular novel:

SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s

[code]...

My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.

When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.

One other challenge is the format of the revision- a revision sequence could look like this:

A
B
C1
C2
C
D
E1
E

So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:

A1
A
B1
B

I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).

View 12 Replies View Related

SQL & PL/SQL :: Selecting Via Multiple Tables?

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]

What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]

One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).

View 2 Replies View Related







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