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
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]..........
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
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.
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..
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 ?
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.
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
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.
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).
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?
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.
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.
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.
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 .
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
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;
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.
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"
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:
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"
