I simulated a sample procedure for my requirement.When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall.
insert into dynamic values ('Mike','1','123456');
insert into dynamic values ('Nike','2','1234567');
create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
[code]....
DECLARE CURSOR C1 IS SELECT EMPLOYEE_ID FROM EMPLOYEES; TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER; TABLE_EMP_ID EMP_ID_TYPE; BEGIN
[code].....
But when i am executing below code it is showing error as subprogram or cursor 'C1' reference is out of scope
DECLARE CURSOR C1 IS SELECT EMPLOYEE_ID FROM EMPLOYEES; TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER; TABLE_EMP_ID EMP_ID_TYPE; BEGIN
[code].....
The only difference in above two blocks is that EXIT WHEN Statement.
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
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.
-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;
I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.
This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.
how to reference a field by its location or position in the query.
We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...
foreach($myrow as $field=>$value)
and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.
Table1:event_channel Table2:event_inst I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id). event_inst table column(event_instance_id) has respective 4 records in event_channel
I use some quantity of functions with list of account id like in example below. Some functions use the same account id list, some use another. Everything works fine except those days when changes come and lists should be updated. Then I should edit each function... I think about creating new table for reference list like
CREATE TABLE MYREFERENCELIST AS SELECT XXXX AS ACCOUNTID, YYYY AS LIST1 FROM DUAL UNION ALL SELECT ZZZZ AS ACCOUNTID, UUUU AS LIST2 FROM DUAL
FUNCTION ACCOUNTID (arc_date date, cid number ) RETURN NUMBER AS [code]..........
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL); INSERT ALL INTO test_A VALUES (1, 'mem1', NULL) INTO test_A VALUES (2, 'mem2', NULL) INTO test_A VALUES (3, 'mem3', NULL) INTO test_A VALUES (4, 'mem4', 1) INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
I am getting the error ORA-06531: Reference to uninitialized collection while calling a table function which is of collection type.
CREATE OR REPLACE FUNCTION FN_GET_LINK_SYS_ID RETURN PGIPK_DM_PROCESS.DM_SYSID PIPELINED IS T_SYS_ID PGIPK_DM_PROCESS.DM_SYSID; BEGIN T_SYS_ID := PGIPK_DM_PROCESS.DM_SYSID();
[code]....
Definition of Type is below
--NESTED TABLE TO STORE SYS_ID S OF TABLE TYPE TAB_SYS_ID IS RECORD( POL_NO VARCHAR2(60), POL_END_NO_IDX NUMBER, PSEC_SEC_CODE VARCHAR2(30), PRAI_RISK_ID VARCHAR2(10),
[code]....
when i m writing select * from table(FN_GET_LINK_SYS_ID) , its throwing ORA-06531: Reference to uninitialized collection.
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER;
[code]...
This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
A. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS); B. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS); C. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS); D. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY); Answer: A
----I had gone through this question in dumps,about PRAGMA_RESTRICT_REFERENCES, but i m not aware of WNDS, WNPS..or reference for this term, & how it works in above exampl
Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?
I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data
Sample Hierarchical structure that I have
Emp -> Supv A BA CB DC EC
[Code]....
Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?
When I am trying to create a public synonym for a package then I am getting this error. This error tells us that non editioned object(Public Synonym) can not refenrence the editioned object(Package in this case).One of the solution provided on one of t he site is to create local synonym instaed of public synonym but I need to create public synonym only.
In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.
I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .
CREATE TABLE ref_parent1 ( table_name VARCHAR2(30), order_date DATE, num_rows NUMBER); INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100); INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000); INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000); COMMIT;
[code]....
ORA-14652: reference partitioning FOREIGN KEY IS NOT supported
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 NLSRTL Version 11.2.0.3.0 - Production
Can I call a function from a regexp back reference? I would like to call SELECT UTL_ENCODE.TEXT_ENCODE (:mytext, 'WE8ISO8859P1', 1) FROM DUAL; for all text after Password= and before line break WITH t AS (SELECT '[MyDB] Army=@MyDBarmy Navy=@MyDBnavy Airforce=@MyDBairf
