Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [plsql]

Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

PL/SQL is Oracle's 3GL that provides specific syntax for the specific purpose of processing SQL commands. Since it has the same robustness, security, and portability as Oracle Database itself

  • it supports the same datatypes as SQL
  • it can be stored and compiled in an Oracle Database
  • runs within the Oracle executable

Since V9.7, IBM's DB2 also supports PL/SQL.

603 questions
50
votes
6 answers

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
ursitesion
  • 2,061
  • 8
  • 32
  • 45
18
votes
6 answers

Is Oracle free to download?

I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free? I found this link: Oracle Database 11g Release 2 Downloads, and it…
Entity
  • 191
  • 1
  • 1
  • 4
17
votes
3 answers

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for loop, is it not SQL anymore? PL/SQL is an extension…
Lazer
  • 3,361
  • 15
  • 43
  • 53
15
votes
5 answers

PL/SQL: best way to count elements in an array?

Given this: DECLARE TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; MY_ARRAY T_ARRAY; V_COUNT INTEGER; I would like to do: BEGIN -- ... some code filling the MY_ARRAY array -- obviously COUNT_ELEMENTS() does not exists,…
Maxime Pacary
  • 987
  • 3
  • 10
  • 20
14
votes
2 answers

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have…
Stephan Schielke
  • 243
  • 1
  • 2
  • 6
13
votes
1 answer

What are the benefits of packages over standalone procedures and functions?

Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.
Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
12
votes
2 answers

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how. What exactly causes mutating…
parmanand
  • 427
  • 2
  • 5
  • 10
12
votes
4 answers

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) loop htp.prn(i.student_last_name || ', ' ||…
ini
  • 347
  • 1
  • 3
  • 10
11
votes
2 answers

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a duplicate of this one, if so, please don't be…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
11
votes
3 answers

Why can't we write ddl statement directly into the PL/SQL block

Why can't we write ddl statements directly in PL/SQL block, for example when i write CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; // error END test; / But, CREATE OR REPLACE PROCEDURE test IS BEGIN execute immediate…
Ravi
  • 1,565
  • 4
  • 15
  • 20
11
votes
3 answers

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method of doing this?
rfusca
  • 1,569
  • 4
  • 17
  • 29
8
votes
2 answers

Writing a simple SELECT Stored Procedure in Oracle PL/SQL

This is my SQL Server Stored Procedure which is below: CREATE PROCEDURE passenger_details AS BEGIN SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type FROM Passenger, Ticket, Airline, Class WHERE…
stranger
  • 193
  • 3
  • 4
  • 13
8
votes
4 answers

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of type varchar. As far as I have seen, I have to…
Michael-O
  • 510
  • 3
  • 10
  • 22
7
votes
2 answers

What is an Oracle ASSEMBLY?

What is an Oracle ASSEMBLY, in the context of Oracle SQL? It's listed in GV$SQLCOMMAND. Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of MERGE. INST_ID COMMAND_TYPE COMMAND_NAME …
Jon Heller
  • 524
  • 2
  • 8
  • 27
7
votes
1 answer

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while at the same time both separately: select from a…
smwoody
  • 73
  • 1
  • 4
1
2 3
…
40 41