Stack Overflow
Stack Overflow

Questions tagged [plsql]

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well. Questions with regular DML or DDL statements should be tagged with "sql" and "oracle", NOT with "plsql".

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL.

Oracle clearly distinguishes between SQL and PL/SQL so PL/SQL should not be confused with regular SQL. Plain DML statements should be tagged with and .

Questions about PL/SQL should probably be tagged as well.

Oracle Documentation: 12c Release 1

New PL/SQL Features in 12c Release 1

This is a series of articles written by Steven Feuerstein and published in Oracle Magazine.

Oracle Documentation: 11g Release 2

Oracle Documentation: Previous releases

Wisdom from the Stack

PL/SQL Tutorial

PL/SQL 101

This is a twelve part series of articles on understanding and using PL/SQL. It is written by Steven Feuerstein and published in Oracle Magazine.

  1. Building with Blocks
  2. Controlling the Flow of Execution
  3. Working with Strings
  4. Working with Numbers in PL/SQL
  5. Working with Dates in PL/SQL
  6. Error Management
  7. Working with Records
  8. Working with Collections
  9. Bulk Processing with BULK COLLECT and FORALL
  10. The Data Dictionary: Make Views Work for You
  11. Wrap Your Code in a Neat Package
  12. Working with Cursors

Book recommendations

28559 questions
4
votes
6 answers

Updating database records in a loop?

declare begin for i in (select * from emp) loop if i.sal=1300 then update emp set sal=13000; end if; end loop; end; This code is updating all the records with salary 13000. Instead i want to update records having salary…
musicking123
  • 3,385
  • 9
  • 32
  • 33
4
votes
2 answers

Stored procedure variable error in PLSQL when declaring variables

Using Oracle 11g when creating the following stored procedure create or replace PROCEDURE sp_EqualVote(AREA IN NVARCHAR2, DATEOFVOTE IN DATE) IS DECLARE test nvarchar(255); BEGIN SELECT AREA, DATEOFVOTE, CASE WHEN…
Luke Greenwood
  • 77
  • 1
  • 6
4
votes
2 answers

delayed responses from Oracle caused by SQL parsing

I have a Java web service method getCardInformation which gets data from Oracle database using JDBC. About 300 calls are made per second. Java calls this pl/sql function: PROCEDURE GET_CARDS_BY_ID(p_card_id IN NUMBER …
JiboOne
  • 1,438
  • 4
  • 22
  • 55
4
votes
1 answer

Trigger based partition creation

I have a list partitioned table on a foreign key. So if I want to insert a new entity the missing partition throws an exception on insert. I thought I am a cool duke and use a trigger to create new partitions :-) But the partition will not become…
Chris
  • 43
  • 1
  • 3
4
votes
1 answer

How can I turn the output of this stored procedure that uses a temporary table into a materialized view?

I have a stored procedure that produces the correct output, and I'd like to store the results in a materialized view. I'm having trouble conceptualizing how to get this working because the procedure relies on a temporary table that I created called…
Nathan Jones
  • 4,904
  • 9
  • 44
  • 70
4
votes
3 answers

Oracle hierarchical sum (distance from leaf to root)

I would like to get help for a hierarchical query (Oracle 11gR2). I have a hard time with those kind of queries... In fact, it's a 2 in 1 question (2 different approches needed). I’m looking for a way to get the distance from all individials records…
JGLord
  • 71
  • 1
  • 6
4
votes
3 answers

How to get the current date time in plsql?

My Code: DECLARE BEGIN -- outputs 06-DEC-18 dbms_output.put_line(sysdate); -- also outputs 18-DEC-06 dbms_output.put_line(to_date(sysdate,'yyyy-mm-dd hh24:mi:ss')); END; / The output only shows the date. I want to get also…
ktaro
  • 413
  • 1
  • 7
  • 24
4
votes
1 answer

ORA-04065: not executed, altered or dropped stored procedure

I have an Stored procedure in Oracle Enterprise 12c. This program is calling more than 100 sub-procedures to truncate tables and re-insert (ETL). Master Proc IS BEGIN CHILD1; CHILD2; CHILD3; ETC... END; This process is being executed every…
Jorge Flores
  • 107
  • 3
  • 8
4
votes
1 answer

How to remove '\r\n' from Base64 string in oracle?

I have created the following function in Oracle to convert my string to BASE64 and I am getting a new line after 64 characters. The function is as below function to_base64(t in varchar2) return varchar2 is begin return…
Kinjan Bhavsar
  • 1,439
  • 28
  • 58
4
votes
4 answers

Loop through multiple tables to execute same query

I have a database wherein per day a table is created to log process instances. The tables are labeled MESSAGE_LOG_YYYYMMDD Currently I want to sequentially execute the same QUERY against all those tables. I wrote the PL/SQL below, but got stuck at…
pcvnes
  • 927
  • 2
  • 15
  • 41
4
votes
3 answers

PLSQL - Measure the execution duration of a procedure

I have a procedure that runs every one hour populating a table. The records handled from the procedure are many so it takes approximately 12~17 mins each time it is executed. Do you now if there is a way (i.e. trigger) to record the duration of…
veg123
  • 159
  • 5
  • 12
4
votes
2 answers

Oracle comparing day to second interval to an integer

I am wondering what is going on under the hood of oracle when I am trying to compare day to second interval with a integer. Example below. SET SERVEROUTPUT ON; DECLARE v_date1 TIMESTAMP := current_timestamp ; v_date2 TIMESTAMP :=…
T.Z.
  • 2,092
  • 1
  • 24
  • 39
4
votes
2 answers

How to use apex_string.split?

I'm trying to split a string : OK#15#78 by # I would like to get the first part of the string : Ok I tried the following queries but it's not working : select apex_string.split('OK#15#78','#')[0] from dual; What can I try next?
Thomas Carlton
  • 5,344
  • 10
  • 63
  • 126
4
votes
1 answer

How to call Javascript from inside a PLSQL code in dynamic action of oracle apex

How can we call javascript code from a PLSQL code in dynamic action of Oracle apex. Actually, my requirement is to select a Role(P2_ROLE) from a dropdown list. And than find all the usernames attached to this role using sql query. Once the…
Abha
  • 327
  • 3
  • 11
  • 26
4
votes
2 answers

When running a forall loop in PL/SQL, is it necessary to commit afterwards?

Does a PL/SQL forall loop commit automatically at certain intervals, or do I need to commit after the loop? Oracle 10g and 11g FORALL i IN x.FIRST .. x.LAST delete from table where 1=1; I've currently got a pl/sql script that does a bulk…
kg.
  • 633
  • 1
  • 5
  • 17
1 2 3
…
99 100