Exception raised while wrapping up a procedure Code Using Oracle9i Wrap Utility

CREATE TABLE TEST_TAB(COL1  NUMBER,COL2  NUMBER,COL3  VARCHAR2(100))

/

 Save below procedure as a .prc file

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = 'ALLEN'), 'RAJU'

        FROM DUAL;

   COMMIT;

END;

/

Then try to wrap the procedure code using wrap command

wrap iname=TEST_PROC.prc oname=TEST_PROC_w.prc;

Processing TEST_PROC.prc to TEST_PROC_w.prc

PSU(103,1,5,18):Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null others <an identifier>

   <a double-quoted delimited-identifier> <a bind variable> avg

   count current exists max min prior sql stddev sum variance

   execute forall merge time timestamp interval date

   <a string literal with character set specification>

   <a number> <a single-quoted SQL string> pipe

PSU(103,1,7,40):Encountered the symbol ")" when expecting one of the following:

   . ( * @ % & - + ; / at for mod rem <an exponent (**)> and or

   group having intersect minus order start union where connect

   ||

PL/SQL Wrapper error: Compilation error(s) for:

CREATE OR REPLACE PROCEDURE test_proc

Outputting source and continuing.

Cause:-

--------

Above exception is why because in one select statement another select is used and after that trying to insert some constant value.

Solution:-

---------

Way1:- Convert normal sql statement as a Dynamic Sql statement.

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

  execute immediate 'INSERT INTO test_tab

      SELECT 1, (SELECT empno

                   FROM EMP

                  WHERE ename = ''ALLEN''), ''RAJU''

        FROM DUAL';

   COMMIT;

END;

/

Way2:- Put it in a single select statement

CREATE OR REPLACE PROCEDURE test_proc

IS

BEGIN

   INSERT INTO test_tab

       SELECT 1,empno,'RAJU'

                   FROM EMP

                  WHERE ename = 'ALLEN';   

   COMMIT;

END;

/

Why in dba_constarints index_name is null for some primary constraints

Oracle 9iR2

-------------------

When you create a primary constraint automatically 1 row will be inserted into DBA_CONSTRAINTS and another will be into DBA_INDEXES. And in DBA_CONSTRAINTS index_owner and index_name columns will be populated with index which is pointed to primary key.

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will be deleted from dba_indexes.

When u enable back the constraint, then again index_owner and index_name will become populated in dba_constraints and row will be inserted into dba_indexes.

NOTE:-when u disable and enable it back your user defined index name will be lost and it will be replaced with primary key name.(u can check this in below example).

SQL> CREATE TABLE TEST_INDEX(NUM NUMBER,NUM1  NUMBER);

Table created.

SQL> CREATE UNIQUE INDEX TEST_INDEX_IDX ON TEST_INDEX(NUM);

Index created.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_INDEX_IDX                 TEST_INDEX

SQL> ALTER TABLE TEST_INDEX ADD CONSTRAINT TEST_INDEX_PK PRIMARY KEY (NUM);

Table altered.

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_IDX

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

no rows selected

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK

SQL> ALTER TABLE TEST_INDEX ENABLE CONSTRAINT TEST_INDEX_PK;

Table altered.

SQL> SELECT index_name,table_name FROM DBA_INDEXES WHERE TABLE_NAME='TEST_INDEX';

INDEX_NAME                     TABLE_NAME

------------------------------ ------------------------------

TEST_INDEX_PK                  TEST_INDEX

SQL> select table_name,constraint_name,index_name from dba_constraints where TABLE_NAME='TEST_INDEX';

TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME

------------------------------ ------------------------------ ------------------------------

TEST_INDEX                     TEST_INDEX_PK                  TEST_INDEX_PK

SQL>

Oracle 11g

-------------------

When u try to disable that constraint, then index_owner and index_name will become null in dba_constraints and row will not be deleted from dba_indexes.

To resolve this: neither u have to drop the index manually nor disable constraint with cascade option.

SQL> ALTER TABLE TEST_INDEX DISABLE CONSTRAINT TEST_INDEX_PK cascade;

Find no of commas in a given string?

Using below 2 methods we can find out how many no of commas exists in a given string.

  

SQL> SELECT   TRIM (LENGTH ('PE,ME,TYPE,DATE,END'))

            - TRIM (LENGTH (TRANSLATE ('PE,ME,TYPE,DATE,END', 'A,', 'A'))) str_length

         FROM  dual;

     str_length

     ------------

     4

 SQL> SELECT   TRIM (LENGTH ('PE,ME,TYPE,DATE,END'))

             - TRIM (LENGTH (replace ('PE,ME,TYPE,DATE,END', ','))) str_length

        FROM dual;

     str_length

     ------------

     4

Oracle Interview questions for experienced developers

1. When you drop a table, what will happen to synonym on that table?

>Answer is Synonym will not be dropped, that structure will be available in

dba_synonyms.

SQL> create table scott.test_tab(num number);

Table created.

SQL> create synonym system.test_synonym for scott.test_tab;

Synonym created.

SQL> select OWNER,TABLE_NAME from dba_tables where table_name='TEST_TAB';

OWNER                          TABLE_NAME

------------------------------ ------------------------------

SCOTT                          TEST_TAB

SQL> SET PAGESIZE 100

SQL> SET LINESIZE 2000

SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='TEST_SYNONYM';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK

------------------------------ ------------------------------ ------------------------------ -------

SYSTEM                         TEST_SYNONYM                   SCOTT                          TEST_TAB

SQL> DROP TABLE TEST_TAB;

DROP TABLE TEST_TAB

           *

ERROR at line 1:

ORA-00942: table or view does not exist

  

SQL> DROP TABLE SCOTT.TEST_TAB;

Table dropped.

SQL> select OWNER,TABLE_NAME from dba_tables where table_name='TEST_TAB';

no rows selected

SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='TEST_SYNONYM';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK

------------------------------ ------------------------------ ------------------------------ -------

SYSTEM                         TEST_SYNONYM                   SCOTT                          TEST_TAB

>When you try to select that synonym then u will get fallowing error:

SQL> select * from system.test_synonym;

select * from system.test_synonym

                     *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

>IF you want to reuse this synonym name create a table using same again.( Here u can use any structure).

SQL> create table scott.test_tab(num number, num2  varchar2(10));

Table created.

SQL> select * from system.test_synonym;

no rows selected

SQL>

2.How do you Alter Not null column to nullable?

Using below syntax You can alter not-null column to nullable.

SQL> alter table <table_name> modify <column_name> null;

Example: alter table EMP modify ename null;

3.What will happen if sub-query table doesn’t have column which main table has and if u refer that column in sub query.

When you are writing sub queries should be conscious about column list.

For example:

create table tab1 (a number,b number)

insert into tab1 values (10,10)

insert into tab1 values (20,20)

create table tab2 (a number,c number,d number)

insert into tab2 values (10,10,50)

insert into tab2 values (30,20,60)

case1:  in this case column b not exists in tab2, so it refers main query table columns.

select * from tab1 where (b) in (select b from tab2)

A        B

-------------

10      10

20      20

Case2:  in this case column a exists in tab2, so it refers from sub query table.

select * from tab1 where (a) in (select a from tab2)

A        B

-------------

10      10

4.When u drop a table what will happen to grants?

>Grants will be dropped automatically along with a table.

5.What happens to view when you drop a table on which u have a view?

>View will not be dropped, view related representation will be available under dba_views. When that table structure is available in database, then that view will be available automatically.

SQL> create view v1 as select * from scott.test_tab;

View created.

SQL> select * from v1;

no rows selected

SQL> drop table scott.test_tab;

Table dropped.

SQL> select * from v1;

select * from v1

              *

ERROR at line 1:

ORA-04063: view "V1" has errors

  

SQL> SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME='V1';

  

VIEW_NAME                      TEXT

------------------------------ ---------------------------------------------------------------------

V1                             select "NUM","NUM2" from scott.test_tab

SQL> create table scott.test_tab(num number, num2  varchar2(10));

Table created.

SQL> select * from v1;

no rows selected

6. how to compile ur entire schema?

  

exec dbms_utility.compile_schema('schemaname');

exec dbms_utility.compile_schema('SCOTT');

7. How To Check Errors In Your Schema

Means If Your Package Is Invalid, How Do you See the Errors)

