Skip to Main Content

Deutsche

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Installationsbeschreibung Oracle FMW Forms und Reports 12.2.1.3 für Windows Server 2012 R2 (Frank Ho

Juergen MengeNov 13 2017

Comments

Etbin

Just to move the post forward

And now the 11.1 implementation....


BEGIN

   intab('ETBIN.EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM ETBIN.EMP WHERE job = 'CLERK'

------------------------------------------------------------------

  Contents of ETBIN.EMP

------------------------------------------------------------------

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------------------------------------------------------------

7369 SMITH CLERK 7902 12/17/1980 800 20

7876 ADAMS CLERK 7788 01/12/1983 1100 20

7900 JAMES CLERK 7698 12/03/1981 950 30

7934 MILLER CLERK 7782 01/23/1982 1300 10

Statement processed.

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-00942: table or view does not exist


Regards


Etbin

Just to move the post forward

And now the 11.1 implementation....


BEGIN

   intab('ETBIN.EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM ETBIN.EMP WHERE job = 'CLERK'

------------------------------------------------------------------

  Contents of ETBIN.EMP

------------------------------------------------------------------

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------------------------------------------------------------------

7369 SMITH CLERK 7902 12/17/1980 800 20

7876 ADAMS CLERK 7788 01/12/1983 1100 20

7900 JAMES CLERK 7698 12/03/1981 950 30

7934 MILLER CLERK 7782 01/23/1982 1300 10

Statement processed.

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-00942: table or view does not exist


Regards


Etbin

So....from which schema did you run intab?

Etbin

So....from which schema did you run intab?

APEX.jpg

Regards

Etbin

APEX.jpg

Regards

Etbin

OK, I am puzzled. Do you understand what is going on in your database there?

Solomon Yakobson

So....from which schema did you run intab?

I am not sure what happens in Etbin's case, but few pointers about procedure:

1. RAISE no_such_table could be misleading. Procedure mistakenly assumes no_such_table if

      SELECT *

      BULK COLLECT INTO g_columns

        FROM all_tab_columns

       WHERE owner = l_owner

         AND table_name = l_table

         AND column_name LIKE NVL (colname_like_in, '%');

returns no rows. It can be simply bad colname_like_in value. For example

SQL> BEGIN
  2
  3     intab('EMP',
  4
  5            where_in          => 'job = ''CLERK''',
  6
  7            colname_like_in   => '%'
  8
  9          );
10
11  END;
12  /
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO  FROM EMP WHERE job =
'CLERK'
------------------------------------------------------------------
Contents of EMP
------------------------------------------------------------------
EMPNO ENAME      JOB       MGR  HIREDATE   SAL     COMM    DEPTNO
------------------------------------------------------------------
7369  SMITH      CLERK     7902 17-DEC-80  800             20
7876  ADAMS      CLERK     7788 23-MAY-87  1100            20
7900  JAMES      CLERK     7698 03-DEC-81  950             30
7934  MILLER     CLERK     7782 23-JAN-82  1300            10

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     intab('EMPLOYEE',
  3            where_in          => 'job = ''CLERK''',
  4            colname_like_in   => 'A%'
  5          );
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.INTAB", line 87
ORA-06512: at "SCOTT.INTAB", line 303
ORA-06512: at line 2


SQL>

2. Procedure asssumes table_in is table/view and checks all_tab_columns while table_in could be a synonym, for example. Or it could be nested table/table expression. I'd parse 'SELECT * FROM ' || table_in using DBMS_SQL.PARSE, then get all column names using DBMS_SQL.DESCRIBE3 and then apply colname_like_in to get desired columns only. This will give all the info needed to construct l_query without using ALL_TAB_COLUMNS.

SY.

Thanks, Solomon. Fine ideas (especially the switch to use DESCRIBE_COLUMNS to get the column information!) and I encourage a reader to take on the challenge if they would like to turn this into an actually useful utility. I don't think it is right now. I simply use it to demonstrate some of the basic steps involved in solving a method 4 challenge.

Solomon Yakobson

Thanks, Solomon. Fine ideas (especially the switch to use DESCRIBE_COLUMNS to get the column information!) and I encourage a reader to take on the challenge if they would like to turn this into an actually useful utility. I don't think it is right now. I simply use it to demonstrate some of the basic steps involved in solving a method 4 challenge.

Hi Steven,

Although it is nice to know DBMS_SQL supports method 4 and know how to use it, I don't see where it can be used. Method 4, IMHO, is client, not server side method.

SY.

Etbin

OK, I am puzzled. Do you understand what is going on in your database there?

It's not my database, it's my APEX Tablespace (i.e. your database in a sense ) anyway:

select owner,table_name,column_name,data_type from all_tab_columns where table_name = 'EMP'

OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPE
LUTZTESTEMPEMPNONUMBER
LUTZTESTEMPENAMEVARCHAR2
LUTZTESTEMPJOBVARCHAR2
LUTZTESTEMPMGRNUMBER
LUTZTESTEMPHIREDATEDATE
LUTZTESTEMPSALNUMBER
LUTZTESTEMPCOMMNUMBER
LUTZTESTEMPDEPTNONUMBER
ETBINEMPEMPNONUMBER
ETBINEMPENAMEVARCHAR2
ETBINEMPJOBVARCHAR2
ETBINEMPMGRNUMBER
ETBINEMPHIREDATEDATE
ETBINEMPSALNUMBER
ETBINEMPCOMMNUMBER
ETBINEMPDEPTNONUMBER

      IF l_dot_location > 0

      THEN

         l_owner := SUBSTR (table_in, 1, l_dot_location - 1);

         l_table := SUBSTR (table_in, l_dot_location + 1);

      ELSE

         l_owner := USER;

         l_table := table_in;

         raise_application_error(-20000,'owner = ' || l_owner || '; table = ' || l_table);

      END IF;

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-20000: owner = APEX_PUBLIC_USER; table = EMP

Regards

Etbin

It's not my database, it's my APEX Tablespace (i.e. your database in a sense ) anyway:

select owner,table_name,column_name,data_type from all_tab_columns where table_name = 'EMP'

OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPE
LUTZTESTEMPEMPNONUMBER
LUTZTESTEMPENAMEVARCHAR2
LUTZTESTEMPJOBVARCHAR2
LUTZTESTEMPMGRNUMBER
LUTZTESTEMPHIREDATEDATE
LUTZTESTEMPSALNUMBER
LUTZTESTEMPCOMMNUMBER
LUTZTESTEMPDEPTNONUMBER
ETBINEMPEMPNONUMBER
ETBINEMPENAMEVARCHAR2
ETBINEMPJOBVARCHAR2
ETBINEMPMGRNUMBER
ETBINEMPHIREDATEDATE
ETBINEMPSALNUMBER
ETBINEMPCOMMNUMBER
ETBINEMPDEPTNONUMBER

      IF l_dot_location > 0

      THEN

         l_owner := SUBSTR (table_in, 1, l_dot_location - 1);

         l_table := SUBSTR (table_in, l_dot_location + 1);

      ELSE

         l_owner := USER;

         l_table := table_in;

         raise_application_error(-20000,'owner = ' || l_owner || '; table = ' || l_table);

      END IF;

BEGIN

   intab('EMP',

          where_in          => 'job = ''CLERK''',

          colname_like_in   => '%'

        );

END;

ORA-20000: owner = APEX_PUBLIC_USER; table = EMP

Regards

Etbin

Ah OK, that's make sense then. As I note below, the intention of this post is not to offer a truly, broadly useful utility but a demonstrate of method 4 steps.

Hi Steven,

Although it is nice to know DBMS_SQL supports method 4 and know how to use it, I don't see where it can be used. Method 4, IMHO, is client, not server side method.

SY.

If it's one thing I've learned over the decades it's that everything ends up getting used everywhere. But you may well be right, Solomon. And backend developers everywhere will be happy to hear it! :-)

James Su

If it's one thing I've learned over the decades it's that everything ends up getting used everywhere. But you may well be right, Solomon. And backend developers everywhere will be happy to hear it! :-)

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

unknown-7404

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

James Su wrote:

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

As BluShadow said in his reply there that is a totally unnecessary 'solution' to what OP ask about.

Oracle already provides a FREE solution to OPs problem in that thread: Sql Developer.

It unloads data very nicely using SIMPLE queries. Want CSV output?

SELECT /*csv*/ empno, ename, sal from emp;

Want other formats? Sql Dev supports plenty of them:

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

Hi Steven,

You use NEXT method in your code to traverse the column collection. I think for loop is more convenient here since the collection is densely filled.

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

Sharp eye, James. Yes, since the collection was populated using BULK COLLECT, I can simply the code by using a FOR loop instead of a WHILE loop.

James Su

James Su wrote:

One use case for method 4 is a data unloader. I have something similar in this post:

Using temp table/CTE in a Stored Procedure

As BluShadow said in his reply there that is a totally unnecessary 'solution' to what OP ask about.

Oracle already provides a FREE solution to OPs problem in that thread: Sql Developer.

It unloads data very nicely using SIMPLE queries. Want CSV output?

SELECT /*csv*/ empno, ename, sal from emp;

Want other formats? Sql Dev supports plenty of them:

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/

PL/SQL solution is useful in some automated jobs. I tried sqldeveloper's save as CSV feature and it's kind of slow when the table is big. I have been dreaming of a native CTAS external table organization CSV feature. If we have that then home grown solution can retire.

Solomon Yakobson

PL/SQL solution is useful in some automated jobs. I tried sqldeveloper's save as CSV feature and it's kind of slow when the table is big. I have been dreaming of a native CTAS external table organization CSV feature. If we have that then home grown solution can retire.

James Su wrote:

PL/SQL solution is useful in some automated jobs.

What jobs? Where you don't know what table you want to create CSV from? Or you don't know layout of the table you want to CSV? How many applications fit in that category? There is a saying in my old country, something like "go somewhere I don't know where and bring me something I don't know what". This is pretty much what method 4 is all about. Yes, it is a must when developing tools like SQL*Plus or SQL*Developer where tool user can issue any query and tool has to execute it and display results. It is also a must in reporting tools. One could say it is needed when using "exotic" features like ODCI table interface where we execute SQL which selects unknown number of columns by passing a dynamic query to ODCI and using method 4 to get column info and define selected columns. But unknown number of columns SQL is used for nothing but reporting and pretty-much any reporting tool does it much better.

And one more thing about using PL/SQL method 4 for producing CSV. Why would we produce CSV on server side? We don't want mixing database and application server. Application which is processing CSV file on database server will be affecting performance of all other applications using same database (or databases residing on same database server) with non-database related workload.

SY.

James Su

James Su wrote:

PL/SQL solution is useful in some automated jobs.

What jobs? Where you don't know what table you want to create CSV from? Or you don't know layout of the table you want to CSV? How many applications fit in that category? There is a saying in my old country, something like "go somewhere I don't know where and bring me something I don't know what". This is pretty much what method 4 is all about. Yes, it is a must when developing tools like SQL*Plus or SQL*Developer where tool user can issue any query and tool has to execute it and display results. It is also a must in reporting tools. One could say it is needed when using "exotic" features like ODCI table interface where we execute SQL which selects unknown number of columns by passing a dynamic query to ODCI and using method 4 to get column info and define selected columns. But unknown number of columns SQL is used for nothing but reporting and pretty-much any reporting tool does it much better.

And one more thing about using PL/SQL method 4 for producing CSV. Why would we produce CSV on server side? We don't want mixing database and application server. Application which is processing CSV file on database server will be affecting performance of all other applications using same database (or databases residing on same database server) with non-database related workload.

SY.

What jobs? Jobs to exchange data with other applications. CSV files are extracted by a procedure then packed and shipped to other applications. Yes I know what tables to extract, I can write static code to select data then output to files. But when I have tens, hundreds of queries I don't want to repeat my code again and again. I would employ a procedure to do this labour for me. Why shouldn't CSV be produced on server side? Expdp creates dump files on server side. It saves the round trips between server and client.

1 - 16

Post Details

Added on Nov 13 2017
6 comments
669 views