SQL & PL/SQL :: How To Obtain Data Returned By Function When Executed Using Dynamic Query

May 25, 2011

I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?

The code I have is as follows:

CREATE OR REPLACE PACKAGE qi_test IS

TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........

I want to be able to execute the above function using dynamic SQL. Initially tried:

DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......

...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:

f2_data := qi_test.get_f2_data;

...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

Oct 25, 2010

I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.

UPDATE transaction SET loannum = lpad(loannum,12,'0')

View 15 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

PL/SQL :: Pipelined Function With Dynamic Query?

Nov 12, 2013

 Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.  

create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,              

req_id number);    type t_disc_req is table of r_disc_req;    function F_GetDiscReq return t_disc_req pipelined;     procedure P_ProcessDiscReq;end;  CREATE OR REPLACE PACKAGE BODY test13_pkgAS   FUNCTION F_GetDiscReq      RETURN t_disc_req      PIPELINED   IS      lo_disc_req   r_disc_req;   BEGIN      FOR r_row IN (SELECT disc_line_id, req_id                      FROM edms_disc_lines_stg                     WHERE ROWNUM < 10)      LOOP         lo_disc_req.disc_line_id := r_row.disc_line_id;         lo_disc_req.req_id := r_row.req_id;         PIPE ROW (lo_disc_req);  
   
[code]...

View 11 Replies View Related

SQL & PL/SQL :: Pipeline Function - No Rows Returned

Aug 11, 2011

I need to create a function where in data from 5 rows is clubbed into one row. Like this I have around 425 rows which should be clubbed to 85 rows. Requirement is similar to pivot but not exactly like a pivot as different columns need to be taken from those 5 rows. This is for reporting purpose in order to get data in the desired report format.

SQL mentioned below works fine. It does return data.When below code is used as a normal procedure with OUT parameter as Index by table of Record type code works fine. It returns data. Functionality is met. But when used as a pipeline function, it returns no data.

Below code gets compiled but returns nothing. I didn't find anything on Google or any website for same.

