Stack Overflow
Stack Overflow

Questions tagged [plsql-package]

A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use this tag for questions referring to the PL/SQL packages usage and behaviour.

240 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

When does an Oracle Package Specification become INVALID

As far as I know a package body can be replaced and recompiled without affecting the specification. A package specification declares procedures and functions, not defines them, so they can not reference objects, that can make the package…
chumakoff
  • 6,807
  • 2
  • 23
  • 45
3
votes
1 answer

Why Plsql package variables and constants cannot be used in Sql?

Create some package: create or replace package my_package is some_var number := 10; end; and use it in Sql: select my_package.some_var from dual So it gives error PLS-221. P.S. I know that I can use wrapper function. My question is why this is…
Si7ius
  • 676
  • 6
  • 14
3
votes
1 answer

Pipelining Between PL/SQL Table Functions

I have a package with 2 pipelined functions. When I'm trying to call one function with another function as it's argument I'm getting "ORA-06553: PLS-306: wrong number or types of arguments in call" error. Here is the package: create or replace…
brungel
  • 33
  • 4
3
votes
1 answer

Oracle modify sys refcursor and return the modified cursor in PL/SQL

Im trying to create a procedure which takes sys refcursor as in out parameter and modifies it based on the logic explained in comments in the below code TYPE t_params IS TABLE OF VARCHAR2(32767 CHAR); / CREATE OR REPLACE PROCEDURE modify_cursor…
DeekuSen
  • 95
  • 2
  • 9
3
votes
1 answer

find string with quotes and double quotes in all oracle packages

I am trying to find a query which will return all the string inside all the packages and procedures like like '2%'. For example in dynamic query WHEN SUBSTR(S.project,0,1) LIKE ''2%'' THEN ''newProject'' and like in normal query WHERE B.project…
mahipal singh
  • 355
  • 3
  • 15
2
votes
0 answers

How to handle parallel execution related issue in plsql procedure

I have a plsql procedure used to insert/update records to a table and this procedure can be called from more than 1 place at a given moment from parallel execution threads. This method checks whether a record exists in the table using the keys and…
Subash Basnayake
  • 133
  • 2
  • 10
2
votes
1 answer

Why PLSQL optimize level is not providing expected result?

I am trying to understand complier optimizations in plsql in depth. Theoretically, default optimisation level PLSQL_OPTIMIZE_LEVEL is set to 2. To get better performance, we can set it to 3. To explain this to myself, I am using an example wherein…
halfwind22
  • 329
  • 4
  • 18
2
votes
1 answer

How to compile Oracle package with APEX functions?

I am trying to compile an Oracle package that my colleague left to me. However, the package spec. contains some functions that may not be built in my Oracle environment. create or replace PACKAGE AAA IS g_session VARCHAR2(400) :=…
Kelvin Ho
  • 376
  • 2
  • 3
  • 14
2
votes
3 answers

How to access just one OUT value from a Procedure that returns multiple OUT values?

I have a PL/SQL procedure that has a single IN/OUT value and multiple OUT values. The Procedure works fine and I am able to read all the OUT values. What I now wish to do is to call the Procedure from within another Procedure but only access one of…
Stephen
  • 23
  • 2
2
votes
2 answers

How can I know if APEX_JSON is installed on my database?

I want to work with json data, in an Oracle database, so I tried the apex_json package. I tried this example from Oracle: DECLARE s varchar2(32767) := '{ "a": 1, "b": ["hello", "world"]}'; BEGIN apex_json.parse(s); sys.dbms_output.put_line('a…
Kresten
  • 810
  • 13
  • 36
2
votes
0 answers

Execution flow for PL/SQL

I have many packages in production system and those packages has as much procedures.Say some of them updates/inserts into a table. How can I identify those packages in sequence of operation without going through 1000s lines of code. Thanks.
user8950495
2
votes
1 answer

Optional condition in Oracle 10g

For a dummy test, I want to show a list of employees in a web form. There is a drop down on the web form that contains a short list of departments, like this: All Depts Sales Dept Marketing Dept Communication Dept HR Dept Finance Dept IT Dept The…
Stack0verflow
  • 1,148
  • 4
  • 18
  • 42
1
vote
0 answers

"ORA-21780: Maximum number of object durations exceeded " error for Nested Table type

I have a function and its return type is a nested table type. I have created a nested table type local variable in the function and the variable gets data from cursor with the help of bulk collect .During the bulk collect operation maybe after…
Maithri Shenoy
  • 21
  • 2
1
vote
1 answer

How to find all Refcurs which return a specific field

Im in need of finding all the refcurs in our system that have a specific field. Important to mention that all said cursors are outs of procedures that themselves are in packages. ive located all of the candidates with SELECT * FROM user_arguments…
KlesierTheSurvivor
  • 29
  • 3
1
2 3
…
15 16