SQL & PL/SQL :: Check If Object Exist Or Not While Creating Procedure?

Mar 5, 2012

I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().

for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP

[code].....

My question is while creating procedure i want to check if the object exist in database or not.

If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST

I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.

The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.

View 14 Replies


ADVERTISEMENT

Replication :: ORA-23308 / Object BABU.EMP Does Not Exist Or Is Invalid

Dec 26, 2007

I am trying to create Master_repobject , getting following error

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'babu_rep',
sname => 'BABU',
oname => 'EMP',
type => 'TABLE',
use_existing_object => TRUE ,

[code]....

ERROR at line 1:
ORA-23308: object BABU.EMP does not exist or is invalid
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2627
ORA-06512: at "SYS.DBMS_REPCAT", line 562
ORA-06512: at line 3

Note : BABU is a empty schema with no tables

View 2 Replies View Related

SQL & PL/SQL :: How To Check If The Cluster Exist In Database

Apr 27, 2010

We can use the clause "CREATE CLUSTER mycluster.." to create a cluster.

How to check if the cluster exist in my database, so I don't have to create the cluster again.

View 1 Replies View Related

SQL & PL/SQL :: How To Check Record Exist In Table

Feb 13, 2013

Usually we check 1 row of data in table and compare if they exist, i want to compare different set of row all together to comapre and take the decisions

eg.

emp table_source
d_key emp_no invol
1 103499 1
1 99262 2
2 103499 1
3 103499 1
3 99262 2

target table

emp_no invol
103499 1
99262 2
103499 1

in the above example i have to check for d_key for set of row exist in target or not, so by above example d_key 1 and 3 has save value in 2 different rows so i want to check if the combination of rows

103499 1
99262 2

as a set appear in source then do nothing else insert so first time d_key 1 both records will insert but for d_key has same set in 2 rows will not be inseted. the number of row can be more than 2 also . so for the given key if we have 3 rows in source then i have to compare all the 3 same set of row in target exist of . in the target i don't have any extra column.

View 3 Replies View Related

Check All Object Privileges

Sep 6, 2011

How can i list all the object privileges from n user?

for example:

I granted to USER1:

GRANT ALL on my_table1 to USER1;
GRANT ALL on my_table2 to USER1;
GRANT ALL on my_tableN to USER1;

How can i list all privileges from this user over all tables?

I don't know if this is possible, but i wanna to list:

USER1 ----- UPDATE, INSERT, DELETE ON my_table1
USER1 ----- UPDATE, INSERT, DELETE ON my_table2
USER1 ----- UPDATE, INSERT, DELETE ON my_tableN

Is this possible?

View 1 Replies View Related

SQL & PL/SQL :: Check If Table Exist In Scheme - If Not Create As Original DDL

Aug 13, 2013

I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:

1- check if table A exist in the scheme
2- if not create the table as the original ddl.

for 1 i've used smth like
-------------------------
SELECT count(*)
INTO a
FROM user_objects
WHERE object_name='A';

[code]....

so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,

View 17 Replies View Related

Instant Client :: ORA-12545 / Connect Failed Because Target Host Or Object Does Not Exist

Jan 31, 2013

I have installed the Oracle Instant Client* on UNIX(Mac OS X Lion 10.7.5), and I want to use it on localhost because i just want to study and test it.but here, the some error appeared to me. so I was so screwed up.

here is the error messages and the settings

Command:

~ user$ sqlplus scott tiger

ERROR:

ORA-12545: Connect failed because target host or object does not exist

env settings:

$ORACLE_HOME=/usr/local/oracle
$ORACLE_SID="Oracle 10g+ASM" // I don't know what ORACLE_SID means
$ORACLE_LIBRARY=/usr/local/oracle/instantclient_10_2
$DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:$ORACLE_HOME/instantclient_10_2
$SQLPATH=$ORACLE_HOME/instantclient_10_2
$PATH=$PATH:$SQLPATH

View 1 Replies View Related

SQL & PL/SQL :: Creating Trigger - Table Or View Does Not Exist

Apr 6, 2012

When i try to create a trigger , i ended up with error.

SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,

[code]....

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist

The command used to resolve the error is

GRANT SELECT ON v_$session TO jack;

Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?

View 3 Replies View Related

SQL & PL/SQL :: How To Check Object (table / Index) Growth Day Wise

