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
ADVERTISEMENT
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
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
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
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
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
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
Sep 7, 2007
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
View 1 Replies
View Related
Feb 25, 2011
Is it possible to:
-define a cursor with bind variables
-get a cursor record from these cursor
-and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000;
DECLARE
--works fine
CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3;
--doesn't work
--CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1;
crec c1%rowtype;
BEGIN
--works fine
OPEN c1;
--isn't possible ?
--OPEN c1 USING 3;
[Code]....
View 3 Replies
View Related
Sep 11, 2011
just looking around to use the new feature available in oracle 11g to convert the dbms_sql numeric cursor to reference cursor, how to do it?
parse and execute the sql string first with dbms_sql and then convert it to ref cursor?
View 1 Replies
View Related
Nov 23, 2011
I want to return ref cursor based on explicit cursors
create table jumbo(id number, name varchar2(20));
insert into jumbo values(1,'jumbo');
create table mumbo(id number, name varchar2(20));
insert into mumbo values(1,'mumbo');
commit;
[Code].....
The above procedure has compilation errors when I am trying to open ref cursor
LINE/COL ERROR
-------- --------------------------------------------------------
20/24 PL/SQL: SQL Statement ignored
20/38 PL/SQL: ORA-00942: table or view does not exist
32/24 PL/SQL: SQL Statement ignored
32/38 PL/SQL: ORA-00942: table or view does not exist
SQL>
View 5 Replies
View Related
Aug 7, 2013
Can i user exist when cursor will using For Cursor .
View 15 Replies
View Related
Aug 31, 2012
i have a program like this
DECLARE
V_VARIABLE1 VARCHAR2(10);
CURSOR_ABC IS select...............;
BEGIN
OPEN CURSOR_ABC;
LOOP
FETCH CURSOR_ABC INTO V_VARIABLE1;
EXIT WHEN CURSOR_ABC%NOTFOUND;
WHAT IF I WANT TO INSERT ANOTHER SAME KINDA CURSOR LOOP HERE LIKE*
CURSOR_XYZ IS select...............;
BEGIN
OPEN CURSOR_XYZ;
LOOP
FETCH CURSOR_XYZ INTO V_VARIABLE2;
EXIT WHEN CURSOR_XYZ%NOTFOUND;
END LOOP;
CLOSE CURSOR_XYZ;
END LOOP;
CLOSE CURSOR_ABC;
View 3 Replies
View Related
Oct 14, 2010
We receive hand punches (clock data) every day. Normally a person badges in(hand punch) which creates a row in the clock_tran_processed table. The information from that hand punch is the employee id (emp_id) the date hand punch occurred and a work_summary id (wrks_id). At the end of the day, the employee badges out (hand punch out) and another entry in the clock_tran_processed table is created. The new row will have the emp_id (employee name), date the hand punch occurred and the same work summary id from the morning.
Normally hand punches should occur in pairs. One in, one out... or one in, out for lunch, in for lunch, out for day. I am seeing intervals of three and five. Meaning the employee clocked in twice and out once, or in once and out twice. This shouldn't happen.
I am writing a report that will show number of clocks per for all employees that have three(3) or five(5) clock entries.I wrote a ref cursor that gives all the employees that have a count of 3 or 5 and the employee id (emp_id). I need to pass that employee id to another query that will then get me the dates of the clocks.Here is the ref cursor thus far (I am printing those with a count of 3 and 5).
CODEDECLARE
TYPE ClockTran_Refcur IS REF CURSOR;
ClockTran_cur ClockTran_Refcur;
NumClock_num number :=0;
NumClock_name clock_tran_processed.emp_id%TYPE;
[code]...
How do I pass that variable to the get the clktranpro_time's? I am using "Easy Oracle PL/SQL Programming" but I am not seeing this type of example(pgs 140-148).
View 5 Replies
View Related
Apr 17, 2013
In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.
DB Version : 11.1.0.7 OS: AIX
View 1 Replies
View Related
Nov 14, 2006
i am using the Oracle 10 g 10.2 with the windows 2003 3 sessions from a single User shows the currenncy on the top activity graph of the enterprice manager they are not getting so much resources of system....when i go in the detail of the session i get
cursor: pin S wait on X
i am unable to kill these sessions how could i get rid of these sessions
View 2 Replies
View Related
Apr 10, 2013
My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.
CURSOR c_arch_trk (p_run_date DATE,
p_nbr_days_arch1 NUMBER,
p_nbr_days_arch2 NUMBER )
IS
SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR,
a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN
[code]....
View 3 Replies
View Related
Jun 30, 2010
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies
View Related
Nov 8, 2010
I am using Oracle 11G. I have set of tables in a schema - DDXX_UTIL.
Table Names wll be with the high level qualifiers DDXX_REJ_EMP*
DDXX_REJ_EMPLOYEE
DDXX_REJ_EMPLOYEE_DEPEND
DDXX_REJ_EMPLOYEE_ADDRESS
DDXX_REJ_EMPLOYEE_SAL
DDXX_REJ_EMPLOYEE_EXP
My requirement is to get the number of rejections happened on that day after the batch cycle for the tables with the high level qualifier DDXX_REJ_EMP* and list out each table followed by the rejection cause and count.
Expected Result
-----------------------------------------------------------------
TABLE NAME : DDXX_REJ_EMPLOYEE
REJECTION_CAUSE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56
TABLE NAME : DDXX_REJ_EMPLOYEE_DEPEND
REJECTION_CASUE COUNT
ID LIST MISSING 25
MANDAORY FIELDS IS NULL 56
Given below is the create statement for one of the table.
CREATE TABLE DDXX_UTIL.DDXX_REJ_EMPLOYEE
(
REJECTION_CAUSE VARCHAR2(510 BYTE),
INTEG_REJ_DATE VARCHAR2(10 BYTE)
);
SAMPLE_DATA
DDXX_REJ_EMPLOYEE
REJECTION_CAUSE INTEG_REJ_DATE
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10
INVALID ID 31-OCT-10
INCORRECT FIELD 31-OCT-10
I need to group by the rejection cause and integ_rej_date for all the tables.
View 5 Replies
View Related
