Oracle

Oracle Community

NOTE: This forum is for non-product questions and discussions on the Oracle database platform.
 
Please post Toad-family product questions in their respective product forums.  Thanks!

Community Discussion

KrishBalu
Follow / 22 Oct 2014 at 6:51pm / Oracle

Pull max value for each period in the other table

Expand content
Let me explain the question in detail. Table1: Period1 - SEQ - Column1 - Column2 1- 1211212 - value1 - valu2 1- 122322 - value3 - value4 2 - 3434343- value5 - value6 2 - 344323 - value7 - value8...

Let me explain the question in detail.

Table1:

Period1 - SEQ - Column1 - Column2

1- 1211212 - value1 - valu2

1- 122322 - value3 - value4

2 - 3434343- value5 - value6

2 - 344323 - value7 - value8

I want to pull the max(seq) for all the distinct periods. I need these details to compare to other table too.  Please help me out.

Unanswered 22 0 / Create an account to join the discussion.
Mohammedtodd
Follow / 21 Oct 2014 at 2:34am / Oracle
Latest post by on 22 Oct 2014 at 6:16pm

Update statement by avoiding the duplicated regardless of case sensitivity

Expand content
Hi Masters, I need a help in updating a table where by if we find similar pattern of data that should not be updated but the remaining should be updated. Say For Example . In the below data all data...

Hi Masters,

I need a help in updating a table where by if we find similar pattern of data that should not be updated but the remaining should be updated. Say For Example . In the below data all data that are like ST00 should be treated as similar which I mean to say case sensitivity should be avoided.  But if we come to k100 it should be treated as single item and should be converted to upper case. There can be any combination case of data in the stock_code. Lik rC00, Cr00, cr00 like that. Any help is much appreciated. Thanks

CREATE TABLE DEMOSYS(STOCK_CODE CHAR(4));

  

INSERT INTO DEMOSYS VALUES('ST00'); 

INSERT INTO DEMOSYS VALUES('st00'); 

INSERT INTO DEMOSYS VALUES('sT00');

 INSERT INTO DEMOSYS VALUES('St00');

 INSERT INTO DEMOSYS VALUES('k100');

Unanswered 77 2 / Create an account to join the discussion.
Mohammedtodd
Follow / 19 Sep 2014 at 8:01am / Oracle
Latest post by on 20 Oct 2014 at 12:47am

Can I merge two column value from one table into one inside a for...

Expand content
Hi Greetings to All, I'm new to Pl/SQL. It's a description about a part so it really need to be fetched such a long part description. . Actually how we store this description into the above...

Hi Greetings to All,


I'm new to Pl/SQL.

It's a description about a part so it really need to be fetched such a long part description. . Actually how we store this description into the above table is we split this into two separate  row and store in two different rows. My task is this need to fetched into a single column while processing in the above function.

 

For example if the part has a description of length more 70 characters then we will store the first 65 characters into the first row with the sequence as "1" and the rest of characters will be stored into the second with the sequence of 2.What I try to accomplish is to combine both description row into one and send to the row variable.

 

The way I tried to get this done as below using a inline view facility.

Try 1:

 

(select ext1.description_text from extended_part_descriptions ext1

         where ext1.part_number = '1') long_desc

 

then I tried to fetch the description once the lopping is performed as below.

LOOP

        IF part_history.long_part_number IS NULL THEN

        RAISE partmaster_block;

        END IF;

        pmrow.part_number := part_history.long_part_number;

        --pmrow.part_description := get_long_part_desc(part_history.long_part_number);

        pmrow.part_description := part_history.long_desc;

        --'PARTEST1';

        --part_history.long_desc;

        pmrow.part_type := part_history.part_type;

 

But got the error

  • Error(71,9): PL/SQL: Statement ignored
  • Error(71,48): PLS-00302: component 'LONG_DESC' must be declared .

 

To cut the long store into short. Two rows of a table of similar type to be joined to gather into one where I'm struck into.

 

