Current position:  Home > Default > Problem with query in cursor (Oracle 8i)

Problem with query in cursor (Oracle 8i)

Time:October 11
Advertisement
Hi all,
Following query works perfectly fine if I test it in my development environment
SELECT dt,
      (SELECT 'Y'
         FROM dual
        WHERE EXISTS (SELECT 'x'
                        FROM top_days t
                       WHERE t.date = dt))
FROM (SELECT to_date(p_date_start, 'dd-mm-yyyy') + LEVEL - 1 dt
                   FROM dual
                 CONNECT BY LEVEL <=
                            to_date(p_date_end, 'dd-mm-yyyy') - to_date(p_date_start, 'dd-mm-yyyy') + 1
                        AND PRIOR dbms_random.value IS NOT NULL) --workaround for oracle 8i
                top_days t
WHERE dt = t.date(+)
ORDER BY dt;However, when I try to include it in my package (where it's used in a cursor) I get a PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + mod null others <an identifier> etc)
CURSOR c_holidays IS
           SELECT dt,
                (SELECT 'Y'
                   FROM dual
                  WHERE EXISTS (SELECT 'x'
                           FROM top_days t
                          WHERE t.date = dt))
           FROM (SELECT to_date(p_date_start, 'dd-mm-yyyy') + LEVEL - 1 dt
                   FROM dual
                 CONNECT BY LEVEL <=
                            to_date(p_date_end, 'dd-mm-yyyy') - to_date(p_date_start, 'dd-mm-yyyy') + 1
                        AND PRIOR dbms_random.value IS NOT NULL) --workaround for oracle 8i
                top_days t
          WHERE dt = t.date(+)
          ORDER BY dt;Is there some special thing I don't know about Oracle 8i that prevents me from using this query in a cursor? Or am I just missing something blatantly obvious?
Note: p_date_start and p_date_end are parameters that are filled in the function
FYI: Oracle 8i release 8.1.7.4.1 and PL/SQL Release 8.1.7.4.0
Advertisement
Hi,
You can do a join instead of using a scalar sub-query:
SELECT        a.dt
,       CASE
          WHEN  t.dt IS NOT NULL
          THEN  'Y'
       END          AS in_top_days
FROM        (
          SELECT      to_date (p_date_start, 'dd-mm-yyyy') + LEVEL - 1 dt
                FROM dual
                CONNECT BY      LEVEL <=
                                 to_date(p_date_end, 'dd-mm-yyyy') - to_date(p_date_start, 'dd-mm-yyyy') + 1
                        AND      PRIOR dbms_random.value IS NOT NULL --workaround for oracle 8i
       )          a
,         top_days     t
WHERE        a.dt      = t.dt (+)
ORDER BY  a.dt; 
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), a few sets of parameters (p_date_start and p_date_end), and the results you want from that data for each set.
Explain, using specific examples, how you get those results from that data.

SSRS 2008 Using Stored Procedure with SysRef Cursor Oracle

-0001-11-30

Hi, I am new to SSRS. I have a ssrs procedure which runs to Return Result of Select Query. ( In form of SYS REF Cursor in Oracle ). When I am trying to see the Value in Test Window. It is showing Output. But when I m Running the Report in the environ

View with columns based on function - problem with query

2015-10-11

Hi, I'm using Oracle 9i; I've created a view which has columns based on a table columns (multiple columns from 1 table) and funtion (multiple columns based on 1 function). The function takes ID as the first argument and name of the column to determin

Problem in SQL with CURSOR( ) ,Why the CURSOR did not work?

2015-10-11

hi All: I have a problem in SQL with CURSOR. The data is as the attachments. Here is the SQL statement as follow: SELECT A.WADCTO,A.WADOCO,B.IGCOST,CURSOR (SELECT X.IGLITM FROM F3102 X WHERE X.IGDOCO=A.WADOCO AND X.IGCOST IN ('B1','D1','C3')) AS DETA

Problem in querying synonym using cachedRowSet & cachedDataProvider

-0001-11-30

Hi.... I have some trouble using cachedRowSet and cachedDataProvider combination. The problem was occured when I refreshed chachedDataProvider, my table component in pages show "No items found.", whereas... i'm sure there is nothing wrong with m

Create a query builder in oracle Apex

-0001-11-30

Hi We have a request to create a query builder in oracle Apex particularly.But it provide the application builder and Utilities when we create the apex workspace.My question is any possibility are to make the application builder and utilizes as invis

