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

Access cursor by column name dynamically

Can I access a cursor's column dynamically? I mean by name? something like this: declare v_cursor := select * from emp; begin FOR reg IN v_cursor LOOP dbms_output.put_line(**reg['column_name_as_string']**); end loop; end; I know the bold part…
noripcord
  • 3,412
  • 5
  • 29
  • 26
4
votes
4 answers

How to automatically display output of all SQL statements inside anonymous PL/SQL block

Our data migration scripts make use of anonymous PL/SQL blocks to help tidy up the code, mainly so we can set the create and updated user Id columns to a "system" user. Our migration scripts look something like: DECLARE v_user_id…
Greg Burghardt
  • 17,900
  • 9
  • 49
  • 92
4
votes
4 answers

Trouble shooting ora-29471

Certain sessions cause ORA-29471 because dbms_sql inoperable for those sessions. We are facing this error in our application for few records. How this can be troubleshooted? How we can identify a particular session has no access on DBMS_SQL? Do we…
Maddy
  • 3,726
  • 9
  • 41
  • 55
4
votes
3 answers

ORA-04061: existing state of package body "PACKAGE.NAME" has been invalidated persists

On one of Oracle DB instances I am working on I am observing a different than normal behavior when recompiling packages. Typically, (as in question Frequent error in Oracle ORA-04068: existing state of packages has been discarded) following error…
Kamil Zadora
  • 2,367
  • 6
  • 34
  • 43
4
votes
3 answers

Sql query optimization and profiling

Let's say I have poorly performing query. How do you usually go about sql optimization? What are the first things I should look in query execution plan? Is there a good article or book about this?
Eduard
  • 3,176
  • 3
  • 21
  • 31
4
votes
2 answers

How to calculate checksum in Oracle?

I do a merge on table and need to update data if they are different. In MSSQL I usually do it by checking a checksum this way: WHEN MATCHED AND CHECKSUM(TARGET.Field1,TARGET.Field2, ... TARGET.Field25) <> …
tylkonachwile
  • 2,025
  • 4
  • 16
  • 28
4
votes
1 answer

How do I get a stored procedure output parameter that is an array to work?

I have been developing Java/JDBC calls to stored procedures with some success. However I am stuck when an output parameter is an array of strings. I have been successful with scalar types as input and output parameters, as well as an array of…
dave
  • 11,641
  • 5
  • 47
  • 65
4
votes
1 answer

Oracle uses exception handler from first block in later blocks

I'm running into a behavior where I'm trying to use case-specific exception handlers for several Oracle PL/SQL blocks in a Flyway script and Oracle, apparently contradicting its documented scoping for exception handlers, sends all exceptions to the…
chaos
  • 122,029
  • 33
  • 303
  • 309
4
votes
4 answers

Check constraint for making sure a date is not in the future?

I'm trying to create a table that records changes made to the estimated hours of another table. The database as a whole is a project management system for a company to assign work to its employees and create invoices for the customer. Currently I…
solent_matt
  • 41
  • 1
  • 1
  • 3
4
votes
3 answers

BULK COLLECT INTO multiple collections

I wonder if using BULK COLLECT INTO multiple collections guarantees that corresponding columns in row will have the same index in nested table. Example: CREATE TABLE tx AS SELECT CAST(level AS INT) AS col1, level || 'a' AS col2, level || 'b' as…
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
4
votes
2 answers

Oracle DBMS Job not running

I defined a job to run from Tuesday to Sundays every 5 min. from 9:00 am to 22:00 pm BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'GET_INVOICES_JOB', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN LOPES.GET_INVOICES; END;', repeat_interval…
en Lopes
  • 1,863
  • 11
  • 48
  • 90
4
votes
2 answers

How to schedule Oracle DBMS Jobs in a window

I want to create an Oracle DBMS Job that runs every week day (not on weekends) from 09:00 to 20:00 every 10 min. I wonder if I can do that in the FREQ parameter of the job definition or I have to create a New Maintenance Window. It seems that with…
en Lopes
  • 1,863
  • 11
  • 48
  • 90
4
votes
1 answer

Returning a cursor from an inner procedure to outer procedure in oracle pl/sql

I am using oracle PL/SQL procedure. I am calling one procedure inside another. I want to return a cursor from the nested procedure to the outer procedure. Is this possible? How adversely does it affect the procedure? Below is the calling structure: …
Tito
  • 8,894
  • 12
  • 52
  • 86
4
votes
3 answers

How to call a function in a package

I'm doing the following but it doesnt work select package_name.function_name(param,param) from dual I'm calling a function that returns a cursor so im guessing "from dual" is the problem is there another way of doing it?
124697
  • 22,097
  • 68
  • 188
  • 315
4
votes
2 answers

Waiting for a submitted job to finish in Oracle PL/SQL?

I'm looking for the equivalent of Java's thread.join() in PL/SQL. I.e. I want to kick off a number of jobs (threads), and then wait for them to finish. How is this possible in PL/SQL? I'm thinking of using dbms_job.submit (I know it's deprecated).…
vicsz
  • 9,552
  • 16
  • 69
  • 101
1 2 3
…
99 100