— SQL — Oracle, Encontre o nome e endereço dos alunos e professores de São Paulo

Olá, estou com uma dúvida na questão que meu professor da Faculdade me passou para treinar em casa, no exercício em questão temos 5 tabelas, sendo uma delas para os alunos e uma delas para os professores, no exercício pede para puxar os residentes de São Paulo porém a maneria cuja a qual eu estou resolvendo trás consigo todos os alunos do professor que mora em São Paulo e no caso do aluno morar em São Paulo trás junto o professor (caso esse resida em outra cidade.)

ps: é para ser resolvido com apenas uma linha de código
pss: os nomes e informações são meramente ilustrativos

Informações do problema p/ resolução ————————-

create table Alunos (
ra number(9) constraint ra_pk primary key,
nm_aluno varchar(30) constraint nm_aluno_nn not null,
endereco varchar(50),
cidade varchar(30)
);

create table Disciplinas (
cod_disc number(5) constraint cod_disc_pk primary key,
nm_disc varchar(50) constraint nm_dis_nn not null,
carga_hor number (4,2)
);

create table Professores (
cod_prof number(9) constraint cod_prod_pk primary key,
nm_prof varchar(50) constraint nm_prof_nn not null,
endereco_prof varchar(50),
cidade_prof varchar(30)
);

create table Turma (
cod_disc number(5) constraint cod_disc_fk references Disciplinas
constraint cod_disc_nn not null,
cod_turma number(5) constraint cod_turma_pk primary key,
cod_prof number(9) constraint cod_prof_fk references Professores
constraint cod_prof_nn not null,
ano number(4),
horario number(4,2)
);

create table Historico (
ra number(9) constraint ra_fk references Alunos
constraint ra_nn not null,
cod_disc number(5) constraint cod_disc_fk_tr references Disciplinas
constraint cod_disc_nn_tr not null,
cod_turma number(5) constraint cod_turma_fk references Turma,
cod_prof number(9) constraint cod_prof_fk_tr references Professores
constraint cod_prof_nn_tr not null,
ano_tr number(4),
frequencia number(4),
nota number(2)
);

insert into Alunos values (945441422,’Wesley Junior Francisco’,’Rua Renato Marques Jr’,’Minas Gerais’);
insert into Alunos values (955655481,’Gustavo Dias Rodrigue’,’Rua Mato Grosso’,’São Paulo’);
insert into Alunos values (458586521,’Lucas Ferreira Silva’,’Rua Benedicto de Souza Branco’,’Rio de Janeiro’);
insert into Alunos values (686955654,’Ana Vicari Beatriz’,’Rua Rangel Pestana’, ‘São Paulo’);
insert into Alunos values (326546985,’Yasmim Santos’,’Rua Macapá’,’São Paulo’);
insert into Alunos values (963258741,’Geraldo Francisco Farias’,’Rua Cisne’,’Guarulhos’);
insert into Alunos values (654987321,’Clarice Jaqueline’,’Avenida Contorno’,’Guarulhos’);
insert into Alunos values (845621789,’Vinicius Murilo Gomes’,’SES 801 Lote 05′,’Brasília’);
insert into Alunos values (541258963,’Bryan Arthur Gonçalves’,’Rua 3 de Setembro’,’Manaus’);
insert into Alunos values (852654951,’Aurora Heloisa’,’Rua Antonio de Godoi’,’São Paulo’);
insert into Alunos values (741489951,’Cauã Eduardo Vicente Peixoto’,’Rua A’,’Palmeira dos Índios’);
insert into Alunos values (951654753,’Adriana Laura’,’Rua Rio Brilhante’,’São Luís’);
insert into Alunos values (357963369,’Miguel Gustavo’,’Rua Carolina Lopes de Faria’,’Garulhos’);
insert into Alunos values (963987951,’Alana Rayssa Caroline Fogaça’,’Quadra Orla 14 Alameda 7′,’Santos’);
insert into Alunos values (456456456,’Elza Daiane Assunção’,’Quadra SHCES Quadra 611 Área Especial s/n’,’Palmas’);
insert into Alunos values (541523587,’Luzia Clara Cavalcanti’,’Praça Humberto Mendes’,’Goiânia’);

