Use Of DBMS_LOB Necessary When Returning CLOB From PL/SQL Procedure?

Oct 23, 2009

I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):

create or replace procedure p_xml_test_1(
p_xml out nocopy clob
) is
begin
p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
'<test><something>some value</something></test>';
end p_xml_test_1;

But I have access to some other source code that basically does this:

create or replace procedure p_xml_test_2(
p_xml out nocopy clob
) is
lv_xml clob;
begin
dbms_lob.createtemporary(
[code]......

I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any, to the second method?

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Instr Versus DBMS_LOB.instr / Search A Pattern Backwards In A CLOB Field?

Mar 10, 2012

I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.

Parameters: instr(text_to_be_searched, pattern, offset, nth)

Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.

select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;

View 6 Replies View Related

SQL & PL/SQL :: Function Returning CLOB - Invalid Format

Jan 22, 2011

I have a problem when trying to create a PLSQL function based on an XML extraction query.

I have three dummy tables:

SQL> get create_address
1 create table ADDRESS
2 (
3 id NUMBER not null,
4 house_number NUMBER,
5 house_name VARCHAR2(20),
6 street_name VARCHAR2(30),

[code]....

And the following dummy data for these:

1 insert all
2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF')
4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH')
6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)

[code]....

So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.

So I try, very simply, this:

SQL> get get_xml_data
1 create or replace function get_xml_data(p_id in number) return clob is
2 Result clob;
3 begin
4 select xmlroot(xmlelement("HomeData",

[code]....

And, alas, i'm greeted by this:

SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION GET_XML_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
SQL>

I've tried to redo the query in several different ways but so far nothing.

View 2 Replies View Related

SQL & PL/SQL :: How To Use Dbms_profiler For A Procedure Returning Sys_refcursor

May 23, 2011

using dbms_profiler for a package having procedure returning sys_refcursor.

I have 2 procedure in a package. let say, proc1 and proc2 ( also proc1 is called inside from proc2)

proc1 has no parameter

proc2 has 2 paramter proc2(p_num in integer, p_data out sys_refcursor) [ in which we pass the p_num (ex: 1) and it run to get the data from the tab le and return that data through sys_refcursor.

for proc1, I am able to use dbms_profiler as below and it is working fine

-------------
DECLARE
l_res BINARY_INTEGER;
BEGIN
l_res := DBMS_PROFILER.start_profiler(run_comment => 'package.proc1: ' || SYSDATE);
package.proc1;
l_res := DBMS_PROFILER.stop_profiler;
END;
/
-------------

but for proc2, i am unable to use dbms_profiler, how to use dbms_profiler for procedure returning sys_refcursor.

i tried using as below:
-------------------------------
DECLARE
l_res BINARY_INTEGER;
P_NUM NUMBER;
P_DATA SYS_REFCURSOR;

[code]....

getting error: PLS-00312: a positional parameter association may not follow a named association

View 1 Replies View Related

SQL & PL/SQL :: Procedure Returning No Data Found

Dec 13, 2011

I have a stored procedure that is returning no data. I have read this is most common in stored procedures that use a SELECT INTO that tries to put a null in the variable.First, the stored procedure (from SQL Developer) then th execute and error.

PROCEDURE prc_add_address (addr_id OUT integer, addr_type_id IN integer, addr_line_1 IN varchar2,
addr_line_2 IN varchar2 := null, addr_line_3 IN varchar2 := null,
prov IN varchar2 := null, zip_id IN number,
country_cd IN varchar2 := 'USA', addr_start_date IN date,
addr_end_date IN date := null, changed_by IN varchar2,
changed_date IN date, channel_txt IN varchar2 := null)

[code]....

The sad conclusion:

Error starting at line 1 in command:
declare addrid integer := 0;
BEGIN
pkg_vic_person.PRC_ADD_ADDRESS (addrid, addr_type_id => 1, addr_line_1 => '351437 Tall Blvd', zip_id => 14906, addr_start_date => '01-FEB-2011', changed_by => 'RS', changed_date => sysdate);

[code]....

View 6 Replies View Related

Precompilers, OCI & OCCI :: Passing Array Of Structure To Oracle Procedure And Returning Back?

Apr 29, 2010

I created a table and some type definitions in oracle 10.2.0.3 DB as follows. There is also a procedure that I defined and is shown below.

SQL> create table test_proc
(
test_name varchar2(10),
test_ver number (3,3),
active_flag number(2)
);

Table created.

SQL> select * from test_proc;
TEST_NAME TEST_VER ACTIVE_FLAG
---------- ---------- -----------
AFP 1.5 2
PSA 11.89 0
EHIV 99.5 3
aHAVM 1.45 9

[code]....

Now from Pro*C function I want to pass a similar array of structure to this procedure and return it via the out parameter of the procedure back to Pro*C. How do I do it?

I am using the attached program but its giving me compiler error as follows..

Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);
1
PLS-S-00306, wrong number or types of arguments in call to 'PROC_MODIFY_TDEFS'
Error at line 31, column 1 in file sample.pc
proc_modify_tdefs (:in_tdefs,:out_tdefs);

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Getting ORA-06502 While Using DBMS_LOB?

Oct 17, 2011

I was trying to insert some data in a table from a file. I am using DBMS_LOB for that.

My code looks like below -

--Create table t ( a number(10), b number(20), c varchar2(30), d varchar2(30), e varchar2(60));

set serveroutput on
declare
l_bfile bfile;

[Code]....

But getting error, after first row insertion. Error is -

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 20

It insert the first row successfully, but in the event of second row formatting, its returning error. I found the next time while its picking up the column "A", its adding some new line charecter in it. I tried to replace before assigning it to rec.a, but didn't work.

View 15 Replies View Related

Auditing Lob Column Changes When Working With Dbms_lob?

Oct 26, 2010

It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package.

As it stated in Oracle documentation:

QUOTE Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.

I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?

View 4 Replies View Related

SQL & PL/SQL :: Execute Privileges To DBMS_LOB.SUBSTR Function

Mar 15, 2011

I am trying to execute the PL/SQL block below:

DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.

I get the below error:

wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.

View 2 Replies View Related

Server Administration :: Auditing Lob Column Changes When Working With Dbms_lob

Oct 26, 2010

It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package. As it stated in Oracle documentation:

Quote:Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.

I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?

View 1 Replies View Related

SQL & PL/SQL :: Returning Old Value During Update?

Jan 2, 2012

In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.

Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.

View 9 Replies View Related

SQL & PL/SQL :: Returning Row With Same Values

Mar 22, 2012

create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)

My requirement is to return

id key
1 10
1 10

because both their id and key are same

View 2 Replies View Related

PL/SQL :: Returning Same Value Set In A Group

Jan 24, 2013

I have the below requirement,

i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),

or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),

