Current position:  Home > Default > SQL Query ( PL/SQL function body returning query ) page

SQL Query ( PL/SQL function body returning query ) page

Time:October 11
Advertisement
Hello Friends,
I have a page with type SQL Query ( PL/SQL function body returning query ).
I have written a pl/sql block that returns a sql query - select statment.
Some times i am getting no data found error - does it got to do with the variable that stores the query .
=======================
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Error ERR-1101 Unable to process function body returning query.
OK
=====================
When the query is returned with records where exactly the records are stored is it in the variable declared in pl/sql block or with the Oracle Apex implicit cursor.
Here's the pl/sql block ..
The query is generated while the user is navigating through pages ..
====================
declare
l_return_stmt varchar2(32767);
l_select varchar2(32000);
l_from varchar2(32000);
l_where varchar2(32000);
l_order_by varchar2(32000);
l_stmt_recordcount varchar2(32000);
l_recordcount number ;
begin
l_select := 'select '||:P10_VARLIST1||:P10_VARLIST2||:P10_VARLIST3
||:P10_VARLIST4||:P10_VARLIST5;
l_from := ' from '||:P10_RELATION;
if length(:P10_WHERE) > 0 then
l_where := ' where '||:P10_WHERE;
else
l_where := '';
end if;
if length(:P10_ORDER_BY) > 0 then
l_order_by := ' order by '||:P10_ORDER_BY;
else
l_order_by := '';
end if;
l_return_stmt := l_select||l_from||l_where||l_order_by;
:P10_STMT := l_return_stmt;
return l_return_stmt;
end;
=============================
Appreciate your help in this regard.
thanks/kumar
Edited by: kumar73 on Apr 22, 2010 6:38 AM
Advertisement
It looks like the query string you are trying to pass back exceeds the 32K limit for a varchar. Where this is happening is kind of difficult to tell as it could be any number of points, and also depends on what you are passing into the process via page items.
I would first try to establish what combination of page items causes this error to occur. Then, starting from the bottom and working your way backwards, I would start 'switching off' some of the items you use to build your query until it breaks again, thus establishing which part is leading to the error.
Also, I'm not sure what :P10_STMT is doing (are you maybe using this for visiblity of the query created)?
It looks like the query string you are trying to pass back exceeds the 32K limit for a varchar. Where this is happening is kind of difficult to tell as it could be any number of points, and also depends on what you are passing into the process via page items.
I would first try to establish what combination of page items causes this error to occur. then, starting from the bottom and working your way backwards, I would start 'switching off' some of the items you use to build your query until it breaks again, thus establishing which part is leading to the error.
Also, I'm not sure what :P10_STMT is doing (are you maybe using this for visiblity of the query created)?

PL/SQL function body returning SQL query - ORA-06502: PL/SQL: numeric or value error

2015-10-11

I'm attempting to dynamically generate a rather large SQL query via the "PL/SQL function body returning SQL query" report region option.  The SQL query generated will possibly be over 32K.  When I execute my page, I sometimes receive the "O

Error in report when executing pl/sql function body returning sql query.

2015-10-11

Hi, I have used the pl/sql function body returning sql query for creating a report. I have created a datepicker( P10_TASK_DATE) which can be submitted.The code is as below DECLARE v_sql varchar2(3000); BEGIN if :P10_TASK_DATE is not null THEN v_sql:=

Region source (PL/SQL function body returning SQL query)

2015-10-11

Hi, guys. Here is what i try to do: Create a region of type SQL Query (PL/SQL function body returning SQL query). In the source area i tried to put this: DECLARE v_new VARCHAR2(10); v_SQL varchar2(32000); BEGIN v_new := :P102_HDN_NEW; -- htp.p(v_new)

Multiple Select List looping thru PL/SQL function body returning SQL query

2015-10-11

Hi, I have a Multiple Select List. I want to loop through the values from the Select List and process them in a PL/SQL function body returning a SQL query. Currently, my code only returns the SQL SELECT results of one item in the select list. How do

SQL query (pl/sql function body returning query) performance issue

2015-10-11

I create my report in building my sql instruction with ( SQL Query pl/sql function body returning sql query ); My report take more than 20 seconds however if i did a cut and paste with the sql code in TOAD the same sql take 1 second. To try to discov

