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
ADVERTISEMENT
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
Nov 16, 2010
it's possible to retrieve the names of tables that contain similar information in an oracle database.
View 7 Replies
View Related
Jul 30, 2010
I 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.
View 6 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
Jun 4, 2013
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies
View Related
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
Apr 12, 2008
I 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;
View 3 Replies
View Related
Dec 14, 2011
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
View 2 Replies
View Related
Dec 20, 2012
i have created two data blocks
First data data block contains a list item and a text item based on the selection of the list item and the value in the text item i need to retrieve more than one row in another data block whose NUMBER OF RECORDS DISPLAYED property are set to 10.
I have a Button in the first data block. So in the WHEN BUTTON PRESSED trigger i wrote SELECT INTO clause which is raising "exact fetch returns more than requested number of rows".Then i used a cursor in the WHEN-BUTTON-PRESSED trigger in the first block to fetch row by row and assign it to the items in second block. But i am able to retrieve only one record in the second block.
View 5 Replies
View Related
Dec 2, 2010
My table XXX has following records with data
ID AMOUNT ID_TYPE APPROVE_FLAG
A1 2 A N
B1 100 B N
A2 3 A N
A3 100 A Y
The Select Query should be..All the records should be considered and if the (amount is greater than 50 and approve_flag is N )then except that records all records should be considered.The output should be 3 rows; 2nd row (B1) should not come. writing a select statement for the above conditions.
View 4 Replies
View Related
Sep 29, 2011
first of all sample data;
create table test_circular_data(c1 varchar2(10),c2 varchar2(10));
insert into test_circular_data values ('c1','l2');
insert into test_circular_data values ('c1','l3');
insert into test_circular_data values ('c3','l3');
insert into test_circular_data values ('c4','l3');
commit;
There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :
c1--> l2 --> l3 --> c3 --> c4
The steps to get that result is :
1.- c1 related to l2 : c1-->l2
2.- c1 related to l3 : c1-->l2-->l3
3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3
4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4
View -1 Replies
View Related
Mar 21, 2013
i have requirement, that is to retrieve the data from pl/sql table.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
[code]....
PL/SQL procedure successfully completed.now i want receive the data from particular x record to y record
View 8 Replies
View Related
Dec 14, 2011
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
View 6 Replies
View Related
Nov 3, 2011
I have one table when I am querying like below
select * from timeoffreqitem
where timeoffreqitemid=134
getting data like below
134 144 07-OCT-11 13-OCT-11 134
I need to see this result as below.
134 144 07-OCT-11 13-OCT-11 134
134 144 08-OCT-11 13-OCT-11 134
134 144 09-OCT-11 13-OCT-11 134
134 144 10-OCT-11 13-OCT-11 134
134 144 11-OCT-11 13-OCT-11 134
134 144 12-OCT-11 13-OCT-11 134
134 144 13-OCT-11 13-OCT-11 134
I am looking at multilple optons.
View 1 Replies
View Related
Sep 6, 2012
I have two blocks on which I am having relation based on booking number and employee code. As I am doing a query, booking number is getting fetched in employee code's field and employee code is getting fetched in booking number's feild. Because of this, I am not able to update the form. It is giving me error unable to update record unique contraint voilated for primary key which is quite obivious. But why the data could get shuffled in each others field though during inserting its going properly.
View 7 Replies
View Related
Mar 27, 2010
I want to retrieve the data from ex.tables. How to get this.
table1:
Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.
Out put:
I want the data like Account_no who is having more than 3 Account_sub_no values.
But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.
View 5 Replies
View Related
