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
-
0
votes2
answers104
viewsTraining ORACLE PL/SQL
Galley, I need to study PL/SQL but my notebook is not supporting Oracle Database Express. Is there a lighter tool or some website I can train? Thank you!
-
0
votes1
answer190
viewsInsert for each record found - PL/SQL
good morning. I’m new to Pl/sql and I need to insert 110 records (Number 1 to 110) in a table ( table: stick) for each ID of the court table. Insert would be more or less: INSERT INTO…
-
0
votes2
answers53
viewsSelect floor() in oracle
In Mysql would be the equivalent of SELECT floor(4.5); How to do the same in Oracle PL SQL?
-
0
votes1
answer20
viewsOracle Loader Help - Variable Field
I need to download a file .txt with multiple records for one table. But this table contains a field (Seq) that does not exist in the text file and this field should always be 1 and 2. For example:…
-
0
votes2
answers995
viewsis not a GROUP BY expression
Hello . I am trying to execute a certain Query on Oracle and I am getting the following error : "is not a GROUP BY expression". Below is the list of tables and query: BILL NRO_CONTA|COD_AGENCIA|NOME…
-
0
votes1
answer823
viewsValidation of CNPJ in PL/SQL Oracle
I wonder if anyone knows or knows a CNPJ validator in PL/SQL Oracle. Needed the user to put the value in a "dbms_output.put_line" and call a validator. I already got a validator that I will put down…
-
0
votes1
answer689
viewsLong to Varchar data conversion
I am trying to convert data from a Long field to Varchar, but when I do the procedure, it is returning the following error: ORA-01422: Exact extraction returns more than the requested number of…
-
0
votes1
answer109
viewsHelp with DBMS_STATS.GATHER_TABLE_STATS - Oracle for DB2
I am passing an Oracle protocol for DB2 and I am not able to run on DBMS_STATS account.GATHER_TABLE_STATS : DBMS_STATS.GATHER_TABLE_STATS ('DMTLDBR','TB_FATO_OBJETIVO',PART_MES_ANT,10, TRUE,'FOR ALL…
-
0
votes0
answers66
viewsLimitations in an SQL query
I have to make a report that shows the main customers of each seller (the top 10 customers who spent on each seller) I am using the Oracle database to make this query. Each seller has more than 40…
-
0
votes1
answer74
viewsError in APEX_APPLICATION.G_F01 - ORACLE APEX
I have a problem with this script below: I have a checkbox on my page ( not in report, on the same page, a page item) and I would like the same when clicking and pressing a Ubmit button to perform…
-
0
votes1
answer238
viewsQuery in two Oracle tables
How do I query two tables at once? I have two table structures as follows. TGFCAB ___________________ |NUNOTA| DTMOV | ------------------| | 1 |01/06/2019| | 2 |02/06/2019| | 3 |03/06/2019| and…
-
0
votes1
answer244
viewsDo you have a better way to format value with space after the symbol of the coin?
Good morning ! I have a need to format the value with a space after the symbol of the coin in Oracle, I managed using the form below: select to_char('0','L')||'…
-
0
votes1
answer110
viewsOracle SMTP does not send hidden copy (BCC)
I’m trying to send a "Blind Copy" by our Email Sending Procedure by documentation would be ok , but is not sending or generating any error , the code is below , the sensitive items were exchanged…
-
0
votes0
answers171
viewsTotal results by products in oracle table
I have two tables similar to the tables below: TGFCAB |NUNOTA| DTMOV | ------------------| | 1 |01/06/2019| | 2 |02/06/2019| | 3 |03/06/2019| TGFITE |NUNOTA|CODPROD|QTDNEG|VLRUNIT |VLRTOTAL|…
-
0
votes1
answer136
views -
0
votes1
answer1449
viewsPL/SQL Insert with cursor
Everybody, good afternoon, everybody. I have 2 tables, one of foro_tribunal and another of vara In the table of vara, need to include 111 records by foro_tribunal, so I created a foro_tribunal as a…
-
0
votes1
answer56
viewsHow to make a "Case When" when the condition is called a Package with the function and Passed the Parameters?
I am trying to make a modification in the Query without destroying a logic already made. Original Query: IF Pcoluman = 'aceito' THEN if (v_respon_alg = 'Y') OR (v_respon_las = 'Y') OR (v_respon_nye…
-
0
votes1
answer70
viewsLog trigger that saves the numbering of the JOB that executed the change
I need an oracle resource to use on a Trigger, which searches which JOB or Scheduler_job executed such a change. I am using Sys_context to get some example information: User, Terminal, Program…
-
0
votes0
answers51
viewsOracle DDL trigger , identify a "drop column"
In Oracle it is possible to create DDL triggers (Data Definition Language) something like create or replace trigger trg_ddl before ddl on database declare begin ... end; My , doubt , how to…
-
0
votes1
answer144
viewsConsume REST API from a PL (Postgresql 11)
Hello, I am working on a project in which I must build a PL that should consume a REST service (return in JSON). Looking at the Postgresql documentation, I did not find equivalent functions to…
-
0
votes1
answer55
viewsSearch records filtering by Count from a subconsulta
I have the following problem: I have three tables and need to show the employee record only when it has 1 and only 1 dependent. And it should also have 0 and at most 1 prize record, according to the…
-
0
votes0
answers40
viewsIs it possible to select periods from a table in a select without subquery?
Greetings, I wonder if it is possible to select a table period without using a subquery as an example below: Select Usuario, Divida.Janeiro , Divida.Fevereiro, Divida.Marco ... from Usuarios a left…
-
0
votes0
answers114
viewsDBMS_OUTPUT.PUT_LINE command in oracle does not print anything and still the block is executed
I’m doing some exercises of fixing PL/SQL only that overnight the DBMS_OUTPUT.PUT_LINE stopped to print things in the SQL Developer console, and I have tried opening a new script or in several types…
-
0
votes0
answers80
viewsPLSQL - Nextval fault
The following code: Create a Trigger that creates a log record every time a product is changed. CREATE OR REPLACE TRIGGER CREATE_LOG AFTER UPDATE OR INSERT ON PRODUTO FOR EACH ROW BEGIN INSERT INTO…
-
0
votes0
answers152
viewsValidate record that has comma in the Oracle PL SQL field
I’m creating a procedure in PL SQL and I have an "unregulated" field that may have more than one comma-separated record. That one procedurecarries out the cancellation of sending extracts of a…
-
0
votes0
answers260
viewsNull values in Oracle
So guys, I’m having a problem inserting a data into my Oracle tables, entering the data through a PLSQL routine, and I’d like to know how to insert data into a respective table, but if one of the…
-
0
votes0
answers37
viewsHow to limit to not being able to pass 3 units of a book in a PL/SQL ORACLE rental
I’m making a bookstore in APEX and I can’t allow the client to rent more than 3 books. But even putting the code the program does not bar. declare W_QUANTIDADE_EMPRESTIMOS number; W_STATUS…
-
0
votes1
answer29
viewsHow do I add a new user to a PL/SQL table using a function?
I recently started working with PL/SQL and one of the doubts I have is in creating and declaring functions. More specifically on this: -Creates a function that creates a new user: 1: Usa uma…
-
0
votes1
answer118
viewsProblem in the condition of Trigger - PL-SQL
My teacher passed that question: Write a Rigger that checks, during registration/change of a sale, the following: 1) The QUANTITY field of the ITEMVENDA table shall not have decimal places and shall…
-
0
votes0
answers35
viewsPROCEDURE ignoring update - PL-SQL
I made the following code: CREATE OR replace noneditionable PROCEDURE comissao IS BEGIN FOR v_linha IN ( SELECT p.codvendedor AS codvend, p.comissao AS comissao, SUM(f.valortotal) AS quant FROM…
-
0
votes1
answer28
viewsUse more than one value to use parameter in an Oracle function
Good afternoon, I have the following problem: I need to perform a function that calculates the average value of a product on a specific date. SELECT VERIF_VL_CUSTO_MEDIO(PRODUCT.CD_PRODUTO,…
-
0
votes1
answer62
viewsHow to insert a constant in a PL/SQL line?
Hello, I’m having some difficulties in data presentation. with atores(nome, data_nasc) as (select nome, data_nasc from representa inner join artistas using(n_artista)) ,realizadores(nome, data_nasc)…
-
0
votes1
answer40
viewsBring manager and employee who are on the same table
I have a table of EMPLOYEES and another call HIER_SAP. In the HIER_SAP I have two important fields for what I want, that are the fields CODSUP and CODFUNC. For each CODFUNC(official) I have a…
-
0
votes0
answers50
views -
0
votes0
answers80
viewsHow to return materials from a product or product levels with PL/SQL
I have a table that contains products and each product has other products that are used to manufacture. The products that are used to manufacture the parent product, can also be manufactured and…
-
0
votes1
answer191
viewsWrite Oracle SQL File
Good morning, I am beginner in Oracle PL/SQL, and I created the script below to record the result of a query in txt file, runs without errors but does not write the file, anyone knows what could be…
-
0
votes0
answers50
viewsCall a PL/SQL precedent by passing a date as parameter
I have a PL/SQL design ( LnCDOrigem IN Company.COD_CD%TYPE, LnSotoreOrigem IN Company.COD_STORE%TYPE, LvTipoEqpt IN VARCHAR2, LdDtMovimento IN DATE, LnRetorno OUT NUMBER) When I make her call…
-
0
votes1
answer73
viewsRelationship with log table
Guys, guys, beauty?I have two tables from an oracle database: Fields in Table A: No. do chamado Data de conclusão do chamado Funcionario que concluiu Fields in Table B: Funcionario Area do…
-
0
votes0
answers65
viewsHow to create Procedure in PL/SQL, Passing CPF as Paramento and returns 1 if it is the birthday date of the CPF informed and 0 if it is not
How I created this project in PL/SQL? i created in SQL SERVER, is working however, I need to create also in PL/SQL. CREATE PROCEDURE [dbo].[SP_DataAniversario] ( @CPF VARCHAR(20) ) AS SELECT * FROM…
-
0
votes1
answer54
viewsORACLE displaying date field with 1 more day at the end of the months with 31 days
I created a function in ORACLE to calculate the date of the last installment by passing as parameter the number of installments and using the function ADD_MONTHS(DT_PRIM_PARCELA, INSTALLMENTS).…
-
0
votes1
answer117
viewsIs there any way to get a JSON "result" using Oracle’s utl_http package
Is there any way to get a JSON "result" using the package utl_http oracle ? The status code of the following routine returns 200 but would also return a "Sponse" with a JSON conformation, an…
-
0
votes0
answers60
viewsAdd values to a PL/SQL cursor
Good night. I am trying to define the values of a cursor in PL/SQL, however one of the values is never filled. In this case, the qtd_daysSummary number never has content, however all others do,…
-
0
votes2
answers59
viewsHow to make a SELECT to get the value of the last registered date
Hello, I would like a help to always use the value of a table. I have a table "COSTS" and every month it suffers price change of the products, but are not all that has update My question is that…
-
0
votes1
answer27
viewsPL/SQL Return specific value of a variable string
I need to separate specific information from a string within a field, in case only the value "refDate:dd:mon:yy", but the string varies in each record. How could it bring about the desired result?…
-
0
votes0
answers45
viewsHow to filter a select result based on data contained in another table?
Good afternoon, Folks! I’m having a hard time building a select that is filtered based on a data contained in another table. To contextualize: It is a warehouse management system (WMS) and need to…
-
0
votes0
answers31
viewsOnly allow the transfer of files sent and recorded on the station (temporary), to the user’s machine after all processed
Someone can help me, such as just downloading the temporary file to the user folder after processed. Below is an example. Example: Temporary Station: Recorded files S-29012021-007-0000192.txt…
-
0
votes1
answer35
viewsPLSQL shows error: PLS-00103 in function when using an Exception
PLSQL is reporting error PLS-00103 when I try to compile this function. Note: If I try to run only with the first option of this case "P_TIP = 1" it runs normally. If I try to add the case "P_TIP =…
-
0
votes1
answer97
viewsOracle PLSQL - How to get the lowest and highest value from a list of values in a range?
CREATE TABLE TABELA_PRAZO ( CEP_INICIO NUMBER(8) NOT NULL, CEP_FIM NUMBER(8) NOT NULL, PRAZO NUMBER(3) NOT NULL ); insert into tron2000.TABELA_PRAZO(CEP_INICIO, CEP_FIM, PRAZO) values (1000000,…
-
0
votes1
answer24
viewsError in the subselect of an sql script to generate update script bringing more than one line and giving errors
I have two tables, a partner of which some fields of cnpj_cpf are wrong and another that I went up with the corrected data, totaling I have approximately 1 million lines to solve, so I generated a…
-
0
votes0
answers39
viewsHow to use one column to get value from the other in PL/SQL
I have the following tables in PL/SQL: employees ( employee_ed, first_name, last_name, phone_number, hire_data, job_id, salary, manager_id, department_id ) departments ( department_id,…