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.
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 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?
There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
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 have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.
My controll file ------------------- LOAD DATA INFILE 'mydata.data' APPEND INTO TABLE parent when (1:1) = 'p' fields terminated by ',' optionally enclosed by '"' trailing nullcols
[code].....
able to load parent data and generate P_ID, but i am not able to get P_ID for child records.
I have a reference number of application number which is sent to 3 different address as outward number . for example application number is intimated to the office , the employee and the treasury. this number is generated as follows
P/11/10/133 P/11/10/133A P/11/10/133B
in my outward report when i run it all three numbers are getting listed.
i want only P/11/10/133 to be listed and ignore the rest of the two. i tried to use SUBSTR(INOUT_NO,9), but it reads only from the left gives me only the number ie 133, 133A and 133B. i tried rtrim and ltrim but it only clears the extra space. how do i ignore the other two dispatch numbers.
I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,
In a not normalized table loaded with data from an excel file, I have a column containing a long string with many carriage returns so that after each of them it begins a new string I have to manipulate. I mean, in the column PERSON I've got something like this:
PERSON _______ Jonh Taylor Software developer ...
Of course I have to write a Pl/Sql procedure extracting each string and I know that each string ends with a carriage return chr(13)||chr(10)
I think I should use the INSTR built-in function but I it'd be useful whether some of you had something ready.
How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE ( trim the values before ^ ) 121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.
i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .
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.
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]....
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]..........
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.
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.