insert into Disciplinas values (12366,’Banco de Dados’,04.30);
insert into Disciplinas values (32145,’Projeto de arquitetura’,02.55);
insert into Disciplinas values(45612,’Construção civil’,03.00);
insert into Disciplinas values(51234,’Direito Penal’,04.25);
insert into Disciplinas values(44566,’Antropologia biológica’,02.30);

insert into Professores values(789456123,’Vania Cristina’,’Rua Brás Pina’,’São Paulo’);
insert into Professores values(852963741,’Marcel Thomé’,’1ª Travessa Beira Rio’,’Rio de Janeiro’);
insert into Professores values(147852369,’Alice Flora’,’Avenida Brasil’,’Rio de Janeiro’);
insert into Professores values(578951258,’Wagner Luiz’,’Travessa Treze de Abril’,’São Paulo’);
insert into Professores values(951753698,’Maria Cristina’,’Avenida Jundiaí’,’Jundaí’);

insert into Turma values(12366,14785,789456123,2015,15.30);
insert into Turma values(32145,14765,852963741,2010,19.15);
insert into Turma values(45612,25851,147852369,2011,07.50);
insert into Turma values(51234,32145,578951258,2000,07.50);
insert into Turma values(44566,32154,951753698,2018,13.30);

insert into Historico values(945441422,12366,14785,789456123,2010,15,4);
insert into Historico values(955655481,32145,14765,852963741,2010,100,5);
insert into Historico values(458586521,45612,25851,147852369,2011,25,7);
insert into Historico values(686955654,45612,32145,578951258,2000,35,5);
insert into Historico values(326546985,51234,32154,951753698,2018,55,5);
insert into Historico values(963258741,44566,14785,789456123,2015,6,6);
insert into Historico values(654987321,44566,14765,852963741,2010,45,8);
insert into Historico values(845621789,45612,25851,147852369,2011,8,10);
insert into Historico values(541258963,51234,32145,578951258,2000,1,10);
insert into Historico values(852654951,51234,32154,951753698,2018,10,6);
insert into Historico values(741489951,44566,14785,789456123,2010,5,1);
insert into Historico values(951654753,12366,14765,852963741,2011,6,3);
insert into Historico values(357963369,32145,25851,147852369,2012,52,6);
insert into Historico values(963987951,45612,32145,578951258,2013,92,9);
insert into Historico values(456456456,51234,32154,951753698,2014,65,2);
insert into Historico values(541523587,44566,14785,789456123,2017,4,8);


Método que eu utilizei mas não é o correto :

select nm_prof, endereco_prof,cidade_prof nm_aluno,endereco from historico h inner join alunos a on h.ra=a.ra inner join professores p on h.cod_prof=p.cod_prof where cidade = ‘São Paulo’ or cidade_prof=’São Paulo’

Resultado está anexado como imagem.
inserir a descrição da imagem aqui

Espero ter conseguido expressar minha dúvida e agraço quem conseguir sana-lá.

Lines from Oracle Spatial Layer in QGIS are not completly visible

I connected a spatial layer from the Oracle database which contains lines (OracleDB objectclass typ is polyline) to be visible in QGIS.

An example geometry is as follows:

sdo_geometry(2002,2056,null,
    sdo_elem_info_array(1,4,3,1,2,1,7,2,2,11,2,1),
    sdo_ordinate_array(2610857.31245004,1266613.32122543,2610857.70740207,1266610.23735626,2610858.72627747,1266602.3016907,2610859.88613344,1266593.26306758,2610859.84612155,1266592.56309404,2610859.5561095,1266591.92511322,2610859.27210097,1266591.49512447))

In QGIS only a part of the above line is visible. I know that this line is not only straight. So I was wondering, if there is kind of an “arc-problem” behind this and if there is any known solution to the problem?

I didn’t find anything in the official QGIS documentation.

Missing Oracle Schema in SSMA Object Explorer

