NAME package
---------- --------------
Anand basic Anand pascal Juliana cobol Kamala dbase Mary oracle Mary c Partick c++ Qadir assembly Qadir c Ramesh dbase Remitha c Remitha assembly Revathi pascal Revathi basic
Write a script to display the first and last names of the targets that have id less than 3.
DECLARE
fname targets.first_name%TYPE;
lname targets.last_name%TYPE;
CURSOR id IS
SELECT target_id FROM targets;
BEGIN
FOR val IN id
LOOP
IF val < 3
THEN
[code]....
I tried using without %TYPE but could not get the result.
I have a table
TAB1 with data
no name
1 abc
2 abc
3 xyz
4 xyz
5 cvb
now I would like to create a form with name counts as
abc 2
xyz 2
cvb 1
I would like to create a datablock to display all the unique names, not sure where I should be writing the query to display unique names. I would like to create a textitem box to display the counts when the form is compiled, but not sure which trigger to use to write the query.
Using the HR schema, I'm trying to write a query which will display the names and salaries of those employees who earn the highest salaries in their respective departments.
I wrote the query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary =
(SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);
However, this query does not execute correctly but I'm not sure why?
I have a table with the following data.
item_code item_name
101 Pen
101 Pencil
101 Scale
102 Pen
[Code]...
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed.
102 Pencil Because the items Pencil and Marker are missed.
102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
Any ways for Debugging in PLSQL.
Ex:In a package or procedure i have thousands lines of Code how can i Debug.
I'm not sure if this belong to this place. move it to the proper place.
I'm having an annoying problem: (I'm not sure if it is a problem)
- In a package body I'm trying to delete/update/insert/select the contents of a table in other schema.
- The respective synonym exists.
- I had created a role with the respective privileges over the synonym.
- I granted the role to the package's owner.
- I try to compile the package, but it keeps returning compilation errors. (Not table found)
- In standard SQL, I can delete/insert/update/select over the table.
- The only way to compile the package, without errors, is to grant the privileges directly to the package's owner.
Is it supposed to work in this way?
I am unable to locate the asm packages for rhel 5 :2.6.8-128 kernel of 32 bit.
Only 64 bit rpm are availbale on OTN.Can ayone suggeet me the URL for 32 bit>
Get following output using user_source(or other) view?
Package_nameProcedure_nameline_startline_end
ABCXYZ1022
In procedure_name column it could be any procedure, function name or any object name exist in package body.
Wanted to know whether it is possible to know a trigger is getting fired due to which all packages/procedures. If it has got fired for a particular transaction say multiple times, then wanted to get details of the packages/procedures which caused an event so that trigger got fired?
View 2 Replies View RelatedWe are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.
I'll provide the timeline of what occurred:
1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.
2.) I ran a "drop package body" statement to drop the package body from MY schema.
3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.
4.) I ran a script to check for any invalid objects under the application user schema. There were none.
5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.
6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.
I also ran utlrp.sql.
I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.
I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.
I am trying to use dbms_jobs in Oracle 8i database.
This is the syntax I am using:
begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;
i have defined the variable jobno as number!
And i am getting the error :
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
I then go and look at the Sys.dbms_job package and see its not compiling
I have this error:
(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored
i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"
[code]....
what is the difference using cursor in specifications & body in packages?
View 1 Replies View Relatedwe have a database application which is done frequently.in these we load data throught Sql loader, we create an DB instances, we do several DML operation on the database.
now for such task in an application we need to keep an logging track of each task performed in PL/SQl procedure packages.
I am really wondering about data loading to the cloud, I've just read the documentation, but regarding the packages, I have more than 100 package and I have to "add to cart" one by one the problem is that the package body is not added by default, so you have to expand the packages one by one then select the package body right click and add to cart. This task is very tedious, is there any other options.”
View 8 Replies View RelatedI'm trying to install 11g Release 2 on a CentOS (4.9) box, but the pre-req check shows that I'm missing the following packages (I had 9 to begin):
gcc-3.4.6 (X86_64)
gcc-c++-3.4.6(X86_64)
libstdc++-devel-3.4.6
Tried to use the yum command "yum install gcc" but get the following error.
Error: cannot find a valid baseurl for repo:update
I think this is due to the "CentOS-Base.repo" file, which I've tried to update by changing the mirror URL's but no success.Then tried to find the rpms online ([URL]...
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
INFO: rm -f ntcontab.*
[code]....
I need to figure out which Oracle Database packages are installed on a certain server.
First problem: no Enterprise Manager available. So I need some command line tools or SQL statements.
I've already found some with them I was able to spot RAC, partitioning and so on. But I'm still missing a option/command to spot
- diagnistic pack
- tuning pack
- web logic
- web server
How to export the procedure,views,function and packages in a database, by using Export commmand.
View 2 Replies View Related i want to do a schema export from Database A. There are hundreds of users under this schema.I have to import this schema into other database say B. My question's are:
1) Do i need to pre-create only schema user or all the users under it.
2) Will the schema export all the roles,procedures,packages,synonyms,funsctions and triggers?
APEX 4.2Oracle 11g Database We are using the standard exception handler that was introduced in APEX 4.1, and we have code in packages & procedures in the database (following proper processes of keeping code in the database where possible). When an exception is found in the procedures/ packages/functions, should the APEX application level exception handler catch any errors that occur or should they be handled in the package/procedure/function they occurred in? Why I ask if, we right now have exception handling code in the pl/code bodies BUT they write their errors to the same table that Apex's Exception handler does, but the errors are NOT presented to the user using the APEX exception handling mechanism.
View 2 Replies View RelatedI have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are
sid varchar2(10),
cob_dt varchar2(10),
deal_id varchar2(10),
new_val varchar2(10),
old_val varchar2(30),
amend_col v archar2(50),
i have return a proc
create or replace
[Code]...
this is my code
DECLARE
V1 VARCHAR2(100);
V2 VARCHAR2(100);
V3 VARCHAR2(200);
BEGIN
[code]......
if i want to dynamically enter the three to four names then i want the count of those names .
when we decribe one table structure, it shows the column name as below:
DUKEDMTS03DB01:SYS@TODS1> desc uet_rep.ECM_MAINTENANCE
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(254)
[Code]...
But, till yday it was showing the orignal column name..
I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.
Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.
Here is my
declare
to_num number;
field_name varchar2(15);
[code]....
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'
Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?
CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');
I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.
So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.
Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.
I wonder if there is any way to return the columns of an select with its letters lowercase?
I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,
i have list item populated with many table names from a schema. i have grid in oracle forms 10g and i want to fill the grid with at least four/more columns.I want to fire the list change trigger when each time any one table name is selected.
how can i find the columns names dynamically for filling the grid.