Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL SP (10G)
Dynamic SQL SP [message #277798] Wed, 31 October 2007 12:25 Go to next message
Dan Codispoti
Messages: 3
Registered: April 2004
Junior Member
I am having a proble getting this to run, caan someone assist?

CREATE OR REPLACE PROCEDURE P_PAGED_ATMS
(
P_PAGE_NUM IN INTEGER,
P_PAGE_SIZE IN INTEGER,
P_WHERE_CLAUSE IN VARCHAR2,
P_ORDER_BY VARCHAR2,
P_RC OUT SYS_REFCURSOR
)
IS
V_QUERY_STR VARCHAR2(32767);
V_FIRSTREC INT := (P_PAGE_NUM - 1) * P_PAGE_SIZE;
V_LASTREC INT := P_PAGE_NUM * P_PAGE_SIZE + 1;
BEGIN
V_QUERY_STR := 'SELECT *'
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT a.*, rownum RN '
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT TERMINALID, TERM_FINAME, STREET, CITY, STATE, ZIP_CODE, '
|| CHR(10) || 'COUNTRY, DEPOSITS, NON_SURCHARGE, LATITUDE, LONGITUDE '
|| CHR(10) || 'FROM EXCH_MRCH_ATM_LOC_L6_ALL_CEX' || P_WHERE_CLAUSE || ' ORDER BY ' || P_ORDER_BY || ') a'
|| CHR(10) || 'WHERE rownum <= V_LASTREC)'
|| CHR(10) || 'WHERE rn >= V_FIRSTREC ';
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (V_QUERY_STR) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (V_QUERY_STR, (i * 250) + 1, 250));
END LOOP;
OPEN P_RC FOR V_QUERY_STR;
END;

SQL> exec P_PAGED_ATMS(1,100,' Where State = ''AZ''',' STATE asc, CITY asc, STREET asc',:g_Ref);
SELECT *
FROM
(SELECT a.*, rownum RN
FROM
(SELECT TERMINALID, TERM_FINAME,
STREET, CITY, STATE, ZIP_CODE,
COUNTRY, DEPOSITS, NON_SURCHARGE, LATITUDE,
LONGITUDE
FROM EXCH_MRCH_ATM_LOC_L6_ALL_CEX Where State = 'AZ' ORDER BY STATE
asc, CITY asc,
STREET asc) a
WHERE rownum <= V_LASTREC)
WHERE rn >= V_FIRSTREC
BEGIN P_PAGED_ATMS(1,100,' Where State = ''AZ''',' STATE asc, CITY asc, STREET asc',:g_Ref); END;

*
ERROR at line 1:
ORA-00904: "V_LASTREC": invalid identifier
ORA-06512: at "BILLRSCH.P_PAGED_ATMS", line 27
ORA-06512: at line 1


Re: Dynamic SQL SP [message #277799 is a reply to message #277798] Wed, 31 October 2007 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW posting Guidelines as stated here: http://www.orafaq.com/forum/t/88153/0/

>ORA-00904: "V_LASTREC": invalid identifier
What is V_LASTREC & where is it defined?
Re: Dynamic SQL SP [message #277804 is a reply to message #277798] Wed, 31 October 2007 13:45 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Modify the sp as below.




CREATE OR REPLACE PROCEDURE P_PAGED_ATMS
(
P_PAGE_NUM IN INTEGER,
P_PAGE_SIZE IN INTEGER,
P_WHERE_CLAUSE IN VARCHAR2,
P_ORDER_BY VARCHAR2,
P_RC OUT SYS_REFCURSOR
)
IS
V_QUERY_STR VARCHAR2(32767);
V_FIRSTREC INT := (P_PAGE_NUM - 1) * P_PAGE_SIZE;
V_LASTREC INT := P_PAGE_NUM * P_PAGE_SIZE + 1;
BEGIN
V_QUERY_STR := 'SELECT *'
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT a.*, rownum RN '
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT TERMINALID, TERM_FINAME, STREET, CITY, STATE, ZIP_CODE, '
|| CHR(10) || 'COUNTRY, DEPOSITS, NON_SURCHARGE, LATITUDE, LONGITUDE '
|| CHR(10) || 'FROM EXCH_MRCH_ATM_LOC_L6_ALL_CEX' || P_WHERE_CLAUSE || ' ORDER BY ' || P_ORDER_BY || ') a'
|| CHR(10) || 'WHERE rownum <= '||V_LASTREC||')'
|| CHR(10) || 'WHERE rn >= '||V_FIRSTREC;
-- optional display of dynamic query:
FOR i IN 0 .. CEIL (LENGTH (V_QUERY_STR) / 250) LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (V_QUERY_STR, (i * 250) + 1, 250));
END LOOP;
OPEN P_RC FOR V_QUERY_STR;
END;

Re: Dynamic SQL SP [message #277806 is a reply to message #277804] Wed, 31 October 2007 14:07 Go to previous messageGo to next message
Dan Codispoti
Messages: 3
Registered: April 2004
Junior Member
Thanks for the reply, figured it out just as your email came in.
Re: Dynamic SQL SP [message #277897 is a reply to message #277806] Thu, 01 November 2007 02:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you'll also need to change the code that builds up the query by adding some spaces between some of the clauses, like this:
V_QUERY_STR := 'SELECT * '
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT a.*, rownum RN '
|| CHR(10) || 'FROM '
|| CHR(10) || '(SELECT TERMINALID, TERM_FINAME, STREET, CITY, STATE, ZIP_CODE, '
|| CHR(10) || 'COUNTRY, DEPOSITS, NON_SURCHARGE, LATITUDE, LONGITUDE '
|| CHR(10) || 'FROM EXCH_MRCH_ATM_LOC_L6_ALL_CEX' || P_WHERE_CLAUSE || ' ORDER BY ' || P_ORDER_BY || ') a '
|| CHR(10) || 'WHERE rownum <= '||V_LASTREC||') '
|| CHR(10) || 'WHERE rn >= '||V_FIRSTREC;
Previous Topic: I need return value from PL/SQL Block
Next Topic: Filtering results where nth character is numeric
Goto Forum:
  


Current Time: Wed Dec 07 18:28:06 CST 2016

Total time taken to generate the page: 0.12083 seconds