Creating dynamic stored procedure problem

[borisch]

I have some minor problems and if you can may be
you could help me because I can not find an answer to this errors. Thanks a lot. I have a problem with a line 57 ("open l_cursor for SQLQuery;&quot and last line where I have "/". Please help.
Thanks again.

57 PLS-00103: Encountered the symbol "SQLQUERY" when expecting one of the
following: select

70 PLS-00103: Encountered the symbol "/" when expecting one of the following:
begin function package pragma procedure form external

CREATE OR REPLACE PROCEDURE SMKT.Account_Search (p_searchname in varchar2 :='1',
p_sname in varchar2 :='',
p_searchnumber in varchar2 :='1',
p_snumber in varchar2 :='')
AS

TYPE rc IS REF CURSOR;
l_cursor rc;
SQLQuery varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT;
l_rec l_template%rowtype;

BEGIN
SQLQuery := 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
--SQLQuery :='SELECT
ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECT
IVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE FROM INTL_ACCOUNT '
DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);
IF p_sname <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchname='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname ||
'%''';
END IF;
IF p_searchname='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname ||
'''';
END IF;
IF p_searchname='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname ||
'%''';
END IF;
IF p_searchname='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME = '''|| p_sname || '''';
END IF;
END IF;
IF p_snumber <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchnumber='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE '''|| p_snumber ||
'%''';
END IF;
IF p_searchnumber='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber ||
'''';
END IF;
IF p_searchnumber='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber ||
'%''';
END IF;
IF p_searchnumber='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE = '''|| p_snumber ||
'''';
END IF;
END IF;
--DBMS_OUTPUT.PUT_LINE('SQLQuery'||SQLQuery);
p(SQLQuery);

--line 57
open l_cursor for SQLQuery;

LOOP
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(SQLQuery);
END LOOP;
close l_cursor;
END;

--line 70
/

 

[vivekm]

In compiling your program, I get two errors - and they are both nothing related to what you have identified. The errors are :
Line 14: DBMS_OUTPUT.PUT_LINE('HI IF' || ACCOUNT);
ERROR: There is no identifier called ACCOUNT.

Line 54: p(SQLQuery);
ERROR: There is no procedure p declared.

After these two have been resolved, the procedure compiles without errors.

I suspect that the error is to do with the way you have executed perhaps because you cannot compile the above code without declaring the variable called ACCOUNT. Try to copy and paste your own code above into SQL Plus and let me know what error you get.

Regards,
Vivek

 

[sem]

I think the problem is in your Oracle version: afaik you may open cursor for string variable only on 8.1.6+. You're probably on 8.0-

 

[borisch]

No that is not a reason I have some minor problems and if you can may be
you could help me because I can not find an answer to this errors. Thanks a lot. I have a problem with a line 57 (&quot;open l_cursor for SQLQuery;&quot and last line where I have &quot;/&quot;. Please help.
Thanks again.

57 PLS-00103: Encountered the symbol &quot;SQLQUERY&quot; when expecting one of the
following: select

70 PLS-00103: Encountered the symbol &quot;/&quot; when expecting one of the following:
begin function package pragma procedure form external

CREATE OR REPLACE PROCEDURE SMKT.Account_Search (p_searchname in varchar2 :='1',
p_sname in varchar2 :='',
p_searchnumber in varchar2 :='1',
p_snumber in varchar2 :='')
AS

TYPE rc IS REF CURSOR;
l_cursor rc;
SQLQuery varchar2(4000) default 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
cursor l_template is SELECT ACCOUNT_CODE FROM INTL_ACCOUNT;
l_rec l_template%rowtype;

BEGIN
SQLQuery := 'SELECT ACCOUNT_CODE FROM INTL_ACCOUNT ';
--SQLQuery :='SELECT
ACCOUNT_CODE,ACCOUNT_NAME,AFFILIATE_NAME,ACCOUNT_NUMBER,EFFECTIVE_START_DT,EFFECT
IVE_END_DT,FORECAST_IND,ACTUAL_IND,REF_RLI_COUNTRY_CODE FROM INTL_ACCOUNT '
DBMS_OUTPUT.PUT_LINE('HI IF' || l_template);
IF p_sname <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchname='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE '''|| p_sname ||
'%''';
END IF;
IF p_searchname='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname ||
'''';
END IF;
IF p_searchname='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME LIKE ''%'|| p_sname ||
'%''';
END IF;
IF p_searchname='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_NAME = '''|| p_sname || '''';
END IF;
END IF;
IF p_snumber <>'' THEN
DBMS_OUTPUT.PUT_LINE('HI IF');
IF p_searchnumber='1' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE '''|| p_snumber ||
'%''';
END IF;
IF p_searchnumber='2' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber ||
'''';
END IF;
IF p_searchnumber='3' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE LIKE ''%'|| p_snumber ||
'%''';
END IF;
IF p_searchnumber='4' THEN
SQLQuery := SQLQuery || ' AND ACCOUNT_CODE = '''|| p_snumber ||
'''';
END IF;
END IF;
--DBMS_OUTPUT.PUT_LINE('SQLQuery'||SQLQuery);
--p(SQLQuery);

--line 57
open l_cursor for SQLQuery;

LOOP
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(SQLQuery);
END LOOP;
close l_cursor;
END;

--line 70
/