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

Oracle SQL - check missing indexes on SQL query

I need to check if in a query, index or indexes are missing. For check indexes in a table I use syntax: SELECT index_name , column_position , column_name FROM user_ind_columns WHERE table_name = 'table_name' ORDER BY index_name,…
Catalin
  • 253
  • 8
  • 21
4
votes
4 answers

CREATE Oracle Procedure

I am trying to create a procedure and it created without error. However when I try to run it then I will get following error. Please advise SQL> begin 2 Update_STUD_Fin ( '1011'); 3 end; 4 / begin * ERROR at line 1: ORA-06511: PL/SQL:…
mohammad
  • 41
  • 1
  • 2
4
votes
1 answer

APEX_WEB_SERVICE.MAKE_REST_REQUEST results in ORA-29248: an unrecognized WRL was used to open a wallet

I am trying to access a rest webservice over SSL/TLS using APEX_WEB_SERVICE.MAKE_REST_REQUEST on Oracle 12c/Apex 5.1. This results in the error message ORA-29248: an unrecognized WRL was used to open a wallet. I have put the necessary certificates…
Nick
  • 328
  • 3
  • 10
4
votes
2 answers

PL/SQL Add Multiple Columns to Query with Single Function

Is there any way to accomplish something like this in PL/SQL... select a.col1, a.col2, a.col3, myFunc(a.id) from myTable a; and the result be more than 4 columns? So basically, is there a way for a function to return or pipe more than one column…
gfrobenius
  • 3,987
  • 8
  • 34
  • 66
4
votes
1 answer

Execute dynamic DDL in PL/SQL procedure through definer role permissions

I want to perform some dynamic DDL in a procedure owned by an admin user. I'd like to execute this procedure with a technical operational user with definer rights (operational user doesn't have the create table role). The problem is the 'create…
JochenDB
  • 588
  • 10
  • 31
4
votes
3 answers

How to join sub-queries in PL/SQL?

i need to join subqueries in oracle. This does not work i get a syntax error for the join operation select s1.key, s1.value, s2.value from ((select key, value from tbl where id = 1) as s1 join (select key, value from…
mrt181
  • 5,080
  • 8
  • 66
  • 86
4
votes
2 answers

PL SQL - Convert timestamp to datetime/date

select to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF') as SCHEDULED_TIME, TRUNC(to_date(to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS')) from S_TIDAL_STATUS The error was: ORA-01830: date format picture…
Anna Huang
  • 287
  • 2
  • 5
  • 15
4
votes
3 answers

How to drop users by mask in the Oracle RDBMS

I want to drop all users who have 'WIN' at the start of their name (for example, 'WIN$DOWS'). Is it possible to write something like like the follownig? drop user where name like 'WIN%'
Petro Semeniuk
  • 6,970
  • 10
  • 42
  • 65
4
votes
1 answer

step by step progress logging for oracle plsql

I'm having a message_log table with ID, Severity Level, Message, Log Date & Time. I'm using below procedure to insert log messages to log table in my PL/SQL scripts. add_log(ID,'START','DEBUG','No CDR Detail to be processed', sysdate); But in…
dvn
  • 41
  • 4
4
votes
2 answers

PL/SQL - Delete current record in FOR IN LOOP

I am trying to figure out how to delete a record using the FOR .. IN .. LOOP FOR REC IN (SELECT * FROM WORKLIST) LOOP GET_DATA(REC); --DELETE REC FROM WORKLIST END LOOP; The GET_DATA procedure explodes every row to multiple ones so the current…
Delphy
  • 306
  • 1
  • 4
  • 16
4
votes
1 answer

Getting nextval from a backing sequence of an identity column

I have a table column declared like this: file_id number(10) generated always as identity primary key, Is it possible to programatically get a currval/nextval from its backing sequence without actually looking into the SYS. tables to obtain the…
Matěj Zábský
  • 16,909
  • 15
  • 69
  • 114
4
votes
2 answers

Ignore bad statements in jdbc batch

I have code to do a batch insert into a SqlServer or Oracle table. How can I tell JDBC to A) Keep going if one of the insert statements fails B) Inform me which one had the issue sql.withBatch("""Insert Into…
Steve
  • 4,457
  • 12
  • 48
  • 89
4
votes
2 answers

How to generate XML from Object Type in Oracle database

As the title explains, how to parse data from object types in Oracle to the XML format? So for instance, if I have an object type CUSTOMER, how one can convert the data from an instance of that object to XML text?
Evandro Pomatti
  • 13,341
  • 16
  • 97
  • 165
4
votes
1 answer

Calling PLsql script with an anonymous PL SQL block from SOCI

I'm searching for a way to call an anonymous PLsql block through SOCI. The data transfer takes place through a refcursor that was previously created as a variable in the script: variable rc refcursor declare v_obj_id number(4,0) := 1; …
Sampath
  • 1,144
  • 1
  • 21
  • 38
4
votes
1 answer

Is there any print statement in db2 just like oracle

Is there( db2 database) any equivalent of DBMS_OUTPUT in Oracle or PRINT in SQL Server for DB2 or do I need to jump over my head again for this sort of thing? please provide some code also.
raj
  • 51
  • 2
  • 5
1 2 3
…
99 100