Signature has been changed Error

ORA-04062 (Signature has been changed)

This error is cause when you have a master Schema & local schema, And the package Spec is not in sync with both the schema.

Solution:

  Compile the package one more time in both the schema.

Explain Plan

To Explain Plan

EXPLAIN PLAN FOR <Any Statement>

Eg:

EXPLAIN PLAN FOR

SELECT * FROM Emp

You can see the plan by selecting the PLAN_TABLE

SELECT * FROM PLAN_TABLE;

Or you can also use

SELECT lpad(' ',level-1)||operation||' '||options||' '||

        object_name "Plan"

   FROM plan_table

CONNECT BY prior id = parent_id

        AND prior statement_id = statement_id

  START WITH id = 0 AND statement_id = '&1'

  ORDER BY id;

You can also assign a name for the statement by

EXPLAIN PLAN SET statement_id = <statement Name> FOR <Statement>;

Example

EXPLAIN PLAN SET statement_id = 'example_plan1' FOR

SELECT full_name FROM per_all_people_f

 WHERE UPPER(full_name) LIKE 'Pe%' ;

What IS the cost of a query and what does it mean?

The formula for the cost (using the CPU Costing Model) of a query is:

Cost = (

#SRds * sreadtime

+ #MRds * mreadtime

+ #CPUCycles / cpuspeed

) / sreadtime

where:

#SRds = number of single block reads

#MRds = number of multi block reads

#CPUCycles = number of CPU Cycles

sreadtim = single block read time

mreadtime = multi block read time

cpuspeed = Standard 'Oracle' CPU cycles per second

The translation of this formula is:

The cost is the time spent on single block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time is takes to do a single block read.

This means that the cost of a query is the PREDICTED EXECUTION TIME, counted in number of single block read times and is effectively the unit of measure of the cost.

How do you find the invalid objects in your schema?

SELECT   object_name,

         object_type,

         created,

         last_ddl_time,

         status

  FROM   user_objects

 WHERE   status != 'VALID'

How do you open a Popup from Oracle Forms

Using   web.javascript_eval_expr Statement

Example

                  web.javascript_eval_expr('window.showModelessDialog("help/SomeFile.html", "''", "dialogLeft:120px; dialogTop:260px; dialogWidth:800px; dialogHeight:455px; scroll-y:on; resizable:yes; status:no; help:no;");');

                  web.javascript_eval_expr('window.showModalDialog("help/SomeFile.html", "''", "dialogLeft:120px; dialogTop:260px; dialogWidth:800px; dialogHeight:455px; scroll-y:on; resizable:yes; status:no; help:no;");');             

                  web.javascript_eval_expr('window.open("help/SomeFile.html", "WinHelp", "location=no,menubar=no,left=220,top=260,width=800,height=455,toolbar=no,resizable=yes,scrollbars=yes");');

RETURN Statement

   
   Can we use RETURN statement in Procedure?
     Yes
     
   Check out the Below Sample
   create or replace procedure p is
    begin  
      dbms_output.put_line('one');
      return;
      dbms_output.put_line('two');
   end;
   
   exec p;
   
   Would Return
   
   one

How do you change the password of an Oracle Schema

ALTER USER <SCHEMA_NAME> IDENTIFIED BY <PASSWORD>


Example
ALTER USER scott IDENTIFIED BY lion;

Generate Insert Script for a table For Oracle

Generating Insert Script may be a simple task if your are using Toad or any Editor. But If you are working with Oracle PL SQL directly, it IS a tough task. You will have to write script. Below function will help you to generate the insert script of the table. First I will list the function then the implementation of the same

create or replace Function GenInsScript(v_table_name varchar2) return varchar2 As

    b_found boolean:=false;

    v_tempa varchar2(8000);

    v_tempb varchar2(8000);

    v_tempc varchar2(255);