We are currently working on a project to move all of our disparate DBs (MySQL, Oracle, older versions of MS SQL) to SQL Server 2014. So far, pretty much everything has gone smoothly, however, there is one Oracle Database (10g 10.2.0.5), which we’re trying to migrate using SSMA that just doesn’t show up in the SSMA object explorer.

I have tried connecting to this database using the schema owner, the sys AND the system users and it just never shows up. ALL the other schemas on this instance show up and we can migrate them successfully.

Has anyone come across this issue before? Could anyone suggest possible reasons for this? I – and the SQL Server DBA – am stumped. In the interim, we’re migrating subsets of data using smacked-together Perl scripts, but this is obviously not ideal.

Deployed application (Oracle 11g, VB.NET 2015 and Crystal Reports) cannot connect to database

I’m developing an application using Oracle 11g, VB.NET 2015 and Crystal Reports on Windows 10. I created a package and installed it on another PC on same network. The package is running fine but unable to connect to the database… Can you give advise/tips how I can deploy the application on client/server, my current network environment is Windows 10.

Oracle XML Database database invalid, how to validate/recompile … can i drop and recreate it. Please…

The installed components and their status,

Oracle Database Catalog Views 12.1.0.2.0 Valid
Oracle Database Packages and Types 12.1.0.2.0 Valid
Oracle XML Database 12.1.0.2.0 Loaded

The XML Database component states as loaded, as there are invalid objects.

–> Tried to recompile invalid objects by running utlrp.sql but no use, still the invalid objects exist.

–> Unable to expdp/impdp due to sys objs being invalid with the below error,

ORA-04063: package body “SYS.DBMS_METADATA” has errors
04063. 00000 – “%s has errors”
*Cause: Attempt to execute a stored procedure or use a view that has
errors. For stored procedures, the problem could be syntax errors
or references to other, non-existent procedures. For views,
the problem could be a reference in the view’s defining query to
a non-existent table.
Can also be a table which has references to non-existent or
inaccessible types.
*Action: Fix the errors and/or create referenced objects as necessary

Tried the below as suggested by Oracle docs but still, the issue persist

1) Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)

Oracle 10.1 and above – XDB Reload
spool xdbreload.log
connect / as sysdba
set echo on;
shutdown immediate;
startup upgrade;
@?/rdbms/admin/xdbrelod.sql
shutdown immediate;
startup;
@?/rdbms/admin/utlrp.sql
spool off

2) XDB is INVALID in DBA_REGISTRY after Fresh Installation (Doc ID 1297620.1)

3) Oracle XML Database (XDB) Installation on 12c – XDB invalid status after fresh installation/upgrade (Doc ID 2222780.1)

an application (oracle 11g vb.net 2015 and crystal Reports )deploy on client/server

Im developing an application using oracle 11g, vb.net 2015 and Crystal Reports on window 10, i created a package and install an other pc on same network package is running fine but unable to connect to the database… can you give advise/tips how i can deploy the application on client/server, my current network environment windows 10

Install oracle java 6 error issue

I’m trying install oracle-java6-installer on ubuntu 16.04.

And I install it with below steps.

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java6-installer

But I will get this error bellow.

