All Questions
295
questions
55
votes
4
answers
74k
views
How to use a package constant in SQL SELECT statement?
How can I use a package variable in a simple SELECT query statement in Oracle?
Something like
SELECT * FROM MyTable WHERE TypeId = MyPackage.MY_TYPE
Is it possible at all or only when using PL/SQL (...
- 3,074
30
votes
3
answers
118k
views
Oracle Error PLS-00323: subprogram or cursor is declared in a package specification and must be defined in the package body
Can someone help me put my pl/sql procedure in a package? I've tried and I'm struggling with it:
This is what I have, for my package specification:
CREATE OR REPLACE PACKAGE film_pkg
IS
title ...
- 447
23
votes
9
answers
34k
views
Since SQL Server doesn't have packages, what do programmers do to get around it?
I have a SQL Server database that has a huge proliferation of stored procedures. Large numbers of stored procedures are not a problem in my Oracle databases because of the Oracle "package" feature.
...
Mark Brady
22
votes
2
answers
160k
views
Oracle: Call stored procedure inside the package
I have the following package:
create or replace package PKG1
as
procedure INIT
(
nRN in number,
nREC_TYPE in number,
nIDENT out number
);
...
- 4,013
16
votes
2
answers
50k
views
Oracle User defined type inside package definition
is it possible to have a User Defined Type inside Oracle package definition? When I try following
CREATE OR REPLACE PACKAGE AF_CONTRACT AS -- spec
-- PROCEDURE my_rpcedure (emp_id NUMBER);
...
- 2,103
12
votes
3
answers
42k
views
Procedure in package specification
I have a package named save_db_values
I have two procedures named store_records and another one called db_activities.
db_activities will be called from my application by passing all values in ...
- 14.6k
12
votes
6
answers
51k
views
What Situations Cause Oracle Packages to Become Invalid?
The scenario that created this question:
We have a package that is a dependency of another package, sometimes making changes to the "parent" package causes the dependent package to become invalid, ...
- 8,068
12
votes
3
answers
53k
views
Can I recover older Oracle pl/sql source code from a package body after i have replaced with newer code
I had created an Oracle PL/SQL package with a header and a body with lots of code.
Later, I ended up accidentally erasing the code from that body after reran the CREATE OR REPLACE PACKAGE BODY... ...
- 121
12
votes
1
answer
9k
views
Is there any way to determine if a package has state in Oracle?
Is there any way in Oracle to determine whether a package has state or is stateless? I'm not aware of any view in the data dictionary that contains that information.
The "ORA-04068: existing state ...
- 15k
11
votes
2
answers
61k
views
ORA-01031: insufficient privileges when creating package
I'm getting ORA-01031: insufficient privileges when creating a package my own schema. Shouldn't I have complete control over my schema. If this is not the case, what privileges does my schema need?
- 4,257
10
votes
2
answers
28k
views
Obtain stored procedure metadata for a procedure within an Oracle package using ADO.NET
I am trying to obtain the stored procedure metadata (procedure name,parameter types,parameter names etc) for a procedure declared within an Oracle package, using the standard ADO.NET API - ...
- 4,623
8
votes
8
answers
8k
views
How do you work on Oracle packages in a collaborative, version-controlled environment? [closed]
I'm working in a multi-developer environment in Oracle with a large package. We have a DEV => TST => PRD promotion pattern. Currently, all package edits are made directly in TOAD and then compiled ...
- 36.9k
7
votes
4
answers
12k
views
Unable to 'SELECT INTO' when value doesn't exist
SELECT Value1 INTO lValue
FROM Table1
WHERE Field1 = lTempValue;
This works fine when the match is true. But if the match isn't true, I receive an error.
ORA-01403: no data found
Ideally, that'...
- 4,276
7
votes
2
answers
7k
views
In Oracle PL/SQL is there any way to import packages and their members?
Given a package:
create or replace package foo as
f1 number := 1;
end;
/
Instead of:
declare
begin
dbms_output.put_line('f1 = ' || foo.f1);
end;
/
I'd like to write:
declare
begin
-- ...
- 10.6k
6
votes
2
answers
52k
views
Create a SSIS Package - To Copy data from Oracle to SQL Server
I want to write a SSIS Package for copying data from oracle to MS Sql server. and also the condition is, What are the data has to be moved from oracle to SQL will be known by selecting a table in SQL....
- 117
6
votes
2
answers
22k
views
SqlPlus query issue (Package Spec and Body)
I am trying to get package spec and body from sqlplus by doing so..
select text from all_source
where name = 'PACK_JACK'
order by line;
but I am only getting its body not the spec.. what I have to ...
- 785
6
votes
1
answer
3k
views
Bypass "table or view does not exist" in package compilation
There are two schemas in a Oracle database.
MYSCHEMA that is controlled by me.
OTHERSCHEMA that is not controlled by me.
I just know I can get result from select * from OTHERSCHEMA.OTHEROBJECT. ...
- 2,515
6
votes
3
answers
4k
views
How can I deploy new version PL/SQL package in Oracle DB server under high load?
There is a high loaded 24/7 system based on Oracle DB Server.
Many of the client applications work with the package pkg1.
Is there any possibility (Oracle implementation or best practice) of ...
- 91
6
votes
1
answer
1k
views
Impact of Package Size to Performance in Oracle 10g
Using Oracle 10g. The original Oracle designer for this project has moved on and those of us remaining are reasonable developer's for Oracle but we need some tuning and planning assistance.
We have ...
- 5,074
6
votes
6
answers
4k
views
Discover PL/SQL package-level types using Oracle dictionary views
Can I discover types declared in Oracle PL/SQL packages using dictionary views? I.e. when I declare this:
CREATE OR REPLACE PACKAGE my_types AS
TYPE t_cursor_type IS REF CURSOR;
TYPE t_table_type ...
- 218k
5
votes
1
answer
1k
views
Is there any way to namespace PL/SQL packages?
I have several different packages, one for each logical part of my application. Some packages are getting huge but I would like to keep all the procedures/functions grouped in some way rather than ...
- 8,871
5
votes
1
answer
21k
views
Calling PL/SQL package code in a Java Program
I am trying to call a procedure defined with a PL/SQL package in a Java program.
I am aware one can call stored procedures using connection.prepareCall in Jdbc. But there is very little information ...
- 2,290
5
votes
1
answer
26k
views
update the package body in Oracle Database (Sql developer)
I need to have a miner update to the package body in Oracle Database using Oracle SQL Deverloper 3.2. The update does not need to change the package specification.
Using the Oracle SQL Deverloper, I ...
- 317
5
votes
0
answers
1k
views
cx_Oracle - DLL load failed: %1 is not a valid Win32 application
I have windows 7 64 bit OS with python 2.7.10 64 bit running and installed the Oracle Instant Client for 64 bit OS and as well as the cx_Oracle package for the 64 bit. I am getting the error like ...
- 303
4
votes
3
answers
36k
views
Function/Package issue PL/SQL
I am trying to create a package with four functions. Each function will add a set of numbers and subtract one from the total. I have been having lots of trouble getting the syntax correct. The ...
- 578
4
votes
4
answers
14k
views
Using Pragma in Oracle Package Body
I'd like to create an Oracle Package and two functions in it: A public function ( function_public ) and a private one ( function_private ). The public function uses the private one in an sql statement....
- 6,160
4
votes
3
answers
10k
views
'ORA-01031: insufficient privileges' error received when inserting into a View
Under the user name 'MY_ADMIN', I have successfully created a table called 'NOTIFICATIONS' and a view called 'V_NOTIFICATIONS'. On the 'V_NOTIFICATIONS' view I have successfully created a trigger and ...
- 189
4
votes
3
answers
4k
views
Find package global variables from data dictionary
I have a package:
CREATE PACKAGE my_pkg
IS
g_var NUMBER;
END;
/
Is there any way I can query SYS views, to find that this package has this global variable? I'm interested in explicit variable name ...
- 2,817
4
votes
2
answers
3k
views
Getting ORACLE programming object definitions
Let's say I have an ORACLE schema with contains a package.
That package defines types, functions, procedures, etc:
CREATE PACKAGE...
DECLARE
FUNCTION ...
PROCEDURE ...
END;
Is there a query I ...
user286353
4
votes
1
answer
14k
views
How to translate Oracle package to SQL Server?
In Oracle I have lots of stored procedures using a package which basically stores (encapsulates) and initializes all variables used by these procedures. There is one function in the package as well ...
- 788
3
votes
1
answer
10k
views
Initialization section of the package
This is the package specification:
CREATE OR REPLACE PACKAGE employee_info
IS
PROCEDURE p;
FUNCTION f RETURN BOOLEAN;
END employee_info;
This is the package body:
CREATE OR REPLACE PACKAGE ...
- 555
3
votes
3
answers
3k
views
Overhead for calling a procedure/function in another Oracle package
We're discussing the performance impact of putting a common function/procedure in a separate package or using a local copy in each package.
My thinking is that it would be cleaner to have the common ...
- 13.1k
3
votes
3
answers
54k
views
How to call an Oracle procedure within a package?
I'm trying to call an Oracle stored procedure within a package and I'm getting this error:
SQL Error: ORA-06576: not a valid function or procedure name
I'm using SQL Developer and this is the command ...
- 57
3
votes
3
answers
27k
views
How to execute a procedure which is inside a package?
There is a package ABC and many procedures inside it. I want to execute a single procedure inside that (say xyz). I used the below commands
begin
ABC.xyz;
end;
I am not able to run the same. Can any ...
- 1,969
3
votes
2
answers
14k
views
PLS-00386: type mismatch found between FETCH cursor and INTO variables
The following package throws :
PLS-00386: type mismatch found at 'V_STUDYTBL' between FETCH cursor and INTO variables
Purpose of the code:
Define two types outside the package, one is used to send a ...
- 151
3
votes
1
answer
3k
views
PL/SQL package type vs schema type
What I'm trying to do is the following:
create or replace
package MyPackage
as
type string_list_t is table of varchar2(32767);
function GetPrimaryKeys ( p_table_name varchar2, p_owner ...
- 5,175
3
votes
3
answers
6k
views
Oracle DBMS package command to export table content as INSERT statement
Is there any subprogram similar to DBMS_METADATA.GET_DDL that can actually export the table data as INSERT statements?
For example, using DBMS_METADATA.GET_DDL('TABLE', 'MYTABLE', 'MYOWNER') will ...
- 3,415
3
votes
1
answer
18k
views
How to identify all stored procedures refering a particular table in Oracle
I am working with Oracle 12c and need to find all references where a specific table or view is being used in Stored Procedure/Function and packages.
I have found a this answer about MS SQL Server, ...
- 984
3
votes
1
answer
13k
views
How to find Lines of Code of packaged procedures and functions in Oracle [duplicate]
Possible Duplicate:
How to View Oracle Stored Procedure using SQLPlus?
Im new to Oracle. Could you please help me on this?
How to fine Lines of Code of packaged procedures and functions in ...
- 198
3
votes
2
answers
5k
views
Why can't execute SCHEMA_NAME.PACKAGE_NAME.PROCEDURE, Oracle
I have a schema A, Package B and Procedure C. B is in A schema and C is in B's Package Body.
It works fine when I say:
Begin
Exec B.C;
END;
But it throw an error when I say:
Begin
Exec A.B.C;
END;...
- 143
3
votes
2
answers
3k
views
How to execute a PL/SQL package from powershell?
I'm trying to write a powershell script to help with some user maintenance tasks across multiple databases.
Database 1 contains my user ID and user type (ex: employee, subcontractor, etc...)
Database ...
- 4,547
3
votes
1
answer
3k
views
Oracle package global variables with multiple sessions
I have an Oracle Package with some Global Variables which are initialized and used on all procedures of that package.
When I call the procedures (via jdbc connection and calls) the global variables ...
- 33
3
votes
1
answer
543
views
procedure does not exist in the target list of the run pl/sql?
I compile the package spec and body without any problem. However, when I want to test my procedure, it is not in "Target:" list of the run PL/SQL menu of the oracle sql developer. And also, I could ...
- 349
3
votes
1
answer
2k
views
Is there a way to recreate an ODI package using ODI Scenario?
I mistakenly deleted an ODI package from my project which is very large in size. Is there a way to recreate the same package if I have a previously exported scenario for the same project?
- 95
2
votes
3
answers
138
views
I've seen one but... what exactly is a 10g Package?
Scenario: I know nothing about packages except that they contain functions and procedures all wrapped into a nice little present. So today I was given this package (rather large) with the instructions ...
- 5,386
2
votes
3
answers
7k
views
Oracle 9i: Synonymed Table Does Not Exist?
I've created a package that contains a stored procedure that I plan to invoke from a separate application. The stored procedure will return a sorted list of all the views and tables in the schema. To ...
- 16.8k
2
votes
4
answers
3k
views
Oracle procedures vs MS procedures vs Oracle packages
I was told by someone that when you create procedures in Oracle you should create a Package with procedures in it. Is this a true statement? Are procedures in MS the same as Oracle?
- 1,364
2
votes
4
answers
491
views
Auto increment in Oracle package
I am trying to use autoincrement on my package without using trigger.. Can someone explain me how I have to use it in my package.. I did this way didnt work its complaining abt variable not being ...
- 163
2
votes
4
answers
6k
views
Is recompiling Oracle Packages safe
Hi
We have a third party oracle based application, that ships with precompiled binary(wrapped) packages.
But when I compile them in Oracle SQL Developer (right click -> compile all), they get ...
- 305
2
votes
2
answers
6k
views
Maximum number of lines in an Oracle Package
Can anyone tell me the maximum number of lines in an Oracle Package?
What is recommended by Oracle?
What is programmatically possible?
