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
-
2
votes1
answer468
viewsHow to declare "nullable" parameters in an Oracle database?
I need my process to accept null values in some parameters of type Number. How do I do this in PL/SQL? Currently: PROCEDURE SP_EDITAR_QUADRO( P_IDQUADRO IN NUMBER, P_IDFUNC IN NUMBER, P_IDTRAB IN…
-
2
votes1
answer2546
viewsHow do I know if a column exists in an Oracle table?
I’m trying to add a new column to an Oracle table, and I want to know if it already exists or not. I’ve tried something like this: IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE…
-
2
votes3
answers1756
viewsHow to declare a cursor variable in PL-SQL?
I have a course: Cursor Pessoa is Select nomeFuncionario From empresa; -- Variaveis para uso da rotina variavel How would the variable ?
-
2
votes1
answer61
viewsHow to pass schema by paramentro in PLSQL?
I have a function that receives a varchar2 value with the Schema name to save the data in the database but in the schema. create or replace FUNCTION hospitalTeste123(MatriculaMedico in number,…
-
2
votes1
answer296
viewsSelect returns 2 rows per GROUP BY account
The code below returns the number of marked and the number of served, but when adding the CASE to bring me the amount of absences it separates the line of attendees from the line of the defaulters,…
-
2
votes1
answer68
viewsPerform table grouping with two parameters
good night. I’m still learning database and would like to ask a question with you. I know that if I want to organize my table with the invoiced values per month, I can use group by and filter it…
-
2
votes1
answer134
viewsHow to find the size of a file by plsql?
Hello, I’m having doubts about how to find the file size in the PL/SQL language. I’ve searched several places and found this solution as a pattern: declare vExists BOOLEAN; vFileLength NUMBER;…
-
2
votes1
answer342
views -
2
votes1
answer47
views -
1
votes1
answer94
viewsCheck if CONSTANT exists in Package
I have a Package: CREATE OR REPLACE PACKAGE LETRAS AS possuiA CONSTANT BOOLEAN NOT NULL := TRUE; possuiB CONSTANT BOOLEAN NOT NULL := TRUE; possuiC CONSTANT BOOLEAN NOT NULL := TRUE; possuiD…
-
1
votes1
answer79
viewsOverride PL-SQL condition
I have a condition, like this one below, in a query. If you receive the empty parameter or with 0, I would like this condition to cease to exist, or be nullified, so that the query search for all…
pl-sqlasked 9 years, 4 months ago Rodrigo Segatto 743 -
1
votes1
answer57
viewsUnderstanding pl/sql syntax Oracle 11g
I have an Oracle PROC 11g. Inside proc has something like this: v_prazo_subs_ans ts_odo.odo_controle_sistema.val_parametro%type; Where: v_prazo_subs_ans is a variable ts_odo.odo_controle_sistema is…
-
1
votes1
answer91
viewsBitand in the Where clause
I need to do the following Query, but it is generating some syntax or logic errors. SELECT campo_X, campo_Y FROM tabela_Z WHERE( ( BITAND(1, 1) > 0 AND campo_A NOT IN (2,4,8,9) ) OR ( BITAND(2,…
-
1
votes1
answer622
viewsError Case and Group by - Oracle SQL Developer
Hello, I’m trying to group the results of a query with case as follows: select case when sal BETWEEN 0 and 100000 then 1 when sal BETWEEN 100001 and 200000 then 2 when sal BETWEEN 200001 and 300000…
-
1
votes1
answer353
viewsTrigger prevents table change
I need to make a Rigger that prevents the same employee from being part of more than one crew on the same day. The crew table is as follows: Tripulação = {id_Voo, data, id_Funcionário } The 3…
-
1
votes1
answer921
viewsUpdate in table with date field
Hello! I need to update a table. I have a kind of chat in the bank, where it records the time the conversation was started, and should record the time it ended. However, before I implemented the…
-
1
votes1
answer133
viewsProcedure pl/sql
Do you guys talk? Well I am new in PL/SQL and I am creating my first Precedent, because I need to load in two tables (Insert or Change), I am using Merge. But when I try to run my past I already get…
-
1
votes1
answer1927
viewsORA-01799: a column may not be Outer-joined to a subquery
I am having a problem with oracle, it is not accepting a subquery in a left Join, if I change to Inner Join works, but changes the result of my select so it does not meet the solution. the problem…
-
1
votes2
answers414
viewsInsert CSV file data into the pl/sql Database using VB.NET
I am reading a csv file by a Console Application, and up to this point is returning me correctly. Now I’m having trouble understanding how to include each field in a Bank column. This is the code I…
-
1
votes2
answers1031
viewscalculate how many hours between call opening date and current date in pl/sql
I need to make a query that brings the calls that are outside the 48 hrs SLA calculating the dates and times of the opening of the call and current date, someone can help me?
-
1
votes0
answers497
viewsHow to pass a parameter of type 'Nested Table' for a stored file in Oracle
I have been working with Oracle for a little more than 2 months and I need to pass a data set to be used in the query parameter within the process. I searched a lot on the net and did not find any…
-
1
votes1
answer959
viewsHow I list the Packages in the oracle
How do I query(PL/SQL) to list a specific Package existing in the database(Oracle)?
-
1
votes1
answer983
viewsException SQL Oracle - Update and Insert
I have a question regarding a precedent that I am making oracle bank. A small explanation for what I am doing: I am reading data from a table, and throwing them on a cursor, after playing on the…
-
1
votes2
answers718
viewsSELECT that brings products active in one company and that are not active in all others
I have a table that shows products that are Active (A) or Inactive (I) in 16 branches. IE, has 16 lines for each product. I want to select all products that are Active (A) in Enterprise 16 and at…
-
1
votes2
answers4867
views -
1
votes1
answer428
viewsAdd values and subtract values from another condition
Good afternoon I have a table named TITLE In it I have columns with name CODOPERACAO, VLROPERACAO, DTAOPERACAO I have 2 different records of CODOPERACAO that are: 16 = Inclusion of title 28 =…
-
1
votes1
answer38
viewsHow to change a table indicating that data from a certain field can only vary from date 1 to date 2?
I have a table called "LOC_VEICULO" where the vehicle data are contained. I have the following field: DT_FABRICACAO (DATE), where the dates of manufacture of vehicles are stored. I need to change…
-
1
votes3
answers1184
viewsRead an Array or Bulk Collect as a table in an Oracle PLSQL
How to load an Array or Bulk into a PLSQL and then read this as a table. Example DECLARE VA_ARRAY ....DEFINIÇÃO DO ARRAY VN_QTD NUMBER; BEGIN -- TABELA01 É UMA TABELA FÍSICA EXISTENTE NO BANCO…
-
1
votes1
answer793
viewsReturn Oracle Timestamp
I want to convert a date to a number, a timestamp. Within a select I need to return beyond the date her timestamp. Example of consultation: Select sysdate, TIMESTAMP_DE_SYSDATE from dual Timestamp…
-
1
votes1
answer87
viewsMake a select according to an indexed table
I have a table, getSalas, that simplified is something like this: CREATE TABLE getSalas( ID_SALA NUMBER(4), NOME_CARATERISTICA VARCHAR2(20), VALOR_CARATERISTICA NUMBER(4) ); The contents of this…
-
1
votes1
answer96
viewsOracle , parameters of procedures of an Owner
On Oracle which table or view of metadata I have the parameters of a Procedure. My question is , that procedures HAS parameters with the name similar to "XPTO" ? I tried for the "Dict" but I…
-
1
votes0
answers195
viewsView button after Ubmit from oracle page Apex
I have a test page on https://apex.oracle.com/pls/apex/f?p=43921:2 user test password test, which already working ok, in general. It migrates employees using a shuttle object, and using Collections…
-
1
votes1
answer78
viewsDifficulty assigning value in plsql
In a college exercise the following was requested: In a presidential election there are three candidates. Votes are by code. The data used for screening are codified as follows: 1, 2, 3 = votes for…
-
1
votes1
answer3155
viewsHow to solve the ORA-01795 (maximum number of expressions) error in Codeigniter
I am developing a report in Codeigniter, but my select contains a filter that can have more than 1000 expressions in my where_in and then it returns me the error: ORA-01795: o número máximo de…
-
1
votes2
answers1117
viewsChange oracle characterset 11g
When performing a dump file import, oracle is returning me the following messages: IMP-00019: rejected line due to ORACLE 12899 error IMP-00003: Oracle error: 12899 found ORA-12899: very large value…
-
1
votes1
answer3824
viewsPL/SQL - Incorrect number of argument types in the call
I’m having trouble with a simple function I created. create or replace FUNCTION dry_login ( username IN VARCHAR2, password IN VARCHAR2 ) RETURN BOOLEAN IS l_user varchar2(70); l_pwd varchar2(70);…
-
1
votes0
answers37
viewsMonitoring of SQL script
Someone can help me, In my company I have a script that checks how many emails have been processed and sent by the server, but sometimes the program that makes the submissions crashes and I only…
-
1
votes2
answers245
viewsorder by in oracle subquerys
RESOLVED I solved as follows, I created a view equal to my table of service orders more that was ordered by the team and by the priority, and then I kept the query above by only changing the table…
-
1
votes1
answer72
viewsError running Function in PL/SQL
Code CREATE OR REPLACE FUNCTION BuscaNome RETURN t_name IS t_name VARCHAR(20); BEGIN SELECT T.DS_TURMA INTO t_name FROM TURMA T WHERE T.CD_TURMA = 13; RETURN t_name; END; Error: Error: PL/SQL:…
-
1
votes1
answer178
viewsUpdate in PL/SQL
Given two tables, the product table and the order table, I am trying to update(update) the product description of the products table when the product status is 'INACTIVE' and the order table status…
-
1
votes0
answers341
viewsPL/SQL - Trigger: Can I see the whole line updated in a line after Rigger?
I am studying PL/SQL in college and the teacher passed some questions based on the Human Resources model of Oraclexe itself. The question I’m asking asks a Rigger to prevent an employee from earning…
-
1
votes2
answers40
viewsDifficulty with Select
I want to make a query of 1 table that has the records: Predio | Local | Produto | FIFO P01 | WH01 | Maçã | 21/02/17 P01 | WH01 | Maçã | 22/02/17 P01 | WH01 | Maçã | 23/02/17 P01 | WH01 | Maçã |…
-
1
votes1
answer81
viewsC# - Check if dates are missing in the selected period
I have the following SQL SELECT dt_finished::date AS "DataFinalizacao", count(DISTINCT tba.id_batches) AS "Quantidade Total" FROM tb_batches AS tba INNER JOIN tb_routes tro ON (tro.fk_id_products =…
-
1
votes1
answer1287
viewsHow to view SQL History in SQL Developer?
I need to view all changes in a trial. The SQL Developer tool has a toolbar that stores a history of changes. How can I enable/view ? Image of the fuctionality…
-
1
votes1
answer1277
viewsError running Procedure on oracle
I got this trial: CREATE OR REPLACE PROCEDURE buscaturma(cod in INTEGER) AS nomeT varchar2(20); BEGIN dbms_output.put_line('inicio'); IF (cod=0)then dbms_output.put_line('ZERO'); ELSE SELECT…
-
1
votes1
answer111
viewsError using Case and Select in PL-SQL
I have this code: DECLARE BEGIN SELECT p.categoria( CASE WHEN p.categoria = 'A' THEN DBMS_OUTPUT.PUT_LINE('A') ELSE 2000 END ) FROM PRODUTO_TESTE2 p; END; Error message: Bug report - ORA-06550: line…
-
1
votes1
answer484
viewsVariable without pl/sql value
I tried to run this pl/sql but it didn’t work declare v teste%rowtype; begin v.ra; select ra into v.ra from teste where nome = 'Danilo'; dbms_output.put_line(v.ra); end; after a long time I decided…
-
1
votes0
answers206
viewsOracle - Lock preventing SELECT
Lock preventing SELECT The situation is as follows : An ERP application (Totvs) with multiple tables All these tables use as primary key a standard field R_E_C_N_O_ The application (I believe)…
-
1
votes1
answer3357
viewsWhat is the number of decimal places of type NUMBER(12,2)?
How to know the number of decimals for type NUMBER(12, 2) in an Oracle database ? V_FATURA NUMBER(12, 2)
-
1
votes2
answers105
viewsConcatenate varchar2 PLSQL
I have 2 tables with relationship 1 to many and I need to concatenate all the values in the column Nome table 2 in only one select column. Ex.: In the example, the return of what I need would be…