To be specific If I have the table data

 

 

Table _ Extended_Descriptions.

 

PART                                DESCRIPTION_SEQUENCE               DESCRIPTIONS

K200                                 1                                                           CHECKOUTTHEFLAGBEFOREYOUPROCEED

K200                                 2                                                           WELLTESGINGALWAYSMAKESPERFECTION

 

 

We need to join as

 

K200      CHECKOUTTHEFLAGBEFOREYOUPROCEEDWELLTESGINGALWAYSMAKESPERFECTION

 

MY FUNCTION IS BELOW. sorry for the commented line and they are the tries I gave. 

FUNCTION get_part_amendment_history(
in_start_date DATE,
in_end_date DATE
) RETURN ie92_tab pipelined
AS
pmrow ie92_row := ie92_row();
start_date DATE;
end_date date;
desc1 varchar2(65);
desc2 varchar2(65);
long_desc varchar2(160);

partmaster_block exception;
BEGIN

start_date := nvl (in_start_date, trunc (CURRENT_DATE) - 1);
end_date := nvl (in_end_date, trunc (CURRENT_DATE));

for part_history in (

select distinct pm.long_part_number,
--ext1.DESCRIPTION_TEXT as long_desc,
--ex.description_text long_desc,
-- ( SELECT description_text FROM
-- extended_part_descriptions epd WHERE
-- epd.description_sequence =1
-- AND epd.description_sequence =2) d,

CASE pm.part_type
WHEN '0' THEN 'C'
ELSE 'R'
END part_type,
pm.stock_group,
pm.unit_of_issue,
CASE pm.withdrawn_part
WHEN 'Y' THEN 'N'
ELSE 'Y'
end withdrawn_part
from part_master pm

left join part_number_amendment_history pnah
on pm.part_number = pnah.part_number
- join extended_part_descriptions ext1
-- on pm.part_number = ext1.part_number and
-- ext1.description_sequence = 1 and
-- ext1.description_sequence = 2
-- (select ext1.description_text from extended_part_descriptions ext1
-- where ext1.part_number = '1') long_desc
-- left join extended_part_descriptions ex
-- on pm.part_number = ex.part_number
--
-- where ex.description_sequence =1 and
-- --=1 and ex.description_sequence =2
-- LEFT JOIN extended_part_descriptions ex
-- on pm.part_number = ex.part_number
--where
-- ( ext1.description_sequence =1 and
--
-- ext1.description_sequence =2)
where
pnah.history_date >= trunc (start_date)
AND pnah.history_date < trunc (end_date)
AND pnah.vduwka_field_name IN
('STMI02',
'STMI04',
'STMI08',
'STMI17',
'STMI05'))
LOOP
IF part_history.long_part_number IS NULL THEN
RAISE partmaster_block;
end if;
-- select description_text into desc1 from extended_part_descriptions ext where ext.part_number = part_history.long_part_number and
-- ext.description_sequence =1 ;
-- select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = part_history.long_part_number and
-- ext1.description_sequence =2 ;
--
pmrow.part_number := part_history.long_part_number;
--pmrow.part_description := get_long_part_desc(part_history.long_part_number);
--pmrow.part_description := desc1 || desc2;
--'PARTEST1';
--part_history.long_desc;
pmrow.part_type := part_history.part_type;
pmrow.stock_group := part_history.stock_group;
pmrow.uom := part_history.unit_of_issue;
pmrow.block_status := part_history.withdrawn_part;
-- select description_text into desc1 from extended_part_descriptions ext where ext.part_number = pmrow.part_number and
-- ext.description_sequence =1 ;
-- select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = pmrow.part_number and
-- ext1.description_sequence =2 ;

PIPE ROW(pmrow);
END loop;
RETURN;
EXCEPTION
WHEN partmaster_block THEN
pmrow.part_number := NULL;
pmrow.part_description := NULL;
pmrow.part_type := NULL;
pmrow.stock_group := NULL;
pmrow.uom := NULL;
pmrow.block_status := NULL;