View 2 Replies View Related

PL/SQL :: Returning A Single Row

Jun 5, 2013

I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:

ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order

select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;

but this seems to return more than one entrty:

table date below:
drop table employee_list;
drop table employee_historic;
[code]...

What is the best way to return a single row, if the primary key is not available / applicable.

View 9 Replies View Related

Oracle Returning Max Value And Value Prior To Max?

Nov 5, 2012

I have this query to return to me the latest case note, now I would like to add the date prior to the latest case note.

select case_notes.applicant_id,
reg.program_code,
reg.last_name,

[Code].....

View 1 Replies View Related

Returning Multiple Rows

Mar 1, 2011

I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.

CREATE OR REPLACE PROCEDURE proc_query
DECLARE
TYPE all_dest IS TABLE OF NUMBER;
destIds all_dest;
BEGIN
SELECT dest_id from sb_packet WHERE src_id = 32;
RETURNING dest_id bulk collect into destIds;
END;

View 3 Replies View Related

SQL & PL/SQL :: Why To_Date Not Returning Whole Year

Jan 16, 2011

I run following query.

select to_date(sysdate,'DD/MM/YYYY') Dte FROM DUAL

Result is

Dte
--------
01/17/0011

why it is not returning '01-17-2011'?

View 3 Replies View Related

SQL & PL/SQL :: Function Returning Top Value If Not Exists Then Next One?

Nov 20, 2010

how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :

If I've got table

customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11

For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11

Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...

but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.

View 9 Replies View Related

SQL & PL/SQL :: Returning First Rank After Aggregating

Mar 27, 2013

I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...

