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
votes1
answer275
viewsHow to compare two fields of identical values to different sizes?
I have a database in which have fields with the same name but with different size I need to pick the field with the larger VARCHAR. How can I do that ?…
-
0
votes1
answer1960
viewsHow to call another Procedure or Function within the same Packge in oracle?
I have rods procedures and functions within the same package. How can I reference some within my PL-SQL code? PACKAGE: CAIXA1 PROCEDURE: CLOSE BOX, SUM TOTAL FUNCTION: TOTALGRADES How can I…
-
0
votes1
answer616
viewsIn pl-sql can I assign the SUM value to a variable?
In the system has a variable vsoma which shall receive the total sum of the column Values. How can I assign the sum value SELECT sum(valor) from PRODUTOS2?…
-
0
votes1
answer268
viewsHow to display two tables in Sqldeveloper at the same time?
I am unable to filter two tables at the same time in SQL Developer at the same time. When I insert two tables into the same filter sqldeveloper shows nothing!…
-
0
votes0
answers33
viewsIgnored INSERT clause in the past
I’m having a problem that I’m already starting to believe is lack of faith. I have a proc that performs a series of Inserts and updates. She goes through the first Insert and for the first update…
pl-sqlasked 7 years, 3 months ago Christopher Queiroz 1 -
0
votes0
answers105
viewsHow do I scan a string and bring different parts of it?
Using REGEX it is possible to "concatenate" masks to bring different parts of the same string? How do I scan a string and bring different parts of it? I have a text field with the following…
-
0
votes1
answer72
viewsSelect the value of a field depending on a date
I have a table with the fields DT_FIMCALCULO, VL_DEPREREVVIDAUTILACUMULADA e CD_SEQUENCIALBEM. And for each CD_SEQUENCIALBEM I have several DT_FIMCALCULO each with a VL_DEPREREVVIDAUTILACUMULADA…
-
0
votes3
answers127
viewsSelect dates less than a given year
How can I create select to show only employees of a company who have been admitted to it before a certain year ? The type anoAdmissao is date ie: dd-mm-AA…
-
0
votes3
answers381
viewsHow to select the year in PLSQL?
I have a chart of company employees. Must show who completed a year of work at the company. The data type for the field Information: DATE (dd/mm/YY)…
-
0
votes1
answer138
viewsError After Running Error: No output displayed
Code CREATE OR REPLACE PROCEDURE buscaturma(cod NUMBER) AS nomeT varchar2(20); BEGIN IF (cod=0)then dbms_output.put_line('ZERO'); ELSE SELECT DS_TURMA into nomeT FROM TURMA WHERE CD_TURMA = cod;…
-
0
votes1
answer214
viewsHow to run PROCEDURE and FUNCTION at the same time?
I have this plsql code and I must run at the same time: /*Testar se o codigo é valido*/ CREATE OR REPLACE FUNCTION TESTE1(cod number) RETURN BOOLEAN IS begin if(cod>0)then return true; end if;…
-
0
votes1
answer551
viewsIn PL-SQL how to partially add a value?
I have to add the value partially the values of a category (goes the code below) DECLARE --VARAVEIS CURSOR CPRODUTO IS SELECT * FROM PRODUTO_TESTE2; VPRODUTO PRODUTO_TESTE2%ROWTYPE; Vsoma real;…
-
0
votes2
answers9821
viewsSelect to display the column name of a table?
Tables col1| col2| col3| ...| coln 1 | 2 | 3 | ...| n Display the name : col1 col2 col3 ... coln…
-
0
votes1
answer952
viewsFind Relationship between Oracle Tables
I wonder if it is possible to find the tables and fields involved in an oracle relationship, a query that would return a table like: Table_name, Column Name, Table_references Funcio,id_Cargo,Cargos…
-
0
votes1
answer231
viewsSelect Scale in PL/SQL - Oracle SQL Developer
I come from a background of T-SQL (MS SQL Server) and have things that do not work equally in PL/SQL. I would like to know how to select scalars in PL/SQL. Examples in MS SQL Server Example 1:…
-
0
votes0
answers294
viewsError while displaying message in PL-SQL
Code DECLARE BEGIN FOR I IN (SELECT * FROM PRODUTOS_TESTE2) LOOP DBMS_OUTPUT.PUT_LINE(I.codigo|| ' - ' || I.categoria || ' - ' || I.VALOR); END LOOP; END; Error message: Relatório de erros -…
-
0
votes0
answers684
viewsOracle error ora-06550 pls-00103:
I have this PL-SQL code: /*1. Criar um bloco PL/SQL anônimo para imprimir a tabuada abaixo:*/ DECLARE VN CONSTANT NUMBER(2) := 5; BEGIN FOR i IN 1..1O LOOP DBMS_OUTPUT.PUT_(VN || ' X ' || i ||'='||…
-
0
votes2
answers358
viewsTrigger to Retrieve Last Record Inserted in PL-SQL Table
I have a Rigger that after being inserted a new user in the table tFunctioning I must fill another table tPlanoSaude with the data of the employee. employee table table Health Plan Code of the…
-
0
votes1
answer407
viewsError generating PLSQL file on oracle!
Code CREATE OR REPLACE PROCEDURE CRIARARQUIVOTXT AS Arquivo_saida UTL_File.File_Type; Dir_Arq VARCHAR2(60); Cursor cur_arq is Select f.NOME from tfuncionario f; BEGIN -- Diretorio do Arquivo Dir_Arq…
-
0
votes2
answers5935
viewsIs there any clause similar to LIMIT in PL/SQL
I am using PL/SQL and would like to know if you have any clause or sql script that is similar to sql LIMIT clause, I’ve been searching here and found rownum < = X , but does not meet what I want…
-
0
votes2
answers39
viewsHow to insert for all users in PLSQL when there is no data?
Good afternoon! I’m trying to insert to all users when there is no type entered for all users: INSERT INTO USUARIO ( TIPO, PRIORITARIO) SELECT '130', 'N' FROM dual WHERE not exists (SELECT * FROM…
-
0
votes1
answer724
viewsBatch with parameters in Sqlplus
Hello, I need to create a batch file that drops the database user, recreates and imports the database with an existing backup. I have already created a routine that drops in the user and recreates…
-
0
votes1
answer919
viewsInsert record automatically when doing a PL/SQL insert
I’m starting to mess with databases PL/SQL and I’m having a hard time creating a Trigger when inserting a row in the table, the Trigger place a value in my column whose name is token. I made a code…
-
0
votes1
answer49
viewsColumn that receives a result if the condition is true
SELECT DISTINCT P.CD_PACIENTE , P.NM_PACIENTE , DECODE (P.TP_SEXO, 'M', 'MASCULINO', 'F', 'FEMININO', 'I', 'INDEFINIDO') SEXO , TRUNC(P.DT_NASCIMENTO) DT_NASCIMENTO , (SELECT FN_IDADE…
-
0
votes1
answer272
viewsseveral tables in PL/SQL on Cursor
I need to put the SELECT below to work within a cursor, but I’m not able to join the 3 tables, someone can help me? SELECT DISTINCT PROFESSOR.NOME, ESTUDANTE.NOME FROM PROFESSOR INNER JOIN…
pl-sqlasked 6 years, 8 months ago Ana magalhaes 1 -
0
votes2
answers136
viewsReplace a fixed CASE value with a SELECT and JOIN?
SELECT DISTINCT P.CD_PACIENTE , P.NM_PACIENTE , DECODE (P.TP_SEXO, 'M', 'MASCULINO', 'F', 'FEMININO', 'I', 'INDEFINIDO') SEXO , TRUNC(P.DT_NASCIMENTO) DT_NASCIMENTO , (SELECT FN_IDADE…
-
0
votes1
answer1184
viewsPLSQL resume error while retrieving data from Database
I try to extract data from the database and insert it into a record and then show it in the cmd, But when I get a higher registration number the procedure requires an error message. Code: SET…
-
0
votes0
answers411
viewsConversion error in oracle
I have a problem: when I run a function no problem occurs but when running in the php, this error occurs: ORA-06502: PL/SQL: Numeric or value error: Character to number Conversion error the problem…
-
0
votes1
answer1219
viewsI need explanations of OUT mode and INOUT PL/SQL and Mysql Procedure
Well, I put PL/SQL and mysql because these two modes have in the procedures both Oracle and mysql but anyway.. The IN mode I understood that it works as a Constant, it is passed by the parameter and…
-
0
votes0
answers44
viewsUnknown date format on Oracle
Good morning. I have to access the data in an Oracle database, where a field described as "validation date" has the following data: DtValid ------- 115365 115364 115363 115362 115361 Does anyone…
-
0
votes0
answers87
viewsPL/SQL - how to see the amount of lines manipulated through a Rigger?
I already tried with the sql%rowcount but when it’s a Rigger it just doesn’t work.. how can I return the number of lines manipulated in a DML instruction from a Trigger?
-
0
votes1
answer156
viewsHow to create a Registry vector in PLSQL
I have a record of TYPE trec IS RECORD ( cd_multi_empresa NUMBER(8,2), tp_atendimento CHAR(2) ); vcAtendimento trec; I need a registry vector, I used this and command: TYPE TcAtendimento IS…
-
0
votes1
answer436
viewsPLSQL shows error: PLS-00487: Invalid reference to Variabel
I have a PLSQL code: in which I receive two values one numerical and another varchar. Code: create or replace FUNCTION hospitalTeste123(MatriculaMedico in number, nomeSchema in varchar2 ) return…
-
0
votes1
answer173
viewsUse variable in a run
I’m creating the pl sequinte: declare cont integer; tabela varchar2(100) := 'TABLE_TESTE'; col varchar2(100) := 'TESTE'; begin SELECT COUNT(*) into cont FROM USER_TAB_COLUMNS WHERE TABLE_NAME =…
-
0
votes0
answers440
viewsError executing a Trigger ORA-04092: not possible on a trigger
I have the following problem: - I’m not able to perform a job (Scheduling) after an Insert rescued by Trigger. Row 2: ORA-04092: not possible on an ORA-06512 trigger: in "SYS.DBMS_ISCHED", line 135…
-
0
votes1
answer43
viewsnot in the same table
Let’s say I have the table CON_CONTRATOS and in it I have con_customerId, con_vencimento among others. And I need to make a select that returns ALL contracts of a Costomer whose same only contract…
-
0
votes1
answer319
viewsProblems for invoking/inserting data with Procedure
I created this project in Oracle, but I’m not able to insert data with the EXEC. Way I’m declaring the EXEC: EXECUTE SP_PRODUTO(8,'teste' 1); erro APRESENTADO: ORA-06550: linha 1, coluna 126:…
-
0
votes1
answer239
viewsString size in PL/SQL
CREATE OR REPLACE FUNCTION TamanhoString(texto char(100)) RETURN NUMBER IS tamanho NUMBER; BEGIN tamanho := LENGTH(texto); RETURN tamanho; END TamanhoString; I need to create a function that…
-
0
votes2
answers59
viewsSELECT Cursor using top1
I need to return only the highest value of one of the lines below, taking into account that I also need to submit the order code. I tried to rotate via cursor but without success. The cursor would…
-
0
votes2
answers26
viewsfailure in function snippet to read table
I’m developing a function that reads from one table and inserts into the other: The error is: Warning: Function created with build errors. I removed the select line and it works. So, the error is in…
-
0
votes1
answer1396
viewsconvert varchar to date oracle plsql
I need to convert a varchar for date so that you can filter all dates longer than a certain date, for example: I want all dates that are larger than November 2018 to be listed, I am doing as…
-
0
votes1
answer187
viewsSearching character in Specific Position - SQL Server
Hello, can anyone help me? I am building a query and I came across some problems in the way the bank was structured. I have a base with several phone numbers, and I need the 5°digito to be = 9. I…
-
0
votes1
answer160
viewsPLSQL - Query between tables
Hello, I would like suggestions on how to perform a query between two tables (Person and Person Permissions), where we have the table Person (person, name), the table Permissions(Permission_id,…
-
0
votes1
answer721
viewsProcedure in Package receiving an array (coming from a cursor/function) as input parameter - Oracle
Good afternoon, I already have some experience with PL/SQL but now I started working with oracle and Packages. I have a problem from which my package process is receiving two parameters and from it…
-
0
votes1
answer1805
viewsSELECT inside a FOR in declare SQL/ORACLE
I need to generate an excel of a table data but I need to do for each my company, so n have to run select changing the company id I thought to do with Procedure but the ones I have and just update…
-
0
votes1
answer640
viewsHow to Count With Subquery
I need to count table fields, however, these fields have different types of filters within Where. I need to be shown the consolidated information, I tried to do openquery but returns error. select…
-
0
votes1
answer924
viewsPL/SQL - Record Type vs Rowtype
When to use a record type, defined by the programmer, rather than declaring a variable of type %rowtype? ex: /**Definido pelo usuário**/ declare type bbb is record( id int, nome varchar(100)); v…
-
0
votes0
answers106
viewsCreate a Boolean column in Oracle Database
In an oracle bank I have to create a column with the Boolean type ALTER TABLE dbamv.prepad ADD editable BOOLEAN default FALSE; But the database displayed an error message: ORA-00902: invalid data…
-
0
votes2
answers378
viewsGet the invoiced amount from the previous month
Good evening staff. I am a student beginner in database and would like to take a question for study purposes. Suppose I have the following structure: MES QTDUSUARIOS MUNICIPIO VALORFATURADO 2018-01…
-
0
votes1
answer9613
viewsOracle - ORA-01722: invalid number
Can you help me with this error? It usually gives invalid number when I don’t quote simply. I tried to convert to number (although column is varchar2), but error persists DECLARE TYPE cVetor IS…