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
2 answers

Overloading problem: Error(83,5): PLS-00307: too many declarations of 'REPORTEQ' match this call

So I am trying to use overloading but I am having some troubles. My package has 4 procedures with the same name but different type of arguments (VARCHAR2, NUMBER, BOOLEAN, DATE). The package header and package body are at the end of my question. I…
Richard Knop
  • 81,041
  • 149
  • 392
  • 552
4
votes
1 answer

Fetching data from a nested table into cursor

Requirement The table XYZ contains the column p_type which contains the column name of PQR table . Now here I am fetching a record from the XYZ table and sum(amount) on the basis of the group by p_type which is fetched from the record from table…
gaurav
  • 345
  • 2
  • 9
  • 20
4
votes
1 answer

ORA-00918: Column ambiguously defined when inserting multiple rows

I am trying to add multiple rows to a table using this reference, statement 8: INSERT INTO people (person_id, given_name, family_name, title) WITH names AS ( SELECT 4, 'Ruth', 'Fox', 'Mrs' FROM dual UNION ALL SELECT 5,…
user9507446
  • 333
  • 1
  • 3
  • 14
4
votes
1 answer

How To Format Timestamp

I have col1 in myTable which is varchar, and I have to insert here timestamp eg:- 09-MAY-11 10.23.12.0000 AM. Now please tell me: How to insert into myTable with taking sysdate in above format... How to retrieve data from col1 in tha same timestamp…
Avi
  • 1,115
  • 8
  • 20
  • 30
4
votes
1 answer

Validate data inserted based on other data in the same table (Oracle)

I am doing a project in SQL Oracle and have found a problem, as far as I am quite new to SQL. I have a system that administrates booked rooms, in a table called bookings. Some of the attributes are: room_id arrival_date nights_nr booking_id (which…
ada109
  • 53
  • 4
4
votes
1 answer

Oracle SQLPlus: Echo without line numbers?

I'm working on a solution where several SQL and PL/SQL scripts are being run together, in a batch of sorts, via SQL*Plus. I'm declaring SET ECHO OFF; and SET ECHO ON; at relevant points in the scripts so as to output relevant code. Currently the…
Kjartan
  • 18,591
  • 15
  • 71
  • 96
4
votes
2 answers

MD5 in Oracle (DBMS_OBFUSCATION_TOOLKIT.MD5)

I'm trying to compose a function to obtain MD5 hashes from bits I've gathered here and there. I want to obtain the lower-case hexadecimal representation of the hash. I have this so far: CREATE OR REPLACE FUNCTION MD5 ( CADENA IN VARCHAR2 )…
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
4
votes
1 answer

Get table return value from PL/SQL stored function using JDBC

I have a PL/SQL stored function which returns a table of integers: CREATE TYPE INT_TABLE IS TABLE OF INTEGER; CREATE FUNCTION f (someParam IN INTEGER) RETURN INT_TABLE IS ... I wish to use JDBC to retrieve the result of this function so that I can…
Christopher Riches
  • 769
  • 6
  • 21
4
votes
1 answer

Python ETL - Batch or Iteratively load Large datasets into Oracle Database with cx_Oracle

Using Python to load a dataset of 10MM records into Oracle Database table. Dataframe created with no issue. When loading the dataframe record count too large error from cx_Oralce. Seeking to loop over the dataframe and batch load the 10MM records…
BGDev
  • 135
  • 1
  • 8
4
votes
2 answers

How to create stored function from Spring using JdbcTemplate?

For administration needs, I need to create and replace stored procedures from Spring Repository. Has anyone already done this? I tried to use following code(unfinished): @Component public class JdbcRepository { @Autowired private…
tw1911
  • 43
  • 2
4
votes
1 answer

Can I make a table of objects that have nested tables as attributes?

Here is a snippet of my OR schema: CREATE TYPE artist_table_type AS TABLE OF REF artist_type; / CREATE TYPE track_type AS OBJECT ( title VARCHAR(1000), duration INT, release_date DATE, producers artist_table_type, MEMBER…
alexgolec
  • 26,898
  • 33
  • 107
  • 159
4
votes
1 answer

About new line characters in Oracle PL/SQL quote operator: can the default behaviour be changed?

Today I noticed something interesting about the quote operator. Here I'm running this code via SQL*Plus on a 12.1 database running on my Windows 10 laptop. newline_CRLF.sql set serveroutput on declare l_str1 varchar2(100 char); l_str2 …
Max
  • 2,508
  • 3
  • 26
  • 44
4
votes
1 answer

Can I use INSERT ALL with multiple recordtype variables?

Given a PL/SQL block where I have access to 2 recordtype variables, I want to insert these 2 records into the same table in a single statement, but my attempts to use INSERT ALL have failed thus far. Is it possible to use INSERT ALL with record…
julealgon
  • 7,072
  • 3
  • 32
  • 77
4
votes
1 answer

How to loop through numbers in SQL with varying increments

I am trying to loop through numbers in SQL that have have a start value of 10 and increment by a value of 7 up to 59. In a C-based language this would be incredibly simple, but I'm having trouble implementing it in Oracle SQL. I have tried using a…
Luke Orth
  • 89
  • 2
  • 9
4
votes
3 answers

How to rename multiple columns in oracle using one Alter table statement?

The only thing I found is renaming one column at a time: ALTER TABLE table_name RENAME COLUMN old_name TO new_name; I read Oracle documentations, and couldn't get the answer for many columns at a time . Ref:…
Amin
  • 41
  • 1
  • 1
  • 4
1 2 3
…
99 100