Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Returning Column Value from a table
Returning Column Value from a table [message #256180] Fri, 03 August 2007 05:33 Go to next message
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 #256184 is a reply to message #256180] Fri, 03 August 2007 06:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And it does, so what's your question?
Re: Returning Column Value from a table [message #256185 is a reply to message #256180] Fri, 03 August 2007 06:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Returning Column Value from a table [message #256202 is a reply to message #256185] Fri, 03 August 2007 07:22 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
I hope in both situations, the returning value is VARCHAR2 only.
Am I doing any mistake?
Re: Returning Column Value from a table [message #256207 is a reply to message #256202] Fri, 03 August 2007 07:28 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
If you are using Application Express, then you might be better asking in the application Express forum (it's just a little further down) What you have posted is invalid syntax for PL/SQL
Previous Topic: Apex & Ebusiness Suite
Next Topic: how we can export and import (one schema to other schema)in Oracle Application Express
Goto Forum:
  


Current Time: Wed Dec 07 04:59:08 CST 2016

Total time taken to generate the page: 0.09140 seconds