Identify a useful persistent package variable for the system.
If i am not wrong a package is like a box where you can add functions procedures inside of it!
variables are something like (x number :=2;) which those are part of functions and all.
2nd question is identify suitable package for the system that implements constraints.create the program package program.
Below SQL query (for making a view) is not working.
CASE WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_1
then 'New' WHEN IN_TYPE = PKG_CONSTANTS.INV_TYPE_2 THEN 'old' ELSE 'N/A' end as
Status PKG_CONSTANTS.INV_TYPE_1 ==> PKG_CONSTANTS is a package and INV_TYPE_1 is a constant variable. INV_TYPE_1 CONSTANT VARCHAR2(10) := '55600'; INV_TYPE_2 CONSTANT VARCHAR2(10) := '55601';
error: oracle.dbtools.raptor.controls.sqldialog.ObjectActionController$EditorObjectActionListener .. some 10 similar errors are coming in Loggin Page of SQL Developer
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
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 RelatedWrite a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:
If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".
For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".
We have system.current_item and system.cursor_item.....
both represent the same....
like this we have few other system variables also .....
what is the main difference between current_item and cursor_item?
couple of questions about below parameters, do they measure single processor or avg usage across all processorss and cpus for example will i recieve alert if CPU system time goes above 95% for single processors? or not
CPU Idle Time (%)
CPU System Time (%)
CPU User Time (%)
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
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:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
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?
My manager has a couple of 2-proc dual-core Opteron servers with 8GB of RAM and RAID controllers. These servers currently run Solaris 8, but he wants to migrate them to a newer Linux systems.
View 4 Replies View RelatedI was wondering with dba_constraints and i found unexpected result shown as below
select constraints_name, constraint_type from dba_constraints
constraint_name constraint_type
SYS_C00141 c
SYS_C00142 c
SYS_C00143 c
SYS_C00144 c
SYS_C00145 c
SYS_C00146 c
SYS_C00147 c
SYS_C00148 c
SYS_C00149 c
i jst want to know that what is this system generated constraints ?
I have a dmp files of one table . the dmp files are from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
I want to export into my oracle db. i have not create any db or tables . i just installed oracle 11g.
On one of our systems, the system tablespace ist growing up fast. The largest objects are:
SYS.C_OBJ# 3 GB (CLuster)
SYS.I_COL1 2,8 GB (INDEX)
SYS.I_COL2 1 GB (INDEX)
SYS.I_COL3 980 MB (INDEX)
why theses objects are going so big? Is it possible to shrink these objects? Table of the three index ist sys.col$ and part of CLuster sys.c_obj#
what is exactly system.cursor_record?? actually i read documentation on that...but i still not getting..
View 2 Replies View Relatedi have installed oracle 8i on my windows xp system. it has not given me any problems during installation. i could log on to oracle and work on it without any problems. but when i restarted my system, my system has failed to start the oracle service, and i could no longer logon to sql * plus.
I've got messages 'oracle not available'. i've even tried to start the service from administrative tools-services, but of no use. then i created a new database and log on to sql* plus. but when i rebooted my system, the problem continued.
I have a promotion table which is having 3 columns.
PROMOTION_ID
display_name
ASSET_VERSION
This ASSET_VERSION
field keep on getting updated by other system where they may update the display name of the promotion_id.The main concept of this table is there will be a unique combitination of asset_id and promotion id.
Promotion_idDISPLAY_NAME ASSET_VERSION
123 1ST VERSION 1
123 2ND VERSION 2
123 3RD VERSION 3
456 1ST VERSION 1
456 2ND VERSION 2
456 3RD VERSION 3
789 1ST VERSION 1
789 2ND VERSION 2
789 3RD VERSION 3
So,my requirement is that i have the fetch promotion_id ,display_name that is having highset asset_version.
The OUTPUT SHOULD BE:
Promotion_idDISPLAY_NAME
123 3RD VERSION
456 3RD VERSION
789 3RD VERSION
I need a single sql for this .
Driver used: oracle.jdbc.xa.client.OracleXADataSource
Orcale version: 11G
When I attempt to connect to the DB as SYS, SYSTEM using sqldeveloper I get:
ORA-01017:invalid username/password; logon denied
Now I am certain the username and password are correct, I think it has something to do with remote login of SYS account. Are DBA accounts not allowed to connect via network? I get the same error when I try to sqlpython wrapper which I believe uses TCP
[oracle@oracle ~]$ sqlpython sys as sysdba
which: no gedit in (/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/sbin:/bin:/usr/sbin:/usr/bin)
which: no kate in (/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/sbin:/bin:/usr/sbin:/usr/bin)
Password:
Connection failure.
Opens the DB connection. Some sample valid connection strings:
[code]....
As per my req, i need to get the system grants of one user (GRANTEE) using DBMS_OUTPUT API. The requirement get completed using 'SYSTEM_GRANT' as parameter for OPEN function in the metadata api. Please look into part of code which works.
v_meta_handle := DBMS_METADATA.OPEN('SYSTEM_GRANT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'GRANTEE','SCOTT');
When using above piece, i get sys grants granted to SCOTT user. But i need to use 'DATABASE_EXPORT' as a parameter to my 'OPEN' function.
v_meta_handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'INCLUDE_PATH_EXPR','IN''SYSTEM_GRANT''');
DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''SCOTT''','GRANTEE_EXPR');
The second set filter doesn't work to get SYS GRANTS of one user. It does not throw any error. Simply the filter doesn't work. 'GRANTEE_EXPR' is not a correct value in the 2nd set filter. What parameter need to pass in object_path_expr ('GRANTEE_EXPR')?
Why Package has to be created as two parts
1) Package spec
2) Package Body ?
Quote:PLS-00304: cannot compile body of '<PACKAGE_NAME>' without its specification
I learned it as a Thumb of rule! But IF there is any rule for it give that URL/link for that.
is it possible that two different package can contain same procedure?
View 1 Replies View RelatedTrying to accomplish:
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?
View 14 Replies View RelatedThere are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.
Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.
ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []
I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?
We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.
The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.
what type of PL/SQL output we should use here?
Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.
I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.
SQL>drop table BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0;
I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table.
step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table.
I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.
View 6 Replies View RelatedHow to use DBMS_SQL package. I tried with the following procedure.
CREATE OR replace PROCEDURE Crt_tab_inst(tab_name VARCHAR2,
col1_name VARCHAR2,
col1_value VARCHAR2)
IS
cur BINARY_INTEGER := dbms_sql.open_cursor;
fdbk BINARY_INTEGER;
[code]........
But when Iam executing the procedure, it is throwing the below error. This is the error Iam getting:
SQL> EXEC crt_tab_inst('MYTAB','MYCOL','NAME1');
BEGIN crt_tab_inst('MYTAB','MYCOL','NAME1'); END;
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.CRT_TAB_INST", line 21
ORA-06512: at line 1
I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .
select * from dba_ddl_locks where session_id=111 and owner='RAHUL';
session_id owner name type mode_held mode_requested
111 RAHUL RAHUL_PKG BODY NULL None
111 RAHUL RAHUL_PKG Table/Procedure/Type NULL NoneThe session details of the session id 111 is as below
select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122
sid serial# user# username command status process SQL_EXEC_START
111 3558 222 RAHUL 47 KILLED 4420 13-DEC-12 02.00.00 AM
The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??
In the article regarding gathering CBO Statistics, it states: QUOTE When an Oracle database is created, a job will be scheduled that will generate the database statistics for you. You will still need to collect system statistics however, as these are not collected by the automatic statistics gathering mechanism.
what is the difference between "database statistics" and "system statistics"? In other words, do I need to run this script for each schema owner in my 10g/11g instance?
variable whoami varchar2(20);
begin
select user into :whoami from dual;
end;
exec dbms_stats.gather_schema_stats( -
ownname => :whoami, -
options => 'GATHER AUTO', -
estimate_percent => 15, -
cascade => true).