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

Nov 10, 2011

how to add a auery to my procedure.

View 12 Replies


ADVERTISEMENT

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

PL/SQL :: Add Additional Parameter To Existing Procedure?

Oct 4, 2013

I have an procedure with four in parameters in it. Now, I want to add an out parameter to it. The issue is the procedure is called from many other procedures and triggers. Since we can't set default value to out parameter as we can for in parameter, how we can achieve the  requirement without having to update all the places where this procedure is being called.

View 3 Replies View Related

PL/SQL :: CTXRULE Matches Non-existing Query String?

May 25, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
==============================================
SQL> select * from books_cat;
     ID CAT               QRY
---------- -------------------- --------------------------------------------------
141 Oracle          {Oracle} OR {RDBMS}
142 Russia          {Russia} OR {Russian}
SQL> select id, file_desc from books;

[code]....

I am learning Oracle Text and created the above routine to check whether Russia/Russian appears in the OracleTextDeveloper.txt file (text version of the standard Oracle documentation - B28303-03).

Some more information: -

DBMS_METADATA.GET_DDL('INDEX','IDCTXR_BOOKS_CAT')
--------------------------------------------------------------------------------
CREATE INDEX "READER"."IDCTXR_BOOKS_CAT" ON "READER"."BOO
KS_CAT" ("QRY")
INDEXTYPE IS "CTXSYS"."CTXRULE"
SQL> select token_text, token_type from dr$idctxr_books_cat$i;

[code]....

View 0 Replies View Related

Precompilers, OCI & OCCI :: PLS-00306 / Wrong Number Or Types Of Arguments In Call To Existing Stored Procedure

Feb 1, 2010

I'm using an existing stored procedure in my C code. The stored procedure in question has been compiled and is proven to work without any errors. However, when I use the same in my C code, its failing with the above error.

The Store procedure definition looks like :

CREATE OR REPLACE FUNCTION SP(
srq_id integer ,
unid IN SPkg.arr_parmid,
parm_typ IN SPkg.arr_parm_typ,

[code].....

Type definitions

TYPE arr_parm_typ IS TABLE OF char INDEX BY BINARY_INTEGER;
TYPE arr_parmid IS TABLE OF tbl_parm.UNID%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_lbl IS TABLE OF tbl_parm.PARM_LBL%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_parm_vlu IS TABLE OF tbl_parm.PARM_VLU%TYPE INDEX BY BINARY_INTEGER;
TYPE arr_vlu_hint IS TABLE OF tbl_parm.VLU_HINT%TYPE INDEX BY BINARY_INTEGER;

My C code looks like :

typedef struct param
{
char lbl[30][81];
char vlu[30][256];
char typ[30];
ub8 seq_no[30];

[code].....

The way I invoke the stored procedure:

char command[250] = "begin
:retval := SSP_srq_parm_all(:srq_id,:unid,:parm_typ,:parm_lbl,:parm_vlu,:commit_flag,:vlu_hint,:create_flag);
end;";
OCIStmtPrepare2((OCISvcCtx *)svchp, (OCIStmt **)&(stmthp),
(OCIError *)errhp, (OraText *)command,

[code].....

OCIStmtExecute() fails with the above error.

View 3 Replies View Related

SQL & PL/SQL :: Existing Role Claimed To Be Not Existing?

Dec 19, 2011

I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.

GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'

[code]....

View 5 Replies View Related

RAC/ASM Clusterware Installation :: Query On Adding New Node To Existing 2 Node Cluster

Jun 15, 2012

current environment

Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI

on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2

we shutdown vmorarac1 to clone it to vmorarac5

on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211

mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5

152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip

[Code]....

Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2

152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip

hostname change in /etc/sysconfig/network

On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1

addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing

View 3 Replies View Related

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 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

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

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

SQL & PL/SQL :: Recreating Existing Trigger

Nov 28, 2011

I am trying to update an existing trigger in my Prod DB, but it may be possible that while recreating the trigger,existing trigger might be under any transaction.

So what could be the possible scenarios? Will I be throwing any error like ORA-0054: resource busy or any other error or what? If it does what could be possible escape ways apart from downtime.

View 17 Replies View Related

SQL & PL/SQL :: How To Add Row Dependencies In Existing Table

Jul 20, 2012

How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..

View 2 Replies View Related

SQL & PL/SQL :: Give New ID To Existing Employee?

Mar 13, 2012

want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :

1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).

The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.

Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.

When step 2 ends, we release all the tables from the markers.

My simple question : how to achieve this ?

View 12 Replies View Related

SQL & PL/SQL :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

SQL & PL/SQL :: Update Existing Table

Apr 24, 2012

I want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table

the output will shows like below

empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012

the forth one column is in date format and I want to update table with respect to order by the column start_date

View 1 Replies View Related

SQL & PL/SQL :: ORA-00955 / Name Already Used By Existing Object

Mar 15, 2012

During import , tables were imported but index was not imported due to ORACLE home was full.after import i was trying to create the the same index as it was not imported i am getting below error.

The "error ORA-00955: name is already used by an existing object"

My index name is GL01PK . I am trying to verify if the index with above name is present or not , using following query.

select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID ,OBJECT_TYPE from dba_objects where object_name like '%GL01%'

below is the output which shows that GL01PK index is not present.

GLOT1 GL01 150943 TABLE
FNSONLC3 GL01 147240 TABLE
FNSONLC3 GL01_13042011 148184 TABLE
FNSONLC3 GL01_07052011 148195 TABLE

how can i create the index here with the same name GL01PK

View 3 Replies View Related

How To Modify Existing Spfile

Jul 15, 2013

I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.

View 3 Replies View Related

PL/SQL :: To Set A Bonus Value From The Existing Column

Jun 2, 2013

we are providing a salary column. from that salary column we need to calculate bonus with the condition as bonus=(salary+10%). how to execute this.

View 5 Replies View Related

PL/SQL :: How To Create New Array Using Existing One

Apr 9, 2013

I would like to know how to create an associative array from an existing array plus some columns using other tables using a SQL query, bulk collect to a new array.

For example:
1. array 1 is created like select emp_no, emp_name, dept_no bulk collect into l_emp from emp;
2. array 2 needs to be created from array 1 along with the table department and the output should have all columns of array 1 plus dept_name, loc_no from department table.
3. I know this can be done using a simple join between 2 tables but for some reason I would like to create it as explained above.
4.i wanted them as associate arrays and not object collection.

View 2 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

Inserting Restriction By Existing Data

Nov 8, 2010

In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.

that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.

View 2 Replies View Related

Getting Timestamp For Existing Records In Table?

Nov 15, 2010

I need to get the timestamp for all the existing records in my table...I am having one user definition field, is this possible?

View 4 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related







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