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

syedraoof
Follow / 19 Nov 2014 at 11:32am / Oracle
Latest post by on 26 Nov 2014 at 5:54am

Toad Auto debugger doesn't work. Unable to find RAC connection...

Expand content
Hi I am trying to use debug option in toad 9.7 but it shows this message "UNABLE TO FIND RAC CONNECTION INFORMATION FOR RAC (INST_ID=1)" I found some information and it says that Oracle doesn't...

Hi

I am  trying to use debug option in toad 9.7 but it shows this message "UNABLE TO FIND RAC CONNECTION INFORMATION FOR RAC (INST_ID=1)"

I found some information and it says that Oracle doesn't support debugging accros RAC instances. We have a RAC instances and It was working fine until now but all of a sudden i faced the error message today.

Even I followed some instructions to create additional entries in tnsname.ora for each RAC nodes without load balancing parameter but it fails and faced the same error message.

Following is the tns entries.

Actual Entry

RAC =

 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = mcmsdb)(PORT = 1521))
   (FAILOVER=on)
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = mcmsdrac)
     (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
   )
 )

Instance 1


RAC1 =
 (DESCRIPTION =
   (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = xx.xx.xxx.xx)
     (PORT = 1521)
   )
   (CONNECT_DATA =
     (SERVICE_NAME = mcmsdrac)
     (instance_name = mcmsdrac1)
   )
 )

Instance 2

RAC2 =
 (DESCRIPTION =
   (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = xx.xx.xxx.xx)
     (PORT = 1521)
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = mcmsdrac)
     (instance_name = mcmsdrac2)
   )
 )

Please help me to get rid out of this issue .

321 16 / Create an account to join the discussion.
murathankocan
Follow / 24 Nov 2014 at 10:11am / Oracle

getting results like pipe function but in editor window

Expand content
Hi all, quite new in toad & oracle. Until this time I had mostly work on ms-sql, but nowadays I need to work on oracle. Here is my question: I have managed to generate a pipelined function in...

Hi all,

quite new in toad & oracle. Until this time I had mostly work on ms-sql, but nowadays I need to work on oracle. 

Here is my question:

I have managed to generate a pipelined function in oracle pl/sql which is like below and it works great.

FUNCTION get_trx_history (i_TRUCK_PLATE_NR varchar2,
i_date_START DATE DEFAULT SYSDATE - 1,
i_date_end DATE DEFAULT SYSDATE)
RETURN tbl_trx_history
PIPELINED
IS
r_pipe r_trx_history;
L_TRUCK_ID INTEGER;
BEGIN
SELECT ID
INTO L_TRUCK_ID
FROM VHCL100
WHERE IDENTIFIER = I_TRUCK_PLATE_NR;

FOR r
IN (SELECT a.*,
LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS odometer_before,
"ODOMETER"
- LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS delta_odometer,
LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
AS fuellevel_before,
"FUELLEVEL" / 10
- LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
AS delta_fuellevel
FROM asbotek.positionhistory a
WHERE label = i_truck_plate_nr
AND positiondate >= i_date_start
AND positiondate <= i_date_end)
LOOP
R_pipe := NULL;
r_pipe.trck100_id := L_TRUCK_ID;
r_pipe.POSITIONNO := r.positionno;
r_pipe.LABEL := r.label;
r_pipe.POSITIONDATE := r.positiondate;
r_pipe.UPDATETIME := r.updatetime;
r_pipe.HUBREFERENCE := r.hubreference;
r_pipe.LATITUDE := r.latitude;
r_pipe.LONGITUDE := r.longitude;
r_pipe.SPEED := r.speed;
r_pipe.ODOMETER := r.odometer;
r_pipe.TOTALFUEL := r.totalfuel;
r_pipe.FUELLEVEL := r.fuellevel;
r_pipe.ANGLE := r.angle;
r_pipe.POSSRC := r.possrc;
r_pipe.LOCATION := r.location;
r_pipe.IGNITION := r.ignition;
r_pipe.odometer_before := r.odometer_before;
r_pipe.delta_odometer := r.delta_odometer;
r_pipe.fuellevel_before := r.fuellevel_before;
r_pipe.delta_fuellevel := r.delta_fuellevel;


PIPE ROW (r_pipe);
END LOOP;
END;
END;

my problem is; that I cannot make this work in edito. I managed to walk until:

declare
i_TRUCK_PLATE_NR varchar2(10) := '34 ff 444';
i_date_START DATE := SYSDATE - 1;
i_date_end DATE := SYSDATE;
r_pipe r_trx_history table;
L_TRUCK_ID INTEGER := 0;
BEGIN
SELECT ID
INTO L_TRUCK_ID
FROM VHCL100
WHERE IDENTIFIER = I_TRUCK_PLATE_NR;

FOR r
IN (SELECT a.*,
LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS odometer_before,
"ODOMETER"
- LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS delta_odometer,
LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
AS fuellevel_before,
"FUELLEVEL" / 10
- LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
AS delta_fuellevel
FROM asbotek.positionhistory a
WHERE label = i_truck_plate_nr
AND positiondate >= i_date_start
AND positiondate <= i_date_end)
LOOP
R_pipe := NULL;
r_pipe.trck100_id := L_TRUCK_ID;
r_pipe.POSITIONNO := r.positionno;
r_pipe.LABEL := r.label;
r_pipe.POSITIONDATE := r.positiondate;
r_pipe.UPDATETIME := r.updatetime;
r_pipe.HUBREFERENCE := r.hubreference;
r_pipe.LATITUDE := r.latitude;
r_pipe.LONGITUDE := r.longitude;
r_pipe.SPEED := r.speed;
r_pipe.ODOMETER := r.odometer;
r_pipe.TOTALFUEL := r.totalfuel;
r_pipe.FUELLEVEL := r.fuellevel;
r_pipe.ANGLE := r.angle;
r_pipe.POSSRC := r.possrc;
r_pipe.LOCATION := r.location;
r_pipe.IGNITION := r.ignition;
r_pipe.odometer_before := r.odometer_before;
r_pipe.delta_odometer := r.delta_odometer;
r_pipe.fuellevel_before := r.fuellevel_before;
r_pipe.delta_fuellevel := r.delta_fuellevel;