END;

Thanks

Unanswered 215 2 / Create an account to join the discussion.
vallesmx
Follow / 16 Oct 2014 at 7:38pm / Oracle
Latest post by on 18 Oct 2014 at 3:00pm

how can identify deadlocks

Expand content
hi folks, im new in toad, someone knows how can identify deadlocks in Toad for mac? apreciatte ur help

hi folks, im new in toad, someone knows how can identify deadlocks in Toad for mac? apreciatte ur help

Unanswered 109 1 / Create an account to join the discussion.
Abu Fazal Abbas
Follow / 14 Oct 2014 at 10:15am / Oracle

ASM Filter driver doesn't support Unbreakable Linux Kernel

Expand content
I was in the process of configuring ASM Filter Driver (ASMFD) and it was failing with following errors. -bash-3.2# $ORACLE_HOME/bin/asmcmd afd_configure Connected to an idle instance. AFD-620: AFD is...

I was in the process of configuring ASM Filter Driver (ASMFD) and it was failing with following errors.

-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_configure
Connected to an idle instance.
AFD-620: AFD is not supported on this operating system version: '2.6.32-100.26.2.el5'
ASMCMD-9524: AFD configuration failed 'ERROR: afdroot install failed'

When I googled, could not find a single match for this error.


I recalled a similar issue with ACFS as per Bug 10185375. As per the Bug description, ACFS is not supported on Unbreakable Enterprise Kernel and they did not mention a workaround too.

To verify, I have checked my OS release

-bash-3.2# uname -r
2.6.32-100.26.2.el5

Yes, I am using UEK.

Now, me being an impatient person; was looking for an alternative option as I was too determined to explore ASMFD.


Here, what I did.

Rebooted my Linux, went to Grub Menu, booted the system with Red Hat Compatible Kernel.


-bash-3.2# uname -r
2.6.18-238.el5


Guess what!! I was able to successfully install ASMFD with a Red Hat Compatible Kernal.

-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_configure
Connected to an idle instance.
AFD-627: AFD distribution files found.
AFD-636: Installing requested AFD software.
AFD-637: Loading installed AFD drivers.
AFD-9321: Creating udev for AFD.
AFD-9323: Creating module dependencies - this may take some time.
AFD-9154: Loading 'oracleafd.ko' driver.
AFD-649: Verifying AFD devices.
AFD-9156: Detecting control device '/dev/oracleafd/admin'.
AFD-638: AFD installation correctness verified.
Modifying resource dependencies - this may take some time.
ASMCMD-9524: AFD configuration failed 'ERROR: OHASD start failed'

 

I have ignored the error ASMCMD-9524 as it was not impacting the configuration.

-bash-3.2# $ORACLE_HOME/bin/asmcmd afd_state
Connected to an idle instance.
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DEFAULT' on host 'labserver.home.com'

Now, I shall log a SR with Oracle for this ASMFD error.

 

56 0 / Create an account to join the discussion.
Snaks
Follow / 14 Oct 2014 at 9:21am / Oracle

Ist es möglich? SELECT als Loop ausführen und Dauer der...

Expand content
Hallo alle Zusammen, ist es möglich über PLSQL im Toad (for Oracle Professional V. 12.1.0.22 ohne Addons)... eine SQL auszuführen (Cursor?!?) -> ermitteln wie lange diese SQL zur...

Hallo alle Zusammen,

ist es möglich über PLSQL im Toad (for Oracle Professional V. 12.1.0.22 ohne Addons)...

eine SQL auszuführen (Cursor?!?) -> ermitteln wie lange diese SQL zur Ausführung benötigt hat und das Ergebnis auszugeben (DBSM?!?) -> SQL nach einer definierten Zeit wieder auszuführen (zB.: alle 5sec)

Peter

Unanswered 54 0 / Create an account to join the discussion.
sgag
Follow / 4 Oct 2014 at 12:34am / Oracle

