SQL & PL/SQL :: Decode Functionality In DBMS_OUTPUT

Jun 28, 2010

I would like to use the below decode in DBMS_OUTPUT in place of cur_rec.data_type. Could I know how can I achieve this.
The DBMS_OUTPUT is inside a loop and output is shown below as an example.

NOT NULL -- EMP_ID NUMBER
,NOT NULL -- EMP_NAME NUMBER
,NOT NULL -- HIRE_DT DATE
,NOT NULL -- SALARY NUMBER

DBMS_OUTPUT STMT:

Dbms_Output.Put_Line(nullable ||CHR(9)||CHR(9)||' -- ' ||upper(cur_rec.column_name)||CHR(9)||CHR(9)||cur_rec.data_type);

STMT to use in place of cur_rec.data_type:

substr(decode( data_type, 'NUMBER', decode( data_precision, NULL, NULL,'('||data_precision||','||data_scale||')' ),data_length),1,11)

Also I am not able to align the output using CHR(9).

View 14 Replies


ADVERTISEMENT

SQL & PL/SQL :: Decode Functionality With Null

Jun 29, 2010

Explain me the following behavior of DECODE?

SQL> select decode(null,null,'Matched','Not Matched') from dual;

DECODE(
-------
Matched
As expected, output should be 'Not Matched' instead of 'Matched'

View 10 Replies View Related

PL/SQL :: Increase Buffer Size Of Dbms_output.put_line

Feb 28, 2013

I am executing a plsql procedure and trying to increase buffer size to display all characters, procedure is given below:

create or replace procedure prc_p(prm_t1 in VARCHAR2,
prm_t2      in VARCHAR2,
prm_tab           in varchar2
)
AUTHID CURRENT_USER
as
str_sql   VARCHAR2 (4000);
[code]..........  

View 1 Replies View Related

Client Tools :: How To Get DBMS_OUTPUT.PRINT_LINE Output In TOAD

Sep 22, 2010

How i can get DBMS_OUTPUT.PRINT_LINE out put in TOAD.

View 6 Replies View Related

PL/SQL :: OUT Parameter Functionality

Mar 25, 2013

I thought that you are not supposed to be able to assign the value of an out parameter to a variable (Feuerstein, 5th ed) and yet I was just
able to do this without any compilation error messages. Is Feuerstein wrong or am I missing something? Has the functionality changed?

PROCEDURE Parse_HC
          (p_timestamp_string IN VARCHAR2,
          p_timestamp_date OUT DATE)
AS
v_date DATE;

[Code]....

Here is my banner:

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
"CORE     11.1.0.7.0     Production"
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 - Production

View 8 Replies View Related

Stream Functionality Replacement In 11g

Dec 15, 2010

Earlier I am using oracle 10g (10.2.0.4) and now we are planning to use 11gR2 (11.2.0.1).we are using oracle stream functionality in 10g. I just want to know is there any new feautre available in 11g which replace the stream functionality? we dont want to use stream (in 11g) any more.

View 3 Replies View Related

Forms :: GET_FILE_NAME Functionality

Mar 8, 2010

I have my forms application working on both windows and Unix platform. When i am using GET_FILE_NAME ( ), it is working fine in Windows based application but the same is not working in Unix based application..

View 11 Replies View Related

Forms :: JTF Grid Functionality

Sep 12, 2009

what is JTF GRID functionality,i have use that grid in custom form. how to proceed with that and what are steps to be taken to display JTF GRID Functionality ?

View 6 Replies View Related

SQL & PL/SQL :: Procedure Functionality As Triggers

Oct 26, 2010

I am creating triggers for audit operation when any insert/update/delete happens on a table automatically.Now i need the same functionality in procedure.

My requirement is to create procedure for all those triggers.

View 2 Replies View Related

DATE-ADD In Oracle - Convert Functionality?

Oct 28, 2011

I need to convert below functionality in Oracle. At present it is working with SQL server

current_end_time TIMESTAMP,
current_start_time TIMESTAMP
current_duration int
current_end_time := DATEADD(second, current_duration/10, current_start_time)

View 1 Replies View Related

PL/SQL :: Replicate For Loop Functionality Through SQL Query

Jun 14, 2013

I have a requirement where i need to replicate the for loop functionality  through sql query.In pl/sql  i can achieve this through cursors or for loop but is there any way  to do  or replicate the same functionality in sql queries .  For example i want to repeat the  operation for  set of records  or iterate through set of records and fire query accordingly .Is it possible  through sql query

View 19 Replies View Related

Forms :: Restrict Standard Save Functionality?

Jun 20, 2011

how can we restrict standard save button functionality in custom form?

View 4 Replies View Related

Forms :: Auto Stretch Functionality In Oracle?

Oct 6, 2013

Is it possible to have auto stretch functionality (similar to Oracle ADF) in Oracle Forms?

The requirement is to adjust the data blocks/ canvases according to the window size automatically.

I know it is possible in Oracle ADF. But I want to achieve the similar kind of functionality in Oracle Forms.

View 1 Replies View Related

Client Tools :: Access Violation And F4 Functionality

Jan 14, 2012

I am using the Toad10.After i enter the user, Pwd it displays the Toad error that""Access violation at address 6761CB21 in module 'ORA805.dll'. Read of address 00000010 ""

while i try to use the F4 functionality, it again displays the above msg and Object not found...After i execute the query, all the characters data displays in chinees.

View 1 Replies View Related

SQL & PL/SQL :: Create A Trigger With Requirement Of Achieving Primary Key Functionality?

Sep 22, 2011

I want to create a trigger with the requirement of achieving Primary key functionality.

I have a "EMP" table. the table already contains a duplicate data on "EMPNO" column. i want to restrict entering duplicate data further into table for that i want to create a trigger.

where can i find different triggering events of DML(like update, delete etc...)and DDL(database and schema level).

View 6 Replies View Related

Application Express :: Tabular Form Delete Row Functionality

Aug 29, 2013

I have a wizard created tabular form, adding a row is easy using addRow() function, is there a way that I can delete the row from it?  Let's say I have added a row and not submitted the form, but now I want to delete just that row from the form, how can I do that?

View 1 Replies View Related

Application Express :: No Page Edit Functionality After Upgrade To 4.2.2?

Jul 12, 2013

Apex has been upgraded from 4.1 to 4.2.2. I don't know if the upgrade was done correct or not by the administrator, but I don't have the page edit link anymore,

and anytime I run a individual page from the Edit Application, I have to Log in. Did something went wrong during the upgrade or is this the behavior of Apex now, which I don't think so.

View 1 Replies View Related

Client Tools :: SQL Developer - Disable Save Password Functionality?

Feb 18, 2011

I want to install SQL Developer on a server but don't want the user to be able to store passwords by checking the "Save Password" box.

View 14 Replies View Related

Application Express :: Creating Functionality For Changing Users Password

Mar 12, 2013

I'm trying to create a functionality on my application that lets the user change their password whenever they want or when the password gets expired.

But to do that, first I need a Branch to the page "Password Change" whenever the login procedure returns "Password Expire", but when the user has an expired password he can't access none of the pages of the application.

My other problem is:

Even if I can get the user to be redirect to the "Password Change" page, I would need a function that validate his current expired password, because in order to alter his own, he would need to inform his current password and the new one he desires. If I was using Apex Authentication that would be very easy I think, but I'm obligate to use Database Account Authentication.

View 4 Replies View Related

Application Express :: Interactive Report Subscription - Missing Search Functionality?

Oct 11, 2012

when receiving the output of a report subscription in Apex 4.1.1, the search Box is missing, only the Text string 'Suche:' is displayed.If i download the report as Email or html, the search box is there and usable.

Maybe i'm wrong, but this was not under Apex 4.0.2.

View 0 Replies View Related

Application Express :: Interactive Report Functionality For Classic Report?

Jun 27, 2012

I have to implement IR report search functionality (*when ever we click on column header it will display all the values in that particular column , once we click on particular value , the report will filter based on that particular value * ) in classic report .

View 8 Replies View Related

How To Use Decode And Sum

Nov 16, 2009

Which warehouses have pending orders for products, which are not in stock at the warehouse at the moment? Provide warehouse number, id of the product that is not in stock, number of orders issued for this product and total quantity ordered.

The tables I am using are

Warehouses:
Name Type
----------------------------------------
W_ID NUMBER(38)
CITY VARCHAR2(20)
W_SIZE NUMBER(38)

Inventories:
Name Type
-----------------------------------------
P_ID NUMBER(38)
W_ID NUMBER(38)
QUANTITY NUMBER(38)

Orders:
Name Type
-----------------------------------------
ORD_ID NUMBER(38)
SUPPLIER_ID NUMBER(38)
ISSUING_EMP_ID NUMBER(38)
ORDER_DATE DATE
ORDER_STATUS CHAR(1)

This is my code so far:

select w.w_id, i.p_id,
sum(decode(o.ord_id, ' ', i.p_id, 0)) Orders_issued,
select sum(i.quantity)
from
inventories i
orders o,

[code]...

but I get this error:

select sum(i.quantity)
*
ERROR at line 3:
ORA-00936: missing expression

View 2 Replies View Related

SQL & PL/SQL :: What Does DECODE Do

Sep 7, 2011

Just working with Unix for the first time and trying to understand this decode statement?

cursor l_cursor is select decode(type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1','/'||chr(10), null) ||
decode(line,1,'create or replace ', '') ||
decode(type||'-'||to_char(line,'fm99999'),
'JAVA SOURCE-1','and compile ' || type || ' named "' ||
name || '"' || chr(10) || 'AS' || chr(10), '') ||
text text
from user_source
where upper(name) = upper(p_name)
order by type, line;

View 8 Replies View Related

SQL & PL/SQL :: Use Of Decode

Feb 28, 2011

In my Hard code i saw decode like this

DECODE(tablename.columnname,'k',text,NULL,text, NULL)

can I use decode like this.

View 6 Replies View Related

Alternative To DECODE In Regards To Just SQL (not PL)

Jul 1, 2007

im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.

View 6 Replies View Related

SQL & PL/SQL :: Decode With Like Operator

Jul 12, 2013

I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.

create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.

select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a

given error as:

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

How to write this.

View 5 Replies View Related

SQL & PL/SQL :: Decode In Where Clause

Aug 3, 2013

I have a scenario where in report table I need to get

IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.

I tried using decode in WHERE CLAUSE , but not successful.

Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.

Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.

Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.

Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...

View 14 Replies View Related

SQL & PL/SQL :: Case Vs Decode?

Feb 13, 2011

How to use decode and case in "where" and "from" clause of a select statement.

I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition

View 7 Replies View Related

SQL & PL/SQL :: Decode On One Line

Jul 28, 2010

I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.

What do I have to do to get the data on one line? My code is as follows:

SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Decode With Between Clause

Aug 11, 2011

I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"

select distinct record_number
from meta m, procedu b, control v, fact f, calendar dc, person p
where f.key = m.key and f.group_key = b.group_key
and b.proc_key = v.proc_key
and f.calendar_key = dc.calendar_key
and f.person_key = p.person_key
and VALUE BETWEEN DECODE((Select distinct operator_type
[code]....

But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved