SQL & PL/SQL :: What Part Of Query That Is Being Run Now From Procedure

Apr 14, 2010

I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.

View 23 Replies


ADVERTISEMENT

Application Express :: Get Part Of URL As Part Of Dynamic Action When Clicking A Button?

Nov 3, 2013

I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?

View 6 Replies View Related

Text :: Make Part After AND Not Match Already Matched First Part?

Nov 2, 2012

I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.

The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:

Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;

But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?

View 2 Replies View Related

Select Query Matches Part Of The String

Mar 11, 2010

Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.

Here xxx.a will contain values like 123 or 456 like that .

View 6 Replies View Related

SQL & PL/SQL :: Query Of Functions - Display Half Part Of String

Jun 8, 2011

I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk

I want to display only the later part of the string

myharbour.org.uk as output.

Can we write using string functions in plslql?

View 19 Replies View Related

SQL & PL/SQL :: How To Add Query To Existing Procedure

Nov 10, 2011

how to add a auery to my procedure.

View 12 Replies View Related

SQL & PL/SQL :: Convert Procedure To Single Query

Jul 26, 2012

I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.

FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER)
RETURN VARCHAR2 IS
v_act_shows booking_wk_screen.act_shows%TYPE;
v_expected_shows booking_wk_screen.expected_shows%TYPE;
v_return VARCHAR2(50);
BEGIN
SELECT NVL(act_shows, 1), NVL(expected_shows, 1)
INTO v_act_shows, v_expected_shows
FROM booking_wk_screen
WHERE booking_wk_id = i_booking_wk_id
[code]...

Is there anyway through which we can achieve this in Oracle 10g.

View 16 Replies View Related

Alias Not Usable In WHERE Part?

Apr 30, 2007

why this query works fine:

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
FROM zajezd Z
ORDER BY Z.JMENO

But when I try to use the alias to filter the results like this (note the WHERE part):

SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK

[code]...

Then it throws an error message ORA-00904: Unknown Identifier for the column/alias POCET_ZASTAVEK?

View 2 Replies View Related

SQL & PL/SQL :: Convert Time Part Into Second

May 10, 2013

How can i convert the time part into second. for example

in mu table i have below rows

1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000

I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.

But that is complex column.

View 14 Replies View Related

SQL & PL/SQL :: Selecting Only Part Of A String

Feb 25, 2010

I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Extracting Part Of A String

Jan 9, 2013

To make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));

First field content with below record:

FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP

to filter the above record from FLIST column thorugh sql script as below:

FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP

means remove first 5 charator and after APXXXXXXXXX.

Output of SQL query should come like below:

CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION

View 24 Replies View Related

Create Query On Procedure - Change Value In Table?

May 21, 2013

how can i create this query on a procedure:

Insert into COMPUSOFT.PESAJE@DB_2
(PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
select /*+ FULL(Tbl1) */

[code]..

plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2

View 5 Replies View Related

SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure

May 14, 2013

I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:

Procedur MyProc
(
In_EmpID Number default null,
In_EmpName Varchar2 default null,
in_JoinDate Date default null
[code]....

I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.

View 2 Replies View Related

JDeveloper, Java & XML :: Procedure To Query A XML Document?

Apr 13, 2012

I am using an oracle 11g database. I have an XML document named test_table.xml stored in table TEST_CLOB, Ex: TEST_CLOB(ID NUMBER, XML_DOC CLOB)

Here are the contents of test_table.xml file.

<xml version = "1.0"" ?>
<! DOCTYPE main [
<! Element main (DATA_RECORD*) >
<! COLUMN1?, COLUMN2?, COLUMN3, COLUMN4?, COLUMN5, COLUMN6?, GEOM?) +>
<! ELEEMENT COLUMN1 (#PCDATA)>
<! ELEEMENT COLUMN2 (#PCDATA)>

[code]....

I need a database procedure where I will pass in COLUMN1, COLUMN2 AND COLUMN3 values and it should return all the columns from the xml document. ie. ex: below is what I would like to query

select column1, column2, column3 column4 column5, column 6
from the TEST_TABLE.XML ---- XML DOCUMENT FILE NAME
WHERE column1 = 'ARENAS'
AND column2 = 'NBA'
AND column 3 = 'UNITED CENTER';

View 21 Replies View Related

PL/SQL :: Create A Procedure For SELECT Query - Error?

Nov 1, 2013

how do I create a procedure for a SELECT query like the following?

When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE  MyProcISBEGINselect 'Dakota' as ALIAS      ,A.StartDate      ,B.EndDatefrom Customer A    ,Clients bwhere  a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND  MyProc;

View 17 Replies View Related

How To Execute Part Of WHERE Clause Conditionally

Oct 30, 2010

I need to insert Data into a table based on particular condition, So I'm using an Insert select as follows:

INSERT INTO error_table
(error_code,cust_id,customer_name,error_message )
SELECT '34', cust_id,customer_name, 'Missing xxx'
FROM customers
WHERE cust_id = cur_cust.cust.id
AND ( NOT EXISTS ( Select .....) -- control 1
OR (EXISTS ( Select .....) --control 2
OR (NOT EXISTS ( Select .....) -- control 3

I have around 10 controls. I want these controls to run conditionally..I was wondering if this can be done using a decode based on some variable outside the query.

I want the query to run like this:

if A then execute control 1: AND ( NOT EXISTS ( Select .....)

If B then execute control 2: AND ( OR EXISTS ( Select .....)

View 2 Replies View Related

SQL & PL/SQL :: Extract Only Number Part Of Column?

Mar 25, 2010

In a table, I have a column in a table with the following values:

targetcol

30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0

-
-
-
-
-
-
-
-
-

where WD is working days and CD Calendar days.

I just need to extract number from above.

View 11 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

SQL & PL/SQL :: Date - Hardcode Time Part As 18:00:00?

Oct 16, 2012

For an entered date, I want to hardcode the time part as 18:00:00, and rest of the date should be whatever is passed as input, how can i do this?

View 4 Replies View Related

Forms :: Migration Part From Oracle 6i To 10g?

Jun 14, 2012

i am working on sql server developer in on of the organization. i got requirement from client that migration part from oracle 6i forms to oracle 10g and i got existing forms backup from client that is have .fmb, .mmb, .rdf and some of libraries having .pll files, but am new to oracle which form need to open from form builder first?

i tried to open .fmb file given database connection also but it is showing some libraries are missing. what should be the solution for my problem.

View 1 Replies View Related

SQL & PL/SQL :: Improve Procedure Which Is Looping Through Query To Make Inserts

Oct 10, 2013

I am trying to improve a procedure which is looping through a query to make inserts.

FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....

View 12 Replies View Related

SQL & PL/SQL :: How To Execute Dynamic Query In Procedure With Special Characters

Dec 17, 2012

I have the following procedure body in a package.

PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;

begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';

EXECUTE IMMEDIATE(v_sql);
commit;
end;

when I execute the above procedure it gives the following error.

ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.

I think dynamic sql is not executed because of the pipe character in the sql statement.

View 2 Replies View Related

PL/SQL :: Procedure To Make Minus Query As Output For 2 Tables

Nov 30, 2012

create a procedure so that I could get minus query of 2 table as a result.

I have two table 1- src_table_list ,2- tgt_table_list both tables have 2 columns : serial_no,table_name and 100 records each. and details mentioned in column "table_name" are actually tables name which present in my testing database.

so I need one procedure which will pick one table_name from src_table_list and one table_name from tgt_table_name each time recursively and provide minus query as a result. as below.

select c1,c2,c3,c4 from table1 --(fetched from src_table_list)
minus
select b1,b2,b3,b4 from table2 --(fetched from tgt_table_list)

create the procedure..as I have to prepare minus query for more than 200 tables and then I need to test them for integration testing..

View 11 Replies View Related

Reports & Discoverer :: How To Set Part Of String In Bold

Apr 30, 2013

I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.

I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string

for now, i set bold text i want to and i have created a fonction that make text bold like this

l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;

but it doesnt look to works..

how can i do that? And where i can do that?

View 2 Replies View Related

SQL & PL/SQL :: Part Number And Manufacturer Combination Must Be Unique?

Aug 31, 2010

i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"

-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies View Related

SQL & PL/SQL :: Calculate A Field Taking Numeric Part?

May 11, 2011

I have a column where the values are like

SQL> SELECT distinct a.legacy_product_code FROM im_polprdmap_pukka a where a.new_product_code like '%GIFT%';
LEGACY_PRODUCT_CODE
-------------------
GIFT25
GFTPF21NP
GIFT21/H
GIFT21//

[code]...

I need to calculate a field taking the numeric part of the above filed.How can i write this?

View 2 Replies View Related

Adding Time Part To Current Date To Form Timestamp

May 17, 2011

I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .

so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .

query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual

View 1 Replies View Related

Server Administration :: Utilize Temp Tables As Part Of A Scheduled Job?

Jan 18, 2013

There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.

Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?

View 2 Replies View Related

JDeveloper, Java & XML :: Parsing With Special Character As Part Of Data

Sep 10, 2013

I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken

I'm receiving a XML from some external source and can not change it.

View 8 Replies View Related







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