SELECT * FROM DBA_ERRORS WHERE OWNER='your OWNER name' AND TYPE='your PACKAGE NAME';

SELECT * FROM DBA_ERRORS WHERE OWNER='SCOTT' AND TYPE='P1';

INITIALLY DEFERRED DEFERRABLE -(Deferring Constraint Checking)

Sometimes it is necessary to defer the checking of certain constraints, most commonly in the "chicken-and-egg" problem. Suppose we want to say:

CREATE TABLE chicken (cID INT PRIMARY KEY,

                      eID INT REFERENCES egg(eID));

CREATE TABLE egg(eID INT PRIMARY KEY,

                 cID INT REFERENCES chicken(cID));

But if we simply type the above statements into Oracle, we'll get an error. The reason is that the CREATE TABLE statement for chicken refers to table egg, which hasn't been created yet! Creating egg won't help either, because egg refers to chicken.

To work around this problem, we need SQL schema modification commands. First, create chicken and egg without foreign key declarations:

CREATE TABLE chicken(cID INT PRIMARY KEY,

                     eID INT);

CREATE TABLE egg(eID INT PRIMARY KEY,

                 cID INT);

Then, we add foreign key constraints:

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg

    FOREIGN KEY (eID) REFERENCES egg(eID)

    INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE egg ADD CONSTRAINT eggREFchicken

    FOREIGN KEY (cID) REFERENCES chicken(cID)

    INITIALLY DEFERRED DEFERRABLE;

INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking.

For example, to insert (1, 2) into chicken and (2, 1) into egg, we use:

INSERT INTO chicken VALUES(1, 2);

INSERT INTO egg VALUES(2, 1);

COMMIT;

Because we've declared the foreign key constraints as "deferred", they are only checked at the commit point. (Without deferred constraint checking, we cannot insert anything into chicken and egg, because the first INSERT would always be a constraint violation.)

Finally, to get rid of the tables, we have to drop the constraints first, because Oracle won't allow us to drop a table that's referenced by another table.

ALTER TABLE egg DROP CONSTRAINT eggREFchicken;

ALTER TABLE chicken DROP CONSTRAINT chickenREFegg;

DROP TABLE egg;

DROP TABLE chicken;

ORA-29532: Java call terminated by uncaught Java exception: java.lang.Error: Updater commit failure

BEGIN

DBMS_JAVA.grant_permission('SCOTT', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');

END;

/

BEGIN

Dbms_Java.Grant_Permission('SCOTT', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');

END;

/

Error:

BEGIN

*

ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.Error:

Updater commit failure

ORA-06512: at "SYS.DBMS_JAVA", line 793

ORA-06512: at line 2

Sometimes an application running in the Oracle JVM will fail with a java permissions error having the following format:
java.sql.SQLException: ORA-29532:Java call terminated by uncaught Java exception:

1^st  methodology to solve this issue:
Brought down JVM application and then retry your application.

2^nd methodology to solve this issue:

1) Format a call "dbms_java.grant_permission" procedure as described below.
2) Logon as SYS or SYSTEM
3) Issue the TWO commands shown below
4) Logoff as SYS or SYSTEM
5) Retry your application

---------------
For Example
---------------
1) Logon as SYS or SYSTEM
2) Issue the following commands :

BEGIN

DBMS_JAVA.grant_permission('SCOTT', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');

END;

/

commit;
/
Note: Commit is mandatory !!

3) Logoff as SYS or SYSTEM

4) Retry your application