Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Code [Ref Cursor] Help NOT Urgent
Procedure Code [Ref Cursor] Help NOT Urgent [message #251412] Fri, 13 July 2007 08:53 Go to next message
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
Member

Procedure Code
PROCEDURE pr_insert_st_rtn_arrears_prin (
schm IN VARCHAR2,
gstttab IN VARCHAR2,
pkid IN NUMBER
)
IS
st_rtn_arrears_prin_cursor Pkg_Centstax.st_rtn_arrears_prin_curs_type;
v_tab_st_rtn_arrears_prin Pkg_Centstax.tab_st_rtn_arrears_prin;
-- ERROR TRACING EXCEPTION HANDLING
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
BEGIN
OPEN st_rtn_arrears_prin_cursor FOR 'SELECT
''200506'' || RPAD(TRIM(N_CIRCLE),4,0) ||''1''|| LPAD (ROWNUM, 7, 0) RTN_ARREARS_ID,
RTN.RTN_MASTER_ID,
1 ARREAR_TYPE,
0 SE_FE_FLAG,
''NA'' DESCRIPTION,
GST.PRINCIPAL AMOUNT,
NVL(SUBMISSION_DATE,RECEIVED_DATE) ARREARDATE,
0 ARREAR_TYPE_VALUE
FROM '
|| schm
|| '.'
|| gstttab
|| ' GST, ST_RTN_MAIN RTN
WHERE
(GST.GSTPKID=RTN.GSTPKID AND RTN.GSTFILE = '||gstttab||')
AND NOT (NVL(PRINCIPAL,0) =0)
ORDER BY RTN.STRN, RTN.TAX_PERIOD, RTN.RTN_KIND, RTN.RETURN_TYPE, GST.RECEIVED_DATE';

----------(LOOP TO OPEN THE CURSOR)-------------
LOOP ---START LOOP
FETCH st_rtn_arrears_prin_cursor
BULK COLLECT INTO v_tab_st_rtn_arrears_prin LIMIT 5000;

--P_ARRAY_SIZE; FATCH RECORD BY LIMIT
FORALL rec IN v_tab_st_rtn_arrears_prin.FIRST .. v_tab_st_rtn_arrears_prin.LAST SAVE EXCEPTIONS
INSERT /*+ APPEND NOLOGGING PARALLEL */INTO centstax.ST_RTN_ARREARS
VALUES v_tab_st_rtn_arrears_prin (rec);
EXIT WHEN st_rtn_arrears_prin_cursor%NOTFOUND;
END LOOP;

CLOSE st_rtn_arrears_prin_cursor;
EXCEPTION
WHEN bulk_errors
THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE
( SQL%BULK_EXCEPTIONS (j).ERROR_INDEX
|| ','
|| SQLERRM
(-SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
);
END LOOP;
END pr_insert_st_rtn_arrears_prin;
Execution code
DECLARE
SCHM VARCHAR2(200);
GSTTTAB VARCHAR2(200);
PKID NUMBER;

BEGIN
SCHM := 'CBR';
GSTTTAB := 'GSTT05_0506';
PKID := NULL;

CENTSTAX.Pkg_Centstax.PR_INSERT_ST_RTN_ARREARS_PRIN ( SCHM, GSTTTAB, PKID );
COMMIT;
END;

==============================
Error
there is following error as

ORA-00936: missing expression
ORA-06512: at "CENTSTAX.PKG_CENTSTAX", line 153
ORA-06512: at "CENTSTAX.PKG_CENTSTAX", line 21
ORA-06512: at line 9
=============================
I think there is error due to '||gstttab||' in the where clause Like wise i get the error if I use the default value in the ref cursor query -columns like as:
Cursor gets error
OPEN st_rtn_arrears_prin_cursor FOR 'SELECT
pkid|| RPAD(TRIM(N_CIRCLE),4,0) ||''1''|| LPAD (ROWNUM, 7, 0) RTN_ARREARS_ID,
RTN.RTN_MASTER_ID,
1 ARREAR_TYPE,
0 SE_FE_FLAG,
''NA'' DESCRIPTION,
GST.PRINCIPAL AMOUNT,
NVL(SUBMISSION_DATE,RECEIVED_DATE) ARREARDATE,
0 ARREAR_TYPE_VALUE
FROM '
|| schm
|| '.'
|| gstttab
|| ' GST, ST_RTN_MAIN RTN
WHERE
(GST.GSTPKID=RTN.GSTPKID AND RTN.GSTFILE = '||gstttab||')
AND NOT (NVL(PRINCIPAL,0) =0)
ORDER BY RTN.STRN, RTN.TAX_PERIOD, RTN.RTN_KIND, RTN.RETURN_TYPE, GST.RECEIVED_DATE';

noted the I replaced the 200506 with parameter value pkid.

Kindly help me in correction of my code or guide me in alternative way


From
Muhammad Aurangzaib
itroome@yahoo.com
+923009737559

[Updated on: Fri, 13 July 2007 09:19] by Moderator

Report message to a moderator

Re: Procedure Code [Ref Cursor] Help too Urgent [message #251413 is a reply to message #251412] Fri, 13 July 2007 08:56 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
format your code so that it is readable (read the sticky)
And your post is NOT urgent
Re: Procedure Code [Ref Cursor] Help NOT Urgent [message #251419 is a reply to message #251412] Fri, 13 July 2007 09:20 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Nothing is urgent in forum.

Noone will download something that can contain a virus. Only txt and bmp files are safe.

Regards
Michel
Previous Topic: Getting all the Records from the Database
Next Topic: Database Links
Goto Forum:
  


Current Time: Thu Dec 08 20:10:53 CST 2016

Total time taken to generate the page: 0.06842 seconds