Reading package lists... Done
Building dependency tree       
Reading state information... Done
Suggested packages:
binfmt-support visualvm ttf-baekmuk | ttf-unfonts | ttf-unfonts-core 
ttf-kochi-gothic | ttf-sazanami-gothic ttf-kochi-mincho | ttf- 
sazanami-mincho ttf-arphic-uming
The following NEW packages will be installed:
oracle-java6-installer
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/21.0 kB of archives.
After this operation, 209 kB of additional disk space will be used.
Preconfiguring packages ...
Selecting previously unselected package oracle-java6-installer.
(Reading database ... 215570 files and directories currently 
installed.)
Preparing to unpack .../oracle-java6-installer_6u45- 
0~webupd8~8_all.deb ...
oracle-license-v1-1 license has already been accepted
Unpacking oracle-java6-installer (6u45-0~webupd8~8) ...
Processing triggers for bamfdaemon (0.5.3~bzr0+16.04.20180209- 
0ubuntu1) ...
Rebuilding /usr/share/applications/bamf-2.index...
Processing triggers for gnome-menus (3.13.3-6ubuntu3.1) ...
Processing triggers for desktop-file-utils (0.22-1ubuntu5.1) ...
Processing triggers for mime-support (3.59ubuntu1) ...
Processing triggers for shared-mime-info (1.5-2ubuntu0.1) ...
Setting up oracle-java6-installer (6u45-0~webupd8~8) ...
Downloading Oracle Java 6...
--2018-04-19 09:46:17--  http://download.oracle.com/otn- 
pub/java/jdk/6u45-b06/jdk-6u45-linux-x64.bin
Resolving download.oracle.com (download.oracle.com)... 23.53.64.147
Connecting to download.oracle.com 
(download.oracle.com)|23.53.64.147|:80... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://edelivery.oracle.com/otn-pub/java/jdk/6u45-b06/jdk- 
6u45-linux-x64.bin [following]
--2018-04-19 09:46:17--  https://edelivery.oracle.com/otn- 
pub/java/jdk/6u45-b06/jdk-6u45-linux-x64.bin
Resolving edelivery.oracle.com (edelivery.oracle.com)... 
104.116.18.92, 2600:1417:1b:184::2d3e, 2600:1417:1b:188::2d3e
Connecting to edelivery.oracle.com 
(edelivery.oracle.com)|104.116.18.92|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: http://download.oracle.com/otn-pub/java/jdk/6u45-b06/jdk- 
6u45-linux-x64.bin? 
AuthParam=1524102497_fa8d9de89ff9d81e73b656b883c0b633 [following]
--2018-04-19 09:46:17--  http://download.oracle.com/otn- 
pub/java/jdk/6u45-b06/jdk-6u45-linux-x64.bin? 
AuthParam=1524102497_fa8d9de89ff9d81e73b656b883c0b633
Connecting to download.oracle.com 
(download.oracle.com)|23.53.64.147|:80... connected.
HTTP request sent, awaiting response... 404 Not Found
2018-04-19 09:46:18 ERROR 404: Not Found.

download failed
Oracle JDK 6 is NOT installed.
dpkg: error processing package oracle-java6-installer (--configure):
subprocess installed post-installation script returned error exit 
status 1
Errors were encountered while processing:
oracle-java6-installer
E: Sub-process /usr/bin/dpkg returned an error code (1)

How can I solve this problem?

gnome-shell on Bionic as guest in Oracle Virtualbox is super slow — system%

I just installed Ubuntu Bionic (18.04.1) (Unity desktop) on both Oracle VB VirtualBox (5.2.18) and VMware Player (v14). I have Guest Additions on both VMs.

On VB, all graphics interactions are super slow. Opening the Dash takes forever (~ 1min) and opening both LibreOffice Write and Calc take several minutes.

The problem seems to be gnome-shell. When I use nmon, I see 1 CPU at 100% system wait state. When I kill a gnome-shell, that goes away — for a while. I know gnome-shell is very 3-D graphics intenstive; but VMware doesn’t seem to have this problem. I’d rather use VB, but I can’t under these circumstances.

I have 3D acceration enabled on both, and I’ve checked /usr/lib/nux/unity_support_test. I’m using 8Gb Ram on both. I assigned 4 “CPUs”. My host is a Dell XPS i7.

Has anyone else been able to get Unity on VB to run ‘acceptably’ like it’s a real machine? It feels like something with the graphics emulation, but I can’t figure out what? (Xubuntnu runs acceptibly on VB, but not Unity.)

Oracle custom aggregate [duplicate]

This question already has an answer here:

  • How do I create a user-defined aggregate function?

    3 answers
Year    Value
2012     10
2013     20
2014      0

I need 10 * 20 * 0 for multiple rows.
If I use this function EXP (SUM (LN (Value))), I get an ORA-01428 error:

argument ‘0’ is out of range.

I have seen How do I create a user-defined aggregate function? but that is for MySQL, not Oracle and my requirement is to include zeroes.