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

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 resultsout 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 resultsout 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 5 0 / Create an account to join the discussion.
Mohammedtodd
Follow / 19 Sep 2014 at 8:01am / Oracle
Latest post by on 22 Sep 2014 at 9:14pm

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 55 1 / 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) ?

 

22 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 231 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 76 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?

109 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 141 2 / Create an account to join the discussion.
dorianpc
Follow / 6 May 2014 at 7:57pm / Oracle
Latest post by on 9 Aug 2014 at 7:31pm

Why is Rename Identifier greyed out?

Expand content
Guys, Can someone explain to me why "Rename Identifier" is greyed out in Toad, when I am trying to refactor my code? I want to do just one rename for my whole code.

Guys,

Can someone explain to me why "Rename Identifier" is greyed out in Toad, when I am trying to refactor my code?

I want to do just one rename for my whole code.

Unanswered 232 1 / Create an account to join the discussion.
rtheron
Follow / 11 Jul 2014 at 6:09pm / Oracle
Latest post by on 9 Aug 2014 at 7:28pm

Default tnsnames.ora file location config

Expand content
I prefer to use the oracle net services connection type, but I have to specify the location of the tnsnames.ora on my MAC each time I add a new database connection, is there a way to let toad know automatically...

I prefer to use the oracle net services connection type, but I have to specify the location of the tnsnames.ora on my MAC each time I add a new database connection, is there a way to let toad know automatically where this file is located?


Mine is located in ./Applications/oracle/product/instaclient_64/11.2.0.4.0/network/admin

and my .bash_profile configures relevant environment variables as:

export ORACLE_HOME=$HOME/Applications/oracle/product/instantclient_64/11.2.0.4.0/
export PATH=/usr/local/bin/athena:$ORACLE_HOME/bin:$PATH
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib

301 5 / Create an account to join the discussion.
Sush
Follow / 7 Aug 2014 at 8:25pm / Oracle

Running reports

Expand content
Hello, I use Oracle 9i Release (9.2.0.1.0) for Windows and TOAD for Oracle 8.6.0.38. I am aware of being able to run reports via database. Can anyone please tell me the steps to do that? Thanks in...

Hello,

I use

Oracle 9i Release (9.2.0.1.0) for Windows  and TOAD for Oracle 8.6.0.38. I am aware of being able to run reports via database. Can anyone please tell me the steps to do that?

Thanks in advance.

90 0 / Create an account to join the discussion.
Jumboshrimps
Follow / 6 Aug 2014 at 6:18pm / Oracle
Latest post by on 6 Aug 2014 at 6:46pm

Copy ONE table in Toad Xpert 12.5

Expand content
I need to copy several ORACLE tables on schemas on different servers. I open Schema Browser, right click on the table I want to copy and select Copy Data to Another Schema. I select other Schema I'm...

I need to copy several ORACLE tables on schemas on different servers.

I open Schema Browser, right click on the table I want to copy and select Copy Data to Another Schema.

I select other Schema I'm connected to and press execute.

I get an error message
"Error Message:
ORA-00942: table or view does not exist"

NO KIDDING!!!! THAT'S WHY I'M COPYING THE TABLE!!!!!

Second option is to right click on the table in the Schema Browser

again, and select "Create in Another Schema".

I select other schema, select execute and get an error message:

"ORA-00942: table or view does not exist"

REALLY???????????????? OF COURSE IS DOESN'T EXIST, THAT'S WHY I'M COPYING IT!!!!!!!!!

I just to select a table from one schema drag-and-drop it to the other schema.

Is that possible?

109 1 / Create an account to join the discussion.
ashish_jha
Follow / 19 Mar 2014 at 10:02am / Oracle
Latest post by on 26 Jul 2014 at 5:12pm

RAC Testing

Expand content
In our production we have setup RAC Can anyone Please share steps to test RAC settings.

In our production we have setup RAC

Can anyone Please share steps to test RAC settings.

222 1 / Create an account to join the discussion.
nitinpbhore
Follow / 11 Mar 2014 at 9:57am / Oracle
Latest post by on 26 Jul 2014 at 1:07pm

DB link between Oracle 8i and 11g

Expand content
Hi My requirement is to create DB link between Oracle 8i and 11g. as mentioned in interopertability matric connectivity between 8i to 11g is not supported, workaround is to create one intermidiate...

Hi

My requirement is to create DB link between Oracle 8i and 11g.

as mentioned in interopertability matric connectivity between 8i to 11g is not supported, workaround is to create one intermidiate database of 10g .

is there any other workaround like can ODBC or JDBC (JDBC thin driver) connectivity is possible between 8i and 11g.

Regards,

Nitin

545 1 / Create an account to join the discussion.
AmyHill
Follow / 31 Mar 2014 at 9:16pm / Oracle
Latest post by on 26 Jul 2014 at 12:59pm

Retain leading zeros for declared variables using cursor and fetch...

Expand content
Greetings, I have an sql stmt I need to modify to retain leading zeros and I'm not sure how to modify it. Here is the code: undef mmy_schema; undef BeginDate; undef EndDate; DECLARE -- declare...

Greetings,

I have an sql stmt I need to modify to retain leading zeros and I'm not sure how to modify it. Here is the code:

undef mmy_schema;

undef BeginDate;

undef EndDate;

DECLARE -- declare variables and cursors

startDt  VARCHAR2(10) := '&&BeginDate';

stopDt VARCHAR2(10) := '&&EndDate';

mmy_schema varchar2(200) := '&&mmy_schema'

policyNo   &&mmy_schema..TableName.fieldname%TYPE;  

---------THIS poliycNo IS THE VARIABLE I WANT TO RETAIN LEADING ZEROS....HOW??? IT IS A VARCHAR2(10BYTE) data type in the schema.

The cursor is defined after this, with FETCH and loop stmts.

 

208 1 / Create an account to join the discussion.
Orangeblood26
Follow / 13 Jun 2014 at 5:43pm / Oracle
Latest post by on 26 Jul 2014 at 12:50pm

Oracle RDB Driver connection and using variables

Expand content
I'm currently working out of Toad Data Point 3.2 and have made a connection to a Oracle RDB Driver through ODBC connection within the Toad product. I am trying to "declare" a variable (see...

I'm currently working out of Toad Data Point 3.2 and have made a connection to a Oracle RDB Driver through ODBC connection within the Toad product. I am trying to "declare" a variable (see example below) but keep receiving an error. My issue is I'm trying to write a query to pull a set of data by limiting on a "where" clause, the data type of the field is a date field.

 

Example of my quick test code that is receiving an error (remember I'm connected to a ODBC connection to a Oracle RDB Driver):

declare @dt_timeframe date

the above gives me a syntax error, any guidance helps. Thanks.

Unanswered 161 1 / Create an account to join the discussion.