select hour,
calls
from
(
select hour,
calls,
rank() over (ORDER BY calls desc) as ranking
from
(

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Merge With Returning Clause

Feb 21, 2013

Does the Oracle 11g supports 'RETURNING INTO' clause in MERGE statement? if it was not available is there any alternate to achieve the same ?

View 3 Replies View Related

SQL & PL/SQL :: Regexp_SUBSTR On Array Returning No Value

Oct 1, 2013

I'm trying to query an array where we will have mathnames that have the follow format: variablemathname[00000]

have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.

In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]

I tried using the escape before my pattern as I know that [ is a metacharacter but I receive the same results.i'm trying to use to get the expression correct.

SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT
FROM DUAL;

My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.

View 10 Replies View Related

SQL & PL/SQL :: Returning Values With MORE Option

Jan 19, 2012

Imagine I have the following scenario:

CREATE TABLE AAA_PLACE
(COD_PLACE NUMBER,
PLACE_NAME VARCHAR2(50 BYTE)
)
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (1, 'Munich');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (2, 'Lisbon');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (3, 'Monaco');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (4, 'Madrid');
insert into AAA_PLACE (COD_PLACE, PLACE_NAME) values (5, 'Milan');

Imagine I want to retrieve those records in only 3 distinct rows, like this:

Munich
Madrid
Others

Is there any way I can do this with an simple Statement?

View 6 Replies View Related

SQL & PL/SQL :: Returning Sysdate Even Run On Sunday

Apr 4, 2010

I have following code. It is always returning sysdate even run on Sunday.

declare
vDate date;
begin
if to_char(sysdate,'Day')='Sunday' THEN
vDate := sysdate-3;

[Code]...

if there is something missing or wrong.

View 2 Replies View Related

Simple Update Query Not Returning More Than One Row?

Sep 9, 2009

I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:

update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)

It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.

View 2 Replies View Related

Subquery Returning Multiple Rows

Oct 13, 2009

I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)

Here is my query:

Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;

The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)

Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.

View 2 Replies View Related

Stored SQL And Returning Data In Different Units?

Jan 30, 2007

I work on a client-server application, where users need to be able to run rather complex queries.

We currently have most of the queries defined in views on the Oracle database server and the client application simply downloads the data (i.e. SELECT * from example_view). This is good for us as we can maintain these queries without releasing new versions of the client tool.

However we have some queries implemented by a colleague that have caused a lot of trouble (efficiency and quality) and these are stored client-side.

The issue I have is that these client side queries can return records in different units (i.e. in standard cubic metres, or barrels of oil etc), as the SQL is defined at runtime on the client, and I want to know the best way to replicate this with SQL stored server-side.

The client-side SQL has column definitions such as: SELECT oil_production * decode(&unit,'Nm3',.948,'Sm3',1,'MMBOE',6.0924,1) ... The &unit parameter is then replaced by the appropriate text (i.e. 'Sm3') before the query is sent to Oracle.

Is there anyway to pass variables to server-side SQL and get a recordset back? I don't think PL/SQL procedures can do this? and views can't contain bind variables.

View 1 Replies View Related

Returning A Table Without Creating Objects?

May 4, 2010

I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:

Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT
(...);
CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType;
PROCEDURE tmp_proc (...,
out_param_resultset OUT g_cursor_type )
....
OPEN out_param_resultset FOR
SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType));
....

How do I return the table (referenced by a cursor) without creating objects?

View 1 Replies View Related

SQL & PL/SQL :: Dynamic SQL Returning Clause Into Collection

Nov 8, 2010

I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.

I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'

s@ORCL> select * from t_dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 comp NEW YORK
20 Compt DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 Comp Ahaaa
80 data ab
80 data ab
80 data ab
80 data ab

9 rows selected.

s@ORCL> ed
Wrote file afiedt.buf

1 declare
2 type tp_dept is table of number;
3 arr_dept tp_dept;
4 begin
5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]'
6 returning into arr_dept;
7 dbms_output.put_line('The count is '||arr_dept.count);
8* end;
s@ORCL> /
returning into arr_dept;
*
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored

View 4 Replies View Related

SQL & PL/SQL :: Single Return Sub Query Returning More Value

Aug 21, 2013

i am trying to update below statement that has multiple rows but giving the error like :

update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))

error:- single return sub query return more value.

how to write update join query base on multi[ple ow.

View 8 Replies View Related

SQL & PL/SQL :: Returning Updated Rows With REF CURSOR?

Sep 27, 2013

The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?

-- Incorrect indicative example 1.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;

-- Incorrect indicative example 2.

DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;

View 4 Replies View Related







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