CREATE TYPE r_report_mth_rec_obj AS OBJECT (
acct_num VARCHAR2 (20),
acct_name VARCHAR2 (80),
fund_group VARCHAR2 (80),
fund_type VARCHAR2 (80),
share_class_code VARCHAR2 (10),
share_class_description VARCHAR2 (20),
curr_code VARCHAR2 (10),
[code]...

View 16 Replies View Related

SQL & PL/SQL :: How To Handle Ref Cursor Returned From Another Function

Dec 16, 2011

declare
type osd_refone is ref cursor;
osd_ref osd_refone;
l_status number;
[code]......

abc_reports in this pack "ab_report" it is the function it having the ref cursor as out parameter . when am executing the above anonymous block am getting the below error,so how can i print the out ref cursor data in my block.

ERROR at line 8:
ORA-06550: line 8, column 12:
PLS-00221: 'OSD_REF' is not a procedure or is undefined
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored

View 6 Replies View Related

PL/SQL :: How To Free Temporary LOB Returned By A Function

Apr 4, 2013

I have a business need to have a db function that would construct and return a (temporary) CLOB value.

here is its sample code:

create or replace package PKG_TEST_CLOB
as
function FN_TEST_TEMP_CLOB
return clob;

[code]....

when this function is invoked from a SQL Statement...

***
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual;
***

... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1

when this function is invoked via a PL/SQL block...

***
declare
l_clob clob;
begin
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual;
end;
/
declare

[code]....

... the counter doesn't budge

In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.

View 0 Replies View Related

SQL & PL/SQL :: Order Of Bytes Returned By Dump() Function In Reverse Key Indexes?

May 28, 2013

Oracle Version: 11gR2: 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 X86_64

Currently, I'm reading the online book Oracle Concepts, Chapter 3: 3 Indexes and Index-Organized Tables, section: Reverse Key Indexes in order to understand this topic.

As I understand for each pair of (key, rowid) in the index structure, the rowid for each row in the table obviously remains the same but the bytes of the key are reversed before the key is stored. So for example on a 32 bit machine (just an example) a key = 10 AB CD EF will be stored as FE DC BA 01 , am I right?

According to the documentation, this becomes interesting in RAC environments in order to remove a hot spot from the index (when multiple instances repeatedly modify the same block) with the disadvantage that in some cases there cannot be Index Range Scan any more as data in the index is not sorted by column key when it is stored.

I was just curious to see how bytes of each key are reversed and after a bit googling I found an article where Tom Kyte shows with an example by using dump function (which as I understand gives the internal representation of a given expression) the difference in the sequence of bytes. Here is the link

[URL]

So based on his instructions I tried to do my own test, yet I don't get the same result, that is, the bytes are not reversed for me once we rebuild the index by REVERSE key word.

I'm going to write down here the test that I did, where is/are my error(s)

Test Case:
(I use a copy of the employees table in hr sample schema)
SQL> CREATE TABLE emp_test AS SELECT * FROM hr.employees;
Table created.
SQL> CREATE INDEX emp_test_idx ON emp_test(first_name);

[code]...

Now, a test SQL Query using the index we've just defined (just for giving an example)

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

FIRST_NAME DUMP_RESULT
-------------- ----------------------------------
Kelly Typ=1 Len=5: 4b,65,6c,6c,79

[code]...

So, according to the above Execution plan, Oracle does an Index Range Scan using the index that I defined on my table that is, emp_test_idx. According to the output of dump, the key (first_name) in that index is stored (in terms of bytes) as 4b,65,6c,6c,79

SQL> SELECT chr(to_number('4b', 'xx')) ||
2 chr(to_number('65', 'xx')) ||
3 chr(to_number('6c', 'xx')) ||
4 chr(to_number('6c', 'xx')) ||
5 chr(to_number('79', 'xx')) first_name
6 FROM DUAL;

FIRST_NAME
--------------------
Kelly

Which as we can see corresponds to the first name 'Kelly', the first name we specified in the above SQL query.

Now let's rebuild the index

SQL> ALTER INDEX emp_test_idx REBUILD REVERSE;
Index altered.
SQL>

Once the index keys have been reversed, I run the very same query in order to see the difference

SQL> SELECT first_name,
2 dump(first_name, 16) as dump_result
3 FROM emp_test
4 WHERE first_name = 'Kelly';

[code]...

So the second time after the index has been reversed, I still get the very same sequence of bytes, that is, 4b,65,6c,6c,79, whereas I expected to get 79,6c,6c,65,4b (that is, the reversed order of the initial bytes sequence)

View 8 Replies View Related

PL/SQL :: ORA-01840 - Executed Function - Throw Error?

Mar 7, 2013

I cerated a function with two date parameters. I am using these parameters in query. The function created successfully.But when i executed this function, it throw following error:

ORA-01840: input value not long enough for date format in function

I searched on internet, but not found the proper solution. n with example.Table column is varchar2 type and values store in 'RRRR/MM/DD' format.

View 3 Replies View Related

SQL & PL/SQL :: Get Total Number Of Rows Returned By Query?

Sep 17, 2010

DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;

[Code].....

How can I get the total number of rows returned by the query?

I want to be able to check omething like c1.ROWS = 0

View 4 Replies View Related

Storing List Of Values Which Are Returned By A Select Query?

Oct 9, 2012

I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.

how to proceed further and the ways how can I write this requirement.

View 1 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How Can I delete the returned two rows?

1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Use Records Returned By Query As Column Names In A Select?

Jul 3, 2011

is it possible to use the records returned by a query as column names in a select query.

select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

View 3 Replies View Related

SQL & PL/SQL :: Same Query Taking Different Time When Executed Through Different DBs?

Feb 19, 2013

I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.

View 6 Replies View Related

SQL & PL/SQL :: Record Count Mismatch In Dataset And Query Executed

Oct 14, 2011

I am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.

View 2 Replies View Related

PL/SQL :: Characters Is Greater Than 4 / Actual Data Without Lpad Should Be Returned

Oct 15, 2012

For one of my row its returning as below lpad('abcdef', 4 , 'Z') returning abcd

but instead of this if no of characters is greater than 4 i want the actual data without lpad should be returned.

View 7 Replies View Related

SQL & PL/SQL :: Returning Value Of Dynamic Select In Oracle Function?

Jul 20, 2011

I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!

CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)

--Actually any table can do

CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);

[code]....

B.S. I didnt delete the commented lines to have your review comments.

View 21 Replies View Related

PL/SQL :: Using NVL Function In Dynamic SQL / ORA-00936 / Missing Expression

May 8, 2013

I have created a procedure using the Dynamic SqL and while using the NVL() getting the following error . ORA-00936: missing expression.

The query I have written as

SQL_Txt:=' INSERT INTO VF.tblCData (A, B, C, D, E, F,G,H,I,J)
SELECT '||l_A||',
'||l_B||',
'||l_C||',
'||l_D||',
NULL ,
'||L_F||',

[code]....

For Param1 I have data for one execution and Param2 and Param3 is null for that execution.While executing the same I am getting below

INSERT INTO VF.tblCData (A, B, C, D, E, F,G,H,I,J)
SELECT 25,
1,
7,
6,
NULL ,
5,

[code]....

and error ORA-00936: missing expression is popping up for Param2 and Param3 NVL(,'')

View 10 Replies View Related

SQL & PL/SQL :: Function Returning Table Of Dynamic Structure

Apr 24, 2013

I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies every time it runs i.e,

means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab
as
...
end;

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changed to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))

how to return pl/sql table of dynamic type from func,

