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!
https://cologne-data.de/FMW_InstDoku_12213_v1.pdf
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.
intab('EMP',
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 existRegardsEtbin
So....from which schema did you run intab?
Regards Etbin
OK, I am puzzled. Do you understand what is going on in your database there?
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 207876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307934 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 existORA-06512: at "SCOTT.INTAB", line 87ORA-06512: at "SCOTT.INTAB", line 303ORA-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.
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.
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'
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;
ORA-20000: owner = APEX_PUBLIC_USER; table = EMP
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_TYPELUTZTESTEMPEMPNONUMBERLUTZTESTEMPENAMEVARCHAR2LUTZTESTEMPJOBVARCHAR2LUTZTESTEMPMGRNUMBERLUTZTESTEMPHIREDATEDATELUTZTESTEMPSALNUMBERLUTZTESTEMPCOMMNUMBERLUTZTESTEMPDEPTNONUMBERETBINEMPEMPNONUMBERETBINEMPENAMEVARCHAR2ETBINEMPJOBVARCHAR2ETBINEMPMGRNUMBERETBINEMPHIREDATEDATEETBINEMPSALNUMBERETBINEMPCOMMNUMBERETBINEMPDEPTNONUMBER 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 = EMPRegardsEtbin
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! :-)
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
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
James Su wrote:
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/
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 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.
James Su wrote: PL/SQL solution is useful in some automated jobs.
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.
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.