I 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 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.
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').
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.
how 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,
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
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)
I'm having a problem while exporting an DB to file. I have a certain table with a CLOB column which values are cut when I use the Export option on SQL Developer.
I have no backup taken in my oracle server.Today morning i created a data file and unknowingly deleted it. Now i need to restore the datafile with its contents without using RMAN.
I'm starting with APEX, I would like to know how to insert the contents of a file. Csv in a table, how to map a file to a table, how to insert file contents. Xls in a table.
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
I just need to run javascript to capture the contents of the page( form) like labels and values in the fields using a COPY button to capture all the information available on the page or form to copy that to system clipboard so that information can be used into another system.
Let me explain, I have two items in my page, the first is not required, if it is null the second field is a value list based on selection A (select from A. .. A. .. where . A..) but if the field is not null then the list of values of the second field is based on the selection B (Select B from B where B. ..).
I create a dynamic action on the event "change" of my first field, on the "true action" I tried this:
1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.
2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.
List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF 2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF 3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF 4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF 5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF
3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"
4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.
SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;
I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).
what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.
The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)
How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?
Like: CREATE TABLE club ( clubId NUMBER, name VARCHAR(20) NOT NULL, PRIMARY KEY (ClubId) );
CREATE TABLE team ( teamid NUMBER(10), clubid NUMBER(10), teamname VARCHAR(10) NOT NULL, PRIMARY KEY (teamid, clubid), FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table
