I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.
View 1 RepliesI am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.
To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...
SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;
However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).
So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?
Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?
I'm trying to create a package body but i keep getting an error message saying package created with complication.
CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......
Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
When i compile the package body i get errors.
CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *
[code]...
I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.
View 4 Replies View RelatedI am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:
WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;
[code]....
WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......
In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.
while compiling this package body, i get error
error 102 at line 21 column 11
encounterd symbol "Group" when expecting one of following
begin function package pragma procedure subtype type use form cursor
I sent a request for the DBA's to grant me rights to the package body but I only see the spec with the grant execute command.
Here is the command I just sent:
grant create any procedure to DEVELOPER1;
But the DBA changed my grant command to:
grant create procedure to DEVELOPER1;
But I still cannot see the package body. This is on a test environment.
Would the "create any procedure" command see the package body of different schema owners package bodies?
Or is there another grant command to see different schema package bodies?
1- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?
For example: SchemaName1.PackageName1.ProcedureName1
I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".
SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL';
SP2-0809: Package Body Created with Compilation Errors
SQL> SHOW ERRORS
No Errors
how to check errors for that, or oracle simply pointing that package might have performance issue.
I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).
View 11 Replies View RelatedI have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
I am getting the below error when i am trying to import the dmp file into my oracle 11g.
impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
c_default_limit CONSTANT PLS_INTEGER:=5000;
[code]....
show error
error code
SQL> @pkg_mkt_hub_load_collection.sql
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4 PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
SQL>
I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1
ORA-00933: SQL command not properly ended
what is the difference between WRAPPED and WORDWRAPPED with examples
View 6 Replies View Relatedthis query works ok in oracle.dataAccess but not in Oracle.ManagedDataAccess.Connector does not raise an error. It just stalls.
Dim PKRetrieveCommand As New OracleCommand : PKRetrieveCommand.Connection = JobConnection
PKRetrieveCommand.CommandText = " INSERT INTO TMP_SEQ (FIELD1) VALUES (DBOID_SEQ.NEXTVAL) RETURNING FIELD1 INTO :dboid"
PKRetrieveCommand.Parameters.Add("dboid", OracleDbType.Decimal, ParameterDirection.Output)
PKRetrieveCommand.ArrayBindCount = allInserts.Length
PKRetrieveCommand.ExecuteNonQuery()TMP_SEQ
Is a temporary table that deletes rows on commit, Field1 is a numeric type and DBOID_SEQ. is a sequence.
I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
How can we clean a tablespace(delete all the contents) without dropping the tablespace.
View 3 Replies View RelatedI have some BLOB contents in the format of...
DK99F17,AA,032820130840,Other
ABCD,AA,032820130840,OV
AAZ123,BC,032820130932,DWL
CBA12345,ZA,032820130939,Other
Each BLOB is associated to a file name in the format...
03282013100002_thisfile.txt
The blob for each file may be zero rows to n rows in size, but typically there are 2 to 5 rows (four rows were shown in the rows above).The following kind of gets me there, but not quite as it splits up the BLOB rows at the comma and not the line break (HEX=0D0A / CRLF).
with rec as
(select fs.file_name, utl_raw.cast_to_varchar2(fs.file_data) file_data
from tada.files_store fs
where fs.file_name like '%citations.txt'
and trunc(fs.date_created) = to_date('26-MAR-2013','DD-MON-YYYY'))
[code].....
I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.
I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.
i have table A i.e. contents
----------------------
1-jan-2012 x
2-jan-2012 y
4-jan-2012 x
7-jan-2012 x
I need to discplay below:
1-jan-2012 x
2-jan-2012 y
3-jan-2012 0
4-jan-2012 y
5-jan-2012 0
6-jan-2012 0
7-jan-2012 x
Is there a way to list contents of a dmp file(using expdp generate),which objects(tables/vies/procedures/packages) does it contain?
View 4 Replies View Relatedhow to incorporate counts into my current SQL code that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these columns). I don't really care if the count value repeats in every row for that person.
Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars', T3.Cars from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,
[code]....
I am having a table with contents like
item id, name
100,s_enter
100,p_enter
200,s_enter
i would like the output to be
Variable name cnt_s_enter=1 (this is for 200)
cnt_s_p_enter =1 (this is becoz 100 is both in s_enter and p_enter)
I am working with basic case but thats not working.
select
sum(case when name='s_enter' then 1 else 0 end),
sum(case when name in (s_enter,p_enter) then 1 else 0 end)
from table
How to read the treedump contents of a index IDX_TB_TEST_N1?
SQL> select object_id,object_name from dba_objects where owner='HXL';
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
51786 IDX_TB_TEST_N1
alter session set events 'immediate trace name treedump level 51786'
/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
[code]...
I have some doubts about redo log files,
1) Can we fetch 'select statements' from redo log files through the use of log miner utility or any other?
(I think redo log file contains only insert,update,delete and DDL/DCL commands only)
2) If "No" to the above answer then how can i fetch all select statements fired on the system for a day or particular time.
(setting of sql_trace may be the one of them, but can it be possible for system level)