Apr 20, 2011

DB:Oracle 9i
os :solaris 8

how to check the object(table,index) growth day wise?

View 2 Replies View Related

SQL & PL/SQL :: Creating Trigger - Check If Data Being Inserted Already In Table And If Not Insert It

Aug 24, 2011

I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)
values (:new.user_id, :new.location);
end if;
end;

what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:

create or replace trigger t_triggername
before insert on tbl_tablename
for each row
begin
if(:new.user_id <> :old.user_id) then
insert into tbl_tablename(user_id, location)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: ORA-04067 Not Executed / Procedure Does Not Exist?

Jul 25, 2011

I am facing error in application.

ORA-04067: not executed ,procedure "BALA.SEQ_RR_ID" does not exist.

This procedure exist in database.There is one synonym with the name also exist in database.

View 7 Replies View Related

Oracle Store Procedure - Table Or View Does Not Exist

Nov 21, 2011

I have created this store procedure:

create or replace PROCEDURE INSERT_TESTTABLE
(
PrimaryKey IN NUMBER
,One IN VARCHAR2
,Two IN VARCHAR2
,Three IN VARCHAR2
,Four IN VARCHAR2
[code].......

And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist

View 1 Replies View Related

Forms :: How To Call Procedure From One To Another Object Library

Jan 29, 2013

I have one procedure "PROC_A" in LibraryA, Is it possible to call this procedure PROC_A from LibraryB.

View 1 Replies View Related

Trace Particular Object (procedure / Package) On Database?

Dec 13, 2012

How do I trace an particular object (procedure/package) on the database. my database version is Oracle 11g(11.2.0.2)

View 2 Replies View Related

SQL & PL/SQL :: Pass Object (or) Table As Argument To Remote Procedure?

Jul 18, 2012

Does it possible to pass object (or) table as an argument to a remote procedure?

View 2 Replies View Related

SQL & PL/SQL :: How To Pass Type Object As A Parameter To Stored Procedure

May 9, 2012

I am fairly new to oracle, here's what I am doing.

Create or replace type csc_info as object( source_code varchar2(10),
Docno varchar2(10),
Key_value_1 varchar2(10),
Key_value_2 varchar2(10));

[Code]....

I need to test the procedure how do I pass the inputs for the type object csc_info?

View 2 Replies View Related

Client Tools :: Execute A Procedure With Parameter Of Object Type?

Mar 29, 2010

I am trying to execute the procedure from toad, where the type is like an object.How can I do that?

CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;
CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);

In the following procedure I am calling list_t, but how would i pass the values to it?

PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)
DECLARE
p_List TABLE;

[code]...

View 1 Replies View Related

PL/SQL :: Check Date Of Last Modified Procedure?

Oct 17, 2012

how to check last modified function or procedure.

for example: INVO_75.PCK.PrintInvoices_fct

View 4 Replies View Related

How To Check And Insert New Data Using Stock Procedure

Feb 11, 2007

i've a problem in using store procedure. My code is to get postcode id when i pass a postcode. First it will check the postcode that i pass if already exist it will get postcode id but if not it will insert new postcode and get a new postcode id created then pass into ASP system. When i try run this stock procedure i got error as below :-

SQL> exec INSERT_PCODE_GMDS
BEGIN INSERT_PCODE_GMDS; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERT_PCODE_GMDS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

(
Postcode1 IN varchar2,
citiID IN Number,
county_ID IN number,
city_name IN varchar2,
sub_cityID IN number,
pcode OUT number
)
[code].......

in ASP to pass and get back the values i used code as below. but i think the problems occurs in my stock procedure

set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = OBJdbConnection
cmd.CommandText="INSERT_PCODE_GMDS"
cmd.CommandType= 4
cmd.Parameters.append cmd.CreateParameter("@poskod",adVarChar,adParamInput,5,poskod)
[code].......

View 1 Replies View Related

SQL & PL/SQL :: How To Check Procedure Compiled Warnings And Errors

Sep 26, 2013

I have compiled some procedures, at the time of compilation i saw some warnings & errors but i did not notice, Now can we see that errors or warnings in db console or any other way.

View 7 Replies View Related

SQL & PL/SQL :: Oracle - Create Procedure To Check Username And Password?

Aug 23, 2011

