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.
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
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
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.
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,
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
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?
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)
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
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;
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
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.
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.
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.
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 .
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;
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; /
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.