PIPE ROW (r_pipe);
END LOOP;
END;

but I got error on:

r_pipe r_trx_history table;

where I know there is an error but I do not know how to solve it.

This is just an example:

furthermore I need to be able to get the result table of any query in editor by using variables in it without creating a function.

Let's say you have a table called phones with columns: owner, phone_type and phone_nr.

here is what I like to do:

declare

i_owner varchar2(10) := 'some one';

begin

select * from phones where owner = i_owner;

end;

should give me the results, but within editor pane without creating a function.

 

Thanks beforehand,

Murat KOÇAN

42 0 / Create an account to join the discussion.
Aftab Ahmad
Follow / 21 Nov 2014 at 2:06am / Oracle

Database_Creation

Expand content
As we know ,we can create a database by method DBCA,OMF and MANUALLY, I want to know there is any another method to create a database and also I want to know what is advantage

As we know ,we can create a database by method DBCA,OMF and MANUALLY, I want to know there is any another method to create a database and also I want to know what is advantage

49 0 / Create an account to join the discussion.
falujar
Follow / 22 Sep 2011 at 5:34pm / Oracle
Latest post by on 19 Nov 2014 at 11:08am

Auto Debugger and trace don't work

Expand content
Hi I'm trying to use debug option with a procedure. But when I press execute, it shows this message "UNABLE TO FIND RAC CONNECTION INFORMATION FOR RACCELR (INST_ID=2)" I found some information...

Hi
I'm trying to use debug option with a procedure.  But when I press execute, it shows this message "UNABLE TO FIND RAC CONNECTION INFORMATION FOR RACCELR (INST_ID=2)"
I found some information and it says that Oracle doesn't support debugging accros RAC instances
We have a RAC instances and It works without any problem.  What can I do to sort it out? 
This is the tnsnames that i'm using

RACCELR.CLINICAROSARIO.COM.CO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = raccelr)
    )
 )

Thank you so much por your help

1697 3 / Create an account to join the discussion.
briggsMJ
Follow / 13 Nov 2014 at 12:36pm / Oracle

Amortization Schedule Oracle PL/SQL

Expand content
Anyone have any idea what the code looks like for an amortization schedule in pl/sql? Does SQL Navigator even have a PMT function similar to excel? I like to think it would but cant seem to find it. ...

Anyone have any idea what the code looks like for an amortization schedule in pl/sql?  Does SQL Navigator even have a PMT function similar to excel?  I like to think it would but cant seem to find it. 

Thanks,

Mike

81 0 / Create an account to join the discussion.
detupc
Follow / 4 Sep 2014 at 8:28pm / Oracle
Latest post by on 5 Nov 2014 at 10:53pm

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 222 2 / Create an account to join the discussion.
KM1971
Follow / 31 Oct 2014 at 12:02pm / Oracle

Import XML file for Oracle XMLType Column

Expand content
Hi, I have an XML document that I would like to import into a XMLType column in an Oracle table. Is there a standard import utility to perform this function in TOAD? If not, how is this achievable...

Hi,

I have an XML document that I would like to import into a XMLType column in an Oracle table.

Is there a standard import utility to perform this function in TOAD? If not, how is this achievable?

 

 

Thanks in advance

KM

Unanswered 89 0 / Create an account to join the discussion.
PRAKASH VERMA
Follow / 27 Oct 2014 at 3:14pm / Oracle

How To Open More Than One Data Grid In Toad

Expand content
I Write a Statement in Toad Editor that is................... SELECT * FROM DEPT ; Result Display in the Data Grid After the Above Statement I wrote the other Statement that is ......................

I Write  a Statement in Toad Editor that is................... SELECT * FROM DEPT ;

Result Display in the Data Grid

After the Above Statement I wrote the other Statement that is ..........................SELECT * FROM DEPT;

Result Display in the Same Data Grid.

But I Want to display the data in Other Data Grid means One More Data Grid be Open for the Second Statement.

How can we do it in Toad For Oracle.

Unanswered 92 0 / Create an account to join the discussion.
Abu Fazal Abbas
Follow / 14 Oct 2014 at 9:15am / Oracle
Latest post by on 26 Oct 2014 at 6:22pm

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.

 

169 2 / Create an account to join the discussion.
Snaks
Follow / 14 Oct 2014 at 8:21am / Oracle
Latest post by on 26 Oct 2014 at 3:11pm

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 119 1 / Create an account to join the discussion.
KrishBalu
Follow / 22 Oct 2014 at 5:51pm / Oracle
Latest post by on 26 Oct 2014 at 3:08pm

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 130 2 / Create an account to join the discussion.
vallesmx
Follow / 16 Oct 2014 at 6:38pm / Oracle
Latest post by on 26 Oct 2014 at 3:06pm

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 209 2 / Create an account to join the discussion.
Mohammedtodd
Follow / 21 Oct 2014 at 1:34am / Oracle
Latest post by on 22 Oct 2014 at 5: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 296 2 / Create an account to join the discussion.
Mohammedtodd
Follow / 19 Sep 2014 at 8:01am / Oracle
Latest post by on 19 Oct 2014 at 11:47pm

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 317 2 / Create an account to join the discussion.
sgag
Follow / 3 Oct 2014 at 11:34pm / 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 :(

168 0 / Create an account to join the discussion.