I have been trying to figure out how to write a query that shows each building code building name and number of rooms from a database with four tables : emp, build, room, roombook
View 9 RepliesI am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
i was searching for the example of subject mentioned and visited the following website
[URL]...
here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?First, they have created following Packages
PACKAGE concl_cv IS
type conclass_rec is RECORD
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
[code]...
then, created Functions which created in Ref Cursor Query in Data Model
function q_portdescRefCurDS return port_cv.portdesc_refcur is
temp_portdesc port_cv.portdesc_refcur;
begin
open temp_portdesc for select department_id, department_name from departments;
[code]...
nothing here any Close cursor statement.i am trying to explore this area of the development.
We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36%
Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
if i execute
begin
insert into t values('hgahaha');
.......
....
<17000 times insert statement>
end;
I get following error
ERROR at line 1:
ORA-06550: line 16385, column 13:
PLS-00123: program too large
But instead of pl/sql block if i write the insert statements as sql file and execute it produces no error
Does it mean pl/sql has limit on number of lines of code to 16385?
In an Application Procedure, I'd like to reference some items that are local to the current page. Obviously, hard-coding like the following won't work unless it happens to be on Page 25.
:f105_blah := :p25_blah;
So how can I write this line of PL/SQL code so that the "25" is automatically set to the number of the current page?
I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).
the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?
select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25
I'm about to build a machine whos prinary focus will be running a 'hobby' app that uses oracle express as its db. some info on the db..main tabl has 30 fields and 500K records,all other tables add up to about 20% of that size.
I run huge queries that return most of the db in about 200K rows with 50 fields.these queies contain many 'partion fields' using 'rank', 'percect rank' etc. My old pc really struggles with thhis stuff and im going to invest about 600 euro (exculing monitor etc) in a new mahcine.
general terms what i should get, im thinking about OS, hard drive (1 for data, 1 for indexs?), mutli core processer?, type and size of ram. Any othr consideration i should have.
I have NO experience with APEX but - have been browsing the documentation in considering it's use as a web application/dashboard reporting utility.
However - I'm wondering if it allows for the ability to connect to DB2 as well as Oracle...
i.e. - building a form that allows a user to 'pull' data for analysis from a DB2 instance and - generate reporting results that would then be stored in an Oracle instance.
I have an Across Group Report, but can't create a design / layout what i want (explained more detail as shown on the images).
Current report when it's running:
[URL]
The final layout that what i want:
[URL]
Environments:
- Oracle Developer Reports 10g R2
- Oracle Database 10gR2
Currently we are using "exp and imp" utilities to unload from production and load into Dev server. While importing, we are following below steps
(1) Load only data [by specifying INDEXES=N in the par file]
(2) Unlock statistics
(3) Load indexes, other objects [by specifying ROWS=N]
After doing these steps, both data, indexes and others objects are loaded. To verify indexes, we are checking DBA_INDEXES.
DBA_INDEXES :
-------------
OWNER INDEX_NAME TABLE_NAME STATUS LAST_ANALYZED
----- ---------- ---------- ------ -------------
MYSCH CP_INDEX_1 CP_TABLE_1 VALID 14/JAN/12
Question :-
(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?
(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?
(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ?
(b) I am planning to implement this in two steps :
first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE
then, load data - CONTENT=DATA_ONLY.
I'm using Apex4.1 on a hosted platform. I'm trying to build a business application and the client wants a Dashboard.
Here is the best way I can explain it:
The dashboard displays a series of rows in the table. When you click on the "edit" button, it runs a query and displays it data on a report. How would I build this?
Let me explain it a different way:
The home page shows a table report with Column 1, Column 2, Column 3.
I want to make it so where you can click any ROW and then it goes to a different page that shows all of the data So the Home Page you can just see 3 Columns, but the next page will have all the columns.
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies View RelatedI have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I am trying to build a data warehouse for Consumer Price Index and so I have downloaded data from the Bureau of Statistics.It is in excel format and since I am working with Oracle Warehouse Builder I have converted it to .csv file so that I can use it as a data source.
Question1: Is it practical to use single .csv file as a source of data for a data warehouse?
Question2: I have 3 dimensions tables and a fact table.The dimensions are one for the Region(as the date is organized in region,states etc),two is the consumer goods and services (as the data is organized in groups of goods and services, services/goods types) and finally time(year and month),
Now how am I going to do the mapping here?Is it possible to do a one to one mapping here as all data required by the dimensions is located in the .csv file.
I am looking for different cheap storage options to build test RAC setup for DB on Linux@Dell PowerEdge T100 .
View 1 Replies View Relatedi'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt`
do
echo 'BEGIN WORK; ' > rc1.sql
echo ' ' >> rc1.sql
echo 'insert into Master' >> rc1.sql
echo '(prod, rate, rate_exp) ' >> rc1.sql
[code].....
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
[code]....
I see a strange result when I check for the offset in different databases,From the same SQL*Plus window.
SQL>
SQL> conn sys/**********@db1 as sysdba
Connected.
SQL>
SQL> col sysdate for a20
[code]....
where this difference comes from?why in db1 it shows +10:00, and on db2 it shows +11:00?
When I execute my form, on pressing key F5 it displays the all available block names in the form. I want to restrict this.
View 5 Replies View Relatedi created a form by using wizard...i want to improve that form...
I created one lov which is attached in one of form field..now when i press f9 lov displayed..
But when i select any one record in that LOV , its does not come in my form field
I got the below message in trace file. What does the line "swap info: free = 0.00M alloc = 0.00M total = 0.00M" trying to say?
I have
RAM=1.5G
Swap=3.5G
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle
System name:Linux
Node name:linuxdev
Release:2.6.5-7.97-default
Version:#1 Fri Jul 2 14:21:59 UTC 2004
[Code]....
Imagine a table below and I want to write a statement that shows time,name,ip,received and sent (SELECT *). (time, city, ip are primary key lets say).However there is the condition that I want:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days. How can I do that?
select *
from table
group by city
having max(received)
Is it possible to get output without giving condition to other elements (IP, SENT... etc)
TIME- NAME- IP- RECEIVED- SENT-
10.01.2013,LON,10,2342 ,326
10.01.2013,PAR,10,563463 ,3
10.01.2013,LON,30,23412 ,574
11.01.2013,NY,20,36545 ,246357
11.01.2013,NY,40,23423 ,5
11.01.2013,TOK,40,45475 ,3453
11.01.2013,TOK,30,23423 ,574574
12.01.2013,LON,10,574 ,2342
[code]....
I am trying to investigate my execution plans against my DB; and was informed i can check current status of SQL scripts using:
Performance tab > SQL Monitoring > Monitored SQL Executions
I click on SQL ID i want to investigate and comes back with blank screen instead of the overview tab.
See Oracle URL below for screenshot
Figure 6-8 Monitored SQL Execution Details Page
[URL]
I have EM Agent 10g installed in its own Oracle Home (c:oracleagent10g) and Oracle 11g installed in its own Oracle Home (c:oracle11.2.0) The Server shows in EM console but the only target is the EM Agent,.. neither the Listener or the Database show under the targets tab in EM. Also, when DBCA is ran and we get to step 4 it shows NO AGENTS FOUND.
Have installed patch: 8825226 and 8968580 for EM Grid Control (Windows 2003 R2 32Bit) And 9138201 for 10g Agent on Server 2008 R2 (64bit)
I need to create a report that shows only the DD-MON of a birthday while ignoring the year and then having the output in DATE form so I can sort chronologically. My input is in DD-MON-YYYY format.
I have used Date_Trunc to pull out the month and day but the 29-Feb dates are giving me the ORA-1839 error message.
I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues. I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.
public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)
throws Exception { logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());
FileInputStream is = new FileInputStream(pDoc);
// Create the print job DocPrintJob job = pService.createPrintJob();
//Set print request attributes with file name as job
[code].....
why a Gantt chart would show only the first 15 rows of the query?! I validated id to be unique , parent_id properly pointing to the parent. When I artifically create another level, the chart still subborny shows only 15 rows. The actual query returns 1590 rows.
View 1 Replies View Relatedcreate TABLE pan_number(pan CHAR(10) NOT NULL);
INSERT INTO pan_number(pan) VALUES('ABCDE1234A');
INSERT INTO pan_number(pan) VALUES('FGHIG5678F');
INSERT INTO pan_number(pan) VALUES('ABCDE12345');
INSERT INTO pan_number(pan) VALUES('ABCD1234A');
select * from pan_number;
now i need to validate valid PAN number which is of " The first 5 letters should be alphabets & last letter as alphabet & total length of PAN no. should be 10 digit"
i need to display valid PAN number , only first two rows are valid PAN numbers.