logo
down
shadow

Oracle Stored Procedure Return Recordset with Field Names


Oracle Stored Procedure Return Recordset with Field Names

By : user2952771
Date : November 19 2020, 03:59 PM
it helps some times The programming models used for PL/SQL and TSQL are different. Where you might return a recordset in TSQL, in PL/SQL you would return a cursor. A cursor is just a pointer to an SQL statement which is opened and can be read. It is not limited to returning a single column. Roughly, the PL/SQL equivalent of your TSQL procedure above would be something like:
code :
CREATE OR REPLACE FUNCTION GET_INPUT_LOCATION(pinInput_location IN VARCHAR2(255))
  RETURN SYS_REFCURSOR
IS
  cCursor  SYS_REFCURSOR;
BEGIN
  OPEN cCursor FOR
    SELECT FIRSTNAME,
           COMPANY
      FROM COMPANY
      WHERE LOCATION = pinInput_location;

  RETURN cCursor;
END GET_INPUT_LOCATION;
DECLARE
  cCursor       SYS_REFCURSOR;
  strFirstname  COMPANY.FIRSTNAME%TYPE;
  strCompany    COMPANY.COMPANY%TYPE;
BEGIN
  cCursor := GET_INPUT_LOCATION('SOMEWHERE OVER THE RAINBOW, INC.');

  FETCH cCursor
    INTO strFirstname,
         strCompany;

  CLOSE cCursor;
END;
CREATE OR REPLACE PROCEDURE GET_INPUT_LOCATION
  (pinInput_location IN  VARCHAR2(255),
   poutFirst_name    OUT COMPANY.FIRSTNAME%TYPE,
   poutCompany       OUT COMPANY.COMPANY%TYPE)
IS
  cCursor  SYS_REFCURSOR;
BEGIN
  SELECT FIRSTNAME,
         COMPANY
    INTO poutFirst_name,
         poutCompany
    FROM COMPANY
    WHERE LOCATION = pinInput_location;
END GET_INPUT_LOCATION;


Share : facebook icon twitter icon
What's the easiest way to return a recordset from a PostgreSQL stored procedure?

What's the easiest way to return a recordset from a PostgreSQL stored procedure?


By : RyoHoi
Date : March 29 2020, 07:55 AM
wish helps you I simply have a table that contains a list of countries and their ISO country codes. I'm wrapping the query in a stored procedure (aka function) such as: , You should be able to use output parameters, like this:
code :
CREATE OR REPLACE FUNCTION get_countries(country_code OUT text, country_name OUT text)
RETURNS setof record
AS $$ SELECT country_code, country_name FROM country_codes $$
LANGUAGE sql;
How i return only one recordset from multiple select of a stored procedure in MySQL?

How i return only one recordset from multiple select of a stored procedure in MySQL?


By : Yang Mulia
Date : March 29 2020, 07:55 AM
will help you There are 2 options. You can use a union or a join. It depends on your structure.
code :
 SELECT * FROM usuarios_centro_acopio WHERE email = pusuario AND password = ppassword
 UNION ALL
 SELECT * FROM usuarios_admin WHERE email = pusuario AND password = ppassword;
 SELECT * FROM usuarios_centro_acopio c, usuarios_admin a WHERE c.email = a.email and cpassword = a.password and email = pusuario AND password = ppassword
Run sql stored as value in a table in oracle and return recordset in SSRS report

Run sql stored as value in a table in oracle and return recordset in SSRS report


By : harish saklani
Date : March 29 2020, 07:55 AM
will help you An anonymous PL/SQL block cannot return any data to the caller. If you want to return a SYS_REFCURSOR to the calling application, you would need to create a function (or a procedure). For example
code :
CREATE OR REPLACE FUNCTION get_results
  RETURN sys_refcursor
IS
  l_sql_stmt  VARCHAR2(3000);
  l_cursor    SYS_REFCURSOR;
BEGIN
  select 'select '||FILE_ID||' FILE_ID,'||
          ltrim(sys_connect_by_path('REC_FLD_'||FIELD_NUMBER||' "'||FIELD_NAME||'"',','),',')||
         ' from RESPONSE_DETAILS where FILE_ID = ' ||FILE_ID||';'
    into l_sql_stmt
    from (select t.*,count(*) over (partition by FILE_ID) cnt from RESPONSE_METADATA t)
   where cnt=FIELD_NUMBER 
   start with FIELD_NUMBER=1 
 connect by prior FILE_ID=FILE_ID 
        and prior FIELD_NUMBER=FIELD_NUMBER-1;

  dbms_output.put_line(l_sql_stmt);
  open l_cursor for sql_stmt;
  return l_cursor;
END;
Get field names from Stored Procedure with Dapper

Get field names from Stored Procedure with Dapper


By : Sabertooth
Date : March 29 2020, 07:55 AM
Hope that helps When using the dynamic API (the Query(...) instead of Query(...), as per your example), each row also implements IDictionary. So basically:
code :
foreach(IDictionary<string,object> row in sprocResult)
{
    var colNames = row.Keys;
    //...
}
Stored Procedure test to return recordset

Stored Procedure test to return recordset


By : Zaiton
Date : March 29 2020, 07:55 AM
will be helpful for those in need You need to make your SELECT test assign the value to your variable, like this:
code :
SELECT @test = [SERIAL_NUMBER] FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_ORDER_OPTICS] 
WHERE ([RACK] = @leftStack AND [TRAY] = @leftTray) 
    OR ([RACK] = @midStack AND [TRAY] = @midTray) 
    OR ([RACK] = @rightStack AND [TRAY] = @rightTray)
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org