PL/SQL :: Creating Collection And Joining List To Database Table?
Jul 31, 2012
based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers.
The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. Why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:
SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );
here is my demo SQL, which you should be able to execute against the SCOTT schema without any change
declare
type employee_numbers is table of emp.empno%type index by binary_integer;
type employee_names is table of emp.ename%type index by binary_integer;
type employees_record is record (empno employee_numbers, person_name employee_names);
records employees_record;
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
SELECT c1.c_no, c1.c_name, c1.city FROM customers c1 LEFT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name); SELECT c1.c_no, c1.c_name, c1.city FROM customers c1 RIGHT OUTER JOIN customers c2 ON (c1.city = c2.city AND c1.c_name<>c2.c_name);
We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.
(This table has many fields, one of which is report Name field)
Now i am running a query like this...
SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'), DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A', a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) - TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM, b.PRVDR_ID, a.asctd_id, d.NM FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D' AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT) FROM STATUS_DIM d WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID ) ORDER BY a.RPT_RQST_ID, a.CREATE_DATE
Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??
Basically, i was asked to join Table 1 and Table 2 together in oracle. I have been given the values of Table 1. For example, Depot_ID has value of DEP01, DEP02, DEP02 etc. Therefore, I have to create table 2 with those attributes given below. But the attribute name of the Table 2 is Depot_No which is different.
My question: Is it correct to give the same values of Depot_ID in Table 1 to Depot_No in Table 2, such as, DEP01, DEP02, DEP02?
I create a second "DATE_PAIRS" table, and join a few subqueries. It works with conditions (ID=1), but scanning the whole table (2+ million records) is abysmal.
I have two tables. The first contains all Segment information within Oracle i.e;
Table 1 Segment Description Flex_Value 1 North 1234 1 South 1235 1 East 1236 2 Car C001 2 Boat B001
and the second table contains financial data, but only the segment code;
Table 2 Date Segment1 Segment2 Value 01/01/11 1234 C001 10,000 02/01/11 1235 C001 10,000
what I want to return is some of the columns within table two as well as additional columns for the segment descriptions.
Below is an extract of what I have put together, and it does return what I need, but I'm sure there is a much more efficient way of creating the query;
I am having a little trouble retrieving info from my mock database.
here are my definitions: response.setHeader("pragma", "no-cache"); response.setHeader("Cache-control", "no-cache, no-store, must-revalidate"); response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT"); response.addHeader("Cache-Control", "post-check=0, pre-check=0");
[code]...
and here is my select statement. ds = (DataSource) context.lookup(dSource); conn = ds.getConnection(); stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date"); stmt.setString(1, colleagueID); result = stmt.executeQuery();
***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
we had an issue with our 10.2.0.4.0 two node RAC Database where when running stats collection caused the database performance to go down and in AWR I noticed the following:
Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class gc buffer busy 95,077 73,786 776 76.0 Cluster cursor: pin S wait on X 3,524,808 54,467 15 56.1 Concurrency library cache lock 30,223 13,660 452 14.1 Concurrency gc cr request 2,876 3,594 1,250 3.7 Cluster library cache pin 1,740 1,800 1,035 1.9 Concurrency
when looked under Library Cache Activity section in the AWR report, I see the following:
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations BODY 59 3.39 3,056 0.13 1 0 CLUSTER 70 0.00 73 0.00 0 0 INDEX 178 0.00 925 0.00 0 0 SQL AREA 423,064 50.25 2,465,209 2.37 182 119,671 TABLE/PROCEDURE 1,848 21.27 22,638 0.62 70 0 TRIGGER 14 0.00 1,801 0.06 1 0
does this mean, we are having memory crunch for this instance on node 1? we have sga_target=26gb and sga_max_size=32gb set for this instance.
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.
-- PLS-00642: local collection not allowed in SQL statements
-- PL/SQl: ORA-00902: invalid datatype
The error message are also noted below in the code on the line that fails.
A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.
IMPORTANT: The code is running in an enviroment that does not have permission to create.
i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.
This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)
CREATE OR REPLACE TYPE zy_w AS OBJECT( CODE_INFORMATIONVARCHAR2(4),
[Code]....
**************** Rapport d'erreur : ORA-06550: Ligne 12, colonne 3 : PL/SQL: ORA-00947: nombre de valeurs insuffisant ORA-06550: Ligne 5, colonne 3 : PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action:
I am trying to update a collection and a table.I got some example code and installed on oracle.com, but I just can't seem to get it to work.I always get an errorORA-20001: Current version of data in database has changed since user initiated update process.I think the problem lies with comparing the checksums, but I cannot spot the mistake
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
creating the standby database from Active database using RMAN and getting the below issue after i executed the duplicate command.
Version of Database:11g(11.2.0.1.0) Operating System:Linux 5 Error: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 12/21/2012 17:26:52 RMAN-03015: error occurred in stored script Memory Script RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [code]....
provide any work arounds to proceed further in creating the standby database.
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare type type_name is table of varchar2(10); type type_name1 is table of type_name; names type_name1:=type_name1(type_name('hello')); begin -----HOW TO PRINT A VALUE-------- -----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT null; end;
1) need to print the values of names(1) 2)Assign a value to names(2)
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
I just started using SQL PL/SQL a couple of weeks ago and am now creating a database from scratch.The tables have all been created, and I'm now doing the triggers. One in particular is giving me a headache...
I have 3 different types of client, A, B and C. Each class gets a discount bigger than the one before, and you need to buy until you reach a threshold of X before you move to the next class. I'm trying to do the following trigger:
When I update one of the threshold for the class, I want to make sure the new value isnt bigger than the value of the next class. For example, if a client needs to buy for 100$ before moving to Class B from A and 200$ to move from Class B to Class C and I update the first threshold from 100$ to 250$, I want an error message to pop up saying the threshold is too high.
Right now the Class table looks like this:
Class ID (A,B and C) Threshold (0,100 and 200) Discount (0, 0.05, 0.10)
I am trying to create a physical standby database using the add standby database option within OEM 10g. This is something we have done numerous times before on 10g and 11g db's without a single issue, but for some reason, this time after selecting my RMAN backup location and staging area location , I am getting the following error when I click Next:
RMAN Backup Location - The specified RMAN backup is invalid. Could not identify controlfile from the backup
It is the latest backup (using RMAN catalog) from the previous evening and includes datafiles and control file.
Here is the output for List backup:
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1741586 7.70G DISK 00:03:26 06-DEC-12 BP Key: 1741590 Status: AVAILABLE Compressed: NO Tag: TAG20121206T220033 Piece Name: /carebackup/oradata/CARELIVE/carelive/backup/df_CARELIVE_4811_1.bus
[code].....
The only difference between creating a standby for this db and others we have created is that the file system the backups are stored on is on a non-global zone shared via nfs mount.
I know that when I try and share that filesystem with other servers I get the message: share_nfs: Cannot share filesystems in non-global zones If the /backup filesystem is not shared with any other servers would OEM be able to read from that NGZ? The filesystem is available to connect to using ssh from other locations, but not sure how OEM handles this part of the process.