how can i create procedure to check username and password.

View 10 Replies View Related

Creating Trigger Through Procedure

Jul 24, 2009

I am trying to create trigger through Procedure due to following reasons-

1. The name of the column on which trigger will execute, is to be fetched dynamically.
2. The name of the column to be updated, is to be fetched dynamically.

Here is the sample code-

CREATE OR REPLACE PROCEDURE test2
IS
var VARCHAR2 (4000);
uname VARCHAR2 (30);
attribtask VARCHAR2 (100);
mapcol VARCHAR2 (100);
BEGIN
[code].........

On execution, the procedure throws the error of 'Insufficient privileges'. The 'var' seems to be the main culprit because the issue disappears if var is set to 'select * from dual'. Also, if i take the output (value of var) given by DBMS_output.put_line function and execute it explicitly, trigger gets created.

In addition- The procedure is (and being executed) within the same user/schema under which trigger is going to be created.

View 2 Replies View Related

Creating A Procedure In SQL Server

Mar 12, 2011

Recently decided to try using Oracle. Previously when creating a procedure in SQL server I would have done the following.

Create PROCEDURE GetInfo AS
SELECT InfoID, Name, Description
FROM GetInfo;

How can I write this same procedure in Oracle?

View 10 Replies View Related

SQL & PL/SQL :: Creating A View From Procedure

Mar 4, 2011

I have create a procedure as

create or replace procedure aa is
abc varchar2(1000);
begin
abc:='create or replace view abc_view as select * from abc';
execute immediate abc;
end;

When i try to execute the same as

BEGIN
aa;
END;

i got error as

ORA-01031: insufficient privileges
ORA-06512: at "EARS.AA", line 5
ORA-06512: at line 3

why i get this error. there is no problem in grants. the same create or replace view query works if i directly executes in sql.

View 8 Replies View Related

PL/SQL :: Getting Many Errors When Creating The Procedure

Jun 28, 2012

I am getting many errors when creating the below procedure.

alter procedure sp_compileinvalid as
Begin
Set lines 999;
Spool run_invalid.sql
select
[code].......

View 14 Replies View Related

SQL & PL/SQL :: Creating Procedure Which Will Process All The Cursors?

Apr 19, 2012

My requirement is like as follows,

declare
v1str varchar2(100):='select empno,ename from emp';
v2str varchar2(100):='select empno,ename,sal from emp';
type t_array is varray(2) of varchar2(100);

[Code]....

So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.

View 21 Replies View Related

PL/SQL :: Creating A Procedure Using Explicit Cursor?

Nov 19, 2013

I am writing this procedure with a explicit cursors defined in it.  However when i compile the procedure i get this error: Error(39,1): PL/SQL: SQL Statement ignored Error(39,1):PLS-00394: wrong number of values in the INTO list of a FETCH statement .

create or replace PROCEDURE PRO_ICMISd_customer_no BB_PM.customer_no%type;d_pr_code_bbl BB_PM.pr_code_bbl%type;d_pr_code_pmm BB_PM.pr_code_pmm%type;d_subdept_desc PM.subdept_desc%type;d_class_desc PM.class_desc%type;d_cat_desc PM.cat_desc%type;d_subcat_desc PM.subcat_desc%type;d_brand_name PM.brand_name%type;d_product_desc PM.product_desc%type;d_unit_price_bbl PM.unit_price%type; e_customer_no BB_PM.customer_no%type;e_pr_code_bbl BB_PM.pr_code_bbl%type;e_pr_code_pmm BB_PM.pr_code_pmm%type;e_subdept_desc PM.subdept_desc%type;e_class_desc PM.class_desc%type;e_cat_desc PM.cat_desc%type;e_subcat_desc PM.subcat_desc%type;e_brand_name PM.brand_name%type;e_product_desc

[code]....

View 17 Replies View Related

Client Tools :: Creating Procedure Using SQL Developer?

Jan 23, 2012

I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.

I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:

Error(8,1):PLS-00103:Encountered the symbol "/"

The code is as follows:
--------------------------------------
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS
PROCEDURE sampleproc_4(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC_4;

[code].....

View 9 Replies View Related

SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters

Jun 6, 2012

I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.

create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/

View 6 Replies View Related

Application Express :: Creating Procedure Using Database Package

Oct 23, 2012

I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved