3 Replies Latest reply: Nov 20, 2012 3:55 AM by Billy~Verreynne RSS

    plsql procedure with sql query data

    949210
      plsql newbie(learning sql): please excuse for asking a basic plsql question
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod on windows server 2003
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      :
      CREATE OR REPLACE PROCEDURE p_check
      AS
      v1 NUMBER(20,0);
      BEGIN
      SELECT count(*) INTO v1
      FROM ad_reg;
      dbms_output.put_line(v1);
      end;
      inserts the number of rows in the table ad_reg into v1
      and displays v1 by using dbms_output
      i want my procedure to return table data
      CREATE TABLE "TEST_SH"
      (
      "TEXT_ID" NUMBER,
      "TEXT" CLOB
      );

      Insert into TEST_SH (TEXT_ID) values (1);
      Insert into TEST_SH (TEXT_ID) values (2);
      Insert into TEST_SH (TEXT_ID) values (3);
      Insert into TEST_SH (TEXT_ID) values (4);

      there will be a way with which i can see all the data(select * from test_sh) as the output of the plsql procedure
      1.please tel me what to google
      2.please guide me with a site or tell what is actually used and help me
      3.or post an example here
      thank you much

      Edited by: 946207 on Nov 20, 2012 2:05 PM
        • 1. Re: plsql procedure with sql query data
          jeneesh
          search for CUSOR IN PL/SQL and learn

          Then search for COLLECTIONS IN PL/SQL and learn....
          • 2. Re: plsql procedure with sql query data
            949210
            thanks for your response

            Edited by: 946207 on Nov 20, 2012 2:11 PM
            • 3. Re: plsql procedure with sql query data
              Billy~Verreynne
              946207 wrote:

              there will be a way with which i can see all the data(select * from test_sh) as the output of the plsql procedure
              PL/SQL is
              a) nothing at all like T-SQL
              b) two different languages, PL (Programming Logic) and SQL

              SQL is used to access data in the Oracle database. So select * from test_sh is the correct statement to access the data.

              The client can make execute this SQL statement itself. And view the data. However, this requires the client to understand the SQL language, and the database model. Which is typically not that you want. You want to abstract that from the client.

              Why would you want the client to deal with the complexities of the SQL language? Or deal with the complexities of your database model in Oracle?

              So instead of the client doing SQL, PL/SQL can do it on behalf of the client. The client uses PL/SQL as an API - in a similar fashion as the client would use any other API.

              The PL/SQL code determines the SQL statement - and returns a SQL cursor handle to the client. The client then simply uses the SQL cursor that the PL/SQL code created for it (and the client needs to close the SQL cursor when done, so as to not cause server resource leakage).

              E.g. (using PL/SQL as an API to abstract SQL and the data model)
              create or replace procedure TestProc( c out sys_refcursor ) is
              begin
                open c for select * from test_sh order by 1;
              end;
              The client then calls TestProc and gets a reference cursor handle. E.g. (the client call to Oracle):
              begin
                TestProc( c => :1 );
              end;
              The client needs to supply a host (client language) bind variable of type cursor, as value for bind variable +:1+.

              This is the correct way to deal with PL/SQL and cursors from a client GUI/language perspective. Any other method, like using PL/SQL to cache SQL data as a collection, is usually very wrong.