View 12 Replies View Related

SQL & PL/SQL :: Dynamic Calling Function With Type Record Parameter?

Jul 23, 2010

I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.

My code (sample):
-----------------
DECLARE
type r_parameters is record
(cd_query cons_query_param.cd_query%type,
cd_usuario cons_query_user.cd_usuario%type,
nr_param cons_query_param.nr_param%type,
vl_param varchar2(2000),

[code].....

View 5 Replies View Related

Application Express :: Dynamic Actions Call A Javascript Function On All Text Items

Feb 21, 2013

On a tabular form I have 50 columns each an input box , (basically 50 weeks of the year)

in these boxes I want to make them numbers only and max length 2

currently I have in the ELEMENT ATTRIBUTES for each column

OnFocus="javascript:this.maxLength=2"; onKeyPress="return numbersonly(this,event)";

( I have a function called numbers only in page 0 )

what I would like to do , to make it more maintainable is to remove all the calls in the element attributes of each column

and put it in 2 dynamic actions how would I go about this ?

I have tried using jquery selector of input:text to call javascript code on both events , Get focus and Key Press for every text box but its not working .

View 5 Replies View Related

SQL & PL/SQL :: Obtain Result Set When Doing Select

May 13, 2010

when in sqlplus I execute:

select * from mytable;

in sqlplus I get the returned rows of this table. But this is not the case when I do in plsql the following:

create or replace procedure myproc is

a varchar2(50);
begin
a := 'select * from mytable';
execute immediate a;
end;

there is nothing I can see. Is there a way to catch this result set and see it on the screen? Something that is not creating new structures like cursors, etc?

View 16 Replies View Related

How To Obtain User Session Info

Jan 26, 2007

How do I get my list of user session info? I thought there was a user_session_parameter view or something? Basically, I did an ALTER SESSION ... and want to verify it was set correctly.

View 8 Replies View Related

SQL & PL/SQL :: Dynamic Column Value Query?

Nov 3, 2010

I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)

CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),

[Code]...

After the Data is loaded, we see data look like the above.

(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.

If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?

Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Obtain Top 2 Departments In Terms Of Total Salary?

Aug 21, 2010

I need to obtain the top 2 departments in terms of total salary.

I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.

DNo Fname Salary
2 Tom 10000
3 Mike 20000
2 Harry 30000

DeptNo DeptName
1 Administration
2 Special
3 Finance

View 6 Replies View Related

SQL & PL/SQL :: Define A Cursor Using Dynamic Query?

Aug 29, 2010

Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.

I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).

View 5 Replies View Related

SQL & PL/SQL :: Using Escape Character In Dynamic Query?

Aug 28, 2012

I'm trying to replace the variable value v_tblname in dynamic SQL. But not able to escape the single quotes character.

DECLARE
v_sqlcols VARCHAR2(2000);
v_sqlcols1 VARCHAR2(2000);
v_tblname VARCHAR2(50) := 'DEPT';
BEGIN
v_sqlcols := q'[SELECT LISTAGG(COL,',' ||CHR(10)) WITHIN GROUP (ORDER BY COL)
FROM (

[code]....

View 5 Replies View Related

SQL & PL/SQL :: How To Get Count On Executing Dynamic Query

Sep 16, 2010

l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';

by using the execute immediate or any other command, how can i check whether the query returned any rows or not?

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Query For Swapping Names

Mar 25, 2011

writing dynamic sql query for swapping names:

the output shuld be like that

input output

a a
a b b a
a b c c b a
a b c d d c b a

i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.

View 13 Replies View Related

SQL & PL/SQL :: Using NULL And DECODE In Dynamic Query

Sep 2, 2011

small piece of PL SQL code. how to make this query.Requirement is that a concurrent program is run with parameters and one of them i_num_org_id is non mandatory so it can come as NULL...Now in an existing code which i have to change, it uses a query as

SELECT xyz
FROM abc_table
WHERE <various conditions>
AND DECODE(i_num_org_id,NULL,1,table.organization_id) = NVL(i_num_org_id,1);

Now with the above way, if the program is run with some value for i_num_org_id or run as normal query (with NULL as the value) inside a PLSQL procedure/package then it runs fine.This query if you run in Toad etc then too it will work fine but if it is made a dynamic SQL and then used as either EXECUTE IMMEDIATE or opened as a cursor then we get a "Missing expression". I created this small anonymous block to test this and this will go into missing expression error

declare
l_num_org_id NUMBER := NULL;
l_temp VARCHAR2(100);
l_sql varchar2(1000);
begin
l_sql := 'SELECT '||''''||'abcd'||''''||'
[code].....

how i can reformat this query so that even if NULL value comes for i_num_org_id then it is handled.I am aware about CASE but that cannot be used in WHERE clause i guess.

View 10 Replies View Related







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