| Returning Column Value from a table [message #256180] |
Fri, 03 August 2007 05:33  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
I have following table:
CREATE TABLE TEST(EMPLOYEE_ID NUMBER, sqlQuery VARCHAR2(4000));
INSERT INTO test(100, 'SELECT EMPLOYEE_NUMBER, MANAGER_NAME, SALARY FROM EMP_MASTER;');
INSERT INTO test(200, 'SELECT EMPLOYEE_NUMBER, MANAGER_NAME, DEPT_NAME FROM EMP_MASTER;');
I want to run a PL/SQL Block like the one below but it shows error: In a procedure, RETURN statement cannot contain an expression
DECLARE
strSql VARCHAR2(4000);
BEGIN
SELECT sqlQuery INTO strSql FROM TEST WHERE EMPLOYEE_ID=100;
RETURN strSql;
END;
I want the strSql variable to hold the sqlQuery Column value from Test table.
Please advice.
Thanks
Sandi
|
|
|
|
|
|
| Re: Returning Column Value from a table [message #256185 is a reply to message #256180] |
Fri, 03 August 2007 06:22  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Procedures and anonymous pl/sql blocks don't return a value.
At the point where the code you've written ends, the variable strSql does contain the sqlQuery Column value from Test table.
Tell us what you're trying to do.
|
|
|
|
| Re: Returning Column Value from a table [message #256193 is a reply to message #256185] |
Fri, 03 August 2007 06:46 |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Thanks for the response. I am using Oracle ApEx for building some reports where in you can define your sql in form of PL/SQL blocks.
I can use the below sql block and it works fine.
declare
q varchar2(4000);
begin
q:='SELECT EMPLOYEE_NUMBER, MANAGER_NAME, SALARY FROM EMP_MASTER';
return q;
end;
I wanted the query to be picked up from a table. What could be the option?
Regards,
Sandi
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