How can I copy my Oracle Toad connections from Toad 10 to Toad 12...

Expand content
How can I copy my Oracle Toad connections from Toad 10 to Toad 12? I tried to paste the User Files folder from toad 10 to toad 12 , but no luck :(

 How can I copy my Oracle Toad connections from Toad 10 to Toad 12?

I tried to paste the User Files folder from toad 10 to toad 12 , but no luck :(

139 0 / Create an account to join the discussion.
micke.vrolijk
Follow / 1 Oct 2014 at 9:31pm / Oracle

Return values of ANYTYPE.GetAttrElemInfo

Expand content
Currently, I am building up a transient type in Oracle (11.2.0.2 and 11.2.0.3) PL/SQL to encapsulate access of views in a table producing function. However, I encounter something I don't understand...

Currently, I am building up a transient type in Oracle (11.2.0.2 and 11.2.0.3) PL/SQL to encapsulate access of views in a table producing function. However, I encounter something I don't understand in the results of the ANYTYPE.GetAttrElemInfo. According to the documentation, it returns "the typecode of the attribute or collection element". These typecodes are listed in package DBMS_TYPES. The ony numeric type defined in here is the NUMERIC type (with integer value 2), no subtypes are present here.

However, when I call the method on an object attribute that is defined as an subtype (like float or decimal), I will get a number that is not present in DBMS_TYPES. Does anyone has any pointers to documentation describing this behaviour? Is this a known issue?

I have an example script that shows / reproduces this behaviour: First a type object is defined with a number, float and decimal attribute. The anonymous PLSQL block will display the results of the anytype.GetAttrElemInfo call on the three attributes.

CREATE OR REPLACE TYPE TESTTYPE AS OBJECT
(
   ID NUMBER(38),
   ID2 FLOAT(126),
   id3 DECIMAL
);
/

DECLARE
   adata             ANYDATA;
   atype             ANYTYPE;
   tt                TESTTYPE;
   v_typenum         PLS_INTEGER;
   v_typecode        PLS_INTEGER;
   v_precision       PLS_INTEGER;
   v_scale           PLS_INTEGER;
   v_length          PLS_INTEGER;
   v_csid            PLS_INTEGER;
   v_csfrm           PLS_INTEGER;
   v_attr_elt_type   ANYTYPE;
   v_aname           VARCHAR2(30);
BEGIN
   tt := TESTTYPE( 1, 123, 10);
   adata := anydata.convertObject( tt);
   v_typenum := adata.gettype( atype);

   FOR i IN 1 .. 3
   LOOP
      v_typecode := atype.GetAttrElemInfo( i, v_precision, v_scale, v_LENGTH, v_csid, v_csfrm, v_attr_elt_type, v_aname);
      DBMS_OUTPUT.put_line( 'data of attribute ' || i || ' is:' || v_typecode);
   END LOOP;
END;
/

Hopefully anyone can help me.

Unanswered 62 0 / Create an account to join the discussion.
Chris Reyes
Follow / 30 Sep 2014 at 12:56am / Oracle

Meet Toad World at Oracle Open World

Expand content
We're hoping you're coming to Oracle Open World that started on Sunday and goes through Thursday. I got a last minute ticket and would love to hear from you and meet you in person and hear what...

We're hoping you're coming to Oracle Open World that started on Sunday and goes through Thursday.  I got a last minute ticket and would love to hear from you and meet you in person and hear what you think about Toad World .  Come on by the Dell Booth #1401 in Moscone Center South Exhibition Hall or the Toad Customer Party on Tuesday, September 30th at Lucky Strike in San Francisco.  See you there.  -Chris

92 0 / Create an account to join the discussion.
nivas4u2
Follow / 23 Sep 2014 at 7:15pm / Oracle

Oracle 12c - DBMS_SQL.RETURN_RESULT not showing results in TOAD.

Expand content
How to get results out of stored procedure returning Implicit Statement Results (Oracle 12c feature). Procedure executed from Sql Plus returns results, but same procedure executed from TOAD showing message...

 

How to get results out of stored procedure returning Implicit Statement Results (Oracle 12c feature).  Procedure executed from Sql Plus returns results, but  same procedure executed from TOAD showing message as "PL/SQL procedure successfully completed." without any results.

Please refer this link for example : http://www.toadworld.com/platforms/oracle/b/weblog/archive/2013/10/25/oracle-12c-dbms-sql-return-result-procedure.aspx

 

 

Unanswered 115 0 / Create an account to join the discussion.
detupc
Follow / 22 Sep 2014 at 4:52pm / Oracle

All columns shown after sorting - WHY ?

Expand content
I am frustrated at how Toad displays columns after I click on a column header to initiate a different sort. I first set up Toad to only display certain columns. There may be 80+ columnds I need to look...

I am frustrated at how Toad displays columns after I click on a column header to initiate a different sort.   I first set up Toad to only display certain columns. There may be 80+ columnds I need to look through to pick the ones that are meaningful to my work. I get all that set, then initiate a resort by clicking on a column header, ALL 80+ columns are displayed.

 

Is there a way to select X number of columns and only display those X columns and then sort by any column desired (within the X columns) ?

 

84 0 / Create an account to join the discussion.
ckendra
Follow / 12 Aug 2014 at 3:32pm / Oracle
Latest post by on 6 Sep 2014 at 9:24pm

Session/Table Lock History

Expand content
Is there a way to see the history of session blocks caused by table locking? I can view current blocking sessions through the session browser, but would like to see a history if its possible. Thanks!

Is there a way to see the history of session blocks caused by table locking? I can view current blocking sessions through the session browser, but would like to see a history if its possible. Thanks!

Unanswered 359 2 / Create an account to join the discussion.
detupc
Follow / 4 Sep 2014 at 8:28pm / Oracle

Column select not selected for long - how to select columns ?

Expand content
I am using Toad for Oracle Base 11.6.1.6. I wish to display only certain columns from table xyz. I display table xzy, click on the column select button in the upper left area, un-select all, all become...

I am using Toad for Oracle Base 11.6.1.6.

I wish to display only certain columns from table xyz.  I display table xzy, click on the column select button in the upper left area, un-select all, all become un-selected.  I choose only the columns I wish to see, and then click outside the column select area.  

The table is re-displayed, and only the columns I wish to view are displayed.

I then wish to sort on column abc, and click on that column, select ascending or descending, and the table is redisplayed, sorted on that column, with ALL columns displayed.  

It seems there is NO means to "permanently save" the selection of columns one wishes to see.

Just blink, and it all changes.

Is there a way to truly select ONLY certain columns for display ?

Unanswered 105 0 / Create an account to join the discussion.
jaishree
Follow / 4 Sep 2014 at 12:15pm / Oracle
Latest post by on 4 Sep 2014 at 3:05pm

can we pass %row count as a parameter in cursor?

Expand content
can we pass %row count as a parameter in cursor?

can we pass %row count as a  parameter in cursor?

191 1 / Create an account to join the discussion.
sdcraig
Follow / 27 Aug 2014 at 11:18am / Oracle
Latest post by on 28 Aug 2014 at 4:43pm

Oracle 11g Database Express Fuzzy Searching

Expand content
Hello: Very new to this forum. I've recently installed the above referenced software, but no clue how to use it. I use SQL daily, and would like to utilize Oracle functionality (specifically the...

Hello:

Very new to this forum.  I've recently installed the above referenced software, but no clue how to use it.  I use SQL daily, and would like to utilize Oracle functionality (specifically the TEXT features and FUZZY searching).  I would then use TOAD for Oracle and SQL syntax to query the data.

Is there a link or post somewhere that shows me how to:   a) load data;  b) create necessary indexes;  c) Link TOAD to db;  d) Begin querying.

Thank you.

 

Unanswered 217 2 / Create an account to join the discussion.