Most voted "pl-sql" questions
PL/SQL (acronym for Procedural Language/Structured Query Language) is an extension of the standard SQL language for Oracle DBMS from Oracle Corporation.
Learn more…277 questions
Sort by count of
-
18
votes2
answers7922
viewsWhat is and what is the utility of the DUAL table for Oracle?
I came across this scenario during the modification of a report in the system: One of querys is returning to this such table DUAL: select * from dual But in the system there is no DUAL table, and…
-
9
votes2
answers31808
viewsThe difference between COALESCE and NVL
In Oracle when we have a table that has two campuses A and B, and in selection (select) we want to show B when A is null we have two commands that present us the solution: Coalesce Select…
-
7
votes2
answers4076
viewsSimple example of how to generate an XML file on Oracle
One would have a simple example of a PLSQL Procedure (or documentation location) to generate an XML file based on a Schema (xsd), the data would be generated from an SQL query. In reality archives…
-
6
votes1
answer2639
viewsCall PL/SQL precedent with object collection
Consider the following types: CREATE TYPE meu_tipo AS OBJECT ( meu_id NUMBER(6), meu_nome VARCHAR2(200) ); CREATE TYPE meu_tipo_tabela AS TABLE OF meu_tipo; And the following package: create or…
-
5
votes1
answer419
viewsProblems using Java classes in Oracle Database
I’m having trouble using classes Java a little "complex" in the Oracle. When I use a simple class, as an example below: CREATE JAVA SOURCE NAMED "Welcome" AS public class Welcome { public static…
-
5
votes1
answer9809
viewsOracle - Filter by date
I have a column dt_atualiza_log who’s with the guy DATE in the table (is saved in format 01/12/2011 10:10:48) I’m having trouble filtering through and of my query. I’ve tried several ways, the last…
-
5
votes1
answer9144
viewsUPDATE WITH CASE SQL
Is there any way I can use more than one column of the same table in a case statement in an update? for example, I have this table with these 3 lines create table produto2( codigo int primary key,…
-
4
votes4
answers33983
viewsHow to copy column data from one table to another table
I have a table called UC which has an email column, and I have a backup table of it. I need to copy the data (emails) from the table’s email column backup for the column email table uc, but I don’t…
-
4
votes2
answers3709
viewsHow do I update multiple rows of a table after it undergoes a certain change?
CREATE or REPLACE TRIGGER TG_ATUALIZA_BLOQUEIO AFTER UPDATE ON pcclient FOR EACH ROW begin if :NEW.BLOQUEIO = 'S' THEN UPDATE PCCLIENT SET BLOQUEIO='S' where codcliprinc=:OLD.CODCLIPRINC; END IF;…
-
4
votes1
answer322
viewsWhat is the most efficient way to connect an Oracle BD to a Mysql BD
Having the following scenario: a simple telephony application "writes" in a Mysql BD and an Oracle BD needed from time to time to search for information in this Mysql BD. Someone already did this?…
-
4
votes1
answer396
viewsXA transaction does not commit changes in procedures (intermittent)
I have an EJB Stateles that monitors files that fall into a folder. When a file arrives it is handled, a receiving event is recorded in a central database and the file is inserted into a target…
-
4
votes0
answers133
viewsUncaught Typeerror: Object is not a Function. Clone html and Back button
I’m having a system problem where I need to make a button for come back and thought of a function to clone using and a function for the button come back. But the problem is that when I log in to the…
-
4
votes0
answers84
viewsOracle , how the parameter no_proxy_domains of UTL_HTTP.set_proxy works
In the package UTL_HTTP , we have the method set_proxy in it a parameter no_proxy_domains , how this works. In short I want to request a URL without authentication.…
-
4
votes5
answers3132
viewsWhat is the difference between comparison operators on Oracle?
In Oracle there are several operators to make "different" comparisons, such as: <> ¬= != ^= Example: Select * from tabel where nomeTabela <> 's'; Select * from tabel where nomeTabela !=…
-
4
votes1
answer144
viewsWhy does Oracle not have autoincrement?
Assuming that it is known that Oracle has the resources of the table quence. I would like to understand why they only recently implemented the autoincrement in Oracle, since it was a feature…
-
4
votes2
answers1687
viewsPL/SQL Anonymous block | PLS-00103: Encountered the Symbol "CREATE" when expecting one of the following:
I get the following error : [Err] ORA-06550: line 7, column 2: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: 'Cause I’m using a create Quence inside my BEGIN with ;…
-
3
votes1
answer7688
viewsError comparing ORA-00932 dates
I’m having a problem comparing two dates in the oracle. I’m using the case method to compare the dates and at the end this should return to my table a number, which in the case represents an age,…
-
3
votes2
answers10071
viewsUpdate to clear entire column
I need to make a update in the database in which zero 30 thousand records, however I need to reset only the record of one of the columns. Example table: cd_product|ds_product|pr_product|dt_update…
-
3
votes1
answer204
viewsConsider the final term until the next day at 03 am using PLSQL
Guys, I need to do the following validation: When a record enters my database, I need to validate the effective date of this record. If the term is between the current date then the record is valid.…
-
3
votes5
answers37379
viewsHow to update with Oracle Inner Join
Good afternoon , How do I update with Inner Join , because I need to change the field idnaoleitura = 24 to idnaoleitura = 0 where idrota in (35,45,48,53,60,68,70,79) , remembering the idrota this in…
-
3
votes1
answer3292
viewsMove text in pl/sql to uppercase
I have many create table scripts, Quence and etc, but everything is in lowercase, what the colleague did. Deleting line by line and capitalizing is unproductive as there are more than 1000 scripts…
-
3
votes2
answers1159
viewsOracle reference the type of a column equal to the type of another column
I’m trying to create a table to return in a function. For this, I am trying to create a object type thus: create or replace type relatorio as object ( planta TABLE_NAME.COLUMN%TYPE, resp_0…
-
3
votes1
answer7165
viewsConversion and Grouping of Rows into columns dynamically in Oracle
Context: I have a Project mixed Versions of apps that will be developed, each various Version New Feature Requirement (NF) means each NF with several Code changes in several Repositories. In…
-
3
votes1
answer258
viewsWhy using a column of a virtual table makes an SQL in Oracle slow
Good. Without wanting to leave the subject too "gassy" but wanted only tips of things to investigate in the environment. I have an SQL that uses a virtual table, for a number of reasons that are not…
-
3
votes1
answer1116
viewsRead a DBMS Procedure in ADVPL
It is possible to read a DBMS Procedure in ADVPL (TOTVS language) treating the return of a Cursor ? Is there any object ? The bank in question is Oracle. create or replace PACKAGE abc IS TYPE…
-
3
votes1
answer205
viewsImplement Triggers in Postgre
It has how to implement a Rigger to work in a column that is present in all tables of the database?
-
3
votes1
answer345
viewsSelect cities with the same name in different states
I have the table (result of a query) with the cities and their respective state... ... now only display cities with equal names and different states: How do I command?…
-
3
votes2
answers1482
viewsSQL: Doubt in the Relationship between Tables
I am creating an academic system that consists of a virtual restaurant menu type, for this, I created three tables in the bank: Table of Menu Items: CREATE TABLE ITEMS( ID INT PRIMARY KEY NOT NULL,…
-
3
votes1
answer2370
viewsMy dbms output is not displayed
I’m testing a simple exit from DBMS in Sqldeveloper. I created the connection, ran the set serveroutput on to enable the exits DBMS, but when I run my anonymous code pad, on Saída do Script he…
-
3
votes1
answer64
viewsHelp! How this select works
Help me understand how this works please. I happen to be starting the studies in SQL and hackerhank has an exercise that needed this code: SELECT DISTINCT CITY FROM STATION WHERE…
-
3
votes1
answer580
viewsWhat is the difference between SQL Server, Mysql and other SQL?
I would like to know more clearly what are the main differences between SQL Server, PL/SQL and Mysql, and what are their main strengths/weaknesses.
-
2
votes1
answer263
viewsConditional to print in PL/SQL
I have a report developed in Sybase Infomaker (similar to Crystal Reports) that displays two pages, but I need to print the second page only if there is a calculation. On the second page is…
-
2
votes1
answer175
views -
2
votes1
answer1144
viewsCalling Java class inside Oracle
would like to know if it is possible to call a Java class inside the Oracle database. I was able to install the JVM inside the Oracle server already, but I cannot compile the Java class. Does anyone…
-
2
votes2
answers3093
viewsPLSQL know the amount of records within a "for cursor"
Inside the example below : Begin For r in (select * from tabela) Loop ..... ; End loop; End; Is there any direct way to know inside the loop how many records the cursor has ?…
-
2
votes1
answer388
viewsStored Procedure with low performance
I’m having trouble at the Oracle, where I have two procedures that run one after the other. Where in the first procedure, I have a cursor that does insert on a table. This Insert has approximately…
-
2
votes1
answer300
viewsCall
Does anyone know a method to call a dynamic process? I am creating an html/css/javascript screen in an oracle database. I created a table with the names of the procedures I want to call. That way…
pl-sqlasked 9 years, 4 months ago Mauricio Mendes 51 -
2
votes2
answers3466
viewsSelect all references to a given table (Foreign key)
I need a way to select all the Foreign key of a particular pk. In other words, I want to catch a determined Primary key and select all the Foreign Keys that "point" to her. Preferably I want to get…
-
2
votes1
answer120
viewsQuestion with Table Update Trigger
I have two tables, questions and answers, and would like to update the same according to what is selected: Sql Server Database 2005 Problem: --usuário excluindo a pergunta (não está gravando a data…
-
2
votes0
answers132
viewsreceiving plsql array in javascript
I created a pl cursor, now I’m trying to pass the records to a array javascript to create graphics, the newData will receive the cursor data. Javascript created is this: <html> <head>…
-
2
votes1
answer1201
viewsI must not show negative results
I created a query where I bring some information, among this information the last column returns positive, negative and zeroed values. I don’t want you to display the lines with negative values, but…
-
2
votes2
answers91
viewsQuery mix of oracle products
I made an indicator here in the company where I list product information, quantity sold, average value, etc. That in a period of time. Now I need the following information: Which is the product that…
-
2
votes2
answers24947
viewsHow to do for loop in SQL SERVER?
BEGIN <<LOOP_EXTERNO>> FOR V_EXTERNO IN 1..20 LOOP DBMS_OUTPUT.PUT_LINE('Contador externo: ' || V_EXTERNO); <<LOOP_INTERNO>> FOR V_INTERNO IN 1..5 LOOP…
-
2
votes1
answer2183
viewsList all Synonyms in Oracle
How do I list all the Synonyms(synonyms) present in my database? I need to know which ones are created to compare between banks what exists. I tried with the code below but does not return synonyms:…
-
2
votes2
answers1181
viewsSelect with duplicate record
I have a question that may be trivial for most, but that I stuck here, I seek to research a lot before posting, but I will need a help. I am setting up a select to bring me the closure of the…
-
2
votes1
answer966
viewsIs there a function to calculate the trend line in PL SQL?
Good morning folks. I need a function to calculate a trend line. I have a query (part of the function): select round(sum(nvl(vl_indice, vl_meta))/12, 2) from ( SELECT SUM (vl_indice) vl_indice, SUM…
-
2
votes2
answers60
viewsCalculation for year "turn" with Trigger
How to treat the Trigger below so that the operations carried out between 21/11/2016 and 20/12/2016 return date 20/01/2017 instead of 20/01/2016. DECLARE P_COUNT NUMBER(5); DIA_VENDA VARCHAR(2);…
-
2
votes0
answers384
viewsOracle PL/SQL - Connection with Webserver SEFAZ
Someone here has already been able to establish contact and authenticate SEFAZ (NF-e or Reinf) servers through PL/SQL with Oracle Wallet? For the last few weeks I have been trying to make contact…
-
2
votes4
answers2013
viewsFind the best-selling item along with a particular item
To explain, I prefer an example: I want to find out which item is the most sold along with the salty chicken. For this, I tried the following approach and failed miserably by lack of technical…
-
2
votes2
answers6537
viewsRemove Oracle Special Characters
Good morning, I have a query here made in Oracle where in one of the records is bringing a string starting with quotes, has some function oracle to remove only the quotes at the beginning and at the…