begin

    for tab_rec in (select table_name from user_tables where table_name=upper(v_table_name))

    loop

        b_found:=true;

        v_tempa:='select ''insert into '||tab_rec.table_name||' (';

        for col_rec in (select * from user_tab_columns

                            where

                                table_name=tab_rec.table_name

                            order by

                                column_id)

        loop

            if  col_rec.column_id=1 then

                v_tempa:=v_tempa||'''||chr(10)||''';

            else

                v_tempa:=v_tempa||',''||chr(10)||''';

                v_tempb:=v_tempb||',''||chr(10)||''';

            end if;

            v_tempa:=v_tempa||col_rec.column_name;

            if  instr(col_rec.data_type,'CHAR') > 0 then

                v_tempc:='''''''''||'||col_rec.column_name||'||''''''''';

            elsif instr(col_rec.data_type,'DATE') > 0 then

                v_tempc:='''to_date(''''''||to_char('||col_rec.column_name||',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';

            else

                v_tempc:=col_rec.column_name;

            end if;

            v_tempb:=v_tempb||'''||decode('||col_rec.column_name||',Null,''Null'','||v_tempc||')||''';

        end loop;

        v_tempa:=v_tempa||') values ('||v_tempb||');'' from '||tab_rec.table_name||';';

    end loop;

    if  Not b_found then

        v_tempa:='-- Table '||v_table_name||' not found';

    else

        v_tempa:=v_tempa||chr(10)||'select ''-- commit;'' from dual;';

    end if;

    return v_tempa;

end;

Now to the Implementation

--------------SAMPLE TABLE -------------------------

CREATE TABLE TEST_TABLE

(

  COL1  NUMBER,

  COL2  VARCHAR2(100 BYTE)

)

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;

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

Steps for generating the Script

select GenInsScript('test_table') from dual

This Query would give the below output

select 'insert into TEST_TABLE ('||chr(10)||'COL1,'||chr(10)||'COL2) values ('||decode(COL1,Null,'Null',COL1)||','||chr(10)||''||decode(COL2,Null,'Null',''''||COL2||'''')||');' from TEST_TABLE;

select '-- commit;' from dual;

Executing this Query would result in

insert into TEST_TABLE (

COL1,

COL2) values (1,

'test');

insert into TEST_TABLE (

COL1,

COL2) values (2,

'test2');

insert into TEST_TABLE (

COL1,

COL2) values (3,

'test3');

commit;

Yet Another way of Extracting XML Data

We shall insert the xml into a table and retrieve that


CREATE TABLE xml_test
(
   id          NUMBER DEFAULT 1 ,
   xml_text    VARCHAR2 (4000 BYTE),
   ts_update   DATE DEFAULT SYSDATE
)

SET DEFINE OFF;Insert into XML_TEST
   (ID, XML_TEXT, TS_UPDATE)
 Values
   (2, '<page>
  <id>72054439</id>
  <redactions>
    <redaction>
      <id>0</id>
      <privileges>
        <privilege>
              <id>164</id>
              <Code>ACP</Code>
         </privilege>
               <privilege>       
                  <id>165</id>
                  <Code>AWPD</Code>
               </privilege>
      </privileges>
     </redaction>
     <redaction>
       <id>0</id>
        <privileges>
          <privilege>
                   <id>164</id>
                  <Code>ACP</Code>
            </privilege>
            <privilege>
                  <id>165</id>
                  <Code>AWPD</Code>
            </privilege>
      </privileges>
    </redaction>
  </redactions>
</page>', SYSDATE);
COMMIT;


This Query

SELECT   x1.id page_id, x2.id privilege_id, x2.code privilege_code
  FROM   xml_test t,
         XMLTABLE ('/page' 
                   PASSING xmltype (t.xml_text) 
                   COLUMNS 
                        id NUMBER PATH './id', 
                        xml_privilege XMLTYPE PATH '/page/redactions/redaction/privileges'
                   ) x1,
         XMLTABLE ('/privileges/privilege' 
                    PASSING x1.xml_privilege 
                    COLUMNS
                          id NUMBER PATH './id', 
                          code VARCHAR (5) PATH './Code'
                  ) x2
 WHERE   t.id = 2

would return  PAGE_ID            PRIVILEGE_ID      PRIVILEGE_CODE

72054439      164                         ACP
72054439      165                         AWPD
72054439      164                         ACP
72054439      165                         AWPD

Extract XML Data - ORACLE

How do you extract a tag from a XML


Using XML DATA

Below is a Sample

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below Output
/tag/char Some text
/tag/num 123

Sample for Having more than one node in XML 

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num><char>Next text</char><num>987</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below output
/tag/char Some textNext text
/tag/num 123987

Change the Prompt

How do you change the prompt in SQL PLUS

set sqlprompt "&&prompt_dbname> "

or

set sqlprompt &prompt_dbname>

This will prompt you to enter the DB Name

Enter value for prompt_dbname: MyOracleDB

MyOracleDB>

With the above step you will have to repeat this every time you log in; But how do you avoid that?

When you are using multiple database instances you may get lost where and which database you are in: It is always a good idea to have the SQL Prompt to say what you are connected to. To do this:

Step 1: Create a new file called “login.sql” in the same folder as sqlplusw.exe resides

Step 2: Copy the below content into the “login.sql” file

column user_sid new_value sql_prompt
select lower(user) || '@' || '&_CONNECT_IDENTIFIER' user_sid from dual;
set sqlprompt '&sql_prompt> '

clear screen;
set serveroutput on;

There you go, Now everytime you log in you would see the user and Prompt

scott@MyOracleDB>

 

Oracle Forms - Visual Attribute Vs Property Class

What is a Visual Attribute


Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface. Visual attributes can include the following properties:
Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight
Color and pattern properties: Foreground Color, Background Color, Fill Pattern

What is a Property Class

This object is a named object that contains a list of properties and their associated settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the settings of any property in the class that is appropriate for that object.


Diff. between Visual Attribute and Property Class?

Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.

You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically. When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.