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
-
1
votes1
answer540
viewsHow to select XML and return a specific column from an anonymous block?
I have an anonymous block in PL/SQL that returns to me an XML as the result of running a web service. How do I select this XML to only get a desired column? Anonymous block: DECLARE l_filename…
-
1
votes1
answer680
viewsReturn Oracle PL/SQL string range
Good morning ! I need to return the value inside a string between a range of | in the select below I can get the value from the first |. The expected result is: 83,1 SELECT…
-
1
votes1
answer387
viewsExit command in PL/SQL
I noticed the command exit works like a break, it stops running the current block and switches to the next one, but which command can stop the whole procedure in PL/SQL?
-
1
votes0
answers38
viewsPLSQL to update a node in xmltype
I have to update a column in format xmltype of an Oracle database. Basically I just do update of property valor if another property of that node date and time are equal. That is, in the code below,…
-
1
votes1
answer640
viewsSQL - IN inside a SUB SELECT with NOT IN - ORACLE
I wanted to make my INSERT dynamic, I don’t need to change the ids manually. I have this script that returns all PRODUCTS that does not have 83 relationship with the EST_PROD_PRECO_CUSTO_EMPRESA…
-
1
votes1
answer287
viewsWhat exactly is the UTL_HTTP (ORACLE) SET_PROXY Proxy
utl_http.set_proxy(http://pgau:abcd@[email protected]:5678'); In this example what exactly this Proxy address ? I tried the internal IP and it didn’t work , the below my company said not to have…
-
1
votes1
answer1783
viewsDatabase swapping accent for "?"
In my PL/SQL all accents are being replaced by "?". Example of a table comment: Flag device indicator to be???? used in embracing term or not. Possible???? values: 1 - Yes or 0 - No. Default: 1 When…
-
1
votes1
answer93
viewsCalls from Procedure PL/SQL
Studying PL/SQL I came across the following code: CREATE PROCEDURE incluir_segmercado(p_id IN NUMBER , p_descricao IN VARCHAR2) IS BEGIN INSERT INTO segmercado values(p_id, UPPER(p_descricao));…
-
1
votes2
answers1201
viewsDisplay a message on command output
I am running this PL/SQL and in case the condition falls on else, how to display the message to the user? declare cont integer; tabela varchar2(100) := 'PESSOA'; coluna varchar2(100) := 'NOME';…
-
1
votes1
answer116
viewsRemoving comma from the pl sql column
Good afternoon, I have a table that has names. In some lines the name is separated by comma. Ex: Alberto, Luiz I want to change these lines to stay Luiz Alberto DECLARE final VARCHAR(50); inicio…
-
1
votes0
answers32
viewsReturn of the oci_new_cursor
Hello! I have the following question: the oracle function for PHP oci_new_cursor, can only be used when the cursor already exists in the database? For example, I just created my query within my php…
-
1
votes1
answer445
viewsDifficulty creating Function in oracle
I’m trying to create a function in the oracle, but I’m having a hard time creating her return. CREATE OR REPLACE function simple_function( state VARCHAR2, city_id NUMBER) RETURN VARCHAR2 IS ret…
-
1
votes0
answers54
viewsConnect PL/SQL to a MYSQL database
I have a MYSQL database, I would like to connect to it using my pl/sql development environment, is that possible? And I do?
-
1
votes1
answer2763
viewsHow to recover script history from PL/SQL
I had to reinstall PL/SQL Developer and so lost all script history. Is it possible to recover? Thank you.
-
1
votes1
answer43
viewsHow to Take data from an SQL table by referencing only the first position of the VARCHAR type
I need to delete everything that starts with '9' in my seg1 column, for example: If in the table this '954747A' will be eliminated because the 9 is the first position. If you have '34465922A' It…
-
1
votes1
answer28
viewsInclusion of First Record using a select SUM
In certain routine I have a Trigger that runs a code similar to the one below. I’m having trouble in the first record, as in the first record does not have any code, the sum of + 1 is not being…
-
1
votes2
answers7751
viewsORA-06502 - string buffer too small numerical or value - Long Field
Good morning, I’m performing a query to query data in a Long-type field. I created the following Function to bring the data: CREATE OR REPLACE FUNCTION "SEARCH_LONG" (r rowid) return varchar2 is…
-
1
votes1
answer91
viewsHow to add values of others in a new column? Ora-00904
I created the columns A, B, C e D. When trying an E Column, which is the sum of the previous ones gives this error ORA-00904, have tried some examples I found in pequisa, gives in the same.. SELECT…
-
1
votes1
answer1099
viewsOracle - WHERE WITH SYSDATE
In the first select the BALANCE is coming zeroed, in the second I replace the sysdate by which would be the date it brings the correct value what must be the problem in sysdate+4 in Where? SELECT…
-
1
votes3
answers137
viewsUsing IF and UPDATE in a PROCEDURE to update a table
I am new in SQL and PLSQL and I am having difficulty with the following situation: create or replace PROCEDURE set_value(p_variable VARCHAR2, p_value VARCHAR2) IS BEGIN INSERT INTO co_t_conf…
-
1
votes1
answer82
viewsError while trying to run an Update with Parameter in SQL
I’m having a little problem that’s being a headache. I want to update with a SET, where it involves two parameters concatenated. Following update: update tabela_usin usin set usin.peca = &P1 ||…
-
1
votes1
answer43
viewsTime mask HH:MM from a number type field using oracle sql for reporting
I have a query that I use to report hours worked, I tried to use to_char and some other examples I found in stackoverflow. SELECT CASE WHEN BC.ENTRADA1 IS NULL THEN 0 ELSE…
-
1
votes0
answers51
viewsDelete with Insert in PL/SQL with FK error
Good night, I have a table that calls horariofunc and the primeiry key of this table is linking in the address table. I happen to be using to update the data a delete and an Insert with the new…
-
1
votes1
answer186
viewsSum PL SQL Lines
Galerinha, I created a code that organizes all the tasks of the company by sector. The objective of this code is to identify the percentage of delayed activities by sector. The result was expected.…
-
1
votes0
answers77
viewsUsing pivot, in this query with dynamic columns
I have the query below where I display products, ordered by the product code, the date of movement of the product is from the latest to the oldest, but as I wanted only the last 5 dates of a defined…
-
1
votes0
answers19
viewsMount a table of records from a Collection in PL-SQL
Long live, I have a Collection with records like this: TYPE TABLE_RECORD IS RECORD (n_lin number, n_col number, val varchar(4000)); TYPE TABLE_CHUNK IS TABLE OF TABLE_RECORD; Let’s say I have these…
-
0
votes1
answer8195
viewsMerge two Select
I am developing a report in Powerbuilder and would like to know how I can join results of two SQL queries in the report. If I add the f_bloqueto_nosso_numero function in the first querie and add the…
-
0
votes1
answer3407
viewsImport XML to Oracle tables
. Hello, I need to import an XML file to an oracle table. There are several examples on the internet, but none of them worked for me. In general, I’m having the error: LPX-00210: expected '<'…
-
0
votes1
answer1200
viewsHow to perform procedures within another?
I have this process where I save inside a cursor all the other procedures that need to be executed, then I go through the cursor by placing the names of the procedures inside a variable. How to…
-
0
votes1
answer1166
viewsRun Instant with Break Lines Getting Invalid Package
I’m creating a package dynamically: DECLARE vpackageletras VARCHAR2(2000); vvalorpossuia VARCHAR2(10); vvalorpossuib VARCHAR2(10); BEGIN IF letras.possuia THEN vvalorpossuia := 'TRUE'; ELSE…
-
0
votes3
answers5145
viewsPick number after comma
Working with plsql I am dividing two numbers, and I would like to receive only what comes after the comma. Ex: 7,89111. I need only number 89111.
-
0
votes1
answer1939
viewsQuestions about functions and procedures within Packages(oracle)
I have a package in my bank and can’t see the source code of the procs and functions within it. Below packege code and Inside procs and functions: CREATE OR REPLACE PACKAGE…
-
0
votes1
answer423
viewsObject in oracle giving as invalid(Invalid)
I gave one select * from all_objects where object_name = 'meu_objeto';. Well, that way, I got four lines. Two lines referring to SYNONYM and that doesn’t interest me and two more lines: PACKAGE and…
-
0
votes1
answer487
views -
0
votes1
answer1155
viewsDebug Function within a package
I have a package with several functions and procs. How do I, using pl/sql Developer, debug? I would like to know how I go to the error and see what is being passed. Using the pl/sql Tests tool I…
-
0
votes2
answers670
viewsArrays in the clause 'NOT IN' WHERE
DECLARE string_valor VARCHAR2(100); lista_array dbms_utility.lname_array; contador binary_integer; BEGIN SELECT t.campo_a INTO string_valor FROM tabela t WHERE t.campo_cod = 1;…
-
0
votes1
answer131
viewsFilter last 3 ratings Oracle sql
I am making a report and need to filter the 3 latest supplier evaluations in case that supplier is with the negative review on the date requested in the report. Each evaluation has several questions…
-
0
votes0
answers291
viewsORA-00922 error in table creation in Apex 5
I am trying to create a table in apex5 with reference, possessing foreign key from another table and that this foreign key column is populated. But creating is returning the error: ORA-00922:…
-
0
votes1
answer288
viewsHow to make a change to "Cascade" using oracle
How can I change my table ID BANDAS_E_ARTISTAS and ensure that other foreign tables also change via PL/SQL? TABLE BANDAS_E_ARTISTAS ID NOME_ARTISTICO 01 RAÇA NEGRA 02 OS MENUDOS 03 KELLY KEY 04 LUIZ…
-
0
votes1
answer76
viewsHelp in SQL ORACLE query
Column DEPENDENCY: T - Titular; E - Esposa; F - Filhos; FA - Filho Adotivo As table above, how do I bring data only of CEP S that are divergent as the CEP of the Holder: Titular Admilson has 2…
-
0
votes0
answers88
viewsList only the largest age group?
I am trying to do an age comparison to list only the age corresponding to your age range. Ex: 20 years age range from 0 to 43. SELECT DISTINCT U.USS_CODIGO, t.con_codigo, t.CON_SEQUENCIA,…
-
0
votes2
answers265
viewsNo update of a Collection PL/SQL
I am trying to update a parameter in a table, based on previous values of a Collection, it will update the idAccept attribute of a particular document table id. I also created an Insert in a test…
-
0
votes1
answer80
viewsGrouping of lines Group By
I have the following appointment: SELECT distinct CAPA.COD_HOLDING, CAPA.COD_MATRIZ, CAPA.COD_FILIAL, CAPA.ID_NF_ENTRADA, CAPA.NUM_NF, CAPA.DT_ENTRADA, CAPA.DT_EMISSAO, CAPA.COD_CLIFOR, CAPA.COD_UF,…
-
0
votes1
answer478
viewsOracle plsql email attachment file will empty
I have the PLSQL block listed below, the email is sent but the file that should be attached arrives empty in the destination email. Send an existing file, no ? What am I doing wrong ? Grateful…
-
0
votes0
answers265
viewsHow not to convert an exponential number to decimal?
Guys I’m having the following problem: I created a function to put the thousand point(s) in the values you receive as parameter (VARCHAR2). This function takes integer values and values of type…
-
0
votes1
answer33
viewsSelection Using FROM TABLE
I have a table and a function that returns a pipeline, only the function needs to receive a data from the first table, I imagined that the selection would be something like this: Select…
-
0
votes1
answer350
viewsCondition and loop within a function
I am creating a function to exit the stock, where v_saldo_item_peps is a function that returns a table, with the items that have balance, in their respective entries. I need to check whether…
-
0
votes0
answers172
viewsAdd column to SQL considering some conditions to be valid
I’m trying to gather two pieces of information and I’m not getting it. I set up this SQL to bring me only the products that were purchased that had a discount through a contract. However this…
-
0
votes1
answer436
viewsSum a summed field - PL/SQL
I need to make the sum of a field to which a sum was made as the example below: SELECT A.*, B.* FROM (SELECT DTAGENDA, AG.CODITPROD, CD, ROW_NUMBER() OVER(PARTITION BY AG.CODITPROD, CD ORDER BY…
-
0
votes1
answer528
viewsCalculation in SELECT oracle
Good morning. I need to calculate between values that are stored in my BD, these values are marked with the + sign and - in their respective tables. I wonder if there is any way to make through a…