SQL & PL/SQL :: Search In Nested Tables And Insert Result Into New?
Mar 15, 2011
How can I search in Nested Tables ex: (pr_travel_date_range,pr_bo_arr) using the SQL below and insert the result into a new Nested Table: ex:g_splited_range_arr.
Here are the DDL and DML SQLs;*Don't worry about the NUMBER( 8 )*
CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) );
CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE;
DECLARE
g_splited_range_arr DATE_RANGE_ARR := DATE_RANGE_ARR( );
[code]...
Or can I create a VIEW with parameters of Nested Tables in it so I can simply call
SELECT *
BULK COLLECT INTO g_splited_range_arr
FROM view_split_date(g_travel_range,g_bo_arr);
OK, Now that the syntax has been corrected with "Chanchal Wankhade" I have an entirely new issue. I am sure this issue has to do with my case statement logic. I am getting multiple rows, when I am only looking for one. Here is my code:
SELECT CASE WHEN EP.PHYSICAL_DATE IS NULL THEN CASE WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 365) THEN 'NEEDS PHYSICAL' [code].....
However, only one of these rows should be the output, which is "No". How do you get a nested case statement to evaluate to one result, instead of multiple? I'm quite sure it is in the logic. To spell it out, this is what I am trying to accomplish with the above code: If the "EP.PHYSICAL_DATE" is null, then use these sets of formula's to evaluate the output, BUT if the "EP.PHYSICAL_DATE" is not null, then use these set's of formula's to evaluate the output.
As it stands now, it appears as if my nested case statement is doing exactly what I told it to do, which is to evaluate both conditions, and output both.
Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:
CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT ( PONo NUMBER, CUST_ref REF Customer_objtyp, OrderDate DATE, ShipDate DATE,
[code]...
The LineItemList_ntab is a nested table.
Here is the create code for LineItemList_ntabtyp:
CREATE TYPE LineItem_objtyp AS OBJECT ( LineItemNo NUMBER, Stock_ref REF StockItem_objtyp, Quantity NUMBER, Discount NUMBER )
[code]...
In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.
Here is some of the code I have tried to insert values with:
INSERT INTO PurchaseOrder_objtab SELECT 1008, REF(C), SYSDATE, '12-MAY-1999', LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, NULL
[code]...
The first insert statement above produces the following error:
SQL Error: ORA-00933: SQL command not properly ended
The second insert statement above produces the following error:
SQL Error: ORA-00936: missing expression
successfully insert values into the LineItemList_ntab nested table?
There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.
I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.
The data should be refreshed every day, the data changes moderately, there are more or less 500 records.
In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.
1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.). Is this correct? 2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).
I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.
how to do this interactively, without preparing a SQL script with all exisiting tables?
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system. Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
SF at oracle.com/technetwork/issue-archive/o53plsql-083350.html states that you can compare two database tables (of the same structure) by defining a nested table type (using %ROWTYPE) and two NT variables of that type, and loading the contents of each table into its respective NT variable, before comparing them using the = operator. Having read the Oracle documentation which states that you can only compare NTs for equality if they don't contain record types, I was surprised to read this, but figured I would try it because I must be misunderstanding SF, but it didn't work.
SCOTT@ORCL> create table empcopy3 as select * from emp;
Table created.
declare type emp_ntt is table of emp%rowtype; emp_nt1 emp_ntt;
[Code]....
But SF goes on to say he timed the execution of his NT equality method, comparing it with a SQL-only equivalent, and so I must be missing something. My understanding is that using %ROWTYPE declares a record type.
How to create this pl/sql process to add elements to a nested table or varray within a loop. Here's the scenario: I have an apex package that has some pl/sql processes and some stored procedures. I am dealing with Inspection Areas. An Inspection Area has several sectors. I already have the loop that lists all the Inspection Areas and a loop inside that loop that lists all the sectors. There is an if statement that determines whether or not the sector name gets stored in the varray or table. I am not sure how to correctly do this and am not sure whether to use a nested table or varray. I've posted somewhat of a pseudo coded example below
If (you_belong_in_table) then variable := store_me_in_varray /* OR */ variable := array_type(sector.sector_name) i := i + 1; end if;
/* Now we output our varray or table */ start loop output(sector names one by one)
end loop I hope this makes sense. I more so just need the syntax to be able to continually added values to a table or varray while I'm already inside a loop; and also how to output those values end the end as well.
I have a query regarding nested tables while exporting. Are we not allowed to use Query clause during the export of nested table?
I am getting the error: "EXP-00053: unable to execute QUERY on table NT_LP_COREBAL_MAINT because the table has inner nested tables Export terminated successfully with warnings."
The exp command we are using is: exp XXXX/XXXX@XXXX file=test1.dmp log=test1.log tables=nt_lp_corebal_maint query='where domain_id=10110307'.
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop begin execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null'; commit; [code]........
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64); CREATE TABLE department ( courses CourseList) NESTED TABLE courses STORE AS courses_tab; INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
[code]....
The query returns the correct data, CourseList that are not subset of any other CourseList of the table.
I am trying to convert this not exists in a left outer join query to check if the performance is better, but I don't know how to do it.
I was making some variations of this code :
select d1.courses c_1, d2.courses c_2 from department d1,department d2 where d1.courses<>d2.courses(+);
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE 2- edit my report query to something similar to > Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null 3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this. I am using theme 23 page template without sidebars Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
I have 2 object tables. Location and a department. Department references a location Object. But this wont insert. I get "0 rows created".
CREATE OR REPLACE TYPE location_objtyp AS OBJECT ( locationID NUMBER, name VARCHAR2(48) ); / CREATE OR REPLACE TYPE dept_objtyp AS OBJECT ( deptID NUMBER, name VARCHAR2(48), locationID_ref REF location_objtyp ); [code]...
This does not insert. I get no error. Only - "0 rows created".
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
I have a form which has three detail portions. I want that when I press SAVE, it should insert data in two tables & then run the specific code & then insert data in other two tables.
I am using Developer 6i. Couldn't find out the proper trigger or related thing.
I am trying to insert records in multiple tables. I know how to view data using joinig, but unable to understand how to insert records in multiple tables using Joining. I searched it on net, but didn't find much. I have also tried to write a code, but it is not working, I have seen some examples on different websites where people are using SELECT in INSERT statement for joining. What is the correct Syntax to INSERT record in Multiple tables.
Insert into library_users, library_users_info (library_users.username, library_users.password, library_users_info.address, library_users_info.phone_no) VALUES (...)
We get data from our customers which we load into temporary tables.The goal is to consolidate this data into one single table.
Following are the rules:
1) final table should have all the columns from all the tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)
4) There could be 'N' number of tables
Following is the most realistic data.
1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4
5) the only information we have to work with is the TABLE_NAME(s) and its metadata from USER_TAB_COLUMNS
DROP TABLE T1; DROP TABLE T2; DROP TABLE T3; DROP TABLE T4;
[code].....
Explanation for each row:
row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match) row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match) row3) This row comes from T1 and T3 row4) This row comes from T2 and T3 row5) This row comes from T3
I created a music database, and I'm having trouble inserting the audio, video, and lyrics (.doc) into their respective tables. I searched through the forums and found some example code, but I'm not sure how to modify it to fit my purposes.
What I need is a procedure that can insert a complete record into the track table (including an .mp3 file for each row), one that can insert a record into the lyrics table (including .doc file for each row), and a procedure that can insert a single record into the Video table (including an .mv4 file).