SQL Query (pl/sql function body returning Sql query)

2015-10-11

Hi All, I have created a region of "SQL Query (pl/sql function body returning Sql query)" type and it is working fine , but when I am migrating(export /import) this application from development to systest environment , It gives error for this re

Pl/sql function body returning SQL query - Print function

2015-10-11

Hello all, I have pl/sql function body returning SQL query for my reports for my new project that I am developing. We dont have any BI tool or anything for APEX so we use Oracle reports to get the same reports to be printed in PDF format. I had been

Query on Reoprts-pl/sql function body returning sql query

2015-10-11

Hi, I am facing a starnge problem.. and would like to know the reason for it. The situation is as follows: I have a report (pl/sql function body returning sql query based). My query is as follows: declare l_query1 varchar2(2000); begin if (:P102_min_

SQL Query (PL/SQL function body returning SQL query) when using to_char

2015-10-11

we are trying to build a report page of Type SQL Query (PL/SQL function body returning SQL query). our query is so simple, we need to extract the month from the recording_date column. declare l_query varchar2(1000); begin l_query:='select to_char(rec

SQL Query (PL/SQL Function Body returning SQL query) doesn't return any row

2015-10-11

I have a region with the following type: SQL Query (PL/SQL Function Body returning SQL query). In a search screen the users can enter different numbers, separated by an ENTER. I want to check these numbers by replacing the ENTER, which is CHR(13) ||

Using Package to produce pl/sql function body returning sql query Report

-0001-11-30

I have existing code that we want to use in building reports in APEX. We are needing to modify it slightly to handle some new requirements, but would like to use them in reports based upon SQL query (pl/sql function body returning sql query) function

Sql query(PL/SQL function body return SQL query) with in(x,y,z) condition

-0001-11-30

Hello, I've set up a region definition of type "SQL Query(PL/SQL function body return SQL query). In my query I make use of an "in" condition. When I populate :P755_INC_BARG_UNIT with a value of 0F (no quotes), I get the correct count. Howe

Analyze: PL/SQL function body returning an SQL query

-0001-11-30

I need to obtain the final SQL returned by the PL/SQL function. I will be using this final SQL in a procedure. Please provide some advise on how to obtain the SQL. I have already looked in the DBMS_SQL package but I am not sure if that is the right p

PL/SQL function body return sql query, no data found problem

-0001-11-30

Hi all, we are trying to build a dynamic report based on item selection by user. we are using SQL Query (PL/SQL function body returning SQL query). However when a user change the item and submit the page . The following error appears. ORA-01403: no d

Querying PL/SQL function which returns SYS_REFCURSOR

-0001-11-30

Hello: I have a PL/SQL function with return type of SYS_REFCURSOR. In SQLPlus, I can query it either as var c refcursor exec :c := func(...) print c or select func(...) from dual; Running the latter in Eclipse 3.3 with oracle DTP extensions installed

PL/SQL function body returning SQL - report error:ORA-01403: no data found

2015-10-11

Hi, I am working on Application Express 4.0.2.00.06, and 11G database. I have a problem with classic report area of type - PL/SQL function body returning SQL query. Query works if I define region area as - Use Generic Column Names (parse query at run

How to use a select list value in a PL/SQL function body returning SQLquery

2015-10-11

Hi Friends, I have a select list P6_TEST with values 'nav' anf 'jyo'. I am trying to create a report using "SQL Query (PL/SQL function body returning SQL query)". In my report query can i check if P6_TEST='nav' and do something like the code sho

Subquery not allowed - PL/SQL Function Body returning bolean

-0001-11-30

Is subquery is not allowed in PL/SQL Expresion.???? I am using the following query in one of my derived report column but I am getting the error. BEGIN if :COL1<>'abc' and :COL2 in (select deptno from dept1) then return 1; elsif :COL1<>'abc' a

Conditional display using Pl/SQL function body returning a boolean

-0001-11-30

I am having issues with conditional display of a report. I have to select lists: p50_facility and p50_supervisor. I have entered the below pl/sql function body returning a boolean Begin if (:p50_facility is null or :p50_supervisor is null) THEN Retur