--
--  3/26/99 Richard A. DeVenezia
--
-- This stored procedure is used by SAS programs that will
-- be using the 'purtysql' macro to make legible the text which built a view.
-- The procedure populates a table View_Source_Text_for_SAS that the SAS side
-- will bring across and pass to purtysql
--
-- The SAS application must ensure table View_Source_Text_for_SAS is
-- created before this procedure is called via pass-through SQL from the SAS side.
--
-- The SAS side should always execute this code (in proc SQL after connecting)
-- before executing the Get_View_Source_Text_for_SAS procedure
--
--   DROP TABLE View_Source_Text_for_SAS ;
-- CREATE TABLE View_Source_Text_for_SAS ( text char(80) );
--

--/*-----
-- * group: Data management
-- * purpose: Oracle procedure to extract view source
-- * notes: SAS/Access to Oracle needed. <A HREF="/cgi-bin/push/purtysql-sample.sas">Sample Use</A>.
-- */

create or replace procedure Get_View_Source_Text_for_SAS
( p_viewName IN user_views.view_name%type)
IS
   v_cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   v_fdbk PLS_INTEGER;

   v_position PLS_INTEGER;
   v_piece VARCHAR2(80);
   v_piece_len PLS_INTEGER;
BEGIN
  -- temporary table, can be safely deleted at any time
  -- since the SAS side should always create this if it
  -- does not already exist

  delete from View_Source_Text_for_SAS ;

  DBMS_SQL.PARSE (v_cur
                 , 'SELECT text FROM user_views WHERE view_name = :viewName'
                 , DBMS_SQL.V7);

  DBMS_SQL.BIND_VARIABLE (v_cur , ':viewName', p_viewName);
  DBMS_SQL.DEFINE_COLUMN_LONG (v_cur,1);

  v_fdbk := DBMS_SQL.EXECUTE_AND_FETCH (v_cur);

  IF v_fdbk > 0
  THEN
      v_position := 0;

      LOOP

         DBMS_SQL.COLUMN_VALUE_LONG (
            v_cur,
            1,
            80,
            v_position,
            v_piece,
            v_piece_len);

         EXIT WHEN v_piece_len = 0;

         INSERT INTO View_Source_Text_for_SAS VALUES (v_piece);

         v_position := v_position + v_piece_len;
      END LOOP;
  END IF;

  DBMS_SQL.CLOSE_CURSOR(v_cur);

END Get_View_Source_Text_for_SAS;
/