Looping a query without cursor

-0001-11-30

Hi everybody i want to know that is there is any way by which we can loop through Resultset of a query without using cursor for loop in plsql vipulNo. Reason: Any SQL statement in PL/SQL is a cursor. Any rows "obtained" from a cursor, is fetched

How to Query a Cursor and retrieve a selected records

-0001-11-30

Hi, I'm using a ScrollableCursor for pagination and it is working fine. Now i want to filter that cursor to get a selected records So i need to Query a cursor. Is it possible to Query a Cursor using Oracle Toplink. looking an api i found cursor.setSe

Query works in Oracle Client but not JDBC

2015-10-11

The query is as follows: SELECT COUNT(*) THE_COUNT FROM sentorders WHERE ordersentdate BETWEEN '15-JAN-01' and '15-JAN-03';When I run this query in the Oracle client (SQL+) it tells me that THE_COUNT is 4 which is correct but when I use the following

Installation problem of ISSUE_TRACKER_1.6 on Oracle Express XE

2015-10-11

Sorry, but I'm new to HTMLDB, and have seen similar problems listed on the forum, but they have not given me insight into my problem. I am running Oracle XE beta on Windows. I only have one workspace and user at the moment. I imported the application

Installation problem with Siebel8.0 on Oracle Enterprise Linux 4 system

2015-10-11

We are having installation problem with Siebel8.0 on Oracle Enterprise Linux 4 system. Using interface, when installation come up to "Installing Wizard" window where Gateway Name Server Configuration window should take place, everything just fre

Query off of Oracle using WinSql - Need help with query

2015-10-11

I am trying to query off of Oracle using program WinSql. I have a table(tticpr200110) that has the following sample data: ITEM     CODE     T$AMNT 23500076 ACL     .0049 23500076 APM     0 23500076 APO     .0093 23500076 EXP     .0001 23500076 RES   

Problem with date format from Oracle DB

2015-10-11

Hi, I am facing a problem with date fields from Oracle DB sources. The date format of the field in DB table is 'Date base type is DATE and DDIC type is DATS'. I mapped the date fields to Date characters in BI. Now the data that comes to PSA is in wei

A problem regarding set up of Oracle Lite 3.6.0.2.0 on Win 95, with JDK 1.1.8 &java 2

2015-10-11

A problem regarding set up of Oracle Lite 3.6.0.2.0 on Win 95, with JDK 1.1.8 and Java 2 SDK ( Ver 1.3 Beta) After the installation of Oracle Lite 3.6.0.2.0 on a laptop (with WIN 95 OS), When I run Oracle Lite Designer from start menu, I receive foll

Problem loading Query Designer Add-in

2015-10-11

Hi All, Can anyone please help me on my below issue. Whenever user is trying to start a new query in Essbase , he is getting below error message. It will not allow user to build a fresh Essbase pull Problem loading Query Designer Add-in. Workbook Act

Query based on Oracle and MS Access connections

2015-10-11

I hope this is in the correct spot. I am using Oracle SQL Developer to extract data from some Read Only tables in Oracle. What I would like to do it have a list of numbers in a MS Access database to select the same number from Oracle tables, then exp

Query Builder in Oracle 9i

2015-10-11

We use the tool Query Builder in our Oracle BD version 8.0.5.1. We have a development version of Oracle 9i and I don't see anything like Query Builder. What product is replacing Query Builer in Oracle 9i ?? THANKSVersion 1.5.1 Build MAIN-5440 for Mac

Problem in loading Ntriple to Oracle

2015-10-11

Hello, Can some one please point what could be wrong in loading of Data in Ntriple format using the Code provide by Oracle to convert RDF data in N-Triple format to Oracle's RDF storage type, SDO_RDF_TRIPLE_S in Oracle10g R2. called TestNTriple2NDM.j

Problem in query with the u0091Document numberu0092

2015-10-11

Hi, I got problem in query with the ‘Document number’ There are three columns in the cube 1) Document number 2) Country 3) Count In the cube there are multiple entries for same document number as below.       Document number            country       

Urgent problem in connecting servlet and oracle

2015-10-11

Hi I have problem in connecting servlet with oracle.Kindly let me know what all the classes i should include in classpath to get connected.Where will i find the oracle driver.Kindly help me to do this. With regrds Vaijuhttp://www.